About this lesson
Learn about working with absolute and relative cell referencing, and techniques for copying formulas.
Multiple versions of this lesson are available, choose the appropriate version for you:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Cell Referencing.xlsx
22.6 KB Cell Referencing - Completed.xlsx
23.9 KB Lean More About Cell Referencing
Working with absolute and relative cell referencing, and techniques for copying formulas.
When to use
Use to ensure that formulas are targeted at the intended cells when you copy them from one location to another.
Relative vs absolute referencing guide
When you copy the formula and your original cell is shown as:
Both the column and row will change.
The column will always point to A, but the row will change.
The column will change, but the row will always point to 1.
Both the column and row will remain pointed to A1.
Key points to remember: Ranges are not loyal. You need to pay them (using $ signs) to stay put!
- Right click and choose copy. Select the destination, right click and choose Paste (or PasteSpecial).
- Press CTRL + C to copy, and CTRL + V to paste.
- Use the fill handle to extend formulas across a range.
- Select the range first, enter your formula, then press CTRL + Enter to commit it to multiple cells at once.
Hints & tips
You can toggle the $ signs on a reference when you are creating a formula by pressing F4 when your cursor is next to (or within) a cell reference:
- Pressing F4 once will toggle to absolute columns and absolute rows
- Pressing F4 again will toggle to relative columns and absolute rows
- Pressing F4 again will toggle to absolute columns and relative rows
- Pressing F4 again will toggle to relative columns and relative rows
- Pressing F4 again will restart the process
Lesson notes are only available for subscribers.