Compare Two Sheets: FAQ

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.

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.

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

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.

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

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.

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

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