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.
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.
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
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:
- brackets
( ); - the sign minus (
-5) and percent%(=20%→ 0.2); - power
^(=2^5→ 32); - multiplication
*and division/— left to right; - addition
+and subtraction-— left to right; - 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
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.
| Error | Cause | Example |
|---|---|---|
| #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.
| Address | Type | What changes when copied |
|---|---|---|
| B3 | relative | both the column and the row |
| $B$3 | absolute | nothing |
| $B3 | mixed | only the row (the column is locked) |
| B$3 | mixed | only the column (the row is locked) |
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.
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).
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.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.
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
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.| A | B | C | D | |
|---|---|---|---|---|
| 1 | 4 | 3 | 2 | |
| 2 | 5 | 1 | 6 | =A$1*$B2+$C$3-1 |
| 3 | 2 | 7 | 3 |
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
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
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
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
$.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
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:D5and 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.