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.
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.
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.
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?
- Permanent
- Static
- Mixed
- Joined
Solution: Mixed
2. To make the cell D10 as an absolute reference, how should you specify it?
- #D#10
- $D$10
- D$10
- $D10
Solution: $D$10
3. Which cell reference will not change if copied or moved?
- Relative reference
- Absolute reference
- Statice reference
- Fixed reference
Solution: Absolute reference
Related Terminology
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.
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!
