Formulas and cell references | Excel and Google Sheets formulas

Start with =, use ranges, and understand relative and absolute references.

Every formula starts with =. It can use numbers, operators and cell references such as B2. Columns are letters and rows are numbers.

=B2*C2            multiply two cells
=SUM(B2:B10)      add a range
=AVERAGE(B2:B10)  average
=MIN(B2:B10)  =MAX(B2:B10)
=COUNT(B2:B10)    how many numbers
=COUNTA(A2:A10)   how many non-empty cells

Relative and absolute references

When you copy a formula down, references shift: =B2*C2 becomes =B3*C3. That is a relative reference. A dollar sign locks the part after it, making it absolute.

Reference	Copied one row down and one column right
`B2`	`C3`
`$B2`	`$B3` (column locked)
`B$2`	`C$2` (row locked)
`$B$2`	`$B$2` (fully locked)

Typical use: a tax rate in E1. Write =B2*$E$1 and copy it down, so every row multiplies by the same cell. Press F4 to cycle through the lock types.

NotePercentages are numbers: 15% is 0.15. Format a cell as percent instead of typing 15 and dividing by hand.

Try it

Percentage calculator to check results

Test yourself

Answer all the questions, then check them. Finish with every answer right to mark the lesson as done.

1. Write a formula that adds the cells from B2 to B10.

Hint

SUM with a range.

2. What does $B$2 mean when you copy the formula? It moves with the copy It is a text value It always points to B2 It is an error

3. Which key cycles through relative and absolute references? F2 F12 F5 F4

4. Count the non-empty cells from A2 to A10.

Hint

COUNTA, with an A at the end.

Key terms

Cell reference