Spreadsheets: addresses, ranges and formulas

Cells, rows, columns and worksheets; counting the cells of a range; order of operations and error values; relative, absolute and mixed addresses and how they change when a formula is copied — with DİM’s “D2 → E3” tasks.

  • Grades 8 and 9
  • 24 min
  • 12 questions
  • Lesson 7 of 59

In this lesson you will learn

  • Count the columns, rows and cells of a range and write a range of a given number of cells
  • Calculate a formula’s value using the order of operations and recognise error values
  • Find what a formula with relative, absolute and mixed addresses becomes when copied in any direction
  • Place the $ sign so that a formula still works after copying

Each of the four DİM entrance exams of 2025–2026 had two spreadsheet tasks: one on a chart and one on addresses and formulas. The second usually reads: “The formula in cell D2 is copied to cell E3. Which formula appears there and what is its value?” To answer, you need two things: how addresses change when a formula is copied, and in what order a formula is calculated. In the lesson “Word processors: editing, formatting and keyboard operations” you worked with text documents; now we move to a program that works with numbers — the spreadsheet.

The parts of a spreadsheet

A spreadsheet program calculates, stores data and draws charts from it: Microsoft Excel, LibreOffice Calc, Google Sheets. It is built like this:

  • Workbook — an Excel file (.xlsx); it holds one or more worksheets.
  • Worksheet — a large grid of rows and columns; the sheet names are at the bottom of the window (Sheet1, Sheet2).
  • A column is named by letters: A, B, …, Z, AA, AB, …, XFD — 16,384 columns. A row has a number: 1, 2, …, 1,048,576.
  • The active cell is the cell selected now (with a thick border). Its address is shown in the Name Box, its content in the formula bar.

Cell addressDefinition

The intersection of a column and a row is a cell, and its name is its address: first the column letter, then the row number — C5, AB12 (never 5C). Excel’s help calls it a “reference”; DİM uses the Azerbaijani word ünvan (address).

A cell can hold three kinds of data: a number (aligned right by default), text (aligned left) and a formula, which always starts with =. The cell shows the result, and the formula bar shows the formula itself. As soon as one of the cells it refers to changes, the formula is recalculated — this is the main power of a spreadsheet.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

Ranges and how many cells they contain

RangeDefinition

A rectangular block of neighbouring cells. It is written as the addresses of the top-left and bottom-right cells joined by a colon: B2:D5 means all cells from B2 to D5. Part of one column (C3:C12) or one row (B6:I6) is also a range.

N = (S₂ − S₁ + 1) · (R₂ − R₁ + 1)

Where

N
number of cells in the range
S₁, S₂
position of the first and last column (A = 1, B = 2, …)
R₁, R₂
numbers of the first and last row

Number of columns × number of rows. Do not forget the “+1”: from B to F there are 5 columns, not 4.

Example 1. The size of a range

Problem

1) How many cells are in C3:G7?
2) How many columns and rows does B2:D9 have?
3) How many cells are in H3:K20?
4) In column E, 12 consecutive cells are selected starting from E4. Write the range.
5) In row 6, 8 cells are selected to the right starting from B6. Write the range.

Show solutionHide solution

Solution

1) Columns C, D, E, F, G — 5; rows 3–7 — 5. N = 5 · 5 = 25.
2) Columns B, C, D — 3; rows: 9 − 2 + 1 = 8.
3) H = 8, K = 11: 11 − 8 + 1 = 4 columns; 20 − 3 + 1 = 18 rows; N = 4 · 18 = 72.
4) Last row: 4 + 12 − 1 = 15 → E4:E15 (E4:E16 would have 13 cells).
5) B = 2, last column: 2 + 8 − 1 = 9 = I → B6:I6.

Formulas: order of operations and error values

A formula starts with = and may contain numbers, addresses, operators and functions: =B2*C2, =(A1+A2)/2, =SUM(B2:B9). An empty cell counts as 0 in a calculation. Operations are done as in mathematics, but by Excel’s own order:

  1. brackets ( );
  2. the sign minus (-5) and percent % (=20% → 0.2);
  3. power ^ (=2^5 → 32);
  4. multiplication * and division / — left to right;
  5. addition + and subtraction - — left to right;
  6. joining text & and comparisons = <> < > <= >= (the result is TRUE or FALSE).

Example 2. The value of a formula

Problem

A1 = 4, B1 = 2, C1 = 5. Find the values of the formulas:
1) =A1+B1*C1
2) =(A1+B1)*C1
3) =A1^B1/2
4) =A1/B1^2
5) =C1-B1^2

Show solutionHide solution

Solution

1) Multiplication first: 2 · 5 = 10, then 4 + 10 = 14.
2) Brackets first: 4 + 2 = 6, then 6 · 5 = 30.
3) Power first: 4² = 16, then 16 / 2 = 8.
4) Power first: 2² = 4, then 4 / 4 = 1.
5) Power first: 2² = 4, then 5 − 4 = 1.
ErrorCauseExample
#DIV/0!division by zero or by an empty cell=A1/B1, B1 is empty
#NAME?an unknown function name=SUMM(A1:A3)
#VALUE!arithmetic with text=A1+B1, B1 holds abc
#REF!a reference to a cell that does not exist=A1*2 copied from B2 to A1
#####the number does not fit the column width (not an error)just widen the column

Relative, absolute and mixed addresses

When you copy a formula to another cell (Ctrl+C → Ctrl+V, by dragging the fill handle, or with Ctrl+D / Ctrl+R), Excel does not repeat it letter for letter. It remembers an address as a position: “two columns to the left and one row up from the formula’s cell”. Wherever the formula goes, this relative position stays the same, so the address changes. That is why one formula can calculate a whole column. Sometimes, however, an address must not change — for example a price, an exchange rate or a coefficient shared by all rows. That is what the $ sign is for.

AddressTypeWhat changes when copied
B3relativeboth the column and the row
$B$3absolutenothing
$B3mixedonly the row (the column is locked)
B$3mixedonly the column (the row is locked)
Column′ = Column + ΔcRow′ = Row + Δr

Where

Δc
how many columns the formula moves right (+) or left (−)
Δr
how many rows it moves down (+) or up (−)

Apply the rule to each part of an address separately. A part with $ in front of it does not change. When you cut and paste (Ctrl+X → Ctrl+V), the addresses in the formula do not change at all.

  1. Find the shift

    Compare the source and target cells: for D2 → E3, Δc = +1 (D → E) and Δr = +1 (2 → 3). A shift can also be negative (left, up).

  2. Rewrite the formula address by address

    Shift the part of each address that has no $, keep the part with $ as it is. Numbers, operators and function names do not change.

  3. Look up the new addresses in the fragment

    A cell that is empty or outside the fragment counts as 0; a cell with text gives #VALUE! in arithmetic.

  4. Calculate in the order of operations

    Brackets, power, multiplication and division, addition and subtraction. Then check both the formula and the value in the options — DİM’s wrong options usually get one of the two wrong.

Example 3. Copying a formula in different directions

Problem

1) The formula =A1+$A$1+$A1+A$1 in C3 is copied to E7. What do you get?
2) The formula =$A4+B$2*C3 in C4 is copied to F7 and to B3. Which formulas appear?
3) The formula =A1*2 in B2 is copied to A1. What happens?
4) The formula in C4 is cut (Ctrl+X) and pasted into F7. How does it change?

Show solutionHide solution

Solution

1) C3 → E7: Δc = +2 (C → E), Δr = +4 (3 → 7).
A1 → C5; $A$1 stays; $A1 → $A5 (row only); A$1 → C$1 (column only).
Answer: =C5+$A$1+$A5+C$1.
2) C4 → F7: Δc = +3, Δr = +3: $A4 → $A7, B$2 → E$2, C3 → F6 → =$A7+E$2*F6.
C4 → B3: Δc = −1, Δr = −1: =$A3+A$2*B2.
3) B2 → A1: Δc = −1, Δr = −1. There is no column to the left of A → =#REF!*2, and the cell shows #REF!.
4) Cut and paste does not change addresses: F7 holds =$A4+B$2*C3 again.
ABCD
1432
2516=A$1*$B2+$C$3-1
3273
The spreadsheet fragment for Example 4 (D2 holds a formula).

Example 4. D2 → E3: formula and value (DİM type)

Problem

In the fragment above, the formula in D2 is copied to E3. Which formula appears in E3 and what is its value?
A) =B$1*$B3+$C$3-1; 23
B) =A$1*$B3+$C$3-1; 30
C) =B$2*$C3+$C$3-1; 5
D) =B$1*$B3+$D$4-1; 20
E) =B$1*$B3+$C$3-1; 22

Show solutionHide solution

Solution

D2 → E3: one column right (Δc = +1) and one row down (Δr = +1).
A$1: the row is locked, the column moves → B$1.
$B2: the column is locked, the row moves → $B3.
$C$3: unchanged.
E3: =B$1*$B3+$C$3-1 = 3 · 7 + 3 − 1 = 23.
Answer: A. B does not shift the column, C mixes up the row and the column, D changes the absolute address, and E has the right formula but a wrong value.

Interactive

Loading simulation…
A mini spreadsheet: addresses change on copying just as in Excel.

Example 5. A formula that works when copied to the right (DİM type)

Problem

We need the values of y = k · x² + b. B1 holds k = 3 and B2 holds b = −2 (A1 and A2 contain the letters k and b); the values of x are in B4:F4 (1, 2, 3, 4, 5). Which formula must be entered in B5 so that copying it to C5:F5 gives all values of y correctly?
A) =B1*B4^2+B2
B) =$B1*B4^2+$B2
C) =B$1*B4^2+B$2
D) =$B1*$B4^2+$B2
E) =$A$1*B4^2+$A$2

Show solutionHide solution

Solution

Copying to the right changes only the column letters; the row numbers stay the same.
k and b must always come from column B → lock the column: $B1, $B2.
x must come from its own cell in each column → B4 stays relative.
B) =$B1*B4^2+$B2: in C5 it becomes =$B1*C4^2+$B2 = 3 · 4 − 2 = 10, in F5 3 · 25 − 2 = 73.
In A and C, k and b move to the empty cells C1, C2… (result 0); in D, x is always B4 (1 everywhere); in E, A1 and A2 contain text (#VALUE!).
Answer: B. Note: $B$1 and $B$2 would also work, but that option is not offered.

Interactive

Loading simulation…
Mixed addresses: when copying to the right, lock the column with $.

Example 6. True statements (DİM type)

Problem

Choose the true statements about spreadsheets.
1. The range C2:F4 contains 12 cells.
2. The range A1:A10 consists of 10 columns.
3. $B2 is a mixed address.
4. In $B$3 only the column stays fixed when copied.
5. The formula =SUM(B1:B4) adds the values of 4 cells.
A) 1, 3, 5 B) 2, 4 C) 1, 4, 5 D) 3, 5 E) 1, 2, 3

Show solutionHide solution

Solution

1 — true: 4 columns (C–F) · 3 rows (2–4) = 12.
2 — false: 1 column and 10 rows.
3 — true: the column is locked, the row changes.
4 — false: $B$3 is absolute; neither the column nor the row changes.
5 — true: B1, B2, B3, B4.
Answer: A) 1, 3, 5.

Professional use of addresses in Excel (named cells, references to other sheets) is in the Excel course lesson “Relative and absolute references ($A$1)”. Next lesson: “Spreadsheet functions” — SUM, AVERAGE, COUNT and the other functions of the programme.

Remember

  • Address = column letter + row number (C5); a range is written B2:D5 and has (columns) · (rows) cells — remember the “+1” in both.
  • A formula starts with =; order: brackets → sign minus and % → ^ → * / → + −; an empty cell counts as 0.
  • When copying, relative parts shift by Δc columns and Δr rows, parts locked with $ stay; cut and paste changes no addresses.
  • $B$3 is absolute, $B3 is mixed with the column locked, B$3 is mixed with the row locked; copying down, the row lock matters; copying right, the column lock matters.
  • Split a diagonal copy (D2 → E3) into two steps — one column right, one row down — then compute the new formula’s value from the fragment.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. How many cells are in the range C4:F8?