Excel 2019

Relative, Absolute, Mix and Named References

Most formulas we create include references to cells, range (a collection of cells) or the name. These references enable formulas to work dynamically with the values contained in those cells. For example, if a formula refers to cell C6 and you change the value contained in C6, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves to change the values used in the formulas. We can divide cell references into four types:

  1. Relative references
  2. Absolute references
  3. Mixed references
  4. Named references
  5. Name Manager

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.

Relative formula reference

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:

See all formulas in cells

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 :

Absolute formula reference

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):

See all formulas in cells

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:

Absolute formula reference

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):

See all formulas in 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) :

Mixed Reference Column

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) :

Mixed Reference

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) :

Mixed Reference Row

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) :

Mixed Reference

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:

  1. Select the cell (or range) you want to name
  2. Click the Name box, on the left of the Formula Bar
  3. 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:

Named References

Press Ctrl+` (or go to Formula > Show Formulas from the ribbon):

Named reference see formula

Name Manager

To view the list of Named References in the workbook, click the Formulas tab and select the Name Manager:

Name Manager