Absolute Cell Reference in Excel and Other Spreadsheets

by ryan on December 20, 2016




Sponsored

Learn About Absolute Cell Reference in Excel and Other Spreadsheets

 

Since almost all work done in a spreadsheet deals with cell references or cell addresses, it is important to understand the difference between relative and absolute cell references.

Relative cell referencing is the way a spreadsheet normally works. If you are writing an equation using values and cell references in row 3, then when you copy the equation to other rows, the spreadsheet will update the equation in the other cells to be relative to their position. For example if you type =A3*B5 in cell A3, then when you copy the equation and paste it into cell A4, your equation gets updated to =A4*B5. That is relative cell referencing.

pareto-analysis-report

However, sometimes you don’t want that. Take a look at this example in Excel:

An image showing how relative cell references in Excel can give you the wrong answer.

This example shows a list of authors and how much money you could pay them if they wrote articles for you and you paid them $120.o0 per article. I wrote the first equation in cell C2 which was =B2*C2, and then copied it into C3. You can see that the equation worked for the first cell, but when I copied it into the other rows. You can see that C3 is trying to multiply the value B3 by the value B9, but there isn’t anything in B9!

We can fix this by making the reference to cell B8 into an absolute reference. This way the 8 in the equation will not be updated to a 9 or a 10 etc. We do this by adding a dollar sign ($) in front of the 8. I’ve also added it in front of the B in this case, though it isn’t strictly necessary unless we were copying across columns.




Sponsored

An image showing the use of absolute cell referencing in Excel.

By changing the equation in C2, I know the answer is still correct using absolute cell referencing and when I copy the equation to the other rows, I still get the correct answer because the value in B8 will always be used thanks to absolute cell addressing.

When I teach this topic to students I usually write it on the board like this:
ab$olute cell referencing

This way, I hope they can remember to use the dollar sign when they need to.
Here is the same example in Google Docs Spreadsheets:

And again in OpenOffice Calc:

ShortCuts Can Be Dangerous

Now in Excel and Google Docs Spreadsheets, you can add the $ automatically to a cell reference that has already been typed into the cell by pressing the F4 key (this does not work in OpenOffice). When you have your insertion point or cursor in a cell address, pressing the F4 key will cycle through the different possibilities of where to put the dollar sign, depending on what you want to get done.

Here is an interesting story on why it is important to remember that ab$olute cell referencing can be done with dollar signs instead of the F4 key:

In the online training I use in class, the hints given during training include using the F4 key in Excel which will cycle the currently active cell reference by adding the $ into different positions, allowing you to choose which cell reference format is appropriate.

During the final assessment at the end of the term, I would have students ask me how to complete a question regarding absolute cell referencing because they forgot what it is, but some of them remember it has something to do with F4. Students who only remember the F4 key forget how to use an absolute reference or what the F4 key actually does, which is places a dollar sign in different spots in the cell reference. If they can’t remember how to use F4 key, they end up getting the question wrong.

So remember that learning that the dollar sign is used to make an absolute cell reference and how to use it is much more important than remembering to use the F4 key.

Do you have any tips you’d like to share on using absolute cell referencing in your spreadsheets? Post some in the comments, I’d love to hear from you!

Templates:

personal-budget_large-excel-template

personal-monthly-budget_large-excel-template




Sponsored

Related Post

Sponsored

Previous post:

Next post: