How to Compare Two Excel Files

How to Compare Two Excel Files.

There are two Excel files with the same name in different folders on your computer. How to determine if files are duplicates or different versions of the same Excel workbook? In this tutorial, we will show you how to compare two Excel files even if you don’t have Excel installed on your computer.

Read : How To Merge Data In Multiple Excel Files

These comparison tools can help you find inconsistent data, stale values, incorrect formulas, incorrect calculations, and other problems in your Excel worksheet.

1. Compare Two Excel Sheets: View Side by Side

If you can quickly view the data in the worksheet, open it in a separate window and perform a side-by-side comparison using Excel’s View Side-by-Side feature.

Open an Excel file containing both sheets, go to the View tab and select New Window.

In the new window, select the (second) worksheet you want to compare, or switch to it.

Change the size or order of the windows so that both sheets appear side-by-side on the computer screen. Again, this method is best for comparing Excel sheets with multiple rows or columns.

Also Read : How to Group Worksheets in Excel

If you’d rather use the Excel Compare tool to arrange both windows side-by-side, check the View tab and click the Side-by-Side View icon.

Excel will immediately arrange both sheets horizontally on your computer screen. Comparing sheets in this landscape mode can be a little tricky, so move on to the next step to change orientation to portrait / portrait.

Switch to the View tab again and select Arrange All.

Select “Vertical” in the “Arrange” window and click the “OK” button.

As a result, both sheets will be located on the screen next to each other. There is one more parameter that needs to be enabled to make comparison easier.

Click Synchronous Scrolling and make sure it is highlighted. This allows you to scroll through both sheets at the same time, providing a synchronous line-by-line comparison of your dataset.

If the top rows of both sheets are headers, make sure you freeze them so they don’t move with the rest of the dataset as you scroll.

Select Freeze Regions and then Freeze Top Row. Repeat this step for the second sheet.

Select Reset Window Position to return the comparison orientation to landscape.

When you’re done comparing, select Side-by-Side View to return the sheets to their original dimensions.

Now you can view both sheets and compare them line by line. The main advantage of this feature is that it is built into all versions of Excel. However, you still have to do a bunch of work, such as highlighting cells with different numbers, macros, formulas, etc.

2. Compare Two Excel Files Using Online Tools

There are web-based tools that offer Excel comparison services. You will find these tools useful if you don’t have Excel installed on your computer. This Excel Compare Tool from Aspose is a good web-based comparison tool for two Excel files.

Load the first (main) Excel file into the first field, drag another file into the second field, and click the Compare Now button.

If the files have multiple sheets, select the sheets you want to compare in the Sheets tab. If both worksheets have cells with different values ​​or contents, the Aspose Excel Compare tool highlights the differences in yellow.

The limitation of these web tools is that they mostly highlight different meanings. They cannot highlight inappropriate formulas, calculations, etc.

3. Compare Two Excel Files Using “Spreadsheet Compare”

Spreadsheet Compare is a reliable software for comparing two Excel files or sheets. Unfortunately, it is currently only available for Windows devices. It comes as a standalone program and is also embedded in Microsoft Excel included in Office versions / suites: Office Professional Plus (2013 and 2016) or Microsoft 365.

Using Excel Spreadsheet Comparison

If your Excel application is part of the aforementioned Office suites, you can access the Spreadsheet Compare tool through the Query add-in. If your Excel application doesn’t have a Query tab, here’s how to enable it.

Select File from the menu bar.

In the sidebar, select Options.

Select Add-ins from the sidebar, select COM Add-in from the Manage drop-down menu, and select Go.

Check the box next to Request and click OK.

Note. If the COM Add-ins page does not have a “Request” checkbox, then your version of Excel or Office does not support the Spreadsheet Compare feature. Or, your organization’s administrator may have disabled this feature. Install versions of Office with a spreadsheet comparison tool preinstalled, or contact your organization’s administrator.

Open both Excel files you want to compare in a separate window, go to the Query tab in the menu bar and select Compare Files.

Excel will automatically add the first and second files to the Compare and From dialog boxes, respectively. Select Swap Files to swap the primary and secondary files, or select Compare to start a comparison.

A new window will launch the spreadsheet comparison, highlighting any inconsistencies in your dataset. Regular cells with different values ​​will be highlighted in green. Formula cells are colored magenta, and macro cells are colored turquoise.

Raed : How to convert Excel to Google Sheets 4 best ways

Select “Export Results” to generate and save a copy of the results to your computer as an Excel document.

The report will show sheets and cell references with different datasets, as well as the exact values ​​of the old and new data.

You can share the Excel report with your colleagues, team, or other people collaborating on the file.

Use Spreadsheet Comparison as a Standalone Program

If your version of Excel or Office does not have a spreadsheet compare add-in, install the standalone software from the developer’s website. When installing the installer, make sure you check the box next to Register and activate the add-in in Excel.

After installation, run the Spreadsheet Compare Tool and follow these steps to use the Excel Document Compare.

Select Compare Files on the Home tab.

Click the folder icon next to the Compare (Old Files) dialog box to add the first document you want to compare with the tool. Add the second file to the “K (new files)” field and click “OK” to continue.

Read : How to Securely Password Protect an Excel File

Comparing spreadsheets will process the files and highlight cells with different values ​​in green.

Read : Introduction to Make a Pie Chart in Excel

Spot the Differences

The Side-by-Side View comparison tool is the best choice for Office Home users or students. If you’re using Excel for Microsoft 365 or Office ProPlus (2013 or 2016), you have the built-in Spreadsheet Compare tool. But if you don’t have Excel on your computer, the web-based Excel comparison tools will get the job done. It’s really that simple.

How to Compare Two Excel Files

How to Compare Two Excel Files