Analistable

The best tools to compare two spreadsheets

By the Analistable team · Updated · 5 min read

For a quick check, formulas and conditional formatting inside Excel are enough. For a full report including formulas and formatting, Spreadsheet Compare (Windows, enterprise editions) is the most complete built-in option. For lists whose rows move around, compare by key with Power Query or a tool that matches rows — such as our free browser-based compare tool.

Part of our guide: How to compare two spreadsheets for differences and matches

What do you need to find?

  • Changed cells in two versions of the same layout → a cell-by-cell tool.
  • Added and removed rows in two exports of a list → a key-based tool.
  • Changed formulas or formatting in a model → a workbook comparison tool such as Spreadsheet Compare.

Comparison

Tools for comparing spreadsheets
ToolCell by cellBy key / row matchFormulas & formattingPlatformsCost
Formulas + conditional formattingYesYes (COUNTIF, XMATCH)NoAny Excel, Google SheetsIncluded
View Side by SideVisual onlyNoVisual onlyExcelIncluded
Spreadsheet Compare (Inquire)YesPartlyYesWindows, enterprise editionsIncluded in those editions
Power Query anti/inner joinsVia joinsYesNoExcel 2016+, Microsoft 365Included
Ablebits Compare SheetsYesYesNoWindows add-inPaid
General diff tools (e.g. Beyond Compare)YesPartlyValuesDesktopPaid
Analistable compare toolNoYes (rows only in one file)NoBrowser, no uploadFree

Features summarised from each vendor's documentation; check current editions and prices with the vendor.

Built into Excel

Formulas cover most needs: see comparing two Excel sheets for differences and comparing columns. Spreadsheet Compare is worth knowing if you have it: open it from the Windows Start menu (search “Spreadsheet Compare”) or enable the Inquire add-in under File → Options → Add-ins → COM Add-ins.

Power Query for repeatable reconciliations

If you compare the same two exports every week, Power Query's left and right anti joins give you removed and added rows, and an inner join plus a comparison column gives changed values. Build it once and refresh. See Power Query's join kinds.

Online and browser tools

Many online diff tools upload both files. For confidential data, use one that runs locally — our guide to merging without uploading shows how to check. The Analistable compare tool lists rows that appear in only one of two files, in your browser.

Recommendation

  • One-off check of two versions → formulas or conditional formatting.
  • Auditing a financial model → Spreadsheet Compare, if your edition has it.
  • Weekly reconciliation of two exports → Power Query.
  • Sensitive lists, no setup → a browser-local tool.

How we compared them

Each option was judged on the questions that decide whether a comparison is useful in practice:

  • Row matching: can it match rows by an ID, or does it assume row 5 is always row 5?
  • Output: does it give a list you can filter and share, or only colours on a screen?
  • Repeatability: can next month's comparison run with one click?
  • Scope: values only, or formulas, formatting and structure too?
  • Setup and access: does it need an add-in, a specific Windows edition or an install?
  • Privacy: do the files leave your computer?

Formulas and conditional formatting: strengths and limits

Formulas are free, available everywhere and transparent: anyone can click a cell and see the rule. =A1<>Old!A1 handles fixed layouts and COUNTIF or XMATCH handle lists in any order. The limits show up at scale: thousands of lookup formulas slow a workbook, the setup has to be redone for each new pair of files, and formulas compare displayed values, not the formulas behind them. They are the right choice for one-off checks and for people who need to see exactly how a difference was found.

Spreadsheet Compare: strengths and limits

Spreadsheet Compare opens two workbooks and lists every difference by sheet and cell, categorised as values, formulas, names, formatting and more, and can export the results to Excel. That makes it the strongest built-in option for auditing a model where someone may have overwritten a formula with a typed number. Its limits: it is only available on Windows in certain editions, it compares by cell position (so an inserted row creates many reported changes), and it is not built for a repeatable list reconciliation.

Power Query: strengths and limits

Power Query matches rows by key with merge joins: a left anti join lists rows only in the first table, a right anti join rows only in the second, and an inner join with a custom column comparing two values lists changed rows. Steps are recorded, so the comparison refreshes when you replace the source files. It handles far more rows than formulas comfortably do. The cost is a learning curve and the fact that it does not compare formulas or formatting. Power Query is in Excel for Windows 2016 and later and in Microsoft 365 for Mac, though some features differ on Mac.

Add-ins and desktop diff tools

Commercial Excel add-ins such as Ablebits add a guided wizard for comparing sheets by key or by position and marking the differences, which suits people who compare often but don't want to write formulas. General file-comparison tools such as Beyond Compare can show two spreadsheets side by side and highlight changed values; they are strong for text and CSV exports and are used by developers to compare data files. Both are paid, need installing and may need IT approval. Check each vendor's current feature list and licensing before buying.

Example: choosing a tool for three real tasks

Matching the tool to the task
TaskToolReason
Check what a colleague changed in a budget templateSpreadsheet Compare, or a difference sheetSame layout; formula changes matter
Find customers added or removed between two CRM exportsPower Query anti joins, or a browser compare toolRows move around; match by customer ID
Confirm a 50-row price list matches the supplier'sFormulas (XLOOKUP + comparison)Small, one-off, needs to show changed prices

For the step-by-step methods behind these, see the guide to comparing spreadsheets, and for key-based reconciliation of values such as totals, reconciling data in Excel.

Common mistakes when choosing a tool

  • Using a cell-by-cell tool on exports whose rows are sorted differently — the report flags almost everything.
  • Assuming a tool compares formulas when it only reads values (and the reverse).
  • Uploading confidential payroll or customer data to an online diff site without checking where it goes.
  • Building a complex formula comparison for a task that runs every month, when a refreshable Power Query would save time.

Frequently asked questions

What is the best free way to compare two Excel files?
Formulas and conditional formatting in Excel, or Spreadsheet Compare if your edition includes it. For row-level differences between two exports, a browser-local compare tool is quickest.
Can Google Sheets compare two sheets?
Not with a built-in feature, but the same formulas work: =A1<>Sheet2!A1, COUNTIF and conditional formatting.
Which tool compares formulas, not just values?
Spreadsheet Compare reports changed formulas, values and formatting. Formula-based methods only compare the displayed values.
Can I compare two spreadsheets on a Mac?
Spreadsheet Compare is Windows-only, but formulas, conditional formatting, View Side by Side and Power Query in Microsoft 365 for Mac all work, as do browser-based tools.
How do I compare two spreadsheets with thousands of rows?
Use a key-based method that doesn't need one formula per row: Power Query anti joins, a script, or a compare tool that matches rows by ID.

Related guides