Convert Cell References to Column or Row Absolute in Excel

Sometimes in Excel, a formula needs to keep only part of a cell reference fixed when you copy it to another row or column. For example, $A1 keeps the column fixed while allowing the row to change, and A$1 keeps the row fixed while allowing the column to change. These mixed references are useful when you want formulas to fill correctly across a range without making the entire reference absolute.

In this tutorial, we will walk through several ways to change existing references to column absolute or row absolute references. You can use the F4 key for quick changes, add the dollar sign manually for more control, try Find and Replace in simple cases, use Kutools for Excel for batch conversion, or use VBA if you do not want to install an add-in.

• Method 1: Use the F4 key to toggle reference types
• Method 2: Add the dollar sign ($) manually
• Method 3: Use Find and Replace for simple reference patterns
• Method 4: Batch convert references with Kutools for Excel
• Method 5: Change references in selected formulas with VBA
• Which method works best for you?

Use the F4 key to toggle reference types

This is the fastest built-in method when you need to change one reference at a time in a formula. Excel lets you cycle through relative, absolute, and mixed references by pressing F4.

1. Select the cell that contains the formula, then double-click it or press F2 to enter edit mode.
2. Click the cell reference you want to change in the formula.
3. Press F4 repeatedly until the reference becomes one of the following:
   • $A$1 - absolute reference
   • A$1 - row absolute reference ✅️
   • $A1 - column absolute reference ✅️
   • A1 - relative reference
4. Press Enter to confirm the formula.

Add the dollar sign ($) manually

If you only need to adjust a few references, you can type the dollar sign directly into the formula.

1. Select the formula cell and click in the formula bar, or press F2.
2. Edit the reference manually:
   • Change A1 to $A1 to lock the column only.
   • Change A1 to A$1 to lock the row only.
3. Press Enter to apply the change.

Use Find and Replace to change references

You can also use Find and Replace to change references in formulas, but this method only works in limited cases. It is most suitable when the references you want to change follow the same pattern. For example, if all formulas refer to the same column or the same row, Find and Replace may help you update them quickly.

1. Select the range that contains the formulas you want to update.
2. Press Ctrl + H to open the Find and Replace dialog.
3. In the Find what box, enter the reference pattern you want to change, like the column "F".
4. In the Replace with box, enter the updated reference pattern with the dollar sign added, like "$F".
   • For example, replace F with $F to make the column absolute.
   • Or replace 2 with $2 to make the row absolute.

     

5. Click Replace All.

As a result, all F references in the selected formulas are changed to $F, which locks column F.

Batch convert references with Kutools for Excel

For a more reliable batch solution, Kutools for Excel offers the Convert Refers feature, which can quickly convert selected formulas to column absolute references like $A1 or row absolute references like A$1.

1. Select the range that contains the formulas you want to convert.
2. Click Kutools > More > Convert Refers.
3. In the Convert Formula References dialog, choose one of the following options:
   • To absolute column to convert references to $A1.
   • To absolute row to convert references to A$1.
4. Click OK or Apply. All selected formulas will be converted at once.

   

Use VBA to change references

If you do not want to install an add-in, you can use VBA to change references in selected formulas. This method is more suitable for advanced users and can be useful when you need to update multiple formulas in one go.

Convert selected formulas to Column absolute references

1. Select the cells that contain the formulas you want to convert.
2. Press Alt + F11 to open the VBA editor.
3. Click Insert > Module.
4. Paste the following code into the module:

   Sub ConvertToColumnAbsolute()
       Dim cell As Range
       For Each cell In Selection
           If cell.HasFormula Then
               cell.Formula = Application.ConvertFormula( _
                   Formula:=cell.Formula, _
                   FromReferenceStyle:=xlA1, _
                   ToReferenceStyle:=xlA1, _
                   ToAbsolute:=2)
           End If
       Next cell
   End Sub

5. Press F5 to run the macro to convert references to $A1 style.

Convert selected formulas to Row absolute references

1. Select the cells that contain the formulas you want to convert.
2. Press Alt + F11 to open the VBA editor.
3. Click Insert > Module.
4. Paste the following code into the module:

   Sub ConvertToRowAbsolute()
       Dim cell As Range
       For Each cell In Selection
           If cell.HasFormula Then
               cell.Formula = Application.ConvertFormula( _
                   Formula:=cell.Formula, _
                   FromReferenceStyle:=xlA1, _
                   ToReferenceStyle:=xlA1, _
                   ToAbsolute:=3)
           End If
       Next cell
   End Sub

5. Select the formula cells you want to update.
6. Press F5 to run the macro to convert references to A$1 style.

Which method works best for you?

Conclusion

If you only need to adjust a reference occasionally, the F4 key and manual editing are the most direct options. They are easy to use and give you full control, but they are better suited to small changes rather than larger batches of formulas.

Find and Replace can help in some simple bulk-editing situations, especially when the references follow the same pattern. However, because it works by matching text, it is not always reliable and may also affect matching text inside formulas.

When you need a more practical batch solution, Kutools for Excel makes the job much easier by converting selected references in just a few clicks. If you prefer not to use an add-in and are comfortable with macros, VBA is a flexible alternative for automating the task.

I hope you found this tutorial helpful. If you’d like to explore more Excel tips and practical solutions, please click here to browse our full collection of Excel tutorials.