What is an absolute reference in Excel?
Before we explain what an absolute reference is in Excel, if you are not familiar with the very basics of how to link cells in Excel, then you might want to read this refresher: How to link cells in Excel.
What are those dollar sign symbols in Excel – they are absolute references!
If a cell is copied and pasted into another cell somewhere else on the spreadsheet then any reference in that cell to another cell (which would look something like “=A1” in the formula bar) will also be changed and updated automatically by Excel. Well, what if we wanted to make it so that the reference inside the cell stays fixed even if the cell is moved somewhere else? This way, Excel will not be able to change the reference. Luckily there is an easy way to do this – and it uses the dollar sign symbol to make the reference “absolute”, or fixed, so that Excel can not change the reference.
An example without absolute references in Excel
Let’s first consider what would happen if we did not have absolute cell references in an example below – this means that the dollar sign symbol won’t be used. So, we have some cells B2-B7 with names of tennis players, and we set cell C2 to reference B2 by using “=B2” in the formula bar as you can see in the screenshot below:
What if we now just drag the cell C2 down using the black arrow that appears when we hover over the bottom right hand corner of the selected cell – what exactly happens? Well, Excel will assume that you want to maintain the same relative references and will automatically update the new cells with references to the cells directly to their left. So, the spreadsheet will now look like this:
Note that the currently selected cell (C7 – Agassi) has a reference to the cell to the left of it (B7). Similarly, cell C6 has a reference to cell B6, and C5 to B5, etc. Now, that behavior of Excel is actually nice when it is what we want to have happen, but what if we don’t want that to happen? What if we just want all the cells from C3 to C7 to reference the same cell that C2 references (which is cell B2)? That way all of the cells from C3 to C7 will just say “Federer”.
An example with absolute references in Excel
Well, that is exactly where absolute cell references and the dollar sign symbols are used – in order to create an absolute cell reference we must use dollar sign symbols so that we can “fix” the cell reference and Excel will not change it. Here is how to do it: since we want to have a fixed reference to cell B2, we simply insert a dollar sign before the column reference (in this case column B), and another dollar sign before the row reference (in this case row 2). This means that the cell reference for cell C2 becomes $B$2 – and in the formula bar we would write “=$B$2” .
Now, if we drag cell C2 down using the black arrow, because the cell reference is fixed to point to B2, all of the cells that we create by dragging cell C2 will continue to point to “Federer”. This means that Excel will no longer automatically adjust the reference, because of the dollar signs that we added which make the reference fixed. So, as you can see in the image below, after dragging down cell C2 we get this:
Understanding the Excel dollar sign symbol in more depth
There is a reason why creating an absolute cell reference requires two dollar signs instead of just one. The reason is that in a cell reference like $B$2, the first dollar sign will fix the column reference – in this case the reference to column B. The second dollar sign will fix the row reference – which in this case is the reference to row number 2. When the two dollar signs are used together, they fix both the row and column, which basically means that the cell reference (which is a combination of a fixed row and a fixed column) stays fixed.
How to fix just a column reference with one dollar sign
It is also possible to just fix the column reference – but allow the row reference to remain un-fixed so that Excel will automatically adjust the row (but not the column) reference when the cell is dragged or copied and pasted elsewhere. This means that the cell reference would look like $B3 – which means that the reference to column B is to remain fixed because of the dollar sign in front of the B, but the row does not need to remain fixed since there is no dollar sign in front of the 3. So, Excel can change the row, but the column must always remain as B whenever the cell is dragged or copied to another cell. An example will help clarify that.
Example of using an absolute column cell reference:
Take a look at the image below, which shows different products for different car models. Each product has a different manufacturing cost, and a different shipping cost depending on the car model. The challenge here is how to come up with a formula for the total cost – including shipping – for each product. With that in mind, let’s say we come up with the following formula for cell I3: “= B3+D3”. Now, let’s see what happens when we drag that formula over to the cells to the right that are also used to hold the totals:
Clearly the total cost values are wrong in all of the cells except for I3 – cells J3, K3, and L3 all have values that are obviously too low. Why is this happening? Take a look at the formula in cell L3 (for the Porsche total) – do you notice anything wrong with “=E3+G3”? Well, what’s wrong is the fact that the formula references cell E3 when in fact we want it to reference cell B3 instead. Why is it referencing cell E3?
Absolute cell references vs relative cell references
Well, remember that Excel will automatically update the formula to use the relative reference – read here for a good refresher on that concept : How to link cells in Excel. In our example above, this means that when we drag cell I3 3 cells to the right to copy it over to cell L3, Excel will assume that you also want to update the columns referenced in the formula to be shifted 3 places to the right – so “=B3+D3” becomes “=E3+G3” when cell I3 is dragged to L3, and “=B3+D3” becomes “=D3+F3” when cell I3 is dragged to cell K3, and so on .
Remember that Excel makes this adjustment to the formula automatically when the cell is moved. That actually works well for the shipping costs column, because it’s exactly what we want, but it does not work out for the manufacturing costs column because we want our reference to that column to stay fixed (but we still want the row to adjusted automatically).
So, how can we get it to reference column B instead? Well, clearly you could just go in manually and type B3 into the formula, but the more efficient way is to fix the formula instead so that whenever the cell is dragged and copied to another cell, the formula is updated to reference the correct cell. So, what needs to be done here? Well, we clearly need to fix the reference to column B so that the reference is not updated whenever a cell is dragged to another cell. How can we do this? You should be abel to guess how – we use the dollar symbol to “fix” the reference to column B – which means that we create an absolute cell reference, but just for column B, which is technically more of an absolute column reference.
How to create an absolute column reference
This means that now, for cell I3, instead of using the formula “= B3+D3”, we use the formula “= $B3+D3”, which fixes the reference to column B. Now notice the formula that shows up for cell L3 when we drag cell I3 over:
Note that the formula in L3 now says “= $B3+G3”, and the correct values now show up for the total cost in cells I3 to L3. We can further drag cell I3 down to all the other cells below it, and we will get the correct totals to show up. Once again, this works because of the fact that we are fixing the reference to column B. But, note that we are not fixing the reference to any of the rows in column B – we are allowing Excel to automatically choose the correct row. This is why this is an absolute column reference and not an absolute cell reference (for which we would fix both the column and row reference). So, don’t be confused the next time you see just one dollar sign in a cell reference instead of two dollar signs!
You can read more about using absolute cell references within the context of Excel ranges here: Excel range Formula.