How to Compare Two Excel Workbooks Cell by Cell
Question details
The user wants to compare the contents of two Excel workbooks cell by cell to identify differences in values, formulas, or formatting.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Reviewing or auditing multiple versions of an Excel file to find changes, discrepancies, or updated information.
- Observed behavior
- The user needs an effective method to spot data differences between two separate workbooks natively in Excel.
Make sure both Excel workbooks you want to compare are saved and currently open on your desktop before starting the comparison process.
Use the View Side by Side Feature
This built-in feature allows you to arrange two open workbooks on your screen simultaneously for a manual, visual comparison.
Launch Excel and open the two workbook files you want to compare.
Click on the 'View' tab located in the top ribbon menu.
Click on 'View Side by Side' in the Window group. Both workbooks will tile horizontally or vertically.
Click 'Synchronous Scrolling' in the same Window group. This ensures that when you scroll down or across in one workbook, the other scrolls automatically to match it.
Use the Spreadsheet Compare Tool
Perform an advanced, automated cell-by-cell comparison of values, formulas, and formatting using the standalone Spreadsheet Compare app.
Compare Workbooks Effortlessly with WPS Spreadsheet
WPS Office offers a powerful Spreadsheet tool that perfectly handles Microsoft Excel files. You can easily open multiple workbooks and compare data side-by-side for free.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the Spreadsheet application.
- 2. Open both files: Open the two workbooks you wish to compare within WPS Spreadsheet.
- 3. Select View Side by Side: Go to the 'View' tab on the top ribbon and select 'View Side by Side'.
- 4. Compare Data: Enable 'Synchronous Scrolling' to navigate through both sheets simultaneously and spot data differences.
Frequently Asked Questions
Why is the Spreadsheet Compare tool missing from my PC?
Spreadsheet Compare is not included in all Office versions. It is primarily available in Microsoft Office Professional Plus and Microsoft 365 Apps for enterprise. If you are using Microsoft 365 Family, Personal, or standard student editions, you will not have access to this tool.
Can I use conditional formatting to find differences between two sheets?
Yes. If you copy the data from both workbooks into two separate tabs within the same workbook, you can use conditional formatting. Select the data range in Sheet1, click Conditional Formatting > New Rule > Use a formula, and enter a formula like '=A1<>Sheet2!A1'. Set a fill color to highlight the differences.
How can I stop the two workbooks from scrolling together in Side by Side view?
When using the View Side by Side feature, click the 'Synchronous Scrolling' button located on the View tab to toggle it off. This allows you to scroll through each workbook independently.
Are there third-party tools to compare Excel files?
Yes, there are several third-party add-ins and standalone software options available for comparing Excel files. However, always ensure you back up your original files before processing them with third-party utilities.
