Buy Now

How to Compare Two Excel Sheets

Jun 03, 2022

Comparing two excel sheets can be a little complex, as Excel worksheets only let you view one sheet at a time.

The more data you want to compare, the more difficult this becomes. Luckily, there are several features that allow you to compare excel sheets to see different values.

This article will show you several ways to compare excel files and highlight differences.

How to Compare Two Excel Sheets in Separate Excel Files

If you are keen to compare excel files side by side, luckily, there is a feature within Microsoft Excel that allows you to do this, called the View Side-by-Side option.

This option works very well if you have a small dataset, and you don't expect manually comparing these two excel sheets will be particularly time-consuming.

If your dataset is quite large, I would suggest you use the conditional or formula methods we will touch on shortly.

Using the View Side-by-Side option, you have the power to open two excel files and then arrange them vertically or horizontally. This lets you easily compare your values without wasting time switching between the two.

Here's how:

  • Open up the excel sheets you want to compare.

  • From each file, choose the sheet you are keen to compare.

  • Select the View tab.

  • From the Window group, select the View Side by Side option. This option is available if you have two or more Excel files open.

Once you select the View side-by-side option, Excel will sort the workbook horizontally. Each file will be visible, and you can edit or compare either.

How to Compare Multiple Sheets in Separate Excel Files

From the View Side by Side comparison option, you are only able to compare two excel files at once. If you have multiple excel files open when you select the view side by side option, a Compare-Side-by-Side box will appear, where you can pick which file you compare with your current workbook.

If you want to compare an excel file with multiple other excel worksheets, open every file and select the Arrange All option from the View tab.

From the Arrange All dialog box, select Vertical/Horizontal and hit OK.

This option will arrange all the data from the multiple sheets you have open in the selected order.

How to Compare Two Sheets in the Same Excel Workbook

What about when you want to compare two separate sheets in one workbook? You are unable to use the View Side-by-Side feature, as that system only works for separate Excel files.

But it is still possible to execute a side-by-side comparison.

You can do this using the New Windows feature within Excel. This feature allows you to open the same workbook twice. Once you have two versions of the same workbook open, you have the power to arrange them side-by-side to compare.

Here are the steps to take to compare two sheets from the same workbook:

  • Open your workbook that contains the sheets you want to compare.

  • Select the View tab

  • From the Window group, select the New Window choice, which opens a second version of the same workbook.

  • From the view tab, select the Arrange All option. This option will open the Arrange Windows dialog box.

  • Choose Vertical to compare columns and Horizontal to compare data in rows.

  • Hit OK.

These steps allow you to arrange each version of your workbook vertically.

Compare Two Excel Files/Sheets And Get The Differences Using Formula

If all you need to do is quickly compare and identify differences between two excel sheets, you can use a quick and easy formula to gather the different values.

For this option, you must have a separate worksheet set up where you can gather the differences.

This method is perfect for comparing two excel sheets within the same workbook or two Excel workbooks.

Compare Two Excel Files/Sheets And Get The Differences Using VBA

If you are required to compare Excel files or Excel sheets often, it makes good sense to have an Excel macro VBA code to hand so that you can make these comparisons quickly and easily.

You can also add this macro to your Quick Access Toolbar and access it with a single click to understand how cells differ from one sheet to another.

Frequently Asked Questions About Comparing Two Excel Sheets for Differences

Can I compare two Excel spreadsheets for differences?

Using the View Side by Side function, you can compare two excel files at one time. This option is the easiest for comparing two Microsoft Excel workbooks.

How do I compare two Excel sheets to highlight duplicates?

Choose both columns of data you are keen to compare. From the Home tab, select the Styles Group, then Conditional formatting, and pick Highlight Cells Rules and Duplicate Values.

From the drop-down box, choose the colors you need and choose OK.

Related Articles

How to Sort by Date in Google Sheets: Quick and Easy Steps

How to Make a Calendar in Google Sheets

Google Sheets Highlight Duplicates: Everything You Need to Know

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.