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.