Streamlining Excel Formulas: Removing Same-Sheet References with VBA

Why Remove Same-Sheet References?

When Excel creates formulas across different sheets, it uses the sheet name followed by an exclamation mark to indicate where the referenced cells are located. However, when the formula refers to the same sheet it's on, these references are unnecessary. For example, these two formulas are identical:

=Sheet1!A1 + Sheet1!B1
=A1 + B1

Removing the Sheet1! reference simplifies the formula without changing its functionality.

Handling Sheet Names with and without Spaces

Before diving into the VBA solution, there's a key detail to be aware of:

• If a sheet name has spaces, Excel wraps the name in single quotes: 'Sheet 1'!A1
• If a sheet name doesn't have spaces, only the exclamation mark is used: Sheet1!A1

This nuance needs to be accounted for when building the VBA solution.

The VBA Solution

The following VBA macro scans through all the formulas in the active worksheet and removes references to the current sheet, regardless of whether the sheet name has spaces or not:

Sub RemoveSameSheetReferences()
    Dim sht     As Worksheet
    Dim ShtName As String

    Set sht = ActiveSheet

    ShtName = "'" & sht.Name & "'!"
    sht.Cells.Replace What:=ShtName, Replacement:="", _
                      LookAt:=xlPart, SearchOrder:=xlByRows, _
                      matchCase:=False, SearchFormat:=False, _
                      ReplaceFormat:=False, _
                      FormulaVersion:=xlReplaceFormula2

    ShtName = sht.Name & "!"
    sht.Cells.Replace What:=ShtName, Replacement:="", _
                      LookAt:=xlPart, SearchOrder:=xlByRows, _
                      matchCase:=False, SearchFormat:=False, _
                      ReplaceFormat:=False, _
                      FormulaVersion:=xlReplaceFormula2
End Sub

How It Works

• The macro first identifies the active sheet using the ActiveSheet property.
• It then checks the name of the sheet and formats the name accordingly (with or without single quotes).
• The Replace function is used twice — once for the case with single quotes and once without — to ensure that both types of references are removed.
• After this, all redundant same-sheet references in formulas are cleaned up.

When to Use This Macro

This macro is useful when working with large, complex workbooks where you need to streamline formulas — financial models, multi-sheet reports, or data analysis.

• Efficiency: Automatically cleans up your formulas, saving time from manual adjustments.
• Readability: Makes your formulas shorter and easier to understand.
• Maintainability: Reduces clutter, making future edits and troubleshooting easier.

Final Thoughts

With a little VBA knowledge, you can save a lot of time by automating the cleanup of same-sheet references in your Excel formulas. This simple macro ensures that your formulas are as clean and efficient as possible, reducing redundancy and improving the overall clarity of your workbooks.

Try it out in your own workbooks, and feel free to adapt the code to suit your specific needs. If you have any questions or want to learn more about VBA for Excel automation, feel free to reach out!