Absolute Cells in Excel: Explained With Images

What are absolute cells and why are they useful?

As a daily Excel user, I use “absolute cells” all the time. It may seem useless initially and annoying with the additional keystrokes needed, but it is actually a simple concept that can greatly enhance your Excel productivity.

Quite simply an absolute cell reference is a fixed reference to a cell in a spreadsheet.

Unlike a relative reference, which changes when you copy or move a formula , an absolute reference always refers to the same cell, no matter where it is copied or moved to . This makes it a powerful tool for your own spreadsheet productivity.

In this article, I will explain what absolute cells are, how to use them in Excel, and why they are important. Whether you are a beginner or an advanced Excel user, understanding absolute cell references can help you save time and increase your output.

Understanding Cell References

When working with Excel, it’s essential to understand cell references. A cell reference is simply the address of a cell in a worksheet, like A1 . Excel uses cell references to perform calculations and to link cells together in formulas.

There are three types of cell references in Excel: relative, absolute, and mixed.

Relative References

Relative references are the default type of reference in Excel. When you copy a formula that contains relative references, the references will adjust based on the new location of the pasted formula. This can be useful, but it can also cause problems if you want to keep a reference constant .

Absolute References

Absolute references , on the other hand, always refer to the same cell, regardless of where the formula is copied. To create an absolute reference, you need to add a dollar sign ( $ ) before the column and row number of the cell reference. For example, if you want to create an absolute reference to cell A1 , you would write $A$1 .

Mixed References

Mixed references are a combination of relative and absolute references. You can make either the column or row reference absolute by adding a dollar sign to that part of the reference. For example, if you want to keep the column reference absolute but allow the row reference to adjust, you would write $A1 . And vice versa if you wanted to keep the row reference absolute but allow the column reference to adjust, you would write A$1 .

By using the appropriate type of reference, you can ensure that your formulas are accurate and consistent, especially when they are copied.

How To Use Absolute Cell References

When working with formulas in Excel, it is important to understand how to use absolute cell references. While you can manually insert the dollar signs before the column and row references you can also use a shortcut key to help insert the dollar signs for you when your cursor is at a cell reference or range.

Manually Inserting Dollar Signs

The most obvious way to create an absolute cell reference, is to manually type the dollar sign ( $ ) before the column letter and row number of the cell you want to lock.

For example, if you want to lock cell A1 , you would write it as $A$1 .

One common use of absolute cell references is when you have a common metric used in your formulas. A common scenario is calculating the salary increases of staff if they were all to receive the same increase.

Here’s a demonstration creating a formula that increases the salaries of staff by a common rate using an absolute reference:

In this example, B2 is the cell with the base increase of all staff salaries. Once the increase formula is written in cell C4 with an absolute cell reference, you can copy the formula to other cells and the formula will retain the absolute reference to B2 .

F4 Keyboard Shortcut

Instead of having to type the dollar signs into any cell reference you can also the keyboard shortcut F4 .

Simply have the cursor at the end, or inside, the cell or range reference and tap the F4 key.

You can continue to keep tapping the F4 key until the desired absolute references are set according to your needs.

Absolute cell references are an important aspect in Excel that allow you to lock cells or ranges of cells so that they do not change when you copy or fill formulas. While you can manually enter the dollar signs you can more easily tap the F4 key to have Excel automatically insert the dollar signs on your behalf.

You can continue tapping the F4 keyboard to have Excel cycle through the different absolute and mixed references.

Absolute Cell Reference Examples

As mentioned earlier, absolute cell references in Excel are used to lock the reference of a cell so that it does not change when copied or moved.

Here is another example of how absolute cell references can be used in Excel:

Calculating Discount Sale Price

Suppose we have a table with sales data for the first quarter of the year and we want to calculate the percentage increase in sales from January to February. We can use the following formula:

	A	B	C	D
1	Item	Sale Price	Discount	5.0%
2	Widget A	100
3	Widget B	120

Here, B3 is the cell containing the sale price of Widget B and B2 is the cell containing the sales price for Widget A.

If you needed to apply a discount to the sale price of 5% you could create the following formula in cell C2 :

=B2*(1-$D$1)

This would provide you with the discounted sale price for Widget A.

When this formula is copied down to Widget B and all other products the absolute reference to $D$1 would remain the same. Therefore, all sale prices can be discounted by the same rate.

Absolute Cells: Summary

Overall, absolute cell referencing is an essential skill for anyone who works with Excel spreadsheets on a regular basis. By using the dollar sign to lock specific cells, you can ensure that formulas and functions always refer to the correct data, even if you move or copy them to a different location. This can save you time and prevent errors, especially in larger spreadsheets with many calculations.

Although it can take some practice to get used to absolute cell referencing, it is well worth the effort. By mastering this technique, you can become more efficient and effective in your use of Excel, and produce more accurate and reliable results.