How to Compare Multiple Sheets

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:

Start 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

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 you 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 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

Select the 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:

  • Background color
  • Bottom border
  • Font color

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

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.