Compare Two Sheets: FAQ

Compare Two Sheets is one of our most popular and controversial utilities. The general idea was to create a simple-to-use and easy-to-understand tool that would be capable of performing a comparison of different types of complex data sets.

However, as the task itself is intricate enough, the tool requires your efforts to understand how it functions. As soon as you get how it works, you'll be astonished by a handy and devoted Excel assistant you've just got.

Here you will find answers to the most frequently asked questions about Compare Two Sheets.

Related pages

Can I compare sheets with different structure and layout?

Unfortunately, no. The point is that Compare Two Sheets scans your Excel lists row by row identifying matching or different cells in the same or nearest following rows. Now imagine that you compare two tables that are organized absolutely differently; that would make no sense.

However, if your task is to take two data sets and find unique or duplicate rows no matter where in the table they are located, the Compare Tables tool may be the one you need.

What if my sheets are differently sorted and filtered?

The sheets you are going to compare should be filtered and sorted the same way. The Compare Two Sheets tool processes your sheets row by row. If some rows are hidden or one sheet is sorted and the second is not, the comparison would give you meaningless results.

So please, before getting to work, make sure your sheets are filtered and sorted the same way.

Can I use the tool if one or both the compared sheets are protected?

Both sheets you compare should not be protected as it simply would be impossible for the tool to process data in these sheets.

Can I filter sheets or insert new rows and columns in the Review Differences mode?

The basic idea behind the Review Differences mode was to develop an effective way for you to navigate between the found differences and review then in place. The selection of the found different lines is synchronized in both sheets so you could see them side by side and decide which one is more correct or up-to-date, which one you want to leave or remove. To make it work like this, it is crucial to preserve the original layout and structure of your sheets in this mode.

For these reasons, in the Review Differences mode, we have disabled the Filter option. However, you can exit the mode and save the coloring by choosing to Save and close the workbook keeping the difference marks when you close your sheets. And then simply use Excel's built-in Filter by Color to get all the found differences depicted in a handy way.

The same goes for insertion/removal or rows and columns. When you finish processing the found differences, you can quit the Review Differences mode and do with your data set whatever you need: extract rows, add or remove rows and columns, etc.

Can I find differences in the formatting of my sheets?

Yes! On the final step, check the Show differences in formatting box to make the tool show you differently formatted cells and rows.

How can I remove all the coloring and changes made in the Review Differences mode?

If you close your sheets without reviewing all the differences, the tool offers you four options of dealing with your data. If you want to remove all the coloring made by the tool and undo the changes you made in the Review Differences mode, pick the last option: Close the workbook and restore its contents from the backup copy.

How to find differences in formulas?

To find cells that have different formulas in two sheets, tick the Show differences in formulas box on the final step.

How can I find not different but duplicate rows in two sheets?

To find rows that are the same in two sheets, use the Compare Tables tool.

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