In this article, I will discuss what is an absolute reference and how to do absolute cell reference in Excel. Then I will show how you can shift from one type of reference to another type.
Before understanding absolute cell references, you have to understand cell references in a formula. Say I have a formula like this ‘=B3*C3+D3’. In this formula there are three cell references, they are B3, C3, and D3. Every reference in a formula links with a cell in an Excel worksheet. In another word, every cell has a unique reference. B3 can link to only one cell in the worksheet.
There are two types of references Excel-relative references and absolute references. Relative and absolute references behave differently when they are copied and filled with other cells. If you use relative references in a formula, the formula will change if you copy the formula to another cell. On the other hand, absolute references will remain constant, no matter in which they are copied.
What Is Absolute Cell Reference in Excel?
An absolute reference is known as a cell reference in which the rows and columns are prepared to become constant by using a dollar ($) symbol before them. Sometimes you may need that a cell reference will not change when you will fill cells. In relative references, when you copy your formula to another cell, the formula will be changed according to rows or columns. In contrast, in absolute references when you copy your formula to another cell, the whole formula will not change according to rows or columns.
Say, for example, you have a formula like this ‘=E5*J4‘ in cell F5. If F5 is an active cell then click Fill Handle, hold it, and drag over the cells you want to fill. See the following example. We have filled the cell ranges F6: F15 in our example. Check the formula in every cell. After cell F5, it returns zero for every case because there is no value beyond cell J4. As you didn’t make the cell J4 absolute, Excel understands it as a normal value and takes the next cell as a reference.
A Formula Using Absolute Cell References
But we want the J4 cell’s value will be unchanged, and its value will be as it is in the mother formula. For this, we have to use absolute references. We shall write newly our formula in cell F5 as ‘=E5*$J$4‘. We have used a dollar sign ($) before the column letter and row number. This is the system of how you can make a cell reference absolute
Types of Absolute Cell References in Excel
There are three types of absolute references where you can make absolute the whole cell in a formula, only making the column absolute in a formula or only making the row absolute in a formula. The following table describes three types of absolute references.
|Absolute cell references||Description|
|$F$5||Using these type of absolute references tell us that both column and row are fixed in the formula. It means that cell F5’s value is fixed in every formula in the filled cells.|
|F$5||Using this type of absolute reference tells us that only a row is fixed in the formula.|
|$F5||Using this type of absolute reference tells us that only one column is fixed in the formula.|
We shall mainly use the $F$5 type of absolute reference in the formula. We use the last two types of cell references rarely.
Steps to Do Absolute Cell Reference in Excel
We shall create a formula in cell F5 to calculate the VAT for all items in column F. In our example, the VAT rate is 15% and the value is set in cell J4. We shall use $J$4 absolute reference in our formula. As every formula is using the same VAT rate, we want that reference will remain constant when the formula is copied and filled to other cells in column F. Follow the steps carefully.
- Select the cell that will contain your formula. In our example, the cell is F5 where we shall use the formula.
- Then, enter your formula to calculate the desired value in the selected cell.
- We write the following formula in cell F5.
- Now press Enter on your keyboard. The formula will be calculated internally and you will see the calculated result in cell F5.
- Then, drag the Fill Handle icon down the column from cell F6 to cell F15.
- To check whether your formula is correct, just double-click on any cell from the filled cells. You will find that every formula has $J$4 absolute references, relative references have changed with the row numbers. For row number 9, the formula is ‘=E9*$J$4‘, just the row numbers have been changed.
- After that, you can calculate the total cost by adding menu cost and VAT and completing the table.
Switching Between References in Excel
Typing the $ sign manually before the column letter and/or row number is very painful and boring work. I’m going to show here how you can shift a relative cell reference to an absolute and then change an absolute cell reference to a mixed, and finally change a mixed cell reference to a relative.
1. Making a Cell Reference Absolute
Here I want to show you the absolute cell reference shortcut with an example.
I want to make the A1 cell reference an absolute cell reference in cell C1.
At first, I take the C1 cell to edit mode. Now I keep the cursor on the A1 relative reference.
When the cursor is on the A1 cell reference, I just press the F4 key on the keyboard. A1 cell reference will be converted into $A$1 absolute reference.
2. Shifting Absolute Cell Reference to Mixed Cell Reference
Continuing from the above picture…
Now I press the F4 key again. $A$1 will be converted to a mixed cell reference: A$1. The column will be relative but the row number will be absolute.
Now if we again press the F4 function key again, then A$1 will be converted into $A1, which means the Column letter will be absolute and the row number will be relative.
3. Shifting a Mixed Cell Reference to Relative Cell Reference
Continuing from the above procedure……
Again if we press the F4 function key again (Fourth time), then $A1 will be converted into A1 relative cell reference.
Absolute Cell Reference Keyboard Shortcut in Excel
There is a keyboard shortcut to put a cell reference in the formulas. When you type the cell reference in the formula, then, you can press F4 on the keyboard. This will make the cell reference absolute which means it can’t be changed while dragging the Fill Handle icon down the columns or rows.
Download Practice Workbook
Download the practice workbook below.
In this article, we have shown the details of absolute cell reference and how to do absolute cell reference in Excel. We have also discussed the keyboard shortcut of absolute cell reference and its symbol. In addition to that, we have shown how to switch between references. I hope you found this article interesting. If you have any questions, feel free to ask in the comment section.