compare1b

Compare Worksheets in Excel With This Template

If you’re looking for a way to compare worksheets in excel and find the differences between them, then this is the template for you. It will quickly highlight any changes between the worksheets and also note the differences. With just a push of a button and it being stored in the Ribbon, the macro is quick and easy to run.

 

How the Template Works

The steps involved in this template are fairly straight forward and to compare worksheets in excel becomes very easy. Simply open up this file and copy the sheets in that you want to compare.

Then, on the Ribbon, select the Compare Sheets button.

compare worksheets button

You will then be prompted to select which worksheets that you want to compare.

select worksheets to compare

It’s important to note that when doing the comparison it isn’t looking at formatting and simply looking at the actual values.

Once you’ve selected the tabs you want to compare, click on the Compare button and the macro will run and will now compare worksheets. If you want to compare thousands of rows then this process will take a while and you’ll need to be a bit patient, since the macro will look cell by cell.

Before running the Compare Worksheets template

Important: If you’re not sure how big your data set is, use the CTRL + END shortcut, this will take you to the end of your data.

This is a good double check before you run the macro since you’ll get an idea of just how many rows and columns it’s going to look at. Sometimes the range is a lot bigger than expect since you might have many empty rows and columns if you copied data in before and never shrunk down the range.

But if you’re good to go, then run the macro and it’ll compare the worksheets.

What the output looks like

If there are no differences between the files, you’ll get a message box telling you that. Either way, the macro will end up creating a new sheet where it will plot all those differences. In my data sample, I put in a series of random A’s across the sheet:

compare worksheets results comparison

As you can see, it’ll identify the individual cell as well as the value there, compared to the related cell in the other sheet and what value is there. It will also create a hyperlink so that you can go straight to that cell so that you can review it in case you want to dig a bit deeper.

If you go onto the individual sheets that you compared, any differences from the other tab will be highlighted in yellow and bolded as well. This is just another way to tell you the cell is different than what’s on the other tab.

worksheet comparison highlighted bolded

 

Sheet 1

worksheet comparison highlighted bolded

 

Sheet 2

And that’s all there is to it. You can re-run the macro for any sheets that are within the file, although you’ll probably want to delete the one that gets created to highlight any differences once you’re done with it.

Download the Compare Worksheets Template

Free Version: Limited to first 20 rows. VBA code is locked.

Full Version: No limitations and code is fully unlocked.

Tags: No tags

Add a Comment

You must be logged in to post a comment