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. Several features allow you to compare Excel sheets to see different values.

This article will show 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, a feature within Microsoft Excel called the View Side-by-Side option allows you to do this.

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

If your dataset is significant, I suggest you use the conditional or formula methods we will discuss shortly.

Suggested read: Remainder Formula in Excel

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 reach.
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 can only compare two Excel files at once. If you have multiple Excel files open when you select the side-by-side view option, a Compare-Side-by-Side box will appear, where you can pick which file you compare with your current workbook.

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 cannot use the View Side-by-Side feature, as that system only works for different 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 open two versions of the same workbook, you can arrange for them 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 reach.
Select the View tab.
Select the New Window choice from the Window group, 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 you can 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 compares 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 sheets often, it makes good sense to have an Excel macro VBA code to hand to 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 simultaneously. 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 select OK.

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

Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons

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