How to Use Absolute Cell Reference In Excel

This article explains how to use an absolute cell reference in Excel 2010 and later to prevent formulas from automatically adjusting relative to how many rows or columns you've filled.

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.

1. For example, take a spreadsheet where you wanted to calculate actual sales tax for multiple items, based on reference cells elsewhere in the sheet.

   

2. In the first cell, you'd enter the formula using standard relative cell references for the item prices. But to pull in the correct sales tax into the calculation, you'd use an absolute reference for the row, but not for the cell.

   

3. This formula references two cells in different ways. B2 is the cell to the left of the first sales tax cell, relative to the position of that cell. The A$12 reference points to the actual sales tax number listed in A12. The $ symbol will keep the reference to row 12 constant, regardless of which direction you fill adjacent cells. Fill all the cells under this first one to see this in action.

   

4. You can see that when you fill the column, the cell in the second column uses relative cell referencing for the cost values in column B, incrementing the column to match the sales tax column. However the reference to A12 stays constant due to the $ symbol, which keeps the row reference the same. Now fill the D column starting from the C2 cell.

   

5. Now, you can see that filling the row to the right uses the correct state sales tax (Indiana) because the column reference is relative (it shifts one to the right just like the cell you're filling). However, the relative reference for cost has now also shifted one to the right, which is incorrect. You can fix this by making the original formula in C2 reference the B column with an absolute reference instead.

   

6. By placing a $ symbol in front of "B", you've created an absolute reference for the column. Now when you fill to the right, you'll see that the reference to the B column for cost remains the same.

   

7. Now, when you fill the D column down, you'll see that all relative and absolute cell references work exactly the way you intended.

   

8. Absolute cell references in Excel are critical for controlling which cells are referenced when you fill columns or rows. Use the $ symbol however you need within the cell reference to keep either the row or the column reference constant, in order to reference the correct data in the correct cell. When you combine relative and absolute cell references like this, it's called a mixed cell reference.

   If you use a mixed reference, it's important to align the column or row of the source data with the column or row where you're typing the formula. If you've made the row reference relative, then keep in mind that when you fill to the side, the column number of the source data will increment along with the column of the formula cell. This is one of the things that makes relative and absolute addressing complicated for new users, but once you learn it, you will have much more control over pulling source data cells into your formula cell references.

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.

1. Using the same example spreadsheet from above, you can reference only the single state tax rate by adding the $ symbol to both the column and the row reference.

   

2. This makes both the "A" column and the "12" row stay constant, no matter what direction you fill the cells. To see this in action, fill the entire column for ME sales tax after you've updated the formula with the absolute column and row reference. You can see that each filled cell always uses the $A$12 absolute reference. Neither the column or the row changes.

   

3. Use the same formula for the Indiana sales tax column, but this time use the absolute reference for both column and row. In this case that's $B$12.

   

4. Fill this column, and again you'll see that the reference to B12 doesn't change in ever cell, thanks to the absolute reference for both column and row.

   

5. As you can see, there are multiple ways you can use absolute cell references in Excel to accomplish similar tasks. What absolute references provide you is the flexibility to maintain constant cell references even when you're filling a large number of columns or rows.

   You can cycle through relative or absolute cell references by highlighting the reference and then pressing F4. Every time you press F4, the absolute reference will be applied to either the column, the row, both the column and cell, or neither of them. This is a simple way to modify your formula without having to type the $ symbol.

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).