To compare two Excel files, use Spreadsheet Compare or Excel’s Inquire add-in if your Windows edition includes it; for a simple cell-by-cell check, compare corresponding cells with a formula such as =Sheet1!A1<>Sheet2!A1. The right method depends on whether the workbooks have the same layout, whether rows may have moved, and whether you need to find changes in formulas or formatting as well as values.
What comparing Excel files can reveal
An Excel file comparison can mean several different things. You might want to find changed numbers in two versions of a budget, identify rows added to a customer list, check whether formulas were edited, or see whether someone changed cell formatting. These are related tasks, but one method does not always catch every kind of difference.
The simplest comparison assumes that corresponding information occupies the same cells in both workbooks. If the original value is in cell A1 in one file, for example, the updated value must also be in A1 in the other. If rows have been sorted, inserted, or deleted, matching cells by position can give misleading results. For those cases, compare records using a shared identifier such as an invoice number or product ID.
Compare workbooks with Inquire or Spreadsheet Compare
For a broad comparison of two workbooks on Windows, Excel’s Inquire add-in can open Spreadsheet Compare, a Microsoft tool that reports differences between files. It can be more useful than checking cells individually when a workbook contains many sheets or when you need to inspect formulas, values, or other workbook changes.
- Open Excel and check whether an Inquire tab is present on the ribbon.
- If it is missing, select File > Options > Add-ins. In the Manage box, select COM Add-ins, choose Go, and check Inquire if it is listed.
- With both workbooks available, select Inquire > Compare Files, then choose the two files to compare.
- Review the comparison report and use its categories or results to investigate differences.
Inquire is available only in certain Windows editions, including Microsoft 365 Apps for enterprise and some Office Professional Plus editions. If it does not appear in COM Add-ins, your Excel edition may not include it, or an administrator may have disabled it. Spreadsheet Compare can also be available as a separate application in supported Office installations. The exact availability depends on the license and installation, so check your organization’s software options rather than assuming every Excel copy includes it.
Keep the two source files unchanged while reviewing the report. A comparison identifies differences; it does not decide which version is correct or merge changes safely for you. Save a separate copy if you plan to reconcile the files.
Compare matching cells with a formula
When sheets have the same layout, a formula is a quick way to flag unequal cells. For example, assume the old value is in Sheet1 cell A1 and the new value is in Sheet2 cell A1. Enter =Sheet1!A1<>Sheet2!A1 in another cell. It returns TRUE if the two cell values differ and FALSE if they match. The formula uses simple sheet names; if a sheet name contains spaces, enclose the name in single quotation marks, as in ='Old Data'!A1<>'New Data'!A1.
To check a whole block, put the comparison formula beside the first pair of cells and fill it across and down. For example, if the sheets contain data in A1:D100, the relative comparison in a helper sheet can be adjusted for each corresponding position. A TRUE result marks a difference, while FALSE means the compared values are equal. You can filter the helper results to focus on TRUE cells.
To make differences easier to scan directly on a sheet, use conditional formatting. Select the relevant cells on the first sheet and choose Home > Conditional Formatting > New Rule, then select a rule that uses a formula. With the selected range beginning at A1 and the corresponding comparison values in Sheet2, use =A1<>Sheet2!A1. Choose a format and apply the rule. Since conditional-formatting formulas adjust relative references, the first cell in the formula must match the top-left cell of the selected range. Test the rule on a few known examples before relying on it across a large range.
Compare lists when rows have moved
If the files contain lists whose rows may be in a different order, compare by a stable key instead of comparing row 2 with row 2. Suppose the old sheet has product IDs in A2:A100 and prices in B2:B100; the new sheet has IDs in A2:A100 and prices in B2:B100. On the old sheet, a lookup can find each ID in the new list and return its new price. With XLOOKUP available, enter =XLOOKUP(A2,New!$A$2:$A$100,New!$B$2:$B$100,"Not found") in C2 and fill down. If product ID P104 appears in A2 and its new price is 18.5, the formula returns 18.5; if the ID is absent, it returns Not found.
To flag whether that looked-up price differs from the old price, assume the returned price is in C2 and the old price is in B2. Enter =B2<>C2 in D2. TRUE means the values differ; FALSE means they match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In older versions, a lookup can be built with functions such as VLOOKUP, but the lookup column must be arranged appropriately.
A key-based check still needs care. Duplicate IDs can make a lookup return only one matching record, and a changed or missing ID may look like a deleted-and-added record. If the key is not unique, combine fields that together identify the record or examine duplicates separately. For financial or operational lists, check totals and record counts as an additional safeguard.
Values, formulas, formatting, and blank cells
Decide what “different” means before comparing. Two cells might display the same number but contain different formulas. Conversely, they might contain the same formula but show different calculated results because inputs elsewhere differ. A workbook comparison tool can help distinguish categories of changes, while a basic inequality formula primarily answers whether the compared cell values differ.
Formatting differences matter in some situations, such as a changed date format or a missing warning color, but they may be noise in others. Number formats can make the same stored value appear different: a value of 0.5 may display as 50% in one cell and 0.5 in another. Check both the stored value and the display format when the visible result is important. Also consider whether hidden rows, hidden columns, filters, named ranges, or sheet structure need review; a basic cell formula will not report all such workbook-level changes.
Blank cells deserve attention too. A genuinely empty cell and a formula that returns an empty string may look alike on the screen but are not identical in every context. If blanks have business meaning, test the relevant cells directly and decide whether those two cases should count as a difference. Similarly, numbers stored as text can look like numbers while behaving differently in calculations.
Choose a comparison method
Use the method that matches the structure and purpose of the check:
- Spreadsheet Compare/Inquire: Best suited to inspecting broad changes across supported workbooks, especially when formulas and workbook details matter.
- Cell formulas: Practical for two similarly structured sheets and a focused, repeatable check.
- Key-based lookups: Better for lists where records may have moved, provided the matching key is reliable.
- Conditional formatting: Helpful for visually highlighting differences in aligned ranges, but it does not produce a complete audit report by itself.
- Manual review: Appropriate for a small number of important changes, and useful as a final check after an automated comparison.
For a one-time check of a small table, formulas may be fastest. For a recurring process, standardize the workbook layout, define which fields form a unique key, and keep a documented comparison procedure. For an audit or high-impact update, retain both original files and the comparison results so another person can verify the decisions.
Common problems and how to avoid them
A comparison can report many differences even when the underlying information is nearly the same. The cause may be shifted rows, inconsistent IDs, extra spaces, different date or number formats, or formulas recalculating from changed inputs. Before drawing conclusions, confirm that the files represent the same reporting period and that the relevant sheets and ranges are being compared.
Other practical checks include:
- Confirm that neither workbook is an older or filtered extract that omits records.
- Check for duplicate keys before using a lookup to match rows.
- Compare totals or counts to catch omissions that individual spot checks miss.
- Look for formulas that refer to different ranges or external workbooks.
- Make sure calculation results are current if formulas depend on changed inputs.
Excel for the web and Excel for Mac do not provide the same Inquire/Spreadsheet Compare workflow as supported Windows editions. You can still use worksheet formulas and conditional formatting in supported Excel versions, though menus and feature availability may differ. If your organization needs a full workbook comparison and the Windows add-in is unavailable, use a controlled copy of the files and a method that can compare the specific content you care about.
Frequently Asked Questions
Can Excel compare two files automatically?
Yes, in supported Windows editions, Inquire can launch Spreadsheet Compare to report differences. If that tool is unavailable, formulas and lookups can compare worksheet data, but you must set up the ranges or matching keys.
How do I compare two Excel sheets and highlight differences?
For sheets with matching layouts, select the range and create a formula-based conditional-formatting rule using =A1<>Sheet2!A1 when A1 is the selected range’s top-left cell. Adjust the sheet name and range to fit your workbook.
Can I compare two Excel files if the rows are in a different order?
Yes. Match records by a unique identifier, such as an order number, rather than comparing row positions. A lookup can retrieve the corresponding value from the other list, after which a second check can flag unequal values.
Does a TRUE result mean the cells are different?
For an inequality check such as =A1<>B1, TRUE means Excel considers the compared values unequal and FALSE means they match. It does not by itself tell you whether a formula, format, or hidden workbook feature changed.
Can Excel compare workbooks on a Mac or in a browser?
Worksheet formulas can be used in Excel for Mac and Excel for the web, subject to feature availability. The Inquire/Spreadsheet Compare process described here is for supported Windows desktop editions, not a universal feature across platforms.
Related
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Grouped Scatter Plot: Setup, Steps, and TipsTo make a grouped scatter plot in Excel, give each group its own data series, with one range…
- Can You Copy an Excel Spreadsheet? Methods ExplainedYes. In Excel for Microsoft 365 on Windows, make a separate copy of an entire spreadsheet with File…
- Add Page Numbers in Word: Steps, Sections, and FixesIn Microsoft Word for Microsoft 365 on Windows, select Insert > Page Number, choose where the number should…
- Excel Shortcuts You Need to Know for Faster WorkflowsThe Excel shortcuts worth learning first are Ctrl+C, Ctrl+V, and Ctrl+Z for everyday edits; Ctrl+Arrow keys to move…
- Group Rows in Excel for Sorting Without Splitting DataTo keep related records together when sorting in Excel, select the entire data range (or convert it to…
- Excel Shortcut to Enter a Cell: F2, Enter, and More ExplainedIf you mean “enter” or edit the contents of the selected cell, press F2 in Excel for Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.