Microsoft Excel Absolute and Relative Cell References

Cell references in Excel are a big deal, getting them correct can mean the difference between time saved and time lost scratching your head.

Download the accompanying file(s) for this article.

By learning how to use cell references effectively in your formulas you are able to write more formulas and create more complex workbooks in a shorter space of time

What does ‘Relative’ mean?

Relative cell referencing is where the formula will change based on the relative position of rows and columns. Excel uses relative referencing by default. For example, if your formula is

=A1+B1

Dragging the formula down 1 row would change it to =A2+B2 i.e the position of the rows has moved +1 because you have dragged the formula down +1 row

Dragging the formula across 1 column would change it to =B1+C1 i.e. the position of the columns has moved +1 because you have dragged the formula across +1 column

 

What does ‘Absolute’ mean?

Absolute referencing is when the cell reference remains constant, or locked, to a cell. You can make either the row, column or row & column absolute by typing a $ before the cell reference. For example,

=$A1 makes column A absolute

=A$1 makes row 1 absolute

=$A$1 makes column A row 1 absolute

 

Why is Absolute Referencing useful?

Perhaps you have a data set with values in columns A-E however, you would like to know what the value of column A * Column B, and Column A * Column C and so on is. By using absolute referencing against column A, you can enter the formula once, drag it along and get the results. Relative referencing would not allow you to do this.

Absolute Cell Reference
Click on the image to expand it

If the cell you need to make absolute always resides in a specific row,  you need to move the $ along to the row reference part of the formula.

Absolute Cell Reference
Click on the image to expand it

What about if you have a single cell that contains your multiplier for example, an exchange rate? Simple, make the whole cell reference absolute.

Absolute Cell Reference
Click on the image to expand it

Handy Tip

Instead of constantly typing $ in your formulas, Excel has a handy hotkey! Highlight the portion of your formula which you want to make absolute and press the F4 key. Excel will cycle through the options (full, row, column) and enter the $’s for you.

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Excel Basics

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *