Wondering what is an absolute reference in Excel? Read this essential Excel tutorial now to learn everything you should know about absolute references.

You can reference cells and cell ranges to perform dynamic calculations instead of hardcoding values into a formula. These references automatically update when you copy the formula to another cell, fill down a column, or fill to the right by the row.

But you might not want certain cell references of the formula to update automatically. You might want one cell range to stay fixed while the others update according to the columns and rows of the new grid location. Microsoft Excel has included an absolute referencing technique to give you such outstanding controls over formula referencing.

Read this Excel guide until the end and follow along with the exercises to gain control over accurate cell range referencing.

## What Is an Absolute Reference in Excel?

In Excel, an absolute reference is a way to fix a cell or a range of cells so that they don’t change when you copy a formula to another cell. Also, the references won’t change if you fill the formula down the column or to the right of a row.

Imagine you have a formula in a cell `A1`

that adds up numbers from cells `B1`

and `C1`

. If you copy this formula to the cell `A2`

, you’d expect it to add up the numbers from `B2`

and `C2`

, right? But with absolute referencing, you can make sure it always adds up the numbers from `B1`

and `C1`

, no matter where you copy the formula.

Here’s an example

Let’s say you have the following formula in the cell `A1`

.

`=B1+C1`

The above is a relative reference in Excel. If you copy this formula to the cell `A2`

without absolute referencing, it would change to the following:

`=B2+C2`

But if you use absolute referencing with the `$`

sign before the column letters and row numbers, the formula stays fixed wherever you copy or fill as below:

`=B$1+C$1`

These are some of the important uses of absolute referencing in Excel:

- To create templates where certain values should always remain constant.
- To calculate percentages or ratios based on fixed values.
- To analyze data across multiple worksheets while keeping certain references unchanged.

## When Do You Use an Absolute Reference in Excel?

One scenario where an absolute reference is handy is when you’re dealing with formulas that involve fixed values or constants. By using an absolute reference, you ensure that the formula always refers to the intended cell, which is crucial for accurate calculations. For example, when calculating sales tax based on a fixed tax rate, you’d want to use an absolute reference for the cell containing the tax rate.

Moreover, absolute references are beneficial for reducing storage usage and streamlining formulas. Instead of repeating the same value multiple times within a formula or across different cells, you can reference it once using an absolute reference.

Suppose, you need to calculate the net salary of your employees after adding a bonus component.

One scenario is the dataset you’re seeing above. You can create a column for the bonus component and use a relative formula to add the cells of **column B** with **C**. It’s acceptable if the bonus amount is different for each employee.

However, if the bonus component is the same for all the employees, creating a second column doesn’t make any sense.

You’d usually create a cell for the bonus component and add the salary cell and bonus amount cell in the net salary cell to get the final salary. For example, `G3`

+`J3`

.

When you go to copy this formula or fill it down for all other employees you’ll get inaccurate results. Because, due to the default relative referencing system of Excel, as soon as you copy the formula to a cell below the original cell, Excel changes the cell references.

For example, in `G4+J4`

for cell `H4`

, `G4`

contains the salary and `J4`

is empty. So, you get an incorrect result.

Here, you must include `J3`

, containing the bonus amount, as an absolute reference as shown in the screenshot above. Now, if you fill down the **column H**, Excel will add `H4`

with `J3`

, `H5`

with `J3`

, and so on.

Using an additional column to use relative references for a fixed value is highly storage inefficient. You can witness this if your workbook contains millions of data points.

For instance, if one cell in a column uses 20 characters and the column contains 1,048,576 data cells, your Excel workbook’s size will increase by 20 MB. By using an absolute referencing system for the bonus component, you can save this storage space on your PC’s drive or on a cloud server.

## How to Apply Absolute Referencing in Excel

You can use the `$`

symbol before a column letter or row number to apply absolute cell or cell range referencing in Excel.

Suppose, I need to rectify the formula in the cells of **column C** in the above dataset so that it always refers to `E11`

and not relatively change to `E12`

for `C12`

, `E13`

for `C13`

and so on.

I’d select cell `C11`

and press `F2`

to enter the edit mode.

Then, I’d take the cursor before `E11`

to change the relative reference to **column E** to absolute by adding the `$`

symbol.

Again, I’d put the cursor before the row number `11`

of reference `E11`

and add the `$`

symbol.

I’d calculate the cell by pressing `Enter`.

To apply this formula to the rest of the cells of **column C**, I’d use the fill handle.

You can face challenges when following the above method to convert relative references to absolute.

Use the following secret hack to cycle through different types of absolute references easily:

- Select the reference, like
`E11`

, and pres`F4`to apply full absolute reference. - Highlight a cell range, like
`E11`

, and press`F4`twice to apply a mixed absolute reference where the column is relative but the row is absolute. - Select a cell or cell range address, like
`E11`

, and press`F4`thrice to apply the mixed absolute reference where the column is absolute but the row is relative. - Press
`F4`four times after selecting a cell or cell range to remove all types of absolute referencing.

## How to Use Absolute Reference in Excel

Find below multiple scenarios of different combinations for absolute reference usage:

### Using Absolute Column and Row References

Suppose, you want to add a bonus component to the monthly employee salaries. In the dataset you’ve created, monthly salaries are in **column G**.

You’ve entered the bonus amount in the cell `J3`

.

Now, you want to calculate net salaries in **column H**.

In **column H**, select the cell for the first employee and enter the following formula:

`=G3+$J$3`

In the above formula, for cell reference `J3`

, both the column and row are absolute.

Now, hit `Enter` to calculate the net salary.

Drag the fill handle down **column H** from the first calculated cell to apply the formula to the rest of the cells as needed.

### Using Absolute Column and Relative Row References

You commonly use absolute column and relative row references when you want to perform calculations or analysis across multiple rows but within a fixed column range. This setup is typical in scenarios where you have arranged data horizontally across columns and you want to apply a consistent calculation or analysis to each row.

For example, in the above dataset, you want to find out the prices for the given devices in **EUR** and **CAD**.

To convert **USD** to any of these currencies, you need the current exchange rate. You’ve arranged that data in row `2`

, **USD** to **EUR** in **column C**, **USD** to **CAD** in **column D**, etc.

To calculate the first device’s price in **EUR**, select `C2`

and enter the following formula into it:

`=$B5*C$2`

In the above formula, for the first cell address, the column is fixed while the row number can vary. For the second cell reference, the column is variable and the row is fixed.

Press `Enter` to get the **iPhone 15’s** price in **EUR**.

When you copy this formula to the right of `C2`

or fill it to the right, Excel automatically adjusts the formula to refer to the **USD** to **CAD** exchange rate in cell `D2`

while keeping the product price reference the same, which is `B5`

.

In summary, if one set of reference values is arranged in a row and the other set is in columns and you want to copy or fill from left to right, you must use absolute columns and relative row references.

This example also shows you the use of absolute rows and relative columns combination of absolute referencing when referring to the exchange rates in row `2`

.

## Automate Absolute Referencing in Excel With VBA

You can face many bottlenecks when using the absolute reference convention in Excel. Find below some useful VBA macros that can help you resolve these roadblocks.

### Highlighting Cells Containing Absolute References

This simple VBA script enables you to find all the formula cells containing absolute references in a selected cell range:

```
Sub HighlightAbsoluteReferences()
Dim rng As Range
Dim cell As Range
Dim formula As String
Dim i As Integer
' Prompt the user to select a range
On Error Resume Next
Set rng = Application.InputBox("Select a range", Type:=8)
On Error GoTo 0
' If no range is selected, exit the subroutine
If rng Is Nothing Then Exit Sub
' Loop through each cell in the range
For Each cell In rng
' Check if the cell contains a formula
If cell.HasFormula Then
formula = cell.formula
' Check if the formula contains an absolute reference
If InStr(formula, "$") > 0 Then
' Highlight the cell
cell.Interior.Color = RGB(255, 151, 151)
End If
End If
Next cell
End Sub
```

When you run this macro, Excel shall ask you to choose the input cell range for absolute reference detection.

Once you supply the cell range, Excel shall highlight the cells in **RGB(255, 151, 151)**.

Read this quick Excel tutorial to learn the techniques to use the above script to set up a VBA macro:

📒 **Read More**: How To Use The VBA Code You Find Online

### Creating an Absolute Reference in a Formula

The following script shall guide you through converting a relative reference to an absolute in the selected formula:

```
Sub ConvertToAbsolute()
Dim rng As Range
Dim formula As String
Dim cols As String
Dim rows As String
Dim colArray() As String
Dim rowArray() As String
Dim i As Integer
' Prompt user to select a cell with a formula
On Error Resume Next
Set rng = Application.InputBox("Select a cell with a formula", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
formula = rng.formula
' Prompt user to enter column letters to be made absolute
cols = Application.InputBox("Enter column letters to be made absolute, separated by commas. Current formula: " & formula, Type:=2)
colArray = Split(cols, ",")
' Prompt user to enter row numbers to be made absolute
rows = Application.InputBox("Enter row numbers to be made absolute, separated by commas. Current formula: " & formula, Type:=2)
rowArray = Split(rows, ",")
' Convert specified column letters and row numbers to absolute references
For i = LBound(colArray) To UBound(colArray)
formula = Replace(formula, colArray(i), "$" & colArray(i), , , vbTextCompare)
Next i
For i = LBound(rowArray) To UBound(rowArray)
formula = Replace(formula, rowArray(i), "$" & rowArray(i))
Next i
' Update the formula in the selected cell
rng.formula = formula
' Show the updated formula in an input box
MsgBox "The updated formula is: " & formula
End Sub
```

The script shall show an input box with the selected formula in text format. You can tell Excel which columns you want to modify to absolute references separated by commas.

Similarly, on the next prompt, you can enter which row numbers you want to change to absolute references.

Once done, the script updates the selected cell with the new formula and shows it in another input box.

### Creating Named Ranges to Use as an Absolute Reference

Instead of modifying cell references with the `$`

sign, you can mark them as named ranges. Then, when creating a formula, type the initials so that Excel shows a list of appropriately named ranges that match the first few letters you’ve entered.

The following VBA script allows you to visually create named ranges from any cell or cell range without the need to remember the **Name Manager-based** process.

```
Sub CreateNamedRange()
Dim rng As Range
Dim strName As String
' Show an input box to select the cell or cell range
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Please select the cell or cell range", Type:=8)
On Error GoTo 0
' If no range is selected, exit the subroutine
If rng Is Nothing Then
MsgBox "No range selected. Exiting..."
Exit Sub
End If
' Show another input box to write the name of the named range
strName = Application.InputBox(Prompt:="Please enter the name for the named range", Type:=2)
' If no name is entered, exit the subroutine
If strName = "" Then
MsgBox "No name entered. Exiting..."
Exit Sub
End If
' Create the named range
ThisWorkbook.Names.Add Name:=strName, RefersTo:=rng
' Show a confirmation message
MsgBox "Named range '" & strName & "' has been created successfully."
End Sub
```

You can run the above script and Excel shall guide you visually to create a named range in two simple steps.

In the first step, you select the cell or cell range and give it a name to complete the process.

⚠️ **Warning**: Create a backup of the original workbook before using any of the above VBA scripts on it. Because you won’t be able to revert to a previous state using the Excel undo feature if you run VBA macro.

## Conclusions

Now you know what an absolute reference is in Excel. You’ve also learned when to use such referencing conventions. Furthermore, you’ve learned how to create different types of absolute references in a few clicks.

Through this Excel tutorial, I’ve also explained how VBA can assist you in using an absolute referencing system accurately.

If you liked the article or want to share a tip regarding the process, comment below.

## 0 Comments