What is the name of the reference that refers to the position of the cell that remains fixed when is copied or moved to other cells?

Absolute Reference in Excel

An absolute reference in Excel is a reference that cannot be changed when copied, so you won't see changes in rows or columns when you copy them. Absolute references are used when you want to fix a cell location. These cell references are preceded by a dollar sign. By doing this, you are fixing the value of a particular cell reference. 

For example, if you type the formula = A1+A2 into A3 and copy it to another location: B3, the formula will change to = B1+B2. 

But, if you type the formula as an absolute reference such as = $A$1+$A$2, and then copy the formula to any other location, it remains fixed as = $A$1+$A$2.

You will learn how to make an absolute cell reference in the next section, so stay tuned.

What Does Absolute Cell Reference Mean?

An absolute cell reference in a spreadsheet application is absolute, which means that they remain consistent regardless of the size or shape of the spreadsheet or if the reference is copied or moved to another cell or sheet. For spreadsheets that contain constant values, absolute cell references are essential.

How to Create an Absolute Cell Reference?

The syntax mentioned below helps you create an Absolute Cell Reference in Excel:

We are fixing the cell B1 as an absolute reference. This is done by appending a dollar sign '$' before the row and column. 

To fix the range instead of a single cell, follow the syntax stated below:

How to Use Absolute Cell Reference?

First, let’s look at a simple example where the final cost is calculated by multiplying each product cost with a tax rate multiplier. 

In this example, we use the formula = B2*E4.

This formula works only for the cell to which it is added. If you drag down the formula to the rest of the cells in Column C, it results in error values. 

To view the formulas added onto our Excel sheet, go to the Formulas tab → Show Formulas option and click on it. 

The screenshot attached below shows the formulas on our Excel sheet. Excel follows relative referencing by default. 

You will notice that the Tax Rate Multiplier changes for every cost. So, we need to fix this value, i.e., E4, by making it an Absolute Reference. 

To make the cell E4 an Absolute Reference, add the dollar symbol [$] before the column name and row number like $E$4.

So now, the formula will be = B2*$E$4.

Look at the demo depicted below for a better understanding of the same.

When you drag the formula to copy it to the rest of the cells, the cell E4 will be locked. The screenshot below displays all the formulas entered on the Excel sheet.   

You will notice that the final cost is calculated by multiplying each product cost with a fixed tax rate.

NOTE: Apart from Relative and Absolute references, there is a third category called Mixed Reference. As the name suggests, the cell reference can contain an absolute column and relative row or an absolute row and relative column i.e. mixed. So we add the ‘$’ symbol before either the row or column, whichever is absolute. 

For example: 

• B$1 fixes the row
• $B1 fixes the column
• B1:$B$10 fixes the cell B10

This is the gist on Absolute Reference in Excel that you need to know. Attempt the quiz below to test your knowledge on Absolute references in Excel.

1. Cell References in Excel are classified into 3 types: Relative Reference, Absolute Reference, and?

1. Permanent
2. Static
3. Mixed
4. Joined

Solution: Mixed

2. To make the cell D10 as an absolute reference, how should you specify it?

1. #D#10
2. $D$10
3. D$10
4. $D10

Solution: $D$10

3. Which cell reference will not change if copied or moved?

1. Relative reference
2. Absolute reference
3. Statice reference
4. Fixed reference

Solution: Absolute reference

Conclusion

We hope this article has helped you understand how to use an Absolute reference in Excel. This topic might be a little tricky to understand, but it is very crucial if you want to further enhance your Excel skills. 

In case you have any queries, please mention them in the comments section of “How to Create an Absolute Cell Reference?” article, and our experts will get back to you at the earliest.

Whether you are interested in learning the basics of Excel or want to develop more advanced Microsoft Excel skills, Simplilearn has a “Business Analytics with Excel Free Course” course for you!