How to Create an Absolute Reference in Excel?

In Excel, Relative reference is the default reference type. Therefore, whenever you copy a formula to a range of cells, they change based on the relative position of the rows and columns. But if you want to fix a reference so that the rows and columns don't change when copied down to a range of cells, we use Absolute reference.

In this article, we will walk you through the following topics:

  • What is an Absolute Reference in Excel?
  • How to Create an Absolute Cell Reference?
  • How to Use Absolute Cell Reference?
  • Conclusion

What is an Absolute Reference in Excel?

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.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

How to Create an Absolute Cell Reference?

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

=$B$1

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:

=$B$1:$B$5

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. 

AbsoluteReferenceInExcel-gif

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. 

relativeReference_formula

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.

asbolute_reference_2

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.   

absoluteReference_formula_AbsoluteReferenceInExcel

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

Business Intelligence Tool Guide

Your Complete Guide To The Top BI tools TodayDownload Now
Business Intelligence Tool Guide

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 Certification Course with Excel” course for you!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.