Absolute reference and relative references refer to whether cell address in formulas is fixated. Absolute references use a dollar symbol ($) to fix both row and column position, whereas relative references do not have the dollar symbol in front of row or column number.

Relative Reference Excel

By default, when you enter an address in Excel, it is using relative reference.

As shown in the screenshot, when you drag the formula in cell E3 to cell E5, Excel automatically changes row number so that formulas are calculating correct rows.

Most of the time that could be a good thing.

However, suppose you need all formulas to refer to a fixed cell address, then after you drag the formula across multiple cells, you may need to go back to each formula to change the automatically altered address.

If you are able to fix the address, you can easily drag the formula across other cells the same way. This is where absolute reference comes in.

Absolute Reference in Excel 2016, Excel 2019, Excel 365

You may need to refer back to I4 in many cell formulas. In that case, instead of putting cell reference say I4 in a formula, you can put $I$4. This means when you apply the same formula to other cells, I4 will be a fixed or absolute reference.

Absolute Cell Reference Example

This example uses a count formula to get an accumulated count of numbers in another column.

absolute reference Excel

The formula in cell B2 is

=COUNT(A$2:A2)

This tells Excel to count all the cells from A2 to A2 (one cell in total).

When this formula is dragged down to cell B6, it becomes

=COUNT(A$2:A6)

which tells Excel to count all the cells from A2 to A6 if it contains a number.

As seen in the screenshot below, text or blank cells are not counted.

Absolute Reference Excel Shortcut

When the cursor is in or next to a cell reference, you can press F4 multiple times to cycle between relative reference, absolute reference, fixed row or fixed column reference.

Make sure that your laptop registers F4 as is, or you might need to use Fn + F4 combinations depending on your keyboard setup.

Absolute Reference Excel Mac

If you are using Mac Excel 2011, you can use ⌘+ t to cycle between absolute, relative and mixed reference.

Mixed Reference Excel

Mixed references are where either column or row position is fixed with a dollar symbol ($) but not both.

In the example above, cells D5, E5 and F5 are multiples of the cells to the left. The multiplication factor lies in cell I5, while the formula is across columns, so we fix the column but not the row. That is, use $I5 instead of I5. When the formulas are reused in the cells below, they will refer to the correct cell which is I6.

In general, if the reference cells are across columns, then you would fix the row but not the column. If the reference cells are across rows, then you would fix the column with a dollar sign, but not the row position.

Below is another example of where the reference cells are across columns.

Remove Absolute Reference Excel

To remove an absolute reference in Excel, simply remove the dollar signs by pressing F4 multiple times until both dollar signs are removed.

Conclusion

Once you master the use of mixed references, you will be on your way to mastering Excel.

Pin It on Pinterest

Share This