In this article, I will discuss what is an absolute reference and how to do absolute cell reference in Excel. Then I will show how you can shift from one type of reference to another type.

Before understanding absolute cell references, you have to understand cell references in a formula. Say I have a formula like this â€˜**=B3*C3+D3**â€™. In this formula there are three cell references, they are **B3**, **C3**, and **D3**. Every reference in a formula links with a cell in an Excel worksheet. In another word, every cell has a unique reference. **B3** can link to only one cell in the worksheet.

There are two types of references Excel-relative references and absolute references. Relative and absolute references behave differently when they are copied and filled with other cells. If you use relative references in a formula, the formula will change if you copy the formula to another cell. On the other hand, absolute references will remain constant, no matter in which they are copied.

## What Is Absolute Cell Reference in Excel?

An absolute reference is known as a cell reference in which the rows and columns are prepared to become constant by using a dollar** ($)** symbol before them. Sometimes you may need that a cell reference will not change when you will fill cells. In relative references, when you copy your formula to another cell, the formula will be changed according to rows or columns. In contrast, in absolute references when you copy your formula to another cell, the whole formula will not change according to rows or columns.

Say, for example, you have a formula like this ‘=**E5*J4**‘ in cell **F5**. If **F5** is an active cell then click Fill Handle, hold it, and drag over the cells you want to fill. See the following example. We have filled the cell ranges **F6: F15** in our example. Check the formula in every cell. After cell **F5**, it returns zero for every case because there is no value beyond cell **J4**. As you didnâ€™t make the cell **J4** absolute, Excel understands it as a normal value and takes the next cell as a reference.

**Read More:** Example with Absolute Cell Reference in Excel

## A Formula Using Absolute Cell References

But we want the **J4 **cell’s value will be unchanged, and its value will be as it is in the mother formula. For this, we have to use absolute references. We shall write newly our formula in cell **F5 **as ‘=**E5*$J$4**‘. We have used a dollar sign **($)** before the column letter and row number. This is the system of how you can make a cell reference absolute

## Types of Absolute Cell References in Excel

There are three types of absolute references where you can make absolute the whole cell in a formula, only making the column absolute in a formula or only making the row absolute in a formula. The following table describes three types of absolute references.

Absolute cell references | Description |
---|---|

$F$5 | Using these type of absolute references tell us that both column and row are fixed in the formula. It means that cell F5’s value is fixed in every formula in the filled cells. |

F$5 | Using this type of absolute reference tells us that only a row is fixed in the formula. |

$F5 | Using this type of absolute reference tells us that only one column is fixed in the formula. |

**Note**

We shall mainly use the **$F$5** type of absolute reference in the formula. We use the last two types of cell references rarely.

## Steps to Do Absolute Cell Reference in Excel

We shall create a formula in cell **F5** to calculate the VAT for all items in column **F**. In our example, the VAT rate is **15%** and the value is set in cell **J4**. We shall use **$J$4** absolute reference in our formula. As every formula is using the same VAT rate, we want that reference will remain constant when the formula is copied and filled to other cells in column **F**. Follow the steps carefully.

**Steps**

- Select the cell that will contain your formula. In our example, the cell is
**F5**where we shall use the formula.

- Then, enter your formula to calculate the desired value in the selected cell.
- We write the following formula in cell
**F5**.

`=E5*$J$4`

- Now press
**Enter**on your keyboard. The formula will be calculated internally and you will see the calculated result in cell**F5**.

- Then, drag the
**Fill Handle**icon down the column from cell**F6**to cell**F15**.

- To check whether your formula is correct, just double-click on any cell from the filled cells. You will find that every formula has
**$J$4**absolute references, relative references have changed with the row numbers. For row number 9, the formula is ‘=**E9*$J$4**‘, just the row numbers have been changed.

- After that, you can calculate the total cost by adding menu cost and VAT and completing the table.

## Switching Between References in Excel

Typing the **$** sign manually before the column letter and/or row number is very painful and boring work. Iâ€™m going to show here how you can shift a relative cell reference to an absolute and then change an absolute cell reference to a mixed, and finally change a mixed cell reference to a relative.

### 1. Making a Cell Reference Absolute

Here I want to show you the absolute cell reference shortcut with an example.

I want to make the **A1** cell reference an absolute cell reference in cell **C1**.

At first, I take the **C1** cell to edit mode. Now I keep the cursor on the **A1 **relative reference.

When the cursor is on the **A1** cell reference, I just press the **F4** key on the keyboard. **A1** cell reference will be converted into **$A$1** absolute reference.

### 2. Shifting Absolute Cell Reference to Mixed Cell Reference

Continuing from the above pictureâ€¦

Now I press the **F4** key again. **$A$1** will be converted to a mixed cell reference: **A$1**. The column will be relative but the row number will be absolute.

Now if we again press the **F4 **function key again, then **A$1** will be converted into **$A1, **which means the **Column letter** will be absolute and the row number will be relative.

### 3. Shifting a Mixed Cell Reference to Relative Cell Reference

Continuing from the above procedureâ€¦â€¦

Again if we press the **F4** function key again (**Fourth** time), then **$A1 **will be converted into **A1 **relative cell reference.

## Absolute Cell Reference Keyboard Shortcut in Excel

There is a keyboard shortcut to put a cell reference in the formulas. When you type the cell reference in the formula, then, you can press **F4 **on the keyboard**. **This will make the cell reference absolute which means it canâ€™t be changed while dragging the Fill Handle icon down the columns or rows.

**Download Practice Workbook**

Download the practice workbook below.

## Conclusion

In this article, we have shown the details of absolute cell reference and how to do absolute cell reference in Excel. We have also discussed the keyboard shortcut of absolute cell reference and its symbol. In addition to that, we have shown how to switch between references. I hope you found this article interesting. If you have any questions, feel free to ask in the comment section.

**Related Article**

- [Fixed] F4 Not Working in Absolute Cell Reference in Excel
- How to Use FormulaR1C1 Absolute Reference in Excel VBA

**<< Go Back to Absolute Cell Reference |Â Cell Reference in Excel |Â Excel Formulas | Learn Excel**