Absolute cell reference in Excel is a way to refer to a specific cell or a range of cells that doesn’t change even when the formula is copied or moved to another cell.
In Excel, cell references are typically relative by default, which means they change relative to their location when copied to another cell. Absolute references, on the other hand, remain constant.
Absolute Cell Reference refers to a cell reference that doesn’t change when you copy it to another location. It’s represented by a dollar sign ($) before the column and/or row. For example, $A$1 is an absolute reference to column A and row 1.
Example of Using Absolute Cell Reference in Excel
Below, I have a dataset where I have the names in column A and the sales values in column B, and I want to calculate the commission in column C using the commission value in E2.
To do this, I can use the below formula in the cell C2:
=B2*$E$2
When you copy this formula down the column, the absolute reference to E2 remains fixed, while the relative reference to C5 changes with each new row.
To sum up, the purpose of absolute cell references is to “lock” a reference to a specific cell or set of cells, ensuring that when you copy or move a formula, certain references stay fixed.
What are the Other Types of Cell References in Excel?
There are three types of cell reference in Excel: relative, absolute, and mixed.
- Absolute Cell Reference means that the cell reference remains fixed (as already covered in this article above). For example, =$A$1+$B$1. If you copy this formula down a row, it still reads =$A$1+$B$1, referring to the same exact cells.
- Relative Cell Reference means that the cell reference changes based on the relative position of the formula. If you have a formula in a cell, say =A1+B1, and you copy this formula to the next row, it automatically adjusts to =A2+B2. This is a relative reference.
- Mixed Cell Reference means that either the column or the row reference is fixed, but not both. For example, =$A1+B$1. Here, column A is absolute and row 1 is relative in the first part, and vice versa in the second part.
Also read: What is Excel?
Toggle Between Absolute and Relative Cell Reference
You can use the F4 key as a keyboard shortcut to toggle between relative, absolute, and mixed references in a formula.
This saves time and makes it easier to switch between reference types without manually typing dollar signs.
To use the F4 key, select the cell reference and press F4. Each time you press F4, Excel cycles through the reference types.
What is the Difference Between Relative and Absolute Referencing in Excel?
Relative referencing in Excel means that the cell reference changes based on the relative position of the formula.
Absolute referencing, on the other hand, means that the cell reference remains fixed.
When you copy a formula with relative referencing, the cell references change based on the new location. With absolute referencing, the cell references remain the same even when you copy the formula to a new location.
What Character is Used to Create an Absolute Reference in Excel?
In Excel, the dollar sign ($) is used to create an absolute reference.
When you place a dollar sign before the column letter, row number, or both in a cell reference, it tells Excel to keep that part of the reference constant when the formula is copied to another cell.
When you put the dollar sign before the column letter as well as the row number, that makes the reference absolute (such as in $A$1)
Other articles you may also like:
