⚙️ File Operations · 8 min read

Excel Slow With Large Data? Why It Happens and What to Do

Excel is slow because it holds formulas, not just data, and it recalculates after nearly every edit. The cost scales with the number of formula cells, so the fastest fix is usually to stop making one workbook hold everything: split the data into parts that recalculate quickly, or keep it as data and ask your question outside the grid. Before you buy more RAM, it is worth knowing which of the four things below you actually have, because three of them cost nothing to fix and one of them is not fixable at all.

This is one of the loudest complaints people make about spreadsheets, in every phrasing: “why is Excel so slow with large data”, “how to make Excel faster with lots of data”, and “Excel slow with large files” all describe the same afternoon of watching a spinner.

Match the symptom before you change settings

The last row is the one people misdiagnose, because a big file and a slow file are not the same thing. An .xlsx carries a compressed package of XML parts alongside the values, so a workbook can be slow at 40 MB or quick at 200 MB depending on what is inside it.

Why recalculation is the expensive part

A spreadsheet is a dependency graph, and Excel rebuilds and walks that graph whenever it calculates. In automatic mode, that happens after nearly every edit. The bill is set by how many formula cells have to be revisited, not by how many rows you have.

A worked example. Take 250,000 rows and ten formula columns of lookups and arithmetic. That is 2.5 million formula cells, and each edit can send the engine back over the ones that depend on what changed. Now delete nine of those columns and keep the data. Nothing about the file got smaller in rows, and it becomes usable again, because the cells left behind are values rather than computations.

Multithreading helps less than people expect here. Excel does calculate independent formulas in parallel, and the thread count is configurable at File → Options → Advanced → Formulas, but a dependency chain cannot be split across cores. A column where each row reads the row above it is a serial program wearing a spreadsheet costume, and a faster processor changes nothing.

There is a hard ceiling on all of this, and it is worth stating once: a worksheet holds 1,048,576 rows by 16,384 columns, and a single cell holds 32,767 characters. Those numbers belong to the grid. If you are near them, the problem is size rather than speed, and the row limit explainer covers what happens there.

The five-minute diagnostic

Do these in order. Stop when the numbers look wrong.

1. Press Ctrl and End. If it takes you to a row far below your last record, the sheet has formatting or stray cells down there, and Excel is carrying them around. Go to the row after your data, select to the bottom of the grid with Ctrl, Shift and Down, then delete the rows and clear their formatting.
2. Count the formula cells. Ctrl and End gives you the corner of the used range. Multiply by your formula columns. If the answer is in the millions, that is your bottleneck, and no setting will hide it.
3. Look for volatile functions. Search for OFFSET, INDIRECT, NOW, TODAY, RAND, RANDBETWEEN and CELL. Those are recalculated every time anything recalculates, whether or not their inputs changed, and a single one feeding a large range is enough to make a workbook feel broken.
4. Read your conditional formatting ranges. A rule applied to A:A is evaluated against more than a million rows. Fifty rules like that is a million evaluations per refresh, before any formula runs.
5. Check Save As for both .xlsx and .csv. If the .xlsx is far larger than the CSV of the same data, the workbook is carrying formatting, styles or objects that the data does not need.

Fixes ranked by effort

Manual calculation, and when to turn it back on

File → Options → Formulas → Manual stops the recalculation after each edit. Press F9 to recalculate, or Shift and F9 for just the open sheet. This is the single fastest way to make a heavy file usable while you tidy it up.

Leave it on by accident and you have a worse problem than slowness. Every number on screen is from the last calculation, and a saved file keeps those values. If you are going to work in manual mode, put the setting back before you save, or check that the word Calculate has appeared in the status bar, which is Excel telling you the sheet is out of date.

Cut the formula surface

The changes that stick are the ones that reduce the number of cells Excel has to visit. Bounded ranges instead of whole columns. A helper column computed once instead of the same lookup written three times in three formulas. Static values where a formula is no longer needed, pasted with Paste Special, Values. Remove the volatile functions, especially INDIRECT and OFFSET, which exist for reasons that a modern version rarely needs.

If the sheet is a report rather than a working model, the Data Model is the better answer. Load the file with Power Query and choose Close & Load To → Only Create Connection, then build the pivot against the model instead of the grid. The rows are held in a compressed in-memory engine rather than in cells, no formulas sit on a sheet, and there is no grid limit to run into. It is also the supported way to work with a data set that exceeds the grid.

Split the file when the data itself is the weight

If the slowness is plain volume with no formula problem to fix, give each part a size it can handle. The CSV splitter reads the file in your browser and writes out parts by row count or target size, repeating the header row in each so every part opens as a normal file. Nothing is uploaded, which matters when the data is client records or payroll. The same split is the standard answer when a file will not open at all, and that case is covered separately.

For ad-hoc questions, splitting is more machinery than the question deserves. If all you need is the total, the trend or the outlier, the analyzer reads the file, answers the question and returns a chart. There is no recalculation graph involved, so the size of the file stops being a performance issue and becomes a background detail.

Habits that keep a workbook fast

Most slow workbooks are slow because of three habits, and none of them is exotic.

• Formatting the whole column instead of the data. Setting a column to currency or a date format applies it to every row to the bottom of the grid unless you select only the rows you have.
• One sheet doing two jobs. Raw data wants to be a plain block of values with no formatting. Formulas belong on a summary sheet that references bounded ranges from the data sheet.
• Re-importing instead of appending. Monthly exports stacked into one growing workbook will eventually be unworkable. Keep each export as a CSV file and query the folder, which is what Power Query is for, and it never has to hold the whole history in cells.

One small check worth adopting: after any import, confirm the row count before you start working. Counting the rows without opening the file in Excel takes seconds, and an import that quietly dropped rows is far more expensive than one that is merely slow.

Frequently asked questions

Why is Excel so slow with large data?

Because a spreadsheet holds formulas as well as data, and in automatic mode Excel recalculates after nearly every edit. The cost tracks the number of formula cells rather than the number of rows, so 200,000 rows with ten formula columns is two million calculations to redo every time you type in a cell. Formatting applied past the data and a used range that reaches thousands of empty rows multiply the same problem.

How many rows before Excel slows down?

There is no fixed number, because rows alone are cheap. A sheet of plain values with no formulas opens a million rows without trouble, while a 50,000 row sheet full of lookups, whole-column references and conditional formatting can crawl. The worksheet ceiling is 1,048,576 rows by 16,384 columns; the practical ceiling is the point where recalculation stops feeling instant, and that depends on what you put in the cells.

Which Excel functions make a workbook slow?

The volatile ones. OFFSET, INDIRECT, NOW, TODAY, RAND, RANDBETWEEN and CELL with a format argument are recalculated on every calculation, whether or not anything they depend on changed. Microsoft documents this list in its own article on volatile functions. A single volatile function feeding a few thousand cells turns each keystroke into a full pass over those cells.

Does deleting rows make an Excel file smaller?

Not much, on its own. An .xlsx file is a compressed package of XML parts, and rows you delete leave behind the used range, the leftover styles and any formatting that was applied to them. Save a copy and check the size rather than assuming. Clearing formatting across the old range, or copying just the data into a fresh workbook, is what usually drops the file size.

Should I switch Excel to manual calculation?

It is the right move while you are working on a heavy file, and the wrong move to leave on. Manual calculation, at File, Options, Formulas, stops the recalculation after each edit and lets you press F9 when you want the numbers to update. The risk is obvious: if you save or export without pressing F9, the file keeps stale values, and nothing on screen tells you so.

Does Excel use all my CPU cores?

For calculation, it can. Multi-threaded calculation is on by default and the thread count lives at File, Options, Advanced, Formulas, so independent formulas genuinely run in parallel. What it cannot split is a dependency chain: if each formula reads the one above it, the work is serial no matter how many cores are free, which is why a large block of lookup formulas does not speed up with a better processor.

Why is Excel only slow when the file is on OneDrive or SharePoint?

AutoSave. With the file synced, every change is uploaded and versioned, so the cost of each edit is now network plus disk instead of just disk. Turning AutoSave off while you work on a heavy sheet, and saving deliberately, is usually enough to tell whether sync is the culprit.

Is CSV faster than Excel for large files?

For reading, usually yes, because a CSV carries no formulas, no conditional formatting and no calculation to trigger. It is also plain text, so getting it into a grid is a single parse. The trade-off is that CSV stores no formulas, no charts and no multiple sheets, so it suits data that is meant to be read rather than worked on. An .xlsx is a compressed ZIP of XML parts, so it can be smaller on disk than the CSV holding the same values.