How to Compare Multiple Sheets

Compare Multiple Sheets Wizard 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.

Please be informed that the add-in is compatible only with Excel 2019, 2016 and 2013. Excel 2010 and 2007 are not supported.

Video: How to compare multiple Excel sheets

Before you start

Please read carefully this part before you start working with the add-in.

  • 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 are not supported.
  • 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 Wizard

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 tool, click the Compare Multiple Sheets icon on the Ablebits Data tab in the Merge group:

Start Compare Multiple Sheets.

In the first window, you will see limitations and requirements, please read them carefully.

Click the Agree button.

Step 1: Select the compare sheets

Choose the sheets to compare.
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 your are going to compare other sheets to.

Select the area to take for comparison:

  • Entire Sheet
  • Selected Range
  • Current Table

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 particular sheet. Also, 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

Select the comparison options.
One 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:

  • Background color
  • Botton border
  • Font color

Click the down arrow icon to select the color for the differences and click Compare.