Why Excel Is Slow: 9 Fixes for Large Files

Before fixing anything, press Ctrl+End.

That jumps to the last cell Excel thinks you're using. If your data ends at row 4,000 and Excel lands you on row 600,000, you've found the problem in three seconds — and it's the most common cause of a slow file that isn't actually big.

The nine fixes below are ordered by how often they're the culprit and how much they recover.

The same three answers, computed two ways. The difference is which rows Excel was asked to look at.

First, find out what's slow

Check	What it tells you
Ctrl+End	Whether the used range has run away
File size against row count	20MB for 5,000 rows means something other than data
Ctrl+G → Special → Formulas	The status bar shows how many formula cells you have
Formulas → Calculation Options → Manual, then F9	How long one full recalculation takes

That last one is the measurement that matters. Set calculation to Manual, press F9, and count. If F9 takes eight seconds, every edit you make in Automatic mode costs eight seconds.

1. Reset the used range

Excel remembers the furthest cell ever touched, even after the contents are deleted. Formatting a whole column, or one stray space in row 800,000, permanently expands what Excel considers the sheet — and it allocates memory for all of it.

1. Select the first empty row below your data.
2. Ctrl+Shift+Down, then right-click → Delete (delete the rows, don't just clear them).
3. Repeat to the right with Ctrl+Shift+Right.
4. Save and close the file, then reopen it.

⚠️ Step 4 isn't optional. The used range only resets when the file is saved and reopened, so if you check Ctrl+End immediately after deleting, it'll look unchanged and you'll think it didn't work.

Files routinely drop from 40MB to under 2MB on this alone.

2. Bound your ranges

A whole-column reference asks Excel to consider 1,048,576 rows. Two columns in a SUMIFS is more than two million cells — for a sheet with five thousand rows of data.

Slow:  =SUMIFS(D:D, B:B, $H$2)
Fast:  =SUMIFS($D$2:$D$5001, $B$2:$B$5001, $H$2)

That's 210 times less work, per formula, per recalculation. Multiply by a few hundred formulas and it's the difference between instant and a spinning cursor.

Modern Excel optimises some whole-column operations, but not reliably and not all of them — and the optimisation disappears the moment the column has any formatting applied.

Better still, make the data a Table with Ctrl+T and reference Orders[Amount]. The range then covers exactly the rows that exist and grows as you add more.

3. Get rid of volatile functions

A volatile function recalculates whenever anything anywhere in the workbook changes — not just when its own inputs do. Type a number into an unrelated cell on another sheet and every volatile formula in the file recalculates.

Volatile	Use instead
OFFSET()	INDEX() — does the same job, isn't volatile
INDIRECT()	INDEX(), or a Table name
NOW(), TODAY() in every row	One cell holding TODAY(), referenced by the rest
RAND(), RANDBETWEEN()	Generate once, then paste as values
CELL(), INFO()	Avoid in bulk entirely

OFFSET and INDIRECT are the ones that hurt, because they turn up inside lookups repeated down thousands of rows. Replacing them with INDEX is usually a straight swap.

The TODAY() fix is worth doing even in a small file: one cell computes it, every formula reads that cell. Same answer, one volatile calculation instead of five thousand.

4. Clean up conditional formatting

Open Conditional Formatting → Manage Rules → This Worksheet. If you made three rules and the list shows sixty, that's your problem.

Copying and pasting rows splits each rule into fragments covering a few rows each, and they multiply every time anyone copies anything. Every fragment is evaluated separately on every recalculation.

The fix is blunt: Clear Rules → Clear Rules from Entire Sheet, then reapply your three rules once to the whole range. Afterwards, paste with Ctrl+Shift+V to stop it recurring.

This is covered in more detail in the overdue-tasks guide, where the same fragmenting problem produces rules nobody can account for.

5. Search once, not three times

Pulling three fields with three lookups runs the same search three times:

H2:  =IFERROR(MATCH($A2, Products!$A$2:$A$5001, 0), "")

B2:  =IF($H2="", "", INDEX(Products!$B$2:$B$5001, $H2))
C2:  =IF($H2="", "", INDEX(Products!$C$2:$C$5001, $H2))
D2:  =IF($H2="", "", INDEX(Products!$D$2:$D$5001, $H2))

One MATCH in a helper column, three cheap INDEX lookups off it. Two thirds of the work disappears, and the technique is explained in full here.

6. Break the links to other workbooks

Data → Queries & Connections → Edit Links shows every external file this one depends on. Each is checked on open and refreshed on recalculation, and if any live on a network drive or a synced folder, that's a network round trip in the middle of your calculation.

Where the source data no longer changes, click Break Link — it converts the formulas to their current values permanently. Where it does change, copy the source data into this workbook and update it deliberately.

7. Look for objects you can't see

F5 → Special → Objects. If the status bar reports 900 objects on a sheet you thought had none, you've found a few megabytes.

This happens when data is pasted repeatedly from a web page or another sheet — invisible or tiny shapes come with it and stack up. Once selected, Delete removes the lot.

Pasted screenshots are worth checking too. A phone photo dropped into a cell can be 5MB on its own; compress images through Picture Format → Compress Pictures.

8. Reduce the number of distinct formats

Excel stores every unique combination of font, size, colour, border and number format as a separate style, and it caps out at roughly 64,000 of them. Long-lived files built by several people creep toward that limit, and the file slows and swells well before reaching it.

The cure is to select everything, clear all formatting, and reapply deliberately — drastic, and effective when a file has become genuinely unmanageable. Make a copy first.

The symptom that points here specifically: the file is slow to scroll and open, rather than slow to calculate.

9. Work in Manual calculation

Not a fix so much as a way to work while you apply the other eight.

Formulas → Calculation Options → Manual. Excel then recalculates only when you press F9, so editing is instant and you choose when to pay the cost.

⚠️ Two things to know. The setting saves with the workbook, so anyone who opens it next inherits Manual mode and may read stale figures without noticing. And the whole point of the mode is that what you see can be out of date — press F9 before trusting any number, and switch back to Automatic before sending the file to anyone.

When it isn't a spreadsheet problem

Some honesty about the ceiling. A sheet holds just over a million rows, but Excel becomes unpleasant long before that — somewhere around 100,000 rows with formulas across several columns, on ordinary hardware.

If you've done all nine of these and it's still slow, the file has outgrown the tool. The next steps, in order of effort:

• Power Query to do the transformation before the data lands in cells
• Power Pivot to hold millions of rows in the data model rather than on a sheet
• A database, if it's genuinely at that scale

Recognising this early saves more time than any of the fixes above.

Does this work in Google Sheets?

The principles carry over — bounded ranges, no volatile functions, fewer conditional formatting rules — but the ceiling is much lower. Sheets slows noticeably in the tens of thousands of rows and has a hard limit of 10 million cells per file.

Sheets also recalculates in the cloud, so a slow sheet is slow for everyone at once, and IMPORTRANGE and other web functions add a network delay Excel doesn't have.

Common questions

Will a faster computer fix it?

Partly. Excel's calculation is multi-threaded, so more cores help, and RAM matters for large files. But a badly built workbook stays badly built — a volatile formula across 50,000 rows is wasteful on any machine.

Is .xlsb faster than .xlsx?

Yes, meaningfully. The binary format opens and saves faster and produces smaller files. The trade-off is that it's less portable and some tools can't read it. Worth it for a large working file, not for something you send to clients.

Why is my file huge when there's hardly any data?

Used range, objects, or formats — the first three checks. In that order.

Does deleting rows reduce the file size?

Only after saving and reopening. Until then Excel keeps the old used range in memory.

Should I split it into several files?

Rarely. Links between workbooks are slower than one larger file and break when files move. Splitting by year, into files you no longer open together, is the exception.

The short version

• Ctrl+End first. If it lands far below your data, delete the empty rows, then save and reopen.
• Never use A:A. Bound the range, or use a Table.
• OFFSET and INDIRECT recalculate constantly. INDEX does the same work and doesn't.
• One cell holds TODAY(); everything else references it.
• Sixty conditional formatting rules where you made three: clear and reapply.
• MATCH once into a helper column, then INDEX off it.
• F5 → Special → Objects finds the invisible shapes.
• Manual calculation while you work — and switch it back before sharing.
• Past about 100,000 rows, stop optimising and change tools.

Next steps: the biggest single saving is usually restructuring repeated lookups, and if the file got this way through years of pasted imports, the cleaning sequence is worth running first. For the conditional formatting fragments specifically, the overdue-tasks guide explains how they multiply.