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

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard