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.

Become an AI-powered Business Analyst

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

What Is An Absolute Reference? 

In Excel, an absolute reference is a cell reference in which the column and row coordinates stay constant while copying a formula from one cell to the other. A dollar symbol ($) is used before the coordinates to correct them. For instance, $D$2 is an absolute reference to cell D2. The key F4 in Excel is a shortcut to Absolute Reference.

Making the cell reference absolute ensures that it remains constant regardless of whether the formula is transferred to a different worksheet or workbook. A cell reference in Excel is always relative (like D2), which means it changes when the formula is duplicated.

A cell reference can be blended in addition to absolute and relative. In a mixed reference, either the column name or the row number (such as $D2 or D$2) remains constant.

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:

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

Absolute Cell Reference Uses 

There is a simple method for including absolute cell references in your computations!

When entering your formula, press the F4 key after each cell reference. It is Excel's default setting to make cell references absolute! You can cycle through all the absolute reference options in Excel by pressing F4 repeatedly. F4 only affects the cell reference directly to the left of your insertion point.

Chart Your AI/ML Success Path in 2023

Free Webinar | 4 October, Wednesday | 9 PM ISTRegister Now
Chart Your AI/ML Success Path in 2023

Toggle Between Absolute And Relative Addresses 

The F4 key on your keyboard allows you to switch between absolute, relative, and mixed cell references when composing a formula in Microsoft Excel. This is a simple approach to insert an absolute reference. This shortcut switches cell references from relative to absolute, to partially absolute, then back to a relative when revising a formula:

A1 --> $A

$1 --> A

$1-- > $A1-- > A1

This is considerably faster and easier than manually inputting the $ letter.

By entering cell edit mode, positioning the cursor in or near the reference you wish to convert, and using the shortcut, you can convert an existing formula.

This shortcut was only available in early Mac versions of Excel. fn + F4 works on Mac Excel 365.

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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

Relative Cell References: Relative Cell References in Excel are those that update themselves when the cell is copied and are referred to as relative cell references in Excel. When you need to develop a formula for a range of cells and the formula has to refer to a relative cell reference, they are handy.

Absolute cell references: Absolute cell references, unlike relative cell references, do not alter when the formula is copied to other cells.

Become an AI-powered Business Analyst

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

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!

About the Author

Shruti MShruti M

Shruti is an engineer and a technophile. She works on several trending technologies. Her hobbies include reading, dancing and learning new languages. Currently, she is learning the Japanese language.

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