Mary: Show
Is it safe to assume that the excel sheets you are comparing have the same number of rows and columns? I am assuming that most of the time, the cells match, so most of the time, a cell by cell comparison is a waste of time. (hmmm, a lot of time in that sentence.) The code assumes that column A is a unique key and is the same in both workbooks. I like to use the dictionaries from Dot Net as they are much improved over the dictionary object in vbscript. In the code, I am only comparing “Sheet1” in each workbook. There is more coding than in the original method but I believe the time to complete the comparisons should be much less. Please let me know if I am correct. Thanks, Parke
File details Using Synkronizer, you can compare two or more Excel files or sheets and highlights the differences. You can eliminate the differences from each file or decide to merge the spreadsheets into one that doesn’t have any duplicate entries. Here’s a q... more
User rating Your review
In this tutorial, you will learn a variety of methods to compare Excel files and identify differences between them. See how to open two Excel windows side by side, how to use Excel formulas to create a difference report, highlight differences with conditional formatting, and more. When you have two similar Excel workbooks, or better say two versions of the same workbook, what's the first thing you usually want to do with them? Right, compare those files for differences, and then probably merge them into a single file. In addition, workbook comparison can help you spot potential problems like broken links, duplicate records, inconsistent formulas or wrong formatting. So, let's have a closer look at various methods to compare two Excel sheets or entire workbooks and identify differences between them. If you have relatively small workbooks and a sharp eye for detail, this quick and easy way to compare Excel files might work well for you. I am talking about View Side by Side mode that lets you arrange two Excel windows side by side. You can use this method to visually compare two workbooks or two sheets in the same workbook. Compare 2 Excel workbooksLet's say you have sales reports for two months and you want to view both of them simultaneously to understand which products performed better this month and which did better last month. To open two Excel files side by side, do the following:
By default, two separate Excel windows are displayed horizontally. To split Excel windows vertically, click Arrange All button and select Vertical: As the result, two separate Excel windows will be arranged side by side, like in the below screenshot. If you want to scroll through both worksheets simultaneously to compare data row-by-row, make sure the Synchronous Scrolling option it turned on. This option resides on the View tab, in the Window group, right under the View Side by Side button, and is usually turned on automatically as soon as you activate View Side by Side mode. For more information about using this Excel feature, please see View Excel workbooks side by side. Arrange multiple Excel windows side by sideTo view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button. The Compare Side by Side dialog box will appear, and you select the files to be displayed together with the active workbook. To view all open Excel files at a time, click the Arrange All button on the View tab, in the Window group, and choose your preferred arrangement: tiled, horizontal, vertical or cascade. Compare two sheets in same workbookSometimes, 2 sheets that you want to compare reside in the same workbook. To view them side by side, perform the following steps.
How to compare two Excel sheets for differences in valuesIt is the simplest way to compare data in Excel that lets you identify cells with different values. As the result, you will have a difference report in a new worksheet. To compare two Excel worksheets for differences, just open a new empty sheet, enter the following formula in cell A1, and then copy it down and to the right by dragging the fill handle: =IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "") Due to the we use of relative cell references, the formula will change based on a relative position of the column and row. As the result, the formula in A1 will compare cell A1 in Sheet1 and Sheet2, the formula in B1 will compare cell B1 in both sheets, and so on. The result will look similar to this: As you can see in the above screenshot, the formula compares 2 sheets, identifies cells with deferent values and displays the differences in corresponding cells. Please note that in the difference report, dates (cell C4) are presented by serial numbers as they are stored in the internal Excel system, which is not very convenient for analyzing differences between them. Highlight differences between 2 sheets with conditional formattingTo highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature:
As the result, the cells with different values will get highlighted with the selected color: If you are not very familiar with Excel conditional formatting, you can find the detailed steps to create a rule in the following tutorial: Excel conditional formatting based on another cell value. As you see, it's very easy to compare two Excel sheets by using formulas or conditional formats. However, these methods are not well suited for all-round comparison because of the following limitations:
Compare and merge copies of a shared workbookWhen it comes to merging different versions of the same Excel file, the Compare and Merge feature comes in handy. It is especially useful when several users collaborate on the same Excel workbook because it lets you view the changes and comments of all users at a time. To leverage this feature, be sure to do the following preparations:
Now that all initial preparations are done properly, you are ready to combine the copies of a shared workbook. 1. Enable the Compare and Merge Workbooks feature in your ExcelAlthough, the Compare and Merge Workbooks feature is available in all versions of Excel 2010 through Excel 365, this command is not displayed anywhere in Excel by default. To add it to the Quick Access toolbar, perform the following steps:
2. Compare and merge workbooksWhen all of the users have finished working with your shared Excel workbook, you can merge all the copies into one file.
Done! The changes from each copy are merged into a single workbook. 3. Review the changesTo see all the edits by different users at a glance, just do the following:
To point out the row and columns with differences, Excel highlights the column letters and row numbers in a dark red color. At the cell level, edits from different users are marked with different colors. To see who made a specific change, just hover over the cell.
Note. If the Compare and Merge Workbooks command is greyed out in your Excel, most likely you are trying to combine different Excel files. Please remember, the Compare and Merge Workbooks feature allows merging only copies of the same shared workbook. Third-party tools to compare Excel filesAs you have just seen, Microsoft Excel provides a handful of features to compare data in two or more workbooks. But none of the built-in options is sufficient to comprehensively compare Excel sheets, let alone entire workbooks, spotting all the differences in values, formulas or formatting. So, if you need advanced and really efficient means to compare two Excel files, then most likely you would have to use one of the third-party tools specially designed for comparing, updating and merging Excel sheets and workbooks. Below you will find a quick overview of a few tools that, in my opinion, are best performers in this area. Synkronizer Excel Compare: 3-in-1 tool to compare, merge and update Excel filesThe Synkronizer Excel Compare add-in can quickly compare, merge and update two Excel files saving you the trouble of searching for differences manually. If you are looking for a quick and reliable method to compare two Excel sheets or workbooks, you will certainly find helpful the following features of Synkronizer Excel Compare:
To get some basic idea about Synkronizer Excel Compare's capabilities and performance, let's carry out a couple of field tests. Compare two Excel files for differencesSupposing you are organizing some event and, in your Excel table, you gather information about the participants such as a participant name, arrival date, number of seats, etc. Also, you have a couple of managers in direct contact with participants and the database, and as a result, you have 2 versions of the same Excel file. Okay, let's see how efficiently Synkronizer can compare our two sheets and identify differences between them. To run Synkronizer Excel Compare, go to the Add-ins tab, and click the Synchronizer 11 icon. The Synkronizer pane will show up in the left part of your Excel window, where you do the following:
Visualizing and analyzing the differencesUsually it takes Synkronizer only a few seconds to compare 2 sheets and present two summary reports on the Results tab:
The following screenshot shows the summary report (in the upper part of the Results pane), and cell difference report (in the lower part of the pane) that were created for our sample sheets: Clicking on a difference in the detailed report will select the corresponding cells on both sheets (the below screenshot shows just one sheet because there's enough room to show both :) In addition, you can create a difference repot in a separate workbook, either standard or hyperlinked, and jump to a specific difference with a mouse click: Compare all sheets in two workbooks at a timeIf the two Excel files you are comparing contain multiple sheets, all matching worksheet pairs will be presented in the summary report for your perusal: Highlighting differences between sheetsBy default, the Synkronizer Excel Compare add-in highlights all found differences, like in the following screenshot:
To highlight only the relevant differences, click the Outline button on the Results tab, and select the required options: Update and merge sheetsThe merge function is definitely one of the most useful features of the Synkronizer Excel Compare add-in. You can transfer individual cells or move different columns/rows from the source to target sheet, and have your primary sheet updated in seconds. To update one or more differences, select them on the Synkronizer's pane and click one of the 4 update buttons - the first and last buttons update all differences, while the 2nd and 3rd buttons update selected differences only (the button arrows indicate the transfer direction): Well, these are the key features of the Synkronizer add-in, but there is certainly much more to it. Want to give it a try? An evaluation version is available for download here. Synkronizer is certainly worth your attention, but it's not the only way to compare files in Excel. A handful of other comparison tools exist, which basically provide the same set of features but in different implementations. Ablebits Compare Sheets for ExcelThe latest version of our Ultimate Suite includes over 40 new features and improvements, the most exciting of which is Compare Sheets - our own tool to compare worksheets in Excel. To make the comparison more intuitive and user-friendly, the add-in is designed in this way:
Now, let's try the tool on our sample spreadsheets from the previous example and see if the results are any different.
Review and merge differencesOnce the worksheets are processed, they are opened side-by-side in a special Review Differences mode, with the first difference selected: On the screenshot above, the differences are highlighted with the default colors:
To help you review and manage the differences, each worksheet has its own vertical toolbar. For the inactive worksheet (on the left) the toolbar is disabled. To enable the toolbar, just select any cell in the corresponding sheet. By using this toolbar, you go through the found differences one-by-one and decide whether to merge or ignore them: As soon as the last difference is dealt with, you will be prompted to save the workbooks and exit the Review differences mode. If you have not finished processing the differences yet but would like to take a break for now, click the Exit Review Differences button at the bottom of the toolbar and choose either to:
That's how you compare two sheets in Excel with our tool (hope you liked it :) If you are curious to give it a shot, an evaluation version is available for download here. xlCompare: compare and merge workbooks, sheets and VBA projectsUsing the xlCompare utility, you can compare two Excel files, worksheets, names and VBA Projects. It identifies added, deleted and changed data and allows you to quickly merge differences. In addition, it provides the following options:
Change pro for Excel: compare Excel sheets on desktop and mobile devicesWith Change pro for Excel, you can compare two sheets in desktop Excel as well as on mobile devices with optional server-based comparison. The key features of this tool are:
Online services to compare Excel filesApart from desktop tools and utilities, there exist a number of online services that let you quickly compare two Excel sheets for differences without installing any software on your computer. Probably it's not the best solution in terms of security, but if your Excel files do not contain any sensitive information why not use some free online service for immediate results? For example, you can try XLComparator or CloudyExcel, or find some other similar services on the net. This is how the CloudyExcel service looks like: You just upload the two Excel workbooks you want to compare, and click the Find Difference button at the bottom of the screen. In a moment, the differences in two active sheets will get highlighted with different colors: Well, this is how you can compare Excel files for differences. If none of the solutions described in this tutorial is suitable for your task, check out the following resources that cover other aspects of Excel file comparison. And if you know any other ways to compare two Excel files, your comments will be greatly appreciated. I thank you for reading and hope to see you on our blog next week! Other ways to compare and merge data in Excel |