What Is an Absolute Cell Reference in Excel?
There are two types of ways to reference a cell inside a formula in Excel. You can use a relative cell reference or an absolute cell reference.
Relative cell reference: A cell address which doesn’t contain the $ symbol in front of the row or column coordinates. This reference will automatically update the column or cell relative to the original cell when you fill either down or across. Absolute cell reference: A cell address which contains the $ symbol either in front of the row or column coordinates. This “holds” the reference row or column constant even when filling a column or row with the same formula.
Cell referencing can seem like an abstract concept to those who are new to it.
How to Use Absolute Cell Reference in Excel
There are a few different ways to use an absolute reference in Excel. The method you use depends which part of the reference you want to keep constant: the column or the row.
Using Absolute Cell References to Pin One Cell Reference
Another approach to using absolute cell referencing is by applying it to both the column and the row to essentially “pin” the formula to use only one single cell no matter where it is. Using this approach, you can fill to the side or down and the cell reference will always stay the same.
When You Should Use Absolute Cell References
Throughout nearly every industry and field, there are a lot of reasons you may want to use absolute cell references in Excel.
Using fixed multipliers (like price per unit) in a large list of items.Apply a single percentage for each year when you’re projecting annual profit targets. When creating invoices, use absolute references to refer to the same tax rate across all items.Use absolute cell references in project management to refer to fixed availability rates for individual resources.Use relative column references and absolute row references to match column calculations in your referenced cells to column values in another table.
If you do use a combination of relative and absolute references for columns or rows, you just need to make sure that the position of the source data columns or rows match the column or row of the destination cells (where you’re typing the formula).
