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

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

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