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.

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.

Responses

Hello, I am trying to compare 2 exactly the same datasheets. I save them with the changes but when I come to open them again to complete the comparing, I find that none of my changes have been saved. The 2 worksheets that I am trying to compare are sitting on my desktop.

Katerina Bespalaya (Ablebits Team) says:
February 21, 2020 at 12:42 pm

Hello Anne,

Please make sure that you exit the Review Differences mode and close your sheets correctly after comparing them. For more details, please visit this help page. If you still have any difficulties, please contact us at support@ablebits.com for further assistance. Thank you.

Hello! I am trying to compare 2 inventory sheets to see which inventory in my tracking system has been deleted from our POS system. I am having trouble finding a way to compare Column A to see which SKUs aren't on both sheets. They are all in the same row and formatted the same way, expect the SKUs aren't in the same order on both sheets. What is the best way to do this?

Hello Dylan,
Thank you for contacting us. For us to be able to help you better, please send us a small sample workbook with 2 sheets: 1 - your source data and 2 - the result you expect to get. Please shorten the tables to 10-20 rows.

Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it.

We'll look into your task and try to find the best solution for you.

Hi. Quick question.

Can the tool compare files in a network location and not stored on the hard drive of the user?

Can the tool compare entire workbooks (assuming the content of the various sheets is the same in both) or is it limited to one sheet in each workbook at a time?

Hi Craig,

The Compare Two Sheets tool doesn't work with the files that are placed in any network location. You'll need to download the files you want to compare and open them from your hard drive beforehand.

If there are several sheets to check, please choose the Compare Multiple Sheets tool instead. It will find and highlight differences in two or more Excel sheets for you. Here are the instructions on how to use it for you just in case: https://www.ablebits.com/docs/excel-compare-multiple-sheets/

Mike Sheridan says:
July 30, 2019 at 10:29 pm

My spreadsheets will mostly vary in rows. Week by week some rows are added and some are deleted. I am only interested in seeing the different rows. As I use the Wizard defaults, it highlights differences for all rows below the first row different. I would prefer to only see the different row highlighted.

Is there a way to do that?

Thank you for your interest in our products, Mike.

For us to be able to help you better, please send us small sample workbooks with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten the tables to 10-20 rows if possible.
Also please send us the screenshots of the all the options you select in the Wizard.

We'll look into your task and see if our software can help.

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.