Comparing Workbooks for Differences

Ron has two workbooks that are very similar. In fact, the second workbook started out as a copy of the first to which new information was added. He wonders if there is a way to compare two workbooks to determine what, exactly, has changed between the two of them.

Comparing different workbooks can get a bit tricky and quite complex very quickly. Part of the reason for this complexity is the number of different things that need to be checked when you are determining differences. Checking cells for differences in either values, formulas, or results of formulas can be straightforward, but what about information stored in other objects, such as text boxes, charts, data validation rules, conditional formatting rules, or macros? Do changes in formatting count as a difference? I'm sure you get the idea—defining what constitutes a change can quickly become quite complex and there are numerous other areas where differences could exist, as well.

If the workbooks are simple (meaning, a single worksheet or two) you could copy the worksheets from the second workbook into the first and then add a new worksheet that uses formulas (or formulas in conditional formatting) to compare cells from the duplicate sheets. This is a lot of work, to be sure. It also isn't terribly helpful if the changes in the second workbook may include some inserted or deleted rows or columns.

Another way you might try is to use what Microsoft calls the Compare and Merge Workbooks command. This command is not normally available, but it can be added to the ribbon. I won't go into much detail about it here because it is usable only under some rather strict conditions. For instance, it can only be used on copies of workbooks saved after making changes to what was previously a shared workbook. You can find more information about this tool, though, at this Microsoft site:

https://support.microsoft.com/en-us/office/merge-copies-of-a-shared-workbook-aa7c3598-5ad7-4fea-8c00-2daa7a934239

If you are using Office Professional Plus 2013, Office Professional 2016, or Office 365 ProPlus, then you have access to another Microsoft tool that can compare workbooks for differences. This tool is called Spreadsheet Compare, and it allows you to specify what types of differences you want to note. It isn't widely used because, quite honestly, the users of those specific versions of Excel are only a small subset of the overall universe of Excel users. If you have one of those specific versions, however, you can find information about Spreadsheet Compare here:

https://support.microsoft.com/en-us/office/overview-of-spreadsheet-compare-13fafa61-62aa-451b-8674-242ce5f2c986

A third Microsoft-provided approach is to use a tool they call Spreadsheet Inquire. This tool is available in Excel 2013 or later versions of Excel. You can find out about this particular tool at this Microsoft site:

https://support.microsoft.com/en-us/office/compare-workbooks-using-spreadsheet-inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

There are other ways to compare workbooks, but the method you choose is going to depend largely on the nature of your data and how detailed you want to be in your comparisons. (Remember—comparisons can get quite complex quite quickly.) Rather than reinvent the wheel, however, I'll just refer you to the following page which gives a pretty good overview of six different ways to do comparisons:

https://trumpexcel.com/compare-two-excel-sheets/

Interestingly enough, this page doesn't mention the three Microsoft tools just mentioned. Two of the six methods it does present bear a bit of additional consideration.

The fifth method detailed at the TrumpExcel page is to use macros to do your comparisons. Macros can be a great approach but remember that whatever macro you devise will be tied to the nature of the data in your workbooks. The page presents a rather simple macro, but I've also seen much more complex ones. For instance, the following set of macros will compare the worksheets in any two open workbooks. All you need to do is to replace "File1" and "File2" in the Compare2Wbks macro with the names of the two workbooks. Run Compare2Wbks and you end up with a third workbook that shows differences between those two workbooks. (The macros assume that the worksheets in File1 and File2 have the same names.)

Sub Compare2Wbks()
    'Each of the two workbooks must be open before running this procedure.
    'Replace the file names in this procedure with your filenames.
    'A new workbook is created that lists the results for all worksheets.

    Dim Sh As Worksheet
    Workbooks.Add
    For Each Sh In Workbooks("File1").Worksheets
        Differences Sh, Workbooks("File2").Worksheets(Sh.Name)
    Next
End Sub

Sub Differences(Sh1 As Worksheet, Sh2 As Worksheet)
    Dim iR As Integer, iC As Integer
    Dim D_1 As Range, D_2 As Range

    Range("A1:D1").Value = Array("Address", "Difference", Sh1.Parent.Name, Sh2.Parent.Name)
    Range("A1:D1").Font.ColorIndex = 5
    Range("A2").Select
    For iR = 1 To Application.Max(Sh1.Range("A1").SpecialCells(xlLastCell).Row, _
                      Sh2.Range("A1").SpecialCells(xlLastCell).Row)
        For iC = 1 To Application.Max(Sh1.Range("A1").SpecialCells(xlLastCell).Column, _
                        Sh2.Range("A1").SpecialCells(xlLastCell).Column)
            Set D_1 = Sh1.Cells(iR, iC)
            Set D_2 = Sh2.Cells(iR, iC)
            'Compare the types to avoid getting VBA type mismatch errors
            If TypeName(D_1.Value) <> TypeName(D_2.Value) Then
                IfError D_1.Address, "Type", D_1.Value, D_2.Value
            ElseIf D_1.Value <> D_2.Value Then
                If TypeName(D_1.Value) = "Double" Then
                    If Abs(D_1.Value - D_2.Value) > D_1.Value * 10 ^ (-12) Then
                        IfError D_1.Address, "Double", D_1.Value, D_2.Value
                    End If
                Else
                    IfError D_1.Address, "Value", D_1.Value, D_2.Value
                End If
            End If
            'Record formula without leading "=" to avoid them being evaluated
            If D_1.HasFormula Then
                If D_2.HasFormula Then
                    If D_1.Formula <> D_2.Formula Then
                        IfError D_1.Address, "Formula", Mid(D_1.Formula, 2), Mid(D_2.Formula, 2)
                    End If
                Else
                    IfError D_1.Address, "Formula", Mid(D_1.Formula, 2), "**no formula**"
                End If
            Else
                If D_2.HasFormula Then
                    IfError D_1.Address, "Formula", "**no formula**", Mid(D_2.Formula, 2)
                End If
            End If
            If D_1.NumberFormat <> D_2.NumberFormat Then
                IfError D_1.Address, "NumberFormat", D_1.NumberFormat, D_2.NumberFormat
            End If
        Next iC
    Next iR
    With ActiveSheet.UsedRange.Columns
        .AutoFit
        .HorizontalAlignment = xlLeft
    End With
End Sub

Sub IfError(Address As String, Differ As String, P_1, P_2)
    ActiveCell.Resize(1, 4).Value = Array(Address, Differ, P_1, P_2)
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Row = Rows.Count Then
        MsgBox "More than " & Rows.Count & "differences", vbExclamation
        End
    End If
End Sub

Remember that a macro such as this (or, more correctly, a set of macros such as these) won't catch every change, and if your data doesn't meet the conditions imposed by a rather strict framework, it can fail completely. The purpose of including this macro is, again, to illustrate that a macro-based solution can get as detailed and complex as you may need to do your comparisons.

The sixth method detailed at the TrumpExcel page is to use a third-party application to do the comparison. The only tool presented is XL Comparator, but there are other tools available as well. For instance, you might consider one called Ultra Compare, available at this site:

https://www.ultraedit.com/products/ultracompare/

Well, there you have it—tons of ways that you can do your workbook comparisons. The bottom line is that the method you choose will depend on your data and the type of comparison you want to do. You should explore each of the ideas presented here and pick the one that best suits your needs.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13792) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.