Absolute references in excel are vital to financial modeling. They are cell references which see the row and column coordinates remain constant whilst copying formula from once cell to another. You may know that the default referencing style of excel is the relative reference, and if you attempt to copy your formula to other cells, your cell references would change depending on the number of rows and column movement you made from the original cell containing the formula to the new cell you want the function coped to. To avoid this, you need to know how to perform an absolute reference to your inputs and in this post, we would show you how to do just that.
Absolute reference example
We treated the relative reference with an example on data offers of a Telecommunications company. In this section, we would tweak that example to illustrate how absolute references works.
In our example, we picked just one of the data plans, and whilst the price is fixed, we have different sales count for each month. Our task here is to calculate the revenue for each month using the uniform price of $1.3. This means that we would need to lock in the cell B3 when doing our multiplication. Let’s see the steps below:
Step 1: Type in the formula $B$3 X B6 in cell C6 to calculate January’s revenue. Notice that cell B3 has a dollar ($) sign before and after the column letter. This signifies absolute referencing, and we can say cell B3 is anchored (locked in) meaning that when we copy and paste the formula to the rows below, our formula would still refer to cell B3 for the price. Note, to attach the dollar ($) sign without having to type it, enter the cell you are typing the formula in and on the reference, you want to anchor (say B3 in this case), press the F4 key once.
Step 2: Copy and paste the formula below as we have anchored the price by adding a dollar ($) sign before and after the column letter.
Result: The formula for the rows below still maintains cell B3 as the price, whilst sales count is automatically adjusted based on the relative reference principle in excel.
=$B$3*B7
=$B$3*B8
=$B$3*B9
=$B$3*B10
=$B$3*B11
We have come to the end of this tutorial. Always remember that you can save time having to manually edit cell references by using the correct anchoring. Absolute references should be when you have only one input which all calculations would use to derive their output.
Another thing worthy of note is that if you are selecting a reference outside of the current workbook you are working on, excel would automatically make it an absolute reference as you would see the dollar sign both before and after the column letter. Please make sure to check and adjust the references accordingly.
