What Is and How to Do Absolute Cell Reference in Excel?

What Is an Absolute Cell Reference in Excel?

An absolute reference is known as a cell reference in which the rows and columns are 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.

We have a formula ‘=E5*J4‘ in cell F5. We have filled the cell ranges F6: F15 with that formula. After cell F5, it returns zero for every case because there is no value beyond cell J4. Since we didn’t make the cell J4 absolute, Excel takes the next cell as a reference when filling.

A Formula Using Absolute Cell References

If we want the J4 cell’s reference to stick, the formula in cell F5 will be ‘=E5*$J$4‘. We have used a dollar sign ($) before the column letter and row number.

Types of Absolute Cell References in Excel

Steps to Do Absolute Cell Reference in Excel

In our example, the VAT rate is 15% and the value is set in cell J4. We’ll use the $J$4 absolute reference in a formula.

Steps

• Select the cell that will contain your formula. In our example, the cell is F5 where we shall use the formula.

• Enter your formula to calculate the desired value in the selected cell.
• We write the following formula in cell F5.

• Press Enter on your keyboard. The formula will be calculated internally and you will see the calculated result in cell F5.

• Drag the Fill Handle icon down the column from cell F6 to cell F15.

• To check whether your formula is correct, double-click on any cell from the filled cells. Every formula has the $J$4 absolute references, while the 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.

• You can calculate the total cost by adding menu cost and VAT and completing the table.

Switching Between References in Excel

Part 1 – Making a Cell Reference Absolute

We want to make the A1 cell reference an absolute cell reference in cell C1.

• Take the C1 cell to edit mode.
• Keep the cursor on the A1 relative reference.

• Press the F4 key on the keyboard. A1 cell reference will be converted into a $A$1 absolute reference.

Part 2 – Shifting Absolute Cell Reference to Mixed Cell Reference

• Press the F4 key again. $A$1 will be converted to a mixed cell reference A$1.

• If we again press the F4 function key again, A$1 will be converted into $A1.

Part 3 – Shifting a Mixed Cell Reference to a Relative Cell Reference

• If we press the F4 function key again, $A1 will be converted into A1 relative cell reference.

Absolute Cell Reference Keyboard Shortcut in Excel

Press F4 while the cursor is over a cell reference to cycle between absolute and relative referencing.

Download the Practice Workbook

Related Article

• [Fixed] F4 Not Working in Absolute Cell Reference in Excel
• How to Use FormulaR1C1 Absolute Reference in Excel VBA

<< Go Back to Absolute Cell Reference | Cell Reference in Excel | Excel Formulas | Learn Excel