Spreadsheets: cells, formulas and copied references
Read a cell, then read the formula
A spreadsheet organises data in cells. In the usual A1 reference style in Microsoft Excel, letters identify columns and numbers identify rows: C4 means column C, row 4. A range such as A2:A4 includes A2, A3 and A4.
One small worksheet
For this example, the cells contain actual numbers, not text, blanks or errors.
| Cell | Number |
|---|---|
| A2 | 10 |
| A3 | 0 |
| A4 | 20 |
| E1 | 2 |
A formula begins with =. It can use cell references so that the calculation refers to stored values.
| Formula | Result | Why |
|---|---|---|
=SUM(A2:A4) | 30 | 10 + 0 + 20 |
=AVERAGE(A2:A4) | 10 | (10 + 0 + 20) ÷ 3 |
SUM adds the numbers. AVERAGE gives their arithmetic mean. A numeric zero is included in the average; it is not a blank cell. For these three numeric cells, divide by 3. Do not divide the sum by 2 just because one value is zero.
Copy down: which reference moves?
Suppose B2 contains =A2*2. When you copy the formula one row down to B3, it becomes =A3*2. The relative reference follows the one-row change in position.
Now keep the reference to the multiplier in E1 fixed. In B2, use =A2*$E$1. The dollar signs make both the column and row of E1 absolute for copying. They fix the address during this copy; E1's value can still be edited.
- Read B2:
=A2*$E$1uses the number in A2 and the multiplier at the fixed address E1. - Copy the formula from B2 to B3: the destination is one row below, in the same column.
- Adjust the relative part: A2 becomes A3. Keep the absolute part:
$E$1stays$E$1. - Read the new formula:
=A3*$E$1. With the given numbers it returns 0 × 2 = 0; B2 returns 10 × 2 = 20.
This example concerns copying, not moving the formula or inserting worksheet rows. “Absolute” describes how the reference behaves during this copy; it does not mean that the referenced cell can never change under any operation.
Five practice checks
- In Excel's A1 reference style, what does C4 identify?
Show answer
Correct answer: B
In an A1-style reference, the letter gives the column and the number gives the row. C4 is one cell at column C, row 4.
Option A: This reverses the column letter and row number.
Option C: C4 names one cell, not the range C1:C4.
Option D: C4 does not identify a worksheet number.
Hint: Read the letter first as the column, then the number as the row.
- A2, A3 and A4 contain the numbers 10, 0 and 20. What does
=SUM(A2:A4)return?
Show answer
Correct answer: D
The range includes all three cells. SUM adds their numbers: 10 + 0 + 20 = 30.
Option A: 10 is the arithmetic mean here, not the sum.
Option B: 20 is only the value in A4; the range also includes A2 and A3.
Option C: 3 is the number of cells in the range, not their total.
Hint: Include both ends of A2:A4 and the cell between them.
- With those same three numeric cells, what does
=AVERAGE(A2:A4)return?
Show answer
Correct answer: A
AVERAGE includes the numeric zero, so there are three numbers. Their arithmetic mean is (10 + 0 + 20) ÷ 3 = 10.
Option B: 15 comes from wrongly excluding the numeric zero and dividing 30 by 2.
Option C: 30 is the sum; the average also requires division by the number of numeric values.
Option D: A zero in one cell does not make the average of all three numbers zero.
Hint: Zero counts as a number. How many numeric values are supplied?
- B2 contains
=A2*2. You copy it one row down to B3. What is the copied formula?
Show answer
Correct answer: C
Copying one row down changes the relative row reference from A2 to A3. The column A stays the same, and the literal number 2 does not change.
Option A: This leaves the relative row reference unchanged despite copying down one row.
Option B: The destination moved down, not right; the source reference should not change to column B.
Option D: Copying adjusts relative cell references, not the literal multiplier 2.
Hint: Separate the cell reference A2 from the fixed number written directly in the formula.
- B2 contains
=A2*$E$1. You copy it one row down to B3. Which formula results?
Show answer
Correct answer: B
A2 is relative and becomes A3. Both parts of $E$1 are absolute, so it stays $E$1 during the copy. The result is =A3*$E$1.
Option A: The relative A2 should move down to A3.
Option C: The dollar sign before 1 fixes that row during copying; $E$1 does not become $E$2.
Option D: There is no one-column move, and the absolute E1 reference should not change to F1.
Hint: Which reference has dollar signs before both its column and row?
Check before accepting a result
Read the range, count the numeric inputs, and predict the copied references before calculating. A formula can look familiar and still refer to the wrong cells.
These examples use English function names and A1-style references. Sources: Microsoft guidance on formulas and ranges, SUM, AVERAGE, and relative and absolute references.
Syllabus connection: Office basics appear in section 13.11.5.2 of the SSC CGL 2026 notice, under Tier II Computer Knowledge. This lesson covers selected spreadsheet tasks.
Sign in to keep your progress. Sign in