Monthly Archive for January, 2015

Microsoft Excel Tips: Absolute Referencing

Absolute Referencing is a forgotten tool in your Toolbox called Excel. Daily you will probably find yourself using the fill handle on excel to automatically fill tables. This is called Relative Referencing and this will change the formula/function in a cell depending on which way you drag the cell.

 

So the example below we have a Product List at a made up computer company.Absolute Referencing 1

 

 

As you can see in the Total Quantity Price column I used the fill handle to drag my formula down the formula to the other rows to fill in the column. Because I dragged the fill handle down the numbers row numbers increased 1 at a time.
Absolute Referencing 2

 

 

Absolute Referencing allows us to lock a formula in a cell. Let’s see this in another example.

 

I’ve extended my table and added a Price After Tax column. This column uses the 20% (or 0.2) number in cell H1 to add the Tax onto the Price. Using the $ before both the H and the 1 you are able to “lock” this cell down. So when you use the fill handle the cell wont change.

 

Instead of typing the ‘$’ in the formula you could press the F4 key on your keyboard to automatically add the ‘$’ to that cell reference.