Relative References
It is the default reference. By default, Excel creates relative cell references in formulas. A relative reference allows you to describe a cell in terms of where it is relative to the current cell. If you have B2
selected, then B3
can be described as the cell one row down. The cell references can change when you copy the formula to another cell as Excel looks at the cell address relative to the location of the formula.
For example: In following figure, cell D2
shows the amount spent on Product1
calculated as =B2*C2
.
The formulas for D3
and D4
are created by copying and pasting D2
(or by dragging the fill handle). The cell references in the formula are relative to the position of the cell containing them, and are automatically updated for the new location. Press Ctrl+`
(or go to Formula
tab and click Show Formula
in Formula Auditing Group) to see the formula in each cell instead of the resulting value:
The formulas for D3
and D4
are created by copying and pasting D2
. The cell references in the formula are relative to the position of the cell containing them, and are automatically updated for the new location.
Absolute references
When you refer to a cell in a formula using the absolute reference format, Excel uses the physical address of the cell and the row & column references don’t change when you copy the formula. An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number, for example, $A$1
. In following example, we need to calculate the sales tax for each item separately. The value in cell E2
for the Product1
would be =D2*C6
. The formulas for E3
and E4
are created by copying and pasting E2
:
You can see the result of cells E3
and E4
are incorrect, giving zero values, because reference to the relative reference C6
would be incremented to C7
and then C8
(press Ctrl+`
or go to Formula > Show Formulas
):
To fix the error we need to fix the reference to C6
, so that it doesn’t change when the formula is copies. Let’s edit the formula in E2
cell and place a $
symbol in front of the row and column addresses i.e. $E$2
: Now copy E2
cell (containing formula) and paste it on E3
and E4
:
Copy this formula down into cells E3
and E4
, the reference $C$6
doesn’t change, so the results are correct (press Ctl+`
to show formulas in all cells):
Mixed References
A cell reference with only part of the address fixed, such as $C6
or C$6
(either the row or the column reference is relative and the other is absolute), would be known as a mixed reference.
In our above example, there are two $
signs in $C$6
for a reason. $C
means “wherever you copy and paste this formula to, this reference will always refer to column C
“. Similarly, $6
means “wherever you copy and paste this formula to, this reference will always refer to row 6
“.
Absolute Column Reference
For example if you copy =$B2*C2
and paste it to next rows then the pasted formula looks like this =$B3*C3
and =$B4*C4
(the reference to column B
will not change) :
And if you copy the same formula to next columns then the pasted formula looks like this =$B2*D2
and =$B2*E2
(the reference to column B
will not change) :
Absolute Row Reference
Now copy =B$2*C2
and paste it to next rows then the pasted formula looks like this =B$2*C3
and =B$2*C4
(the reference to row 2
will not change) :
Similarly, copy the same formula to next columns. The pasted formula looks like this =B$2*D2
and =C$2*E2
(the reference to row 2
will not change) :
Named References
Names create absolute references to cells or ranges in the current worksheet. They can be used in formulas, and when these are copied (or dragged by fill handle), the references will not be incremented or changed. To create a name for a cell or cell range:
- Select the cell (or range) you want to name
- Click the Name box, on the left of the Formula Bar
- Type the name that you’ll be using to refer to the selection, then press Enter
You can change an absolute reference into Named references by naming the cell or range and then use that name in the formula. In our Absolute reference example we used $C$6
cell reference for sale tax percentage, now we’ll change the name of C6
cell into STAX
and convert our formula =D2*$C$6
to =D2*STAX
:
Press Ctrl+`
(or go to Formula > Show Formulas
from the ribbon):
Name Manager
To view the list of Named References in the workbook, click the Formulas
tab and select the Name Manager
: