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.
- Type equal (=) sign in cell C5 and apply the formula =B5*C2. As soon as you give reference to the cell C2, press the F4 key one time.
This would do the conversion for the first USD value.
- 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 referencesMixed ReferencesA mixed reference is a type of cell reference that differs from absolute and relative cell reference. In the mixed cell reference, we only refer to the column of the cell or the row of the cell.. Below is the sales data across months for five 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 in ExcelSUMIFS Formula In ExcelThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range). 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. It is also Absolute Reference
- The third part is the criteria; we have selected $E2. 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. It is also an Absolute Reference in Excel.
- The final part is Criteria; here, the cell reference is F$1. This reference means row is locked because the dollar symbol is in front of the numeric number. When you copy the formula cell, the 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 changes, 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 change 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 the 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 –