Knowing how to use relative and absolute cell references will boost your efficiency in Microsoft Excel. Creating a formula then copying it to multiple cells is a big time-saver when you’re building a worksheet – and you’re probably using relative cell references already.
Before I describe what a relative cell reference is, let’s be clear about what a cell reference is. Any time you use a cell address in a formula, you’re referencing that cell. In the expression =B4*C4, B4 and C4 are both cell references.
Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.
Relative Cell References
A relative cell reference will change (or update) if you copy or move the formula. Relative cell references are the default in Microsoft Excel.
Let’s look at an example.
In this screenshot, I selected cell D4 and entered the formula =B4*C4. The result is $3.75.
If I copy the formula to the other cells in column D, each row will display a valid total. This works because Excel uses relative cell references by default.
The formula in cell D5 would say =B5*C5. The formula in D6 would say =B6*C6. The relative cell references within the formula update relative to the current position.
If I move the row about pencils (A4:D4) down to row 12 then double-click the formula in cell D12, it would say =B12*C12.
A relative cell reference will update if you copy or move the formula.
Absolute Cell References
An absolute cell reference will not change if you copy or move the formula.
Let’s look at another example.
This time, I clicked in cell D4 and typed =(B4*C4)*$E$1. The $E$1 cell reference is absolute. It won’t change if I copy or move the formula.
The B4 and C4 cell references are still relative. They will change but $E$1 cell reference won’t.
What Does the Dollar Sign Mean?
The dollar sign indicates that the column letter and / or row number should be constant. $E$1 says that both column E and row 1 are absolute and should not change.
The following tables describes the various combinations of relative and absolute cell addresses.
| Syntax | Type | Explanation |
|---|---|---|
| E1 | Relative | Column and row will change |
| $E$1 | Absolute | Column and row won’t change |
| E$1 | Mixed | Column will change, row won’t |
| $E1 | Mixed | Column won’t change, row will |
The F4 Key
You can type to dollar signs as you’re entering absolute cell references but you may find it easier to use the F4 key to cycle through the various options. Let’s take a closer look at entering that last formula.
To calculate the tax for pencils in row 4, I would go through following steps:
- I would select cell D4 and type =(B4*C4)*E1.
- With my cursor still beside the E1 cell reference, I would press the F4 key.
- Then, I would press Enter.
After copying the formula to the remaining cells in column D, the worksheet would look like this.
