VBA Excel: Compare Two Sheets for Differences Step by Step

To compare two Excel sheets with VBA, loop through the cells at matching addresses, compare their contents, and write any mismatches to a report sheet. The macro below compares formulas as formulas and constant values as values; it is designed for Microsoft Excel for Microsoft 365 on Windows and can be adapted for other workbook layouts.

What a sheet comparison can—and cannot—tell you

A cell-by-cell comparison answers a specific question: “Does this cell in the first worksheet contain the same thing as the cell in the same position in the second worksheet?” If A12 differs from A12, the macro reports it. This works well when the two sheets have the same structure, such as two versions of a monthly report or two exports from the same system.

That is not the same as comparing records by a key. If one sheet has a new row inserted, every row below it may appear different even if the underlying records are present in both sheets. For that situation, match records using a unique identifier, such as an order number, rather than comparing fixed cell addresses.

The example macro reports formulas and values, but it does not compare formatting, comments, data validation, column widths, hidden rows, or worksheet structure. Decide what “different” means for your task before interpreting the report. A changed number format may matter in a financial template but remain irrelevant in a raw data export.

Prepare the workbook before running VBA

Make a separate copy of the workbook first. Excel macros cannot be undone with Ctrl+Z, and a macro can make changes that are difficult to reverse. This example creates a report sheet but does not edit the two sheets being compared.

Check that both worksheets have matching layouts and that you know their exact tab names.
Replace the sample names Sheet1 and Sheet2 in the code with your worksheet names.
Ensure no existing worksheet is named Differences, because the macro uses that name for its report.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains the macro.

On Windows, open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic if the Developer tab is visible. On Mac, use Developer > Visual Basic; the keyboard shortcut may be Option+F11 or Fn+Option+F11 depending on the keyboard. Excel for the web does not run VBA macros, so open the workbook in the desktop app.

VBA macro for differences at matching cell addresses

In the Visual Basic Editor, choose Insert > Module and paste the complete code below into the module. The macro compares cells from A1 through the furthest used row and column on either sheet. For formula cells, it compares the formula text; for other cells, it compares the stored values. Each difference is listed with its address and a readable description of both cells.

Option Explicit

Sub CompareTwoSheets()
    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim report As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim r As Long
    Dim c As Long
    Dim outRow As Long
    Dim differences As Long

    Set wb = ThisWorkbook
    Set ws1 = wb.Worksheets("Sheet1") ' Change to the first sheet name
    Set ws2 = wb.Worksheets("Sheet2") ' Change to the second sheet name

    ' Use the larger used range so extra rows or columns are checked.
    lastRow = Application.Max( _
        ws1.UsedRange.Row + ws1.UsedRange.Rows.Count - 1, _
        ws2.UsedRange.Row + ws2.UsedRange.Rows.Count - 1)
    lastCol = Application.Max( _
        ws1.UsedRange.Column + ws1.UsedRange.Columns.Count - 1, _
        ws2.UsedRange.Column + ws2.UsedRange.Columns.Count - 1)

    ' Add a report sheet; its name must not already be in use.
    Set report = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    report.Name = "Differences"
    report.Cells(1, 1).Value = "Cell"
    report.Cells(1, 2).Value = ws1.Name
    report.Cells(1, 3).Value = ws2.Name
    outRow = 2

    For r = 1 To lastRow
        For c = 1 To lastCol
            If Not SameCellContent(ws1.Cells(r, c), ws2.Cells(r, c)) Then
                report.Cells(outRow, 1).Value = _
                    ws1.Cells(r, c).Address(False, False)
                report.Cells(outRow, 2).Value = _
                    CellDescription(ws1.Cells(r, c))
                report.Cells(outRow, 3).Value = _
                    CellDescription(ws2.Cells(r, c))
                outRow = outRow + 1
                differences = differences + 1
            End If
        Next c
    Next r

    If differences = 0 Then
        report.Cells(2, 1).Value = "No differences found."
    End If

    report.Rows(1).Font.Bold = True
    report.Columns("A:C").AutoFit
    MsgBox differences & " difference(s) found.", vbInformation
End Sub

Private Function SameCellContent(ByVal a As Range, _
                                 ByVal b As Range) As Boolean
    Dim valueA As Variant
    Dim valueB As Variant

    ' A formula and a constant are treated as different content.
    If a.HasFormula <> b.HasFormula Then Exit Function

    If a.HasFormula Then
        SameCellContent = (StrComp(CStr(a.Formula), _
            CStr(b.Formula), vbBinaryCompare) = 0)
        Exit Function
    End If

    valueA = a.Value2
    valueB = b.Value2

    If IsError(valueA) Or IsError(valueB) Then
        If IsError(valueA) And IsError(valueB) Then
            SameCellContent = (a.Text = b.Text)
        End If
        Exit Function
    End If

    If VarType(valueA) <> VarType(valueB) Then Exit Function
    If IsEmpty(valueA) Then
        SameCellContent = True
    ElseIf VarType(valueA) = vbString Then
        SameCellContent = (StrComp(CStr(valueA), CStr(valueB), _
            vbBinaryCompare) = 0)
    Else
        SameCellContent = (valueA = valueB)
    End If
End Function

Private Function CellDescription(ByVal cell As Range) As String
    If cell.HasFormula Then
        CellDescription = CStr(cell.Formula)
    ElseIf IsError(cell.Value2) Then
        CellDescription = cell.Text
    ElseIf IsEmpty(cell.Value2) Then
        CellDescription = "(blank)"
    Else
        CellDescription = CStr(cell.Value2)
    End If
End Function

The code block uses HTML entities for the less-than and ampersand characters as required by the page format; when pasting into the Visual Basic Editor, use the VBA operators <> and & rather than the literal entity text. More simply, replace the displayed & in the message-box line with the VBA concatenation operator &. The comparison itself does not change cell data. It adds a new worksheet, so run it on your copy and review the report before making edits.

How to run the macro and read its report

After pasting the code and updating the sheet names, click inside CompareTwoSheets in the Visual Basic Editor and press F5, or return to Excel and use Developer > Macros. Select CompareTwoSheets and choose Run. If Excel asks about macros, only enable them for a workbook and source you trust.

The report uses one row per mismatch. For example, if B4 contains the number 250 on the first sheet and 275 on the second, the report lists B4, 250, and 275. A blank on one sheet and a number on the other is also a difference. If there are no mismatches within the checked range, the report says so.

Formula cells are compared by formula text, not by their calculated results. Thus, =A1+B1 and =SUM(A1:B1) count as different even if both currently return the same number. Conversely, identical formula text is considered a match even if a volatile formula recalculates to a different result later. If you want to compare displayed or calculated results instead, change the comparison logic deliberately and decide how to handle errors, rounding, and numbers stored as text.

Important limits and common problems

Rows have moved: This macro compares positions, not matching records. A sort or inserted row can create many reported differences. Sort both sheets consistently or use a key-based comparison.
The macro says a sheet name is missing: Worksheet names must match exactly, including spaces. Update the two Worksheets references in the code.
The report sheet name is already taken: Delete or rename the existing Differences sheet before running the example again, or change the report name in the code.
The scan takes longer than expected: Excel’s UsedRange can extend farther than visible data, particularly after cells were formatted and later cleared. The macro starts at A1 and checks every position up to the last used row and column, including gaps.
Formulas look different but results match: That is expected here. Formula text is compared strictly, including spelling and references.
Styles or layout changed: The macro does not inspect formatting, merged cells, hidden rows, or other sheet properties. A content comparison cannot prove that two worksheets are visually identical.

For very large or sparse sheets, a loop over every cell in the rectangle between A1 and the last used cell can be inefficient. A more specialized macro can compare only populated cells or use arrays, but it should preserve the same comparison rules and account for cells present on only one sheet.

Choosing between VBA and Excel’s other comparison tools

VBA is useful when the comparison is repeatable, needs a custom report, or must follow a specific rule such as comparing formulas but ignoring formatting. It also makes the logic visible and adjustable. Its disadvantages are that macros require desktop Excel, may be blocked by security settings, and need maintenance if sheet names or layouts change.

For a quick visual check on a small, aligned range, put the sheets side by side and use View > View Side by Side. This does not automatically identify different values, but it can make a short review easier. You can also use formulas in a helper sheet or conditional formatting when the ranges are small and stable; these approaches need careful setup when the compared range spans different worksheets.

Microsoft’s Spreadsheet Compare (also called Inquire in some Office installations) can provide a more detailed workbook comparison, including certain structural and formatting differences. Availability depends on the Microsoft 365 or Office edition and installation, and it is a Windows desktop tool rather than a general Excel for the web feature. Check whether it is included in your organization’s license before relying on it. Power Query can help reconcile table-shaped lists, especially when you need to match rows by columns, but it is not a drop-in visual comparison of every worksheet feature.

Ways to adapt the comparison safely

Before customizing the macro, specify the comparison rule in plain language. Should text case matter? Should a number stored as text, such as "0012", differ from the number 12? Should formulas be compared as written or by results? Should a tolerance be allowed for floating-point calculations? The example treats strings as case-sensitive, distinguishes text from numeric values, and requires exact formula text.

If the sheets have headers and records may be reordered, a key-based design is usually more appropriate. For instance, match rows using an invoice ID, then compare the amount and date fields for each ID. Decide how to report duplicate IDs and missing records; otherwise a lookup can match the wrong duplicate or conceal an absent row. If you need an audit trail, keep the original sheets unchanged and record the comparison date, workbook copy, and rules used alongside the report.

Frequently Asked Questions

Can VBA compare two sheets and highlight the different cells?
Yes. The sample creates a report rather than changing cell formatting, which keeps the source sheets untouched. You can adapt it to color mismatches, but save a copy first and make the macro clear any old highlights carefully so existing formatting is not accidentally lost.

Does the macro compare formulas or their results?
The sample compares formula text whenever both cells contain formulas. If a formula is present on only one side, it reports a difference. It does not treat two different formulas as equal merely because their current results match.

Will it find differences if rows are in a different order?
Not reliably as a record comparison. It compares A1 with A1, A2 with A2, and so on. Reorder both sheets into the same sequence first, or build a key-based comparison around a unique identifier.

Can I run the VBA comparison in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA; the specific macro and paths in this example are written for desktop Excel.

Does this compare formatting and comments too?
No. It compares cell formulas and values only. Formatting, comments, validation rules, row heights, column widths, and other workbook features need separate checks or a comparison tool that supports those properties.

Before you rely on the results

A VBA comparison is only as meaningful as its rules and alignment. Confirm that both sheets represent the same fields in the same positions, verify a few reported mismatches manually, and check how blanks, formulas, text, and errors should be treated in your particular workbook. Keep the original copy: the report can speed up review, but it does not replace a clear definition of what counts as a difference.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.