Excel Comparison13 min read

Comparing Large Excel Files: Why It Freezes, and What to Change

Excel does not slow down because a file is big. It slows down because of how many comparisons you have asked it to perform, and on every recalculation. Both of those are fixable.

Short answer

A large Excel comparison is slow because every matching formula you copy down the sheet searches the other range on every recalculation. The fixes, in order of how much they usually help, are: replace whole-column references with ranges limited to the actual data, remove volatile functions such as OFFSET and INDIRECT that force a full recalculation, use a binary lookup on a sorted range instead of an exact match over unsorted data, and consider moving the comparison into Power Query, where the work happens outside the worksheet grid.

Why it slows down — the actual mechanism

A file becomes slow because of the volume of comparisons, not the number of rows on its own. Consider what a single formula actually does.

Formula	What Excel does for each row	Why that multiplies
COUNTIF over a whole column	Searches up to the last row of the column	Copied down 50,000 rows, that is 50,000 separate searches, each over a very wide range
XLOOKUP or VLOOKUP over unsorted data	Scans until it finds a match	A miss scans the entire range before returning a result — and misses are exactly what you are looking for
SUMPRODUCT with EXACT	Evaluates the array for every row	The case-sensitive version of COUNTIF, and far more expensive per row
Conditional formatting rule	Evaluated for every cell in the Applies To range	A rule over 50,000 rows in six columns is evaluated 300,000 times

It is arithmetic, not a benchmark

Fifty thousand formulas each searching a hundred-thousand-row column is on the order of five billion comparisons. Excel is fast, but not five-billion-comparisons-in-a-moment fast — and it repeats the work every time anything in the workbook changes. That repetition, not the row count, is what you are fighting.

First, find the real cause

Before optimising anything, confirm what is actually slow. Most slow workbooks have one dominant cause rather than fifty minor ones.

Check the true used range

Press Ctrl+End. If it lands on a row far beyond your data, the sheet carries phantom rows that every whole-column formula has to consider. Delete the empty rows and columns below and to the right of your data, save, and re-check.

Press F9 and time the recalculation

If recalculation is slow but opening is fast, the problem is formulas. If opening is slow and recalculation is instant, the problem is file bloat — excess styles, images, embedded objects or a very wide used range.

Look for volatile functions

Search the workbook for OFFSET, INDIRECT, NOW, TODAY, RAND and RANDBETWEEN. Each one forces a recalculation of itself, and often of anything that depends on it, on every change anywhere in the file.

Count the conditional formatting rules

Home → Conditional Formatting → Manage Rules, showing rules for the whole workbook. A handful of rules over large ranges costs more than most people expect.

Check the file size against the data

An .xlsx file is a zip archive. If the file is far larger than the data should be, that size is the problem — usually a bloated style table, a pasted image, or leftover formatting across a million rows.

Fix 1: limit every range to the actual data

This is the change that most often makes the largest difference, and it is the least clever one.

Instead of	Use	Why
`=COUNTIF($D:$D,$A2)`	`=COUNTIF($D$2:$D$100001,$A2)`	Excel no longer considers a million empty rows on every one of the 50,000 rows you copied it to
`=XLOOKUP($A2, Other!$A:$A, Other!$D:$D, "Not found")`	`=XLOOKUP($A2, Other!$A$2:$A$100001, Other!$D$2:$D$100001, "Not found")`	Same reasoning, and the lookup range is now something Excel can work with efficiently

If the range changes size each month, either leave headroom and keep the range fixed, or convert both sets of data to Excel Tables and reference the structured names — Table1[Invoice] — which expand automatically without becoming a whole-column reference.

Fix 2: let Excel stop recalculating

Formulas → Calculation Options → Manual stops Excel recalculating after every edit. You press F9 when you want it to recalculate. While you are building or editing the comparison, this is the difference between a usable file and an unusable one.

Switch it back before you send the file

A workbook in manual calculation mode shows whatever the values were at the last recalculation. A colleague who opens it sees stale numbers and has no way of knowing unless they check the setting. Sending a file in this state is how a wrong figure gets reported. Set it back to Automatic, save, then send — and if you must leave a working file in manual mode, put a note on the sheet itself.

Fix 3: use a binary lookup on a sorted range

An exact match against unsorted data means scanning. If the lookup range is sorted ascending, XLOOKUP and MATCH can search it in a fraction of the steps.

Method	Requires	Trade-off
XLOOKUP with the default search mode	Nothing	Scans for an exact match — slowest for a range with many misses
XLOOKUP with search mode 2	The lookup range sorted ascending	Materially faster; returns the nearest match if an exact one is absent, so confirm the behaviour on your data
COUNTIF on a sorted range	Nothing — COUNTIF does not benefit from sorting the same way	Limited benefit; the win here is the limited range, not sorting

The binary option changes the answer when there is no exact match, which matters if you are relying on the fallback marker to identify missing records. Test it on a small range before you apply it to the whole comparison.

Fix 4: move the comparison out of the grid

This is the change that solves the problem rather than mitigating it. Power Query does the comparison in its own engine, and the results land in a worksheet table with no formulas at all.

1Load both files with Data → Get Data → From File, rather than pasting them into sheets.
2Set the key column to the same data type on both sides — this is the step that most often goes wrong.
3Merge on the key with a Left Anti join for each direction. Those are your two missing lists.
4Merge with an inner join and expand the value columns to find the records that exist on both sides and differ.
5The three outputs load into worksheets as plain values. There is nothing to recalculate.

Power Query is covered in more detail in Power Query in Excel, and it is also the answer to the other half of the problem — a comparison you run every month can be refreshed rather than rebuilt.

Fix 5: shrink what you are comparing

A surprising share of slow comparisons are comparing far more than they need to. Before optimising formulas, ask whether every column is required.

Extract only the columns you match on. A comparison needs a key and the values being compared. Nine descriptive columns add weight and answer nothing.
Filter to the period first. Comparing a full financial year when the question is about August multiplies the work for no benefit.
Split the comparison by month or by entity. Twenty thousand rows across twelve sheets recalculates far faster than two hundred and forty thousand rows on one sheet, and the totals can be added at the end.
Do not compare what you can exclude on a rule. Cancelled, reversed and zero-value records rarely need to be in a comparison at all.

Fix 6: consider whether Excel is the right tool for this one

Situation	Sensible approach
A few thousand rows, occasional comparison	Formulas on a limited range — the simplest thing that works
Tens of thousands of rows, once	Power Query merge, or a formula comparison built on limited ranges with manual calculation while you build it
Tens of thousands of rows, every month	Power Query with the source files as queries, refreshed each period
Files too large to open comfortably in a workbook	Extract to CSV and do the comparison in something that reads files rather than cells
The comparison is evidence and has to be explained	A tool that records the rules and the reason per record, so the output does not depend on the person who built the sheet

Extracting to CSV is not a defeat

If the two files are large, exporting each to CSV and comparing those is not a workaround — it is often the correct engineering choice. A CSV has no formulas, no formatting, no styles and no calculation model, which is exactly why it loads quickly. Do the comparison on the minimal columns you actually need, and take the answer back to your accounting system.

The three mistakes that make it worse

Leaving manual calculation on. The performance problem is solved and replaced with a correctness problem, which is worse.
Adding more helper columns to speed things up. Each additional column of formulas multiplies the calculation, not reduces it. Fewer, better-ranged formulas win.
Sorting both sides and comparing by position. It looks faster because the spreadsheet appears busy rather than frozen, and it produces wrong answers the moment one side has an extra row.

What a purpose-built comparison does differently

The reason a dedicated tool handles this comfortably is structural rather than clever: it reads the two files into memory and performs the match outside the spreadsheet grid, so there are no formula cells to recalculate and no conditional-formatting rules to evaluate. The rules — which columns form the key, how much tolerance, whether matches are case-sensitive — are configuration rather than formulas.

That is what the Piloteq Automate comparison workflow is built around: read both files, apply the rules you set, and return the missing and changed records as a list. If your comparison is still small enough that formulas are comfortable, the methods in comparing two Excel files are the better starting point.

If you would rather not build this by hand

Take the comparison out of the worksheet

Piloteq Automate compares two files on the key columns you choose and returns the missing and changed records, so the work does not depend on recalculating formula cells in a workbook.

✓Reads the source files directly
✓No formulas copied down a sheet
✓Exact, tolerance and last-digit match rules
✓Results as an exportable difference list

₹1,499 · Single PC License · 12-month license

See how Piloteq Automate compares two files →

Frequently asked questions

Why does Excel freeze when I compare two large files?+

Because every matching formula you copy down the sheet has to be evaluated, and each one searches the other range. A COUNTIF or XLOOKUP repeated down fifty thousand rows against a hundred-thousand-row column means a very large number of comparisons, and Excel performs them all on every recalculation. The freeze is the calculation, not the file size on its own.

What is the quickest fix for a slow comparison sheet?+

Replace every whole-column reference with a range limited to the actual data. A reference such as $A:$A forces Excel to consider over a million rows for every formula, even when only ten thousand contain data. Changing those references to $A$2:$A$10001 is usually the single largest improvement available and takes a few minutes with Find and Replace.

Should I turn off automatic calculation?+

It makes the file usable while you build the formulas, but it carries a real risk: a file left in manual calculation mode shows stale results until somebody presses F9, and that includes the person who opens it next. Use it as a working tool and switch back to automatic before you send the file anywhere. Never email a workbook that is still in manual calculation mode.

Which Excel functions make a large file slow?+

Volatile functions — NOW, TODAY, OFFSET, INDIRECT, RAND, RANDBETWEEN and CELL in some forms — recalculate on every change anywhere in the workbook, regardless of whether their inputs changed. A single volatile function in a sheet with heavy formulas can be responsible for a disproportionate share of the delay. Replacing them with static values where possible is often a large win.

Is Power Query better than formulas for comparing large files?+

For this specific job, usually yes. Power Query loads the data into its own engine rather than into worksheet cells, so the work happens outside the grid and does not depend on recalculating millions of formula cells. A merge on the key column returns the missing and changed records without a single formula being copied down a sheet.

How many rows can Excel compare?+

There is no fixed row limit at which a comparison stops working — the point at which it becomes impractical depends on how many formulas you copy down, how wide they search, how many other rules are in the workbook and how much memory the machine has. The practical test is whether the file recalculates in a time you are willing to wait. If it does not, change the method rather than the file.