Written by Allen Wyatt (last updated October 24, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Want a quick way to access the workbooks you use most often? You can "pin" the workbooks so they can be opened with a click.
Discover MoreIt is a difficult task, in Excel, to determine if a workbook is the target of any external links. This tip examines some ...
Discover MoreHave you ever tried to save a workbook, only to be notified that someone else has made changes in it? What if you are the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments