Search code examples
excelformulabulk

How can I fix formula cells in bulk? Excel

So I have a table of cells in excel that have loads of formulas referencing cells above it.

I want to create a copy of this table below it, and keep all the formulas and its values exactly the same. However when creating those formulas I didn't put $ to fix each cell. I dont want to go into each cell and add the $ to all cells before pasting.

Is there some way I can highlight the cells and bulk fix all the cells being referenced?

Thanks in advance, let me know if im not being clear.

Solution

  • The best way is to use VBA:

    Put this in a Module, select your range of cells, and run it.

    Sub abslt()
    Dim rng As Range
    For Each rng In Selection
        If rng.HasFormula Then
           rng.Formula = Application.ConvertFormula(rng.Formula, xlA1, xlA1, xlAbsolute)
        End If
    Next rng
End Sub

    It will convert all references to absolute references.