Tips & Tricks 171 – Convert your formulas to Absolute References

If your sheet has plenty of formula and you want to convert them into Absolute references i.e.

One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations.

Best way is to use VBA method.

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Sub ConvertToAbsolute()
    Dim Rng As Range, Cell As Range
    Dim Ws As Worksheet
    Set Ws = ActiveSheet
    Set Rng = Cells.SpecialCells(xlFormulas)
    For Each Cell In Rng
        Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute)
    Next Cell
End Sub