Compare Multiple Sheets will help you find and highlight differences in two or more Excel lists or sheets.
You can ignore empty or hidden rows and columns, consider or not leading and trailing spaces, and choose the way and hue to mark the found differences.
Video: How to compare multiple Excel sheets
Before you start
Before running the add-in, here are the key points to take note of:
Before running Compare Multiple Sheets, open all the workbooks that contain the sheets you are going to review.
The supported workbook formats: .xlsx and .xlsm.
Workbooks should be saved before comparison.
Protected workbooks cannot be compared.
Merged cells are not supported.
Some changes will be made to cell formatting (borders, fill, or font color) that may conflict with your existing conditional formatting.
Some differences are ignored: row height, column width, worksheet and workbook options, all embedded objects including charts, pivot tables, images, shapes, controls, etc.
Compared worksheets should have the same structure and layout, including grouping, filtering, and sorting.
How to use Compare Multiple Sheets
Start Compare Multiple Sheets
Open all the files you are going to compare and go to the main sheet - it is the sheet the other worksheets will be compared to. To run the add-in, on the Ablebits Data tab, in the Merge group, click Compare > Compare Multiple Sheets:
In the first window, you will see limitations and requirements, please read them carefully.
Click the Agree button to continue.
Step 1: Select the compare sheets
The tool automatically selects the current sheet as the main one.
Note. It is not possible to select another main sheet when the tool is started. That's why it is important to run the tool from the sheet you are going to compare other sheets to.
Select the area to take for comparison:
In the list of open books, check the worksheets you are going to compare to the main sheet.
You can use the Select range icon and choose the areas of interest for each sheet. Or you can pick a cell in the table and click the Expand selection icon to get the whole table selected.
Click the Next button.
Step 2: Choose comparison options
On step 2, first tick off the options that are suitable for your data:
Show differences in formulas
If your tables contain formulas and you need to find differences in them, check this box.
Show differences in formatting
This option lets you identify differences in formatting.
Note. To mark the differences, the tool will make some changes to cell formatting (borders, fill, or font color) that may conflict with your existing conditional formatting.
Ignore hidden rows/columns
If you tick off this option, the tool will not include hidden rows and columns into the comparison.
Ignore empty rows/columns
Check this box to compare only non-empty rows and columns, empty ones will be ignored.
Ignore leading/trailing spaces
Tick off this option to ignore leading and trailing spaces while comparing the sheets.
Now you can select the way to mark the found differences in the compare sheets:
Click the down arrow icon to select the color for the differences and click Compare.