About this lesson
See absolute and relative cell referencing in practice, and learn about ways to copy and paste formulas.
There are no related exercise files for this lesson.
Cell Referencing - Example
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:
- A1: Both the column and row will change.
- $A1: The column will always point to A, but the row will change.
- A$1: The column will change, but the row will always point to 1.
- $A$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 Paste Special)
- 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
Locking cell references - keyboard shortcut
To lock cell references using a keyboard shortcut, you must be editing the formula and have a cell reference selected. Then, press F4 (or ⌘-T on a Mac) to cycle through each kind of cell reference: both column and row locked, only row locked, only column locked, neither locked.Login to download
Lesson notes are only available for subscribers.