How to compare multiple excel sheets

• RESOURCES
• EXCEL
• GOOGLE SHEETS
• FINANCE
• SUCCESS STORIES
• PRODUCT

Don’t forget to share this post

One of the most common scenarios when working with Excel spreadsheets is having files with similar or duplicate data. The reasons for this could be many, but it usually involves spending a considerable amount of time checking complete files or separate worksheets manually.

This article will explain how to compare two or multiple Excel files, as well as two Excel sheets, for differences. To achieve this, we will describe how to use three useful methods to spot differences in a quick and easy way; these include side-by-side viewing, conditional formatting rules, and the =IF formula.

We will start by illustrating how to compare two Excel workbooks using the side-by-side view. However, we recommend using this method in case your dataset is not too large; if not, we recommend using one of the two methods outlined further on in this article. This is how you can compare two Excel files using the side-by-side viewing feature.

1. 1. Open the two Excel workbooks you would like to compare and go to View > View Side by Side on any of the opened files.

How to compare two Excel files - View side by side

1. 2. By default, Excel will place both files horizontally, as shown below.

How to compare two Excel files - Horizontal view

1. 3. To arrange them vertically, click “Arrange All”, and then select “Vertical”.

How to compare two Excel files - Arrange vertically

1. 4. The two Excel files will now be arranged vertically, as shown below.

How to compare two Excel files - Vertical view

1. 5. Make sure that the “Synchronous Scrolling” option is activated since this will allow you to scroll through the data on both files simultaneously and allow you to compare more easily. Although it activates automatically as soon as you enable the side-by-side view, you can also check that it’s activated in your toolbar within the “Window” group.

How to compare two Excel files - Synchronous Scrolling

Now that we know how easy it is to compare two Excel files, let’s see how we can apply this viewing method to Excel sheets.

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE

Sometimes, similar or duplicate data may appear within the same spreadsheet. IF you want to avoid having to switch from one sheet to another to compare the data, this is how you can quickly compare two Excel sheets side by side.

1. 1. Open the Excel file where you would like to compare sheets. Then, go to View > New Window.

How to compare two Excel files - View New Window

1. 2. You will now have the same Excel file open up in a different window, as shown below.

How to compare two Excel files - Same file in New Window

1. 3. Select “View Side by Side” and make sure to select a different sheet on each file. As before, you can select vertical or horizontal viewing according to your preference.

How to compare two Excel files - Sheet 1 and 2

So far, you have seen how easy it is to compare two files and sheets on Excel. However, what if you would like to compare more than two files at the same time? This is how you can compare multiple Excel files using the side-by-side view.

Comparing multiple files for differences follows a similar process and will only take you a few simple steps.

1. 1. Open all the Excel workbooks you would like to compare and go to View > View Side by Side. Select the file you want to start comparing with in the “Compare Side by Side” dialog box.

How to compare two Excel files - Compare side by side

1. 2. Click “Arrange All” to view all the opened files at the same time.

How to compare two Excel files - Arrange All

1. 3. Then, select the type of arrangement according to your preference. Here, I have chosen the “Tiled” arrangement.

How to compare two Excel files - Tiled arrangement

So far, these methods are useful in case your datasets are not too large and easily manageable. If you want to compare larger datasets for differences in values, the best way is to use the =IF formula or a conditional formatting rule. Let’s explore the =IF formula first.

How to compare two Excel sheets using a formula?

This is the most straightforward way to compare data between two Excel sheets. This formula will allow you to identify cells containing different values, and a comparative report will be generated in a new worksheet.

1. 1. Open a new empty sheet in your Excel workbook and enter the following formula in cell A1: =IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

How to compare two Excel files - Enter IF formula

1. 2. Grab the bottom-right corner of the formula cell, and drag down.

How to compare two Excel files - A1 cell comparison in Sheets

The formula will adapt to the column and row position it fills. This way, the formula in cell A1 compares to cell A1 in “Sheet1” and “Sheet2”; the formula in cell B1 will compare cell B1 in both sheets as well.

We will now turn to how to compare two Excel sheets by highlighting the differences. The best way to do so is using Excel conditional formatting feature.

When comparing two very similar and large datasets, the best and quickest way to spot differences in values is to highlight them using the conditional formatting feature.

1. 1. Open the Excel sheets and select the range of data you would like to compare for differences. A quick way to do this is to click the upper-left cell and then Ctrl + Shift + End to extend the selection to the last cell containing values.

How to compare two Excel files - Select cell range

1. 2. Go to Home > Conditional Formatting > New rule.

How to compare two Excel files - Create New Rule

1. 3. Select the rule type “Use a formula to determine which cells to format” and enter the following, =A1<>Sheet1!A1. The Sheet name included in the formula corresponds to the sheet you are comparing with and not the one you are creating the rule in.

How to compare two Excel files - Use a formula

1. 4. Once you’ve entered the formula, click “Format”, next to the “Preview” pane.

How to compare two Excel files - Format cells

1. 5. Select how you would like to format the cells, i.e. according to “Number”, “Font”, “Border”, or “Fill”. We recommend highlighting with color fill, so we have chosen a color that will clearly stand out.

How to compare two Excel files - Format Fill

1. 6. As you can see, Excel has highlighted the different cells in “Sheet 2” compared to “Sheet 1”.

How to compare two Excel files - Different cells highlighted

Now you know how to compare two or multiple Excel files and two sheets on your desktop. What if you want to compare and highlight differences in your Excel sheets online?

How to compare two Excel sheets and highlight differences online?

In case you don’t have Excel installed on your desktop or simply prefer to work online altogether, there are online tools that allow you to compare Excel files and sheets for differences.

Below, we provide a list of third-party tools that will allow you to compare Excel files and sheets online:

• Synkronizer Excel Compare: In addition to the features outlined in this article, it allows you to combine multiple Excel files into one, while maintaining unique values and avoiding duplicates.
• Ablebits Compare Sheets for Excel: This tool provides step-by-step guidance for efficient comparison and displays the differences found between sheets in the “Review Differences” mode for better management.
• Florencesoft DiffEngineX: Another excellent alternative that allows you to compare Excel files directly from Microsoft Outlook.
• Layer: Apart from allowing you to review spreadsheet changes and combine multiple spreadsheets into one, Layer offers additional features for file storage and management at a business level.

How to track spreadsheet changes with Layer?

Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.

Using Layer, you can:

• Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
• Review & Track: Consolidate input, and easily track changes.
• Collaborate: Define, assign, and automate tasks and set deadlines.

Sign up for early access and start automating your Google Sheets workflows with Layer!

This article has shown you how to compare the data in two Excel files for differences. You can compare data between two files, two sheets, or multiple files using the side-by-side view for a quick and easy comparison. If your dataset is larger, you can apply the IF formula to compare two Excel sheets or use conditional formatting rules to highlight the differences.

Alternatively, for users that prefer to work online, there are platforms that can help you achieve this level of comparison in an online setting, for example, Layer. We also recommend reading our blog article on How To Combine Multiple Files into One as a great way to complete this data comparison process.

Share parts of your spreadsheet and make your data collection 10X better with the new Layer add-on!