Compare Two Workbooks: Side-by-Side Methods

This guide compares two Excel workbooks side-by-side, covering the built-in Spreadsheet Compare tool, the View Side by Side feature, and manual formula-based methods. You’ll learn which approach works best for spotting differences in data, formulas, or formatting, and how to use each one step by step.

Why Compare Two Workbooks?

Comparing workbooks is a daily task for anyone who collaborates on spreadsheets—finance teams checking budget revisions, analysts validating model outputs, or managers reviewing versioned reports. The goal is to find what changed between two files: a cell value, a formula, a named range, or even a comment. Without a structured method, you might miss a critical update or waste hours scrolling through thousands of rows.

Excel offers several built-in ways to compare, and the best one depends on what you need to detect. This article covers the three main approaches: the dedicated Spreadsheet Compare tool (available in Microsoft 365 and Office Professional Plus), the View Side by Side feature for visual inspection, and formula-based methods for creating your own difference report. We’ll also discuss third-party add-ins and when you might need them.

Method 1: Spreadsheet Compare (Microsoft 365 / Office Professional Plus)

Spreadsheet Compare is a standalone tool that ships with certain editions of Office. It’s not part of the Excel ribbon by default; you launch it from the Windows Start menu. To open it, search for “Spreadsheet Compare” in Windows. If you don’t have it, you likely have Office Home & Student or Office 365 Personal—those editions don’t include it.

Once open, click Compare Files and select the two workbooks. The tool then scans for differences and displays them in a grid. Key features include:

Cell-by-cell results—every changed value, formula, or formatting is listed with its location.
Color-coded output—green for entered values, red for formulas, black for formatting, and blue for structural changes like added or deleted rows.
Filter options—you can show only certain types of differences (e.g., only formulas) to narrow down the review.
Export results—save the comparison to a new Excel file or copy it to the clipboard.

The main limitation is that it compares the entire workbook, including hidden sheets and defined names. That’s usually a good thing, but if you only care about a specific tab, you’ll need to filter afterward.

Method 2: View Side by Side (Visual Comparison)

If you just need to eyeball two versions of the same sheet, Excel’s built-in View Side by Side is the quickest option. Open both workbooks, then go to the View tab and click View Side by Side. Excel tiles the windows horizontally or vertically (you can change the arrangement).

Enable Synchronous Scrolling (it’s on by default) so both sheets scroll together. This works best when the two files have identical row and column layouts. If one file has extra rows, the sync breaks and you’ll need to scroll manually.

To make differences pop, you can also use Conditional Formatting on one sheet to highlight cells that differ from the other. For example, select the range in Sheet1, go to Home > Conditional Formatting > New Rule > Use a formula, and enter:

=A1<>'[Workbook2.xlsx]Sheet1'!A1

This highlights every cell in Sheet1 that doesn’t match the corresponding cell in Workbook2. It’s a manual setup, but it gives you a permanent visual flag.

How to Choose the Right Method

Your choice depends on three factors: what you need to detect, how often you do it, and which Excel version you have.

Need to detect everything (values, formulas, formatting)? Use Spreadsheet Compare if available. It’s the only built-in tool that covers all difference types.
Quick visual check on identical layouts? Use View Side by Side with synchronous scrolling. It’s fast and requires no setup.
Need a permanent, shareable list of differences? Build a formula-based report. It’s more work upfront but gives you a reusable template.
Comparing very large files (100k+ rows)? Spreadsheet Compare handles large files better than manual formulas, which can slow down with array-like operations.

If you only have Excel Online or the mobile app, none of the desktop tools are available. In that case, you can copy one sheet into the other as a new column and use a simple =IF(A2=B2,"OK","DIFF") formula.

Common Problems and Pitfalls

Comparing workbooks often fails or gives misleading results due to a few common issues:

Different sheet names—if the sheets are named “Data” in one file and “Data_v2” in the other, formulas and tools may not match them. Rename sheets to be identical before comparing.
Extra or missing rows—View Side by Side sync breaks when row counts differ. Spreadsheet Compare handles this by flagging structural changes, but visual methods don’t.
Formatting differences—a cell with the same value but different font color is a difference that Spreadsheet Compare catches but formula methods miss.
Dates and numbers stored as text—Excel may treat “01/01/2024” as text in one file and a date in another. Use the VALUE() function or format both columns consistently.
External links—if a workbook contains links to other files, those links may update during comparison, causing false differences. Break links or open files with links disabled (hold Shift while opening).

Third-Party Tools and Add-Ins

When built-in options aren’t enough, several third-party add-ins offer advanced comparison features. These tools typically provide:

Merging capabilities—not just showing differences but allowing you to accept or reject changes from either file.
VBA macro comparison—detecting changes in code modules, which Spreadsheet Compare does not do.
Automated reporting—scheduling comparisons and emailing results.

Popular examples include Ablebits Compare Sheets, Spreadsheet Inquire (an older Microsoft tool), and various open-source VBA scripts. When choosing a third-party tool, verify it supports your Excel version and that you trust the vendor with your data—some tools upload files to cloud servers.

Frequently Asked Questions

Can I compare two workbooks in Excel Online?

Excel Online does not have Spreadsheet Compare or View Side by Side. You can manually copy one sheet into the other and use a formula like =IF(A2=B2,"OK","DIFF") to compare values. For formatting differences, you’ll need to use the desktop version.

Why does Spreadsheet Compare show differences in cells that look identical?

This usually happens due to hidden formatting (like different number formats or fonts) or because one cell contains a formula and the other a static value. Spreadsheet Compare treats these as separate types. Check the “Type” column in the results to see why a cell was flagged.

How do I compare only specific columns or sheets?

Spreadsheet Compare doesn’t allow selecting specific sheets; it compares the whole workbook. For a targeted comparison, use a formula-based method on the specific range, or copy the columns you care about into a new workbook and compare those.

Can I compare two workbooks with different structures (e.g., different column orders)?

Yes, but it’s more complex. You’ll need to use a lookup formula like VLOOKUP or INDEX/MATCH to align data by a common key (e.g., an ID column) before comparing. Spreadsheet Compare will not align different structures; it assumes identical layouts.

Is there a way to compare workbooks without opening them?

Spreadsheet Compare can compare files without opening them in Excel—you just select the file paths. For formula methods, you must have both files open. Some third-party tools can also compare files on disk without launching Excel.

What’s the best way to compare two large workbooks (over 1 million cells)?

Spreadsheet Compare is the most efficient for large files because it processes in memory rather than recalculating formulas. If you must use formulas, avoid array formulas and use simple IF statements, and consider comparing only the relevant columns rather than entire sheets.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →