What is an Absolute Reference in Excel?
Absolute reference in excel is one of the types of cell reference in excel where the cells being referred to doesn’t change as they did in the relative reference, to make a formula for absolute referencing we use the $ symbol by pressing f4, $ symbols means lock and it locks the cell reference for all of the formulas so same cell is being referred to all the formulas.
How to Use Absolute Cell Reference in Excel? (with Examples)
Assume you have a data, which includes hotel costing for your project and you want to convert the entire US dollar amount to INR at 72.5 US dollars. Look at the data below.
In cell C2, we have our conversion rate value. We need to multiply the conversion value to all the USD amounts from B5:B7. Here the C2 value is constant for all the cells from B5:B7. Therefore, we can use an absolute reference in excel.
Follow the below steps to apply the formula.
- Step 1: Type equal (=) sign in cell C5 and apply the formula =B5*C2. As soon as you give reference to the cell C2 type F4 once.
This would do the conversion for the first USD value.
- Step 2: Now drag and drop the formula to the remaining cells.
Look at the dollar symbol for the cell C2 ($C2$) which means the cell C2 is absolutely referred. If you copy-paste the cell C5 to the below cell it will not change. Only B5 will change to B6, not the C2.
However, in Relative References, all the cells keep changing but in Absolute Reference in excel whichever the cells locked with the dollar, the symbol will not change.
Now let us look at the absolute cell reference example of absolute along with mixed references. Below is the sales data across months for 5 salespeople in the organization. They sold multiple times in a month.
Now we need to calculate the consolidated summary sales for all the five sales managers in the organization.
Apply the below SUMIFS formula to get the consolidated all the five people.
The result will be:
Closely look at the formula here.
- The first thing is our SUM RANGE, we have selected from $C$2:$C$17. A dollar symbol in front of both the column and row means it is an Absolute Reference.
- The second part is Criteria Range1, we have selected from $A$2:$A$17. This is also Absolute Reference
- The third part is the criteria, we have selected $E2. That means the only column is locked when you copy the formula cell only thing that changes is the row reference, not the column reference. Irrespective of how many columns you move to the right, it always stays the same. However, when you move downwards the row numbers keep changing.
- The fourth part is Criteria Range2, we have selected from $B$2:$B$17. This is also an Absolute Reference in Excel.
- The final part is Criteria, here the cell reference is F$1. This type of reference means row is locked because the dollar symbol is in front of the numeric number. When you copy the formula cell only thing that changes is the column reference, not the row reference. Irrespective of how many rows you move down it always stays the same. However, when you move to the right side the column numbers keep changing.
Play With Reference from Relative to Absolute or Mixed Reference.
We can change from one type of reference to another. The shortcut key that can do the job for us is F4.
Assume you had given a reference to cell D15 pressing the F4 key would do the following changes for you.
- If you press F4 only once, the cell reference changes from D15 to $D$15 (becomes ‘absolute reference’ from ‘relative reference’).
- If you press F4 twice, the cell reference changes from D15 to D$15 (changes to mixed reference where the row is locked).
- If you press F4 thrice, the cell reference changes from D15 to $D15 (changes to mixed reference where the column is locked).
- If you press F4 for the 4th time, the cell reference becomes D15 again.
When copying formulas in Excel, absolute addressing is dynamic. Sometimes you do not want cell addressing to change but rather absolute addressing. All you need to do is make the cell absolute by pressing the F4 key once.
A dollar sign is all about! If you copy this cell from place to place, the formula will not move with it. So if you type =$A1$+A2 into cell A3, then copy and paste that formula into cell B3, one cell automatically change but the other does not change, =$A1$+B2.
In Absolute reference in Excel, each referred cell will not be changing along with the cells you move either left, right, downward and upward.
If you give an absolute cell reference to cell $C$10 and move to one cell down it won’t changes to C11, if you move one cell upward it won’t change to C9, if you move one cell to the right it won’t change to D10, if you move on cell to the left then it won’t change to B10
This has been a guide to Absolute Reference in Excel. Here we discuss its uses and how to use Absolute Cell Reference along with excel example and downloadable excel templates. You may also look at these useful tools in excel –