Comparing Workbooks for Differences

by Allen Wyatt
(last updated October 24, 2020)

1

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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 Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Positioning the Cursor in a New Document

Creating special templates is a great way to establish "standards" for your documents. With a little ingenuity you can ...

Discover More

Relative References when Recording Macros

When you record a macro, make sure that you know how Excel is recording your cell movements. This tip explains the ...

Discover More

Creating a Copy without Formulas

Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Comparing Workbooks

Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the ...

Discover More

Who Has the Workbook Open?

When you are working with workbooks to which multiple people have access, it can be helpful to know who has a particular ...

Discover More

Sudden Increases in Workbook File Size

Workbooks can get rather large rather quickly. If you think your workbook has gotten too big too fast, here are some ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 8?

2020-10-24 05:04:10

Ron S MVP

I just found this tool earlier today, haven't had a chance to try it

Review / Compare – Diffchecker (web and desktop versions)
https://www.diffchecker.com/
Diffchecker is a diff tool to compare differences between two files/folders.
Enter the contents of two text / Image / PDF / Excel / Folders files and click Find Difference
.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.