Absolute Reference in Excel (With Examples)

Absolute Reference in Excel is one kind of cell reference where the referred cell remains constant and doesn’t change when it is copied, moved or used in an array. It is a very useful feature of Excel and provides great help when you need to apply the same value or formula over a range of columns or rows. In this article, I will discuss everything you need to know about absolute cell reference with a number of examples.

Function of Absolute reference

Absolute reference locks the referred cell. When you type a formula in any cell, you can use another cell as a reference. Then You can drag the cell in which the formula is applied to apply the same formula over other cells in the column or row, the cell you used in the formula automatically changes. But if you use absolute reference while using a cell in the formula, the cell won’t change after dragging. This is the beauty of absolute reference.

Absolute Reference in Excel: 4 Examples

Creating Absolute references is easy. Just put a Dollar sign ($) before column number and another Dollar sign($) before Row number.

Keyboard Shortcut for absolute reference

You can apply absolute reference very easily by using a keyboard shortcut. Select the cell to which you want to use as absolute reference and then press F4.

Absolute Column Reference Only

When you put the Dollar sign only before the column number you will get an absolute column with relative row reference. It means when you drag the formula cell, only the row of the selected cell in the formula bar will change, the column will remain unchanged. It will look like $B4.

When you want to change the row but want to keep the column constant you can use absolute column reference only. It is generally used when you copy your data vertically.

Absolute Row Reference Only

When you put the Dollar sign only before the row number you will get an absolute row with relative column reference. It means when you drag the formula cell, only the column  of the selected cell in the formula bar will change, the row will remain unchanged. It will look like B$4.

When you want to change the column but want to keep the row constant you can use absolute row reference only. It is generally used when you copy your data horizontally.

Use of Absolute reference

Consider the following examples. Here we have different principal amounts and years of deposit for different people. We want to find out how much Interest they will get for a specific interest rate.

For that we need to multiply the principals with the years of deposit and a fixed interest rate.

We can find the Interest for the first person by applying the formula “=B8*C8*B4” in cell D8

Now for finding the interest of the other entry, we drag the cell D8 to the end of our dataset.

But it shows multiple errors. This happened because when we dragged the cell D8, The cell B4 had changed.

To avoid this mistake, we have to apply absolute reference to cell B4

Now Drag the cell D8. This time we will get the right value for other cells.

Absolute Reference for a Range

You can use absolute reference for a range of cells. Consider the following example, you want to find out the total price for pen, pencil and notebook for different customers. Unit price for those items are given in cell B4:B6

To apply absolute reference for this range first select cell D9. Type the formula,

Here, $B$4:$B$6 are the selected range of cells with absolute reference.

You will automatically get the value in the D10 and D11 cells.

To get the value for other customers, you have to copy the formula of D9 ,select the first cell of every customer then paste the formula in that cell.

Switch Reference from Absolute to Relative and Relative to Absolute

• First select the cell in the formula bar and press F4, it will make the absolutely referred.
• Press F4 again ,then the cell will have an absolute row with a relative column.
• Press F4 again, the cell will have an absolute column with relative row.
• Press F4 again, the cell will be relatively referred.

For example, let’s use the B4 cell to demonstrate the switch operation.

B4>press F4> $B$4>press F4>B$4>press F4>$B4>press F4>B4

Reference Cell across Different Worksheet

You can refer to a cell in a formula from a different worksheet of the same workbook. Suppose we have some value in the sheet named New Deposit List and we want to use the value of cell B4 from the sheet named Deposit List.

First select the cell where you want to refer. Type the formula

 Here, you have to type ‘The name of the sheet’!, cell number and press F4.

It will make the selected cell absolutely referred

After pressing ENTER you will get the value. By dragging the cell you will get value in the other cell where the referred cell from another sheet remains constant.

Download Workbook

Conclusion

Absolute reference becomes very handy when you have to work with a large dataset. I hope you have found this article useful and have learnt how to use absolute reference. If you have any kind of confusion please leave a comment.

Absolute Reference in Excel: Knowledge Hub

• Absolute Cell Reference Example
• Excel F4 Absolute Reference Not Working
• Excel VBA Formula R1C1 Absolute Reference

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