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.

Download Workbook

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.

How to Make a Reference Absolute

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

Creating Absolute Reference

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.

shortcut for absolute reference

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.

absolute column

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.

Absolute Row

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

Read more: Different Types of Cell References in Excel

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

absolute reference

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

absolute reference

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

absolute reference

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

Absolute reference

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

Absolute reference


Similar Readings:


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,

=C9:C11*$B$4:$B$6

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.

Absolute Reference

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.

Absolute reference

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

Read more: Difference Between Absolute and Relative Reference in Excel

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

=B6*C6*'Deposit List'!$B$4

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

It will make the selected cell absolutely referred

Absolute reference

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.

absolute reference

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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo