How to Compare two Excel Files for Differences

How to Compare two Excel Sheets - Quick Steps

• Open Both Excel Files
• Go to View Tab >> Click on View Side by Side
• Click on "Synchronous Scrolling" from the "View" tab.
• Compare Values and Formulas

Comparing two Excel files or sheets for differences can be a critical task for anyone working with data. Whether you're a project manager, accountant, analyst, or just someone who needs to ensure data accuracy, knowing how to compare Excel files can save you time and prevent costly mistakes. In this article, we'll show you how to compare two Excel files for differences, highlighting any discrepancies in data, formulas, or formatting.

By learning powerful tools and techniques, such as using Excel's built-in features or specialized comparison software, you can quickly and efficiently spot any variations between your datasets. Let's explore the best ways to compare Excel sheets, ensuring your data remains accurate and reliable.

How to Compare Two Excel Workbooks

Learn how to compare Excel files visually by viewing them side by side, making it simple to spot differences. Find out how to open and compare two Excel workbooks to quickly see and analyze data variations.

Step 1: Open Excel

Launch Microsoft Excel by clicking on its icon, and open a blank workbook (or any workbook you want to start with).

Step 2: Open the Two Workbooks

Make sure the two workbooks you want to compare are open in Excel. You can open them from your file location or recent files.

Step 4: Click on "View Side by Side"

In the Window group under the View tab, click on View Side by Side. This will arrange both workbooks side by side for easy comparison.

Note: If you only have one workbook open, you’ll need to open the second workbook first before the option becomes available.

Step 5: Compare Side by Side

Excel will automatically display both workbooks horizontally next to each other. You can now scroll through both workbooks simultaneously to compare the data.

Step 6: View them collectively (if desired)

Excel displays both reports horizontally by default, indicating that they will be shown adjacent.

Step 7: Arrange Vertically (Optional)

If you prefer to see the workbooks stacked vertically instead of horizontally, click on the Arrange All button in the Window group under the View tab.

Step 7: Select "Vertical"

In the Arrange Windows dialog box, select Vertical and click OK. This will stack the workbooks one on top of the other for vertical comparison.

You are now ready to analyze the differences between the two workbooks in either horizontal or vertical layout!

How to Compare Two Sheets in Same Workbook

Follow the below steps know how to compare two worksheets in excel:

Step 1: Open Your Excel File

Open the Excel workbook that contains the two sheets you want to compare.

Step 2: Go to the View Tab

Navigate to the View tab on the ribbon, then click on the New Window button in the Window group to open a second window of the same workbook.

Step 3: Open a Different Window

A new window of the same Excel file will appear, allowing you to work with two views of the same workbook simultaneously.

Step 4: Enable Side-by-Side View

On the View tab, select View Side by Side to arrange the two windows next to each other for easy comparison.

Step 5: Select Sheets to Compare

In the first window, select Sheet 1; in the second window, select Sheet 2. You can now compare the data side by side for differences or similarities.

How to Arrange Multiple Excel windows Side by Side

Here’s how to view and arrange multiple Excel files side by side in a step-by-step format:

To View More than Two Excel Files Side by Side:

Step 1: Open All Workbooks

Open all the Excel files you want to compare. You can open them from your recent documents or file explorer.

Step 2: Click on "View Side by Side"

Go to the View tab in the ribbon and click on the View Side by Side button. This will allow you to view two workbooks side by side.

Step 3: Select Files

In the "Compare Side by Side" dialog box that appears, choose the files you want to display along with the active workbook.

Step 4: Compare all the workbooks

Now, you can scroll through both files and compare them side by side.

To Arrange All Open Excel Files:

Step 1: Click on "Arrange All"

In the View tab under the Window group, click on Arrange All. This option will arrange all open workbooks in a specific layout.

Step 2: Choose Arrangement Style

Select your preferred arrangement style from the options provided:

• Tiled: All windows are displayed in a grid pattern.
• Horizontal: All windows are stacked horizontally.
• Vertical: All windows are arranged vertically.
• Cascade: Windows are displayed in a cascading overlap.

After selecting the arrangement, click OK to apply the layout and view your open workbooks as desired.

How to Compare two Excel Sheets for Differences in Values

Follow the below steps know how to compare the values in two different Excel sheets to identify changes, additions, or inconsistencies.

Step 1: Open Your Excel Workbook

Ensure that the workbook containing the two sheets you wish to compare is open. Both sheets should be in the same workbook.

Step 2: Create a New Sheet for the Results

Add a new blank sheet where the comparison results will be displayed. To do this, click the + (plus) sign in the lower-left corner of the sheet tabs to create a new sheet.

Step 3: Enter the Formula for Comparison

In cell A1 of the new sheet, enter the following formula:

=IF(Sheet1!A1<>Sheet2!A1, "Sheet1: " & Sheet1!A1 & " vs Sheet2: " & Sheet2!A1, "")

This formula checks if the value in cell A1 of Sheet1 is different from the value in Sheet2. If the values are different, it will show both values for comparison. If they are the same, it will leave the cell blank.

Step 4: Use the "<>" Sign for Comparison

The <> symbol in the formula means "not equal to." If the values in the two sheets are different, the formula will display the values from both sheets. If the values are the same, the formula will show nothing (blank).

Step 5: Apply the Formula to More Cells

To apply the formula to additional cells, click and hold the fill handle (the small square at the bottom-right corner of cell A1). Drag it across the rows or columns to copy the formula to other cells, comparing the values in corresponding cells of the two sheets.

By following these steps, you can quickly identify discrepancies between two sheets and analyze any differences in your data.

How to Compare Excel Files for Differences Using Formulas

To compare Excel files for differences using formulas, you can follow these steps to identify discrepancies between data in two worksheets or workbooks:

Step 1: Prepare Your Data

Ensure the data in both files is arranged similarly for easier comparison. If necessary, align columns, rows, or data order.

Step 2: Open Both Excel Files

Open the files you want to compare. Let’s assume File1 is in Sheet1 and File2 is in Sheet2 for simplicity.

Step 3: Create a New Column for Comparison

In a new column, you can use comparison formulas to highlight differences.

Example Formula for Comparing Two Cells

For Identifying Exact Differences:

=IF(AND(Sheet1!A2 = Sheet2!A2, Sheet1!B2 = Sheet2!B2, Sheet1!C2 = Sheet2!C2, Sheet1!D2 = Sheet2!D2, Sheet1!E2 = Sheet2!E2), "Same", "Different")

This formula compares the value in cell A1 of Sheet1 with cell A1 of Sheet2. If they are different, it displays "Different"; otherwise, it shows "Same."

How to Compare two Excel files with Microsoft 365

To compare two Excel files using Microsoft 365, you can use the "Spreadsheet Compare" tool available as part of certain Office 365 versions (like Office 365 ProPlus). Here’s a step-by-step guide:

Step 1: Open Spreadsheet Compare

Click on the Windows Start button. Type "Spreadsheet Compare" in the search box and select it when it appears.

Step 2: Select Files to Compare

In Spreadsheet Compare, click on the “Compare Files” button.

Step 3: Select the two Excel files you want to compare.

A dialog box will appear prompting you to select the two Excel files you want to compare. Click “Browse” to choose the first and second files.

Step 4: Set Comparison Options (Optional)

You can choose specific options for your comparison (e.g., formulas, cell formatting, values). Click “OK” after selecting the desired options.

Step 5: View the Comparison Results

The tool will display a detailed comparison of the two files, highlighting any differences found. Changes such as value differences, formatting changes, and structural differences are highlighted in separate panes or tables for easy analysis.

Compare and Merge Copies of a Shared Workbook

Merging different versions of the same Excel file is easier with the Compare and Merge feature. This is especially useful for team collaborations where multiple users work on the same workbook. This feature allows you to view changes and comments made by all users at once. Here’s how to prepare and use this feature effectively:

Share Your Excel Workbook:

• Before others start working on the file, make sure to share your workbook.
• To share it, go to the Review tab, click on Share Workbook in the Changes group, select Allow Changes by More Than One User checkbox, and click OK. If prompted, save the workbook. This also enables the Track Changes feature.

Save Unique Copies:

• Each person editing the shared workbook must save their changes in a unique file name (.xls or .xlsx format).

With the preparations done, you can combine all copies into a single workbook.

1. Enable the Compare and Merge Workbooks Feature

The Compare and Merge Workbooks feature is available in Excel 2010 to Excel 365, but it may not be visible by default. Follow these steps to add it to your Quick Access Toolbar:

• Open the Quick Access dropdown menu and select More Commands.
• In the Excel Options dialog box, select All Commands from the Choose commands from list.
• Scroll down, find Compare and Merge Workbooks, select it, and click Add to move it to the right-hand section.
• Click OK.

Now, you have added the Compare and Merge Workbooks feature to the Quick Access Toolbar.

2. Compare and Merge Workbooks

After all users have finished their edits, you can merge all copies into one:

• Open the primary version of the shared workbook.
• Click the Compare and Merge Workbooks command on the Quick Access Toolbar.
• In the dialog box, select the copies you want to merge. To select multiple copies, hold the Shift key while clicking on the file names, and then click OK.

All changes from the selected copies will now be merged into one file.

3. Review the Changes

To view the edits made by different users:

• Go to the Review tab > Changes group, and click Track Changes > Highlight Changes.
• In the Highlight Changes dialog, select All in the When box, Everyone in the Who box, clear the Where box, check Highlight changes on screen, and click OK.

Excel will highlight columns and rows with changes in dark red, and edits from different users will be marked with different colors. Hovering over a cell will show who made a specific change.

Note: If the Compare and Merge Workbooks command is greyed out, it means you are trying to merge different files. Remember, this feature only works with copies of the same shared workbook.

Best Tools to Compare Excel Files

Here are some of the best tools to compare Excel files for differences, helping you identify changes, highlight discrepancies, and streamline your data review process:

Xltrail

By using Xltrail, an Excel-focused web-based application similar to GitHub, you may avoid the headache of manually comparing spreadsheets. It is an invaluable tool for anybody who wants to maintain their spreadsheets stress-free and organized since it keeps track of all changes made to the spreadsheet and displays them in an easy-to-read format, including VBA code. Changes are indicated with colors, such as green for new additions and red for deletions.

Synkronizer Excel Compare: 3-in-1 tool to compare, merge and update Excel files

Another excellent spreadsheet comparison tool is Synkronizer. It's a great option for anyone looking for a hassle-free way to stay on top of spreadsheet updates because, unlike others, it handles changes automatically. It displays both of your original workbooks side by side with color-coded cells (green for new info, red for deletions) for a clear picture of what's been modified.

Spreadsheet Compare

Spreadsheet Compare uses Microsoft Excel to compare spreadsheets. Spreadsheet Compare is a free desktop application concealed in some versions of the Office suite (ProPlus in Office 365 and ProPlus since 2013). This tool can compare your spreadsheets, but it requires the correct version of Excel, so it's only for some. Even Excel itself has an add-in named "Inquire" that you may use to launch it.

DiffEngineX

DiffEngineX is a little picky about rows and columns getting mixed up. This tool believes that if you have made any sophisticated changes to your spreadsheets, you should provide a unique "primary key" for each sheet, including the rows and columns. That would work OK for a brief comparison, but this "primary key" thing isn't going to cut it if you have a ton of workbooks to go through.

Online Services to Compare Two Excel Sheets

For quick online comparisons, consider using services like xlCompare, Aspose Cells Comparison, and CloudyExcel. These tools offer secure, web-based ways to compare two Excel files and compare Excel sheets without installing software. Other than Softwares on your computer, there are websites where you can compare Excel sheets quickly. They're free but less secure.

xlCompare: compare and merge workbooks, sheets and VBA projects

XL Comparator is a free tool that allows you to compare two Excel or CSV spreadsheets without programming macros or installing any software on your computer1. It’s a convenient way to identify differences between data sets, making it useful for tasks like data reconciliation, validation, and quality control.

Aspose Cells Comparison

Aspose Cells Comparison is a website where you can compare Excel files easily. You can upload two Excel or CSV files to see the differences between them. It works on any device like Windows, Mac, Android, or iOS without needing to install anything. Your files are processed securely and automatically deleted after 24 hours.

Xlcompare: compare and merge workbooks, sheets and VBA projects

xlCompare.com is the best tool to compare Excel files and highlight the difference. Compare Excel sheets just in one click. Find the difference between two Excel files. Pull matching and unique rows into another file and save it on disk. This online Excel compare tool will save time.

Cloudyexcel

CloudyExcel is an online service that provides tools for comparing and analyzing Excel files. You can upload two Excel workbooks or sheets and compare them to identify differences between the active sheets. The service highlights discrepancies, making it easier to spot changes, inconsistencies, or missing data. Generate customized reports directly from your Excel files.

Conclusion

You can compare two Excel files for differences using built-in tools, third-party software, or online services, depending on your needs. From comparing two Excel worksheets for differences side-by-side to using formulas and conditional formatting, each method helps you compare Excel files for differences effectively.