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.

**Table of Contents**Expand

## 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,

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

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

`=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

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

Thank you for your assistance.

It has been a while since I have used excel and formulas.

I appreciate not being charged for your assistance.

Coral

Dear

Coral Gray,You are most welcome.

Regards

ExcelDemy