Excel Tip - Absolute Cell References

Absolute Cell Reference

I wanted to start with some real basics as a lot of Excel websites tend to skip them. Probably one of the most important things everyone needs to understand is the difference between an absolute cell reference and a relative cell reference. The default in Excel is a relative cell reference.




Think about it, if you copied the formula of A2*B2 as shown in the example, you don't expect to get the answer of 3 everytime do you? Of course not, instead you just assume that Excel is smart enough to know that when you copied the formula to row 3, Excel should adjust the formula to its new relative position and the new formula becomes A3*B3 with the resulting answer of 6 without you having to do anything. That is relative cell referencing.







Sometimes though, you don't want Excel to automatically adjust the formula and that is where the trouble comes in as you need to tell Excel that by specifying the cell is an absolute cell reference. Absolute cell references become very important if you are referencing an input cell, doing percentages or using functions such as Vlookup.



Let's change the example above slightly and use an input cell. Notice that the price for all of these is the same. It would be much more efficient to create a price input cell and reference that. I have done that in the next example. My input cell is E1. Then in Cell B2 I created a formula of =A2*E1. Okay, now if I just go and copy that down to row 3, I am going to end up with =A3*E2. Now, I do want Excel to "pick up" A3 but cell E2 has nothing in it and I will get an error. By using dollar signs around E1 in the formula, it tells Excel to freeze and always refer to cell E1. This makes E1 an absolute cell reference. So, if you go and create this and take a look at cell B4, you should see =A4*$E$1 with the resulting answer of 9.Because the dollar sign is in front of the column heading and the row heading, it knows that it must refer only to Column E and it must refer only to row 1. This is absolute cell referencing.






You can just type the dollar signs in or after typing in the cell reference E1, press the F4 key on your keyboard.

Most of the time, people don't think about absolute cell references so here is a handy tip - always check your formulas when you copy and don't just check the first one- check the 3rd or 4th one to make sure that your formula is actually doing what you wanted. If it is not working, chances are you need to make a cell or cells absolute.

I hope this made sense to everyone. If you have any questions, email me at patricia@cpaselfstudy.com