How To Compare Two Excel Files And Highlight The Differences

There are two Excel documents with the same name on your computer, each in a different folder. How do you tell if two files are identical or if they are separate iterations of the same Excel workbook? In this article, even if Excel isn’t already installed on your computer, we’ll show you how to compare two Excel files.

You can identify issues with your Excel worksheet by using these comparison tools to identify inconsistent data, out-of-date values, broken formulae, inaccurate computations, and other issues.

This quick and simple method to compare Excel files can be useful for you if you have a good eye for detail and a modest number of workbooks. I’m referring to the View Side by Side mode, which enables you to set up two Excel windows side by side. Using this technique, you can graphically compare two workbooks or two sheets within a single worksheet.

Comparison of two Excel workbooks

Imagine you have two months’ worth of sales reports, and you want to view both of them at once to figure out which products did better this month and which did better last month.

Do the following to open two Excel files simultaneously:

1. Start by opening the workbooks you wish to contrast.
2. Select the Window group’s View tab and click the View Side by Side option. All done!
3. Two distinct Excel windows are typically shown side by side by default.
4. Select Vertical from the Arrange All menu to split Excel windows vertically.
5. The end effect will be two distinct Excel windows lined up side by side, as shown in the screenshot below.

Make sure the Synchronous Scrolling option is enabled if you wish to simultaneously browse through both spreadsheets to compare data row by row. This option, which is located directly beneath the View Side by Side button on the View tab’s Window group, is often activated as soon as you switch on View Side by Side mode.

Please check View Excel workbooks Side by Side for additional details on how to use this Excel feature.

Arrange multiple Excel windows side by side

Open any worksheet you want to compare and select View Side by Side from the View menu to see more than 2 Excel files at once. You choose the files that will be displayed alongside the currently active workbook in the Compare Side by Side dialog box that appears.

Select your preferred arrangement from tiled, horizontal, vertical, or cascade by clicking the Arrange All button on the View tab, in the Window group. This will display all open Excel files at once.

Compare two sheets in same workbook

There are situations when two sheets you want to compare are located in the same workbook. Take the next few actions to compare them side by side.

1. After opening your Excel document, select the Window group on the View tab, then click the New Window button.
2. By doing this, a new window will open with the same Excel file.
3. Switch to the View Side by Side mode by clicking the corresponding ribbon button.
4. Choose sheet 1 from the first window and sheet 2 from the second window.

Compare Two Excel Files Using Online Tools

Excel comparison services are provided through web-based programs. If Excel isn’t already installed on your computer, these tools will be helpful to you. A useful web tool for comparing two Excel files is this comparison tool from Aspose.

Drag the other file into the second box after dropping the primary Excel file in the first, then click the Compare Now button.

In the Sheets tab, pick the sheets you want to compare if the files contain several sheets. The Aspose Excel comparison tool will indicate the differences in yellow if there are any cells on either sheet with differing contents or values.

The main drawback of these web-based tools is that they frequently emphasize various values. They cannot draw attention to incorrect computations, formulas, etc.

How to compare two Excel sheets and highlight differences online?

The most practical choice for Office Home or Student users is the “View Side by Side” comparison function. You can use the built-in “Spreadsheet Compare” tool if you’re using Excel for Microsoft 365 or Office Professional Plus (2013 or 2016). However, web-based Excel comparison tools will do the trick if you don’t have Excel installed on your computer. That’s how simple it is.

There are online applications that let you compare Excel files and sheets for differences in case you don’t have Excel installed on your desktop or simply prefer to work entirely online.

You can compare Excel files and sheets online using the third-party tools listed below:

• Synkronizer Excel Compare: In addition to the functions described in this article, it also enables you to combine numerous Excel files into a single one while preserving unique values and preventing duplicates.
• Ablebits Compare Sheets for Excel: This tool offers detailed instructions for effective comparison and shows the discrepancies across sheets in the “Review Differences” mode for better management.

You may compare Excel files straight from Microsoft Outlook using Florencesoft DiffEngineX, another great substitute.

• Layer: In addition to letting you combine many spreadsheets into one and examine changes made to spreadsheets, Layer provides extra tools for file management and storage at the company level.

How can I use a formula to compare two Excel sheets?

The simplest method for comparing data between two Excel sheets is to do it this manner. With the help of this formula, you may spot cells with various values, and a new worksheet will produce a comparative report.

1. Launch your Excel workbook and open a fresh, blank sheet. Type the formula shown below in cell A1: =IF (Sheet1!A1 versus Sheet2!A1, Sheet1: “&Sheet1!A1&” vs Sheet2: “&Sheet2!A1, “

The formula cell’s bottom-right corner can be grabbed and dragged downward.

According to the column and row positions it fills, the formula will adjust. Accordingly, the formula in cell A1 compares to cell A1 in “Sheet1” and “Sheet2,” and the formula in cell B1 compares to cell B1 in both sheets.