What Is An Absolute Reference In Excel?

Photo Credit: IB Photography – stock.adobe.com

There are two types of cell references in Excel: relative and absolute references. They both behave differently when you copy and paste them into other cells. Relative references get modified when a formula is copied to another cell. However, absolute references remain constant even when they are copied to other cells. In Excel, the default reference type is Relative reference. Therefore, when you copy a formula to a range of cells, they automatically change depending on the relative position of the cell among the rows and columns. However, if you wish to fix a reference so that the rows and columns do not get altered upon copying down to a range of cells, you must use the Absolute Reference in Excel. So, let us learn about absolute reference formulas in excel in greater detail and learn how to use them. But first, let’s look at some basic definitions.

Relative references

All cell references in Excel are, by default, relative references. A cell reference in Excel, when copied across multiple cells, change based on its relative position among the rows and columns. For instance, if you copy the formula =A1+B1 from row 1 and paste it into row 2, the formula will become =A2+B2. Because of this feature, relative references are particularly convenient when you need to repeat the same calculation across multiple rows or columns in the sheet.

Absolute Reference in Excel

An absolute reference in Excel, on the other hand, cannot be changed when copied. This means you won’t see any changes based on rows or columns when you copy and paste them elsewhere in the sheet. Absolute reference in Excel is generally used when you want to fix a cell location. Usually, these cell references in Excel contain a dollar sign before them. By adding this dollar sign, you are fixing the value of a particular cell reference in Excel.

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

However, if you type the formula as an absolute reference in Excel, in the form of = $A$1+$A$2, and then copy it to B3 (or any location in the sheet), it still remains as = $A$1+$A$2.

For spreadsheets containing constant values, absolute cell references in Excel are essential.

Let us learn a bit more about this now.

How to Create an Absolute Reference in Excel?

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

=$B$1

Here, we are fixing the cell B1 as the absolute reference by simply appending a dollar sign ‘$’ before the row and column.

To fix a range of cells as the absolute reference in Excel instead of a single cell, the following syntax must be used:

=$B$1:$B$5

How to Use Absolute Reference in Excel?

To understand how to use Absolute Cell Reference in Excel, consider a simple example where the final cost is computed by multiplying the cost of each product with a tax rate multiplier.

Here, we use the formula = B2*E4.

The given formula works only for the particular cell to which it is added. If you drag this formula down to the rest of the cells in Column C, you will get incorrect values.

The screenshot attached below shows the resulting formulas after this step on our Excel sheet. This is because Excel follows relative referencing by default.

Here you will notice that the value of the Tax Rate Multiplier changes for every cost, when it should be constant. So, to fix this issue, we need to make E4 an Absolute Reference.

Now, to make E4 an Absolute Reference in Excel, we simply need to add the dollar symbol ($) just before the column name and row number, i.e., instead of E4, in the formula, we should multiply with $E$4.

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

Now, look at the demo shown below to understand this.

When you drag this absolute reference formula to copy its value to the rest of the cells, cell E4 will be locked. The below screenshot displays all the formulas given on the Excel sheet.