Compare multiple Excel sheets

Compare Multiple Sheets Wizard is part of Ablebits Ultimate Suite for Excel. It will help you find and highlight differences between two or more Excel sheets. The tool checks the sheets for differences in values, formatting, formulas. The different cells and rows are highlighted with the color you choose.

Make sure that all the workbooks that contain the sheets you are going to compare are open. Go to the main sheet – it is the sheet the other worksheets will be compared to. On the first step, the tool automatically selects the current sheet as the main one. It is up to you what to take for comparison: the entire sheet, the selected range, or the current table. Next, select the worksheets to compare: the differences will be marked only in these sheets – the main one will remain intact. On the final step, pay attention to the additional options and select the color to highlight the differences.

Find a more detailed instruction here.

Of course. Your sheets can be either in the same Excel file, or in different workbooks.

Not really. The differences are highlighted only in the compare sheets. The main sheet remains intact – it is the sheet the other files are compared to. If you are looking for a tool that compares two files for differences and matches and marks them in both sheets, have a look at the Compare Two Sheets tool.

Unfortunately, no. The wizard does not work with sheets that contain merged cells, so please unmerge them before starting the utility.

No problem. Check the Show Differences in Formulas box on step 2. But please do not compare tables that contain volatile functions, as they return different values each time Excel opens the worksheet. Such functions are RANDBETWEEN, OFFSET, INDIRECT, NOW, TODAY, RAND. Besides, CELL and INFO can be volatile depending on their arguments.

Yes! For this check the Show differences in formatting box on the second step.

It is ok, but be aware that the tool will make some changes to your cell formatting (depending on your choice: borders, fill, or font color) and that may conflict with your existing conditional formatting.

Sure, you can ignore or consider empty columns and rows by checking or unchecking the corresponding checkbox.

Yes, you can. By default, the tool ignores hidden columns and rows. If you want to consider them, just uncheck the corresponding option on step 2. But until you don’t unhide these rows, you won’t see the found differences there.

By default, Compare Multiple Sheets ignores spaces. But you can uncheck the Ignore leading/trailing spaces box and the tool will consider them while comparing multiple sheets.

You can select one of the following ways to highlight the found differences: bottom border, background color, or font color. Also, you can pick the hue.