The quickest reliable way to compare two Excel spreadsheets is to put both versions in one workbook and use a comparison formula when the data is in the same order, or match records by a unique ID when rows have moved. For a visual, workbook-wide review on supported Windows editions, Microsoft’s Spreadsheet Compare tool can show differences side by side.
Choose a comparison method for your files
Before comparing, decide what “different” means for your task. Two files can differ in cell values, formulas, formatting, row order, worksheet names, or workbook structure. A method that checks values alone will not flag a changed font or formula that currently returns the same result. Knowing what matters prevents a quick comparison from giving false reassurance.
For two similarly arranged tables, a formula is usually the fastest starting point. It gives you a clear match-or-difference result for each position and can be filled across a range. If records may have been sorted or inserted, compare using a stable identifier such as an invoice number or product code instead of comparing row 2 with row 2. For a broader review of workbook changes, try Spreadsheet Compare if it is available in your Windows Office installation.
- Same rows and columns: compare corresponding cells with a formula.
- Rows reordered or added: look up records using a unique key.
- Need to inspect formulas, formatting, or workbook structure: use Spreadsheet Compare where available, or inspect the relevant features separately.
- Only need a quick visual check: open both workbooks side by side and scroll through them.
Compare matching cells with a formula
For the simplest cell-by-cell check, first make copies of the two worksheets in the same workbook, then add a third worksheet for results. Copying the source sheets into one workbook makes the references straightforward and avoids brittle links to separate files. Give the two source sheets clear names, such as Sheet1 and Sheet2.
With the values to compare in the same positions on Sheet1 and Sheet2—for example, both datasets occupy A2:D100—enter this formula in the comparison sheet’s A2 cell to check the two source cells at A2:
=IF(Sheet1!A2=Sheet2!A2,"Match","Different")
Fill the formula across and down to cover the corresponding range. The result is “Match” when the cell values compare equal and “Different” otherwise. In Excel for Microsoft 365 on Windows, drag the fill handle or copy and paste the formula across the comparison area. Excel adjusts relative references as the formula moves. The formula works in Excel for Mac and Excel for the web as well.
To count differences in a comparison range, use a separate count formula. With the results displayed in A2:D100 on the comparison sheet, enter:
=COUNTIF(A2:D100,"Different")
This counts the cells labeled Different; it does not count how many rows contain at least one difference. If you want the results easier to scan, select the comparison cells and use Home > Conditional Formatting > Highlight Cells Rules to format text containing Different. Keep the formula results even if you add colors: the words make the outcome easier to filter, copy, and audit.
Match rows by a key when order changes
A position-by-position formula can report many differences when one version has been sorted or a row has been inserted. In that case, match each record by a unique key. For example, suppose Sheet1 has product IDs in A2:A100 and prices in B2:B100, while Sheet2 has the same fields in columns A and B but in a different order. In a comparison sheet, with the ID from Sheet1 in A2 and its price in B2, use:
=IF(ISNA(XMATCH(A2,Sheet2!$A:$A)),"Missing",IF(XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B)=B2,"Match","Different"))
Fill the formula down for the records on Sheet1. It reports Missing if the ID is not found in Sheet2, Match if the looked-up price agrees, and Different if it does not. XLOOKUP and XMATCH are available in Microsoft 365 and Excel 2021 or later. The formula assumes each ID identifies one record and that the relevant price is in column B on both sheets.
Check that the key is genuinely unique. If an ID appears more than once, XLOOKUP returns the first matching record, which may not be the intended comparison. Also check for blank IDs, leading or trailing spaces, and values stored as text in one sheet but as numbers in the other. When several fields matter, repeat the comparison for each field or build a clearly defined combined key. Do not treat a row as fully matched just because its ID was found.
Use Spreadsheet Compare for a workbook-level review
Spreadsheet Compare is a Windows desktop tool included with some Microsoft Office editions. It can compare two workbooks and present differences in a side-by-side report, including changes to cell values and formulas and, depending on the comparison, formatting or workbook structure. It is useful when the files are complex or when a manual formula would only cover part of the changes you care about.
Availability depends on the Office edition and installation; it is not a feature every Excel user will see. Search the Windows Start menu for Spreadsheet Compare. In some installations, the related Inquire features can be enabled by going to File > Options > Add-ins, choosing COM Add-ins in the Manage list, selecting Go, and checking Inquire if it appears. If the tool or add-in is absent, your edition may not include it. Spreadsheet Compare is not available as the same desktop tool in Excel for Mac or Excel for the web.
Before opening the report, make sure you have selected the intended versions and understand which workbook is treated as the earlier or baseline file. Review the differences rather than assuming every reported change is an error: a changed date format, for example, may be intentional. The tool is a comparison aid, not an automatic way to merge edits or decide which version is correct.
Compare workbooks side by side manually
When you only need to inspect a small area, open both workbooks in Excel and use View > View Side by Side. If Excel prompts you to choose a second workbook, select the other file. Synchronous Scrolling, available with the side-by-side view, helps keep the two windows aligned as you scroll. This is a visual review, so it is less dependable for large tables or subtle differences such as a value that is hidden by the column width.
For formulas that refer to separate workbooks, Excel can create external links, but those links depend on the source file and its location. If the file is renamed or moved, a link may need updating. For a one-time comparison, copying both relevant sheets into a new comparison workbook is often easier to manage. Keep the original files unchanged so that you can verify the source data later.
Common comparison problems to check
- Different row order: use a unique key rather than comparing cells at the same coordinates.
- Displayed value versus stored value: two dates or numbers can look identical because of formatting while storing different underlying values, or look different because the formats differ. Check the formula bar and number formats when the distinction matters.
- Formulas versus results: a direct equality formula compares the values returned by cells, not whether their formulas are written the same way. Two different formulas may currently return the same result.
- Blank cells and empty strings: a genuinely empty cell and a formula that returns an empty string can appear alike on the sheet but are not the same kind of content. Use a method that checks formulas or cell contents if that distinction matters.
- Extra spaces or inconsistent types: an ID with a trailing space may fail to match an otherwise identical ID. Standardize the source data or investigate unmatched records before concluding the information is missing.
- Errors in source cells: an error value can affect comparison formulas. Identify and resolve or separately review source errors instead of treating every unexpected result as a normal mismatch.
How to choose the fastest safe option
For a small, regularly arranged table, the same-position formula is usually the quickest method to set up and verify. For a list that may have changed order, a key-based lookup is more meaningful. For a workbook with many sheets, formulas, and formatting changes, Spreadsheet Compare can provide a wider view if your Windows Office edition includes it. A side-by-side view is best for a brief visual check, not a complete audit.
Comparison results can reveal sensitive information, especially when workbooks contain customer, employee, or financial data. Avoid uploading files to an unfamiliar online comparison service unless your organization permits it and you understand its data-handling terms. Save a separate comparison copy, preserve the originals, and review important differences before replacing or sharing a workbook.
Frequently Asked Questions
Can Excel compare two files automatically?
Some Windows Office editions include Spreadsheet Compare, which can report differences between workbooks. If you do not have it, compare copied sheets with formulas, match records by a key, or use View > View Side by Side for a manual review.
How do I compare two Excel sheets and highlight differences?
Put corresponding data on two sheets in the same workbook and use a comparison formula on a third sheet, then apply conditional formatting to the results. This makes mismatches visible without changing the source sheets.
Can I compare spreadsheets when the rows are in a different order?
Yes. Match records by a unique ID or other stable key, then compare the fields returned for that record. A direct cell-by-cell check assumes corresponding records occupy the same rows.
Does Excel compare formulas or just values?
A formula such as a direct equality check compares the values returned by the cells. It does not establish that the underlying formulas are identical. Use a workbook comparison tool or inspect the formula text when formula changes are what you need to detect.
Can I compare Excel spreadsheets on a Mac or in a browser?
Comparison formulas work in Excel for Mac and Excel for the web, though ribbon details can vary. Microsoft’s Spreadsheet Compare desktop tool is for supported Windows Office installations, so use formulas or a side-by-side review on Mac or the web.
Related
- Open Unsaved Excel Files: Recovery and Next Steps
- Open Point List Template for Excel: Setup and Use Guide
- Is Microsoft Excel Open Source? Free Alternatives Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Auto Flash Fill in Excel: How It Works and When to Use ItTo use Flash Fill automatically in Excel for Microsoft 365 on Windows, type an example of the result…
- Excel Data Validation With Color: Step-by-Step GuideTo add color to Excel data validation, create a drop-down list with Data > Data Validation, then use…
- Excel Goal Seek Shortcut Key: Open and Use the ToolIn Excel for Microsoft 365 on Windows, press Alt+A, W, G in sequence to open Goal Seek; do…
- How to Set Data Validation in Excel for Reliable EntriesTo set data validation in Excel, select the cells you want to control, then choose Data > Data…
- Excel Table Headers on Each Printed Page: How to Repeat ThemTo print the same column headings on every page in Excel for Microsoft 365 on Windows, open Page…
- How to Change Zoom in Excel: Quick Steps and FixesTo change the zoom in Excel for Microsoft 365 on Windows, use the zoom slider at the bottom-right…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.