Computer ApplicationsICSE Class 8 · Spreadsheet: Functions and Charts

Formulas, Ranges and Cell References in a Spreadsheet: ICSE Class 8

Updated 17 October 2026 · Fact-checked

A spreadsheet formula starts with an equals sign and uses cell references so results update. A range such as B2:B6 is a block of adjacent cells. When a formula is copied a relative reference like B2 shifts, an absolute reference (dollar signs before the column and the row) never changes, and a mixed reference locks only the column or only the row.

Understand Formulas, Ranges and Relative, Absolute and Mixed References

Formulas. Always begin with =. Use cell references such as =B2+C2 so the result updates when data changes. Operators: + - * / and ^ (power). Order: brackets, powers, then * and / left to right, then + and -. Examples: =10+4*3-6/2 gives 19; =(10+4)*3 gives 42; =20-8/4*2 gives 16.

Errors. #DIV/0! (division by zero or blank), #NAME? (misspelt function), ##### (column too narrow).

Range. A group of adjacent cells, written first:last. A1:C4 is 3 columns x 4 rows = 12 cells; B2:D6 is 3 x 5 = 15 cells. Select by dragging or Shift-click; Ctrl selects non-adjacent ranges. Name a range by typing in the Name Box (then =SUM(Marks) works). A range name starts with a letter or underscore, has no spaces and does not look like a cell address.

References when copied.

Type	Written	When copied
Relative	B2	changes with position
Absolute	`$B$2`	never changes
Mixed	`$B2` or `B$2`	the part with $ stays fixed

F4 cycles B2, $B$2, B$2, $B2.

Worked copying. =B2+C2 in D2 copied to D3 becomes =B3+C3. =B2*$E$1 in C2 copied to C3 becomes =B3*$E$1. A formula =A1 in D5 copied to F8 (2 columns right, 3 rows down) becomes =C4. =$B2 copied 2 rows down and 1 column right becomes =$B4; =B$2 becomes =C$2.

Typical mistake. Copying =B2*E1 down gives =B3*E2, =B4*E3, which point at empty cells and give 0. Fix: =B2*$E$1.

Sheet tabs. Insert with +; rename by double-clicking the tab; delete by right-click (usually cannot be undone). In Excel a sheet name has up to 31 characters, cannot be blank, cannot contain : \ / ? * [ ] and cannot repeat in a workbook.

Formulas, Ranges and Relative, Absolute and Mixed References: key facts

Reference types: Relative no dollar signs
Absolute dollar before column and row
Mixed dollar before only the column or only the row; A dollar sign locks the letter or number after it.
Order of operations: Brackets
Powers
Multiply and divide
Add and subtract; Left to right within the same level.
Range size: Columns x rows = cells | A1:C4: 3 x 4 = 12; Count columns and rows inclusive.

Relative	no dollar signs
Absolute	dollar before column and row
Mixed	dollar before only the column or only the row

How to answer Formulas, Ranges and Relative, Absolute and Mixed References questions

Use this for formula and reference questions.

1Start every formula with =.
2Apply brackets, powers, then multiply and divide, then add and subtract.
3To find a copied formula count how many columns and rows it moves.
4Shift only the unlocked part of each reference by that amount.
5Check that fixed values such as a rate use an absolute reference.

Formulas, Ranges and Relative, Absolute and Mixed References in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

A hypothetical price list with GST

Suppose prices are in B2:B4 and a tax rate of 5 percent (0.05) is in E1, and the price after tax is wanted in C2:C4.

Write the formula for C2 that can be copied down, and the value for a price of 200.

Try it first, then show the solutionSolution

Price after tax = B2 + B2 x E1.
The rate must not move: =B2+B2*$E$1.
For 200: 200 + 200 x 0.05 = 210.

Answer: Write =B2+B2*$E$1 in C2 and copy down; for 200 it gives 210.

Memory hook: Lock the rate with dollars.

Tricks to remember Formulas, Ranges and Relative, Absolute and Mixed References

Short memory aids. Say each one aloud once, then use it on a question the same day.

Dollar = lock
The $ locks what follows it.
Use it when: Absolute and mixed references.
F4 cycle
F4: B2, $B$2, B$2, $B2.
Use it when: Changing reference types.

Formulas, Ranges and Relative, Absolute and Mixed References: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Which starts a formula? · 1 mark · MCQBoard level

A formula in a spreadsheet must begin with:
(a) a colon (b) an equals sign (c) a dollar sign (d) a bracket

Try it first, then show the solutionSolution

Without = the entry is text.

Answer: (b) an equals sign.

Example 2: Order of operations · 2 marks · Short answerBoard level

Find the value of =10+4*3-6/2 and =(10+4)*3. (2 marks)

Try it first, then show the solutionSolution

4 x 3 = 12 and 6 / 2 = 3, so 10 + 12 - 3 = 19 (1).
(10 + 4) x 3 = 14 x 3 = 42 (1).

Answer: 19 and 42.

Example 3: Copying a mixed reference · 3 marks · Short answerHarder board level

The formula =$B2 is in D2 and is copied to F4. What is the new formula? (3 marks)

Try it first, then show the solutionSolution

F4 is 2 columns right and 2 rows down (1).
Column B is locked and stays B (1).
The row moves from 2 to 4, giving =$B4 (1).

Answer: =$B4.

Example 4: The empty-cell problem · 4 marks · Long answerHarder board level

A student writes =B2*E1 in C2 to apply the rate in E1 and copies it down to C4. Write the formulas in C3 and C4, explain the problem and give the correction. (4 marks)

Try it first, then show the solutionSolution

C3 becomes =B3*E2 (1).
C4 becomes =B4*E3 (1).
E2 and E3 are empty, so the answers are 0 (1).
Use =B2*$E$1 (1).

Answer: C3 is =B3*E2 and C4 is =B4*E3. The rate moves down to empty cells so the results are 0. Write =B2*$E$1 in C2 and copy down.

Example 5: Multiplication grid · 4 marks · Long answerHOTS / competency

A multiplication grid has numbers 1 to 5 in B1:F1 and in A2:A6. Give one formula for B2 that gives the whole grid when copied across and down, and say what it shows in D4. (4 marks)

Try it first, then show the solutionSolution

Row numbers are in column A (locked column), so use $A2 (1).
Column numbers are in row 1 (locked row), so use B$1 (1).
Formula =$A2*B$1 (1).
D4 holds =$A4*D$1 = 3 x 3 = 9 (1).

Answer: =$A2*B$1. In D4 it becomes =$A4*D$1, which is 3 x 3 = 9 (the third row number times the third column number).

Where students lose marks in Formulas, Ranges and Relative, Absolute and Mixed References

The slips examiners see most often, and what to write instead.

Mistake: Forgetting the equals sign
Without = the entry is text.
Wrong working
B2+C2
Right working
=B2+C2
Do this: Start with =.
Usually costs: 1 mark
Mistake: Using a relative reference for a fixed rate
The reference shifts when copied.
Wrong working
=B2*E1
Right working
=B2*$E$1
Do this: Lock constants with dollars.
Usually costs: 1 to 2 marks
Mistake: Ignoring BODMAS in spreadsheets
Multiplication comes before addition.
Wrong working
=10+4*3 gives 42.
Right working
=10+4*3 gives 22.
Do this: Use brackets to change the order.
Usually costs: 1 mark

Exam tips

Show the copied formula for each cell asked for.
Count columns and rows moved before writing the new reference.
Use brackets when the order must change.
State the type of reference in your answer.

Practice questions on Formulas, Ranges and Relative, Absolute and Mixed References

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
How many cells are in the range A1:C4?
(a) 7 (b) 10 (c) 12 (d) 16
Hint
Columns times rows.
Answer
(c) 12.
Q2 · 2 marks · Short answerBoard level
Find =20-8/4*2 and =2+3^2. (2 marks)
Hint
Powers first, then division and multiplication.
Answer
8 / 4 = 2, 2 x 2 = 4, 20 - 4 = 16. 3^2 = 9, 2 + 9 = 11.
Q3 · 3 marks · Short answerHarder board level
The formula =A1 is in D5 and is copied to F8. Write the new formula and explain. (3 marks)
Hint
Count columns right and rows down.
Answer
F8 is 2 columns right and 3 rows down, so A becomes C and 1 becomes 4, giving =C4.
Q4 · 3 marks · Short answerHarder board level
A formula =$C5 is copied from D7 to F9. Write the new formula and state what is locked. (3 marks)
Hint
Column is locked.
Answer
F9 is 2 columns right and 2 rows down from D7. The column is locked, so it stays C, and the row moves from 5 to 7. The formula is =$C7.
Q5 · 4 marks · Long answerHOTS / competency
A teacher names the range B2:B6 as Marks. Explain two benefits and give two rules for a valid range name. (4 marks)
Hint
Readability and rules.
Answer
The formula =SUM(Marks) is easier to read and understand than =SUM(B2:B6), and it is quicker to type. A valid name starts with a letter or underscore, has no spaces (use Total_Marks), and must not look like a cell address such as A1.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.What must a formula begin with?Show answer
An equals sign.
2.What is a range?Show answer
A group of adjacent cells, written first cell:last cell.
3.What does $B$2 mean?Show answer
An absolute reference that never changes when copied.
4.What does F4 do?Show answer
Cycles through reference types.
5.What does #DIV/0! mean?Show answer
Division by zero or a blank cell.
6.What does ##### mean?Show answer
The column is too narrow.

Formulas, Ranges and Relative, Absolute and Mixed References: frequently asked questions

Can a range name have a space?

No; use an underscore such as Total_Marks.

Can I undo deleting a sheet?

Usually not, so check first.

Is the order of operations the same as BODMAS?

Yes, brackets first, then powers, then multiply and divide, then add and subtract.