A Guide to Excel Files That Are Too Big or Too Slow

An Excel file can be too big in several unrelated ways. It can be too big on disk: 80 MB and taking a minute to save. It can have more rows than a sheet can hold, so that it silently truncated. It can calculate slowly, so every edit freezes the screen. Or it can be too big for the program that reads it, so that Python runs out of memory at the first line. People use one word, "big", for all four, and apply the fix for the wrong one. A file that is large because of unused styles does not need a database, and a file with three million rows does not need a styles clean-up.

This guide separates the four, shows how to tell which you have, and gives the sequence of fixes in the order of cost, from cleaning the workbook to leaving Excel for the data. The spokes linked throughout go deeper on each.

Four kinds of big

They overlap. A workbook with a million rows of formulas is big in three ways at once. Start by naming the ones that apply, because the order of treatment depends on them. Cheap fixes come first: clean the container, then the formulas, then change the tool.

Where the bytes are

An xlsx is a zip of XML parts, and the zip lists what each part weighs. This is the single most useful look at a large file:

cp report.xlsx /tmp/r.zip
unzip -lv /tmp/r.zip | sort -k1 -n -r | head -15

The usual findings are an oversized sheet part (a used range that stretches far past the data), a large styles.xml (thousands of unused cell styles), media files (pictures pasted at full size), pivot cache parts (a copy of each pivot's source data) and external link parts. Each has a direct fix, described in reducing workbook size by removing unused styles and data: delete the empty rows and columns that Ctrl+End reveals, remove unused styles, compress pictures, untick "Save source data with file" for pivots, break dead links. A bloated workbook often shrinks by a large factor with none of the data touched.

Switching to the .xlsb binary format reduces size and speeds opening for workbooks that only Excel will read. It is a poor choice for files that other tools consume.

If the compressed sizes of the sheet parts are large because the sheets really contain millions of cells of data, the bytes are real. That moves you to the row-count problem.

More rows than a sheet

A worksheet holds 1,048,576 rows and 16,384 columns. A larger CSV opened by double-click is cut to the limit, with no lasting warning, and every total built on it is short. Check by looking at the last row, and by counting the source with wc -l. A sheet ending at exactly 1,048,576 has lost data (Excel row and column limits).

The remedies keep the data out of the grid:

• The Data Model. Load with Power Query to the model, not to a sheet, and build pivots on it. It stores columns compressed in memory and holds more rows than a sheet. It needs 64-bit Excel and enough RAM, and it does not suit sharing (using the Excel Data Model for larger datasets in practice).

• Aggregate first. Most reports need monthly totals, not every line. DuckDB or a database summarizes the file, and Excel receives a few thousand rows (making a pivot table from data larger than Excel allows).

• Query the file. DuckDB runs SQL directly on CSV and Parquet, with no load step (analyzing more than a million rows without Excel limits).

• A database. If the data is queried often, joined to other tables or shared, load it into SQLite or PostgreSQL and connect Excel to the summary (connecting Excel directly to a database).

The principle is to reduce the data where it lives and move the answer to Excel.

Slow calculation

Excel recalculates what depends on a change. It slows when too much is marked dirty or each formula is expensive. Measure before changing anything: set calculation to manual, press F9 and read the time. Divide the workbook by sheets to see which part costs the most.

The recurring causes, covered in why Excel is slow with formulas over many rows:

• Volatile functions (OFFSET, INDIRECT, TODAY, NOW, RAND) recalculate on every change and drag their dependents with them.

• Scanning lookups. Thousands of exact-match VLOOKUP calls over a large range multiply into billions of comparisons.

• Whole-column references in array formulas, SUMPRODUCT and conditional formats.

• Array formulas per row that repeat work.

• Wide dependencies, an input feeding everything.

The fixes, in order: calculate manually while editing, replace volatile functions (INDEX for OFFSET), look up once in a helper column, bound the ranges with Tables, paste final results as values, and move heavy set-based work to a query or a database. A calculation that takes a minute in a workbook often takes a second in SQL, because the database works on sets and not on a chain of cells (why SQL beats formulas once the data gets large and translating Excel formulas into SQL).

If the calculation is quick and the workbook is still sluggish, look at conditional formatting rules, drawing objects, add-ins and file location.

Too big for the program that reads it

When code reads the workbook, memory is the limit. An xlsx expands when parsed: a 500 MB file holds multiple gigabytes of XML, and a normal load turns every cell into an object. pandas.read_excel has no chunksize, so it loads the lot (how many rows can pandas read from Excel before it struggles).

The approaches:

1. Estimate the DataFrame size from a sample (memory_usage(deep=True)) and the row count, and remember that the peak during the read is higher than the final size.

2. Reduce the load with usecols, dtype, category for repeated text, a lighter engine such as calamine, and one sheet at a time.

3. Stream. openpyxl in read-only mode yields rows with flat memory, so you process batches and write them out (reading a 500 MB Excel file without running out of memory).

4. Convert once. Turn the workbook into Parquet or CSV with a streaming job, then read the new format as often as you like, with read_csv(chunksize=...) or column-selective Parquet reads (converting a huge Excel file to CSV or Parquet safely).

If you choose between readers, measure rather than trust a number from the internet. A fair benchmark uses files shaped like yours, fresh processes, several repetitions, peak memory from the operating system and a check that every reader returned the same data (benchmarking Excel readers in Python with a fair test).

The order to work in

Do not start with the most dramatic fix. A sensible sequence for a workbook that is "too big":

1. Name the kind. Disk size, rows, calculation, memory in code, or several.

2. Inspect the zip and fix the bloat. It is cheap and often decisive.

3. Measure calculation and remove the worst formulas.

4. Check the used range and the row limit. Make sure nothing is truncated.

5. Move large data out of the grid: Data Model, DuckDB or a database.

6. Convert for code: stream once to Parquet or CSV.

7. Change the process so the workbook is no longer the store.

At each step, record the file size, the calculation time and the memory use before and after, with the machine described. A change that produced no measurable gain should be reverted, since every change carries risk.

Measure properly

Three habits make the measurements useful.

Use the same conditions. The same machine, the same Excel version, the same file, nothing else running. Note whether the file was freshly opened or had been used.

Separate the phases. Open time, calculation time, save time and refresh time are different things with different causes. A workbook that opens slowly and calculates quickly has a size problem; the reverse has a formula problem.

Record peak memory for code. Use the operating system's resident-set figure, not Python's own tracking. Run each approach in a fresh process and repeat.

A short log kept beside the workbook, with dates and numbers, turns folklore ("it's the VLOOKUPs") into evidence.

Prevention

Most big-workbook problems are slow accumulation. Habits that prevent them:

• Keep raw data out of the working file. Store it in CSV, Parquet or a database, and let the workbook read and summarize it.

• Start from a clean template rather than copying an old workbook with its styles and names.

• Use Tables and the Data Model for data that grows, not ranges and whole-column formulas.

• Avoid volatile functions unless they are needed.

• Paste values for finished results and keep the logic documented.

• Compress pictures and avoid pasting screenshots of data.

• Archive old periods instead of letting a file hold ten years.

• For generated files, reuse styles and write only the rows needed.

• Set a size alarm. A scheduled job that lists workbooks over a threshold and their largest parts finds the next problem early.

Changing the container

At some point the right fix is to stop using a workbook as the store. Signs: the data has millions of rows, several people refresh or edit it, the same large file is emailed around, or the calculation is really a query. Move the data to a database or to Parquet files, and let Excel connect to a summary. This does not take Excel away. It takes away the job it was never good at.

The cost is real, and it is mostly in the cleaning and the change of habit (when a spreadsheet has become a database covers the decision and the move). The benefit is that the file stops growing and the questions stop depending on one person's laptop.

A short example

An operations analyst has a 140 MB workbook of order data and cost models. It takes ninety seconds to open and every edit freezes the screen. Three problems overlap.

The zip listing shows a worksheet part with an uncompressed size far beyond the data, a styles.xml of several megabytes and two pivot caches as large as the order data. Ctrl+End jumps to row 600,000 where the data ends at 40,000. The analyst deletes the empty rows and columns, removes about three thousand unused styles and unticks "Save source data with file" for both pivots. The file shrinks to a fraction of its size and opens in a few seconds.

The calculation time, measured with manual calculation and F9, is forty seconds. A search finds OFFSET in twenty thousand cells and a column of exact-match lookups into a 40,000-row table. The analyst replaces OFFSET with INDEX, adds a MATCH helper column and converts three finished columns to values. Calculation drops to a couple of seconds.

A month later the order data grows and the analyst needs three years of history. They load the CSV to the Data Model, relate it to a customer table and rebuild the pivots there. The sheet no longer holds the orders at all. The workbook has become a front end to the model.

The order of the fixes mattered. Starting with the Data Model would have preserved the bloat and the slow formulas. Cleaning first made the later changes small.

How big files get made

Big workbooks are rarely planned. They accumulate, and knowing the usual routes helps you spot the next one early.

The first route is the working copy that never gets retired. Someone starts a workbook for one quarter, and it becomes the template for the next, which becomes the template for the one after. Each generation brings styles, names, hidden sheets and links from the one before. After five years the file carries the history of every analyst who touched it. The cure is a clean template, rebuilt from scratch, that people copy and fill, and a rule that old files are archived and not reused.

The second route is the export that outgrew its sheet. A report that used to have ten thousand rows now has two million, and nobody changed the process. The first symptom is a total that is quietly short. The cure is to move the export out of the grid before anyone builds on top of it.

The third route is formulas that were fine at small scale. A lookup per row, a volatile function in a helper column, an array formula copied down: harmless at a few thousand rows, painful at a few hundred thousand. Nothing breaks. The workbook just gets slower each month until someone complains. The cure is to review formulas when the row count crosses an order of magnitude.

The fourth route is the shared file. Several people edit one workbook, each pasting from their own sources. Styles multiply, links spread, and the file doubles. The cure is a controlled place for the data and a read-only workbook that connects to it.

What it costs to leave it alone

A slow workbook is a tax on everyone who opens it. If ten people open a file twice a day and it takes ninety seconds, that is fifteen hours a week of waiting, before counting the interrupted thinking. A file that crashes loses work. A file that silently truncates at the row limit produces wrong figures that people act on. A file that cannot be read by code blocks every automation that depends on it.

Set against that, the cost of fixing is usually a few hours for the container clean-up and a few days for a move to a database or a Data Model. The clean-up has the best return: it is cheap, it keeps the workbook as it is and it often removes most of the size. Do it first and measure what is left.

The hidden cost is risk. A workbook of this kind often has one person who understands it. If the file will not open on someone else's machine, or only with a special setting, it is a single point of failure. Part of the fix is to write down how the file is built and how to rebuild it.

A symptom table

When someone describes a problem, the first sentence usually points at the cause.

Use the table to choose the first measurement. It is quicker to look in the right place than to try every remedy.

Team practices that keep files healthy

A few agreements cost little and prevent most of the trouble.

Keep raw data apart from the workbook that summarizes it. The raw data lives in a database, a Parquet file or a CSV in a known folder; the workbook reads it. When the data is replaced, the workbook is untouched.

Write down the layout of anything that code reads: sheet names, header row, column names and types. A short note beside the workbook is enough, and it is what a person needs when the file fails to load.

Review any workbook that grows past a size you agree, say 25 MB, or whose open time passes ten seconds. The review takes an hour and usually removes the cause.

Use Tables for anything that grows, so formulas and pivots follow the data without hard-coded ranges.

Name an owner for each important workbook, with a deputy. Unowned workbooks decay.

Archive on a schedule. A workbook that holds ten years of history for a report that needs two is carrying eight years of cost.

Before you share a large workbook

Run through these questions before a big file leaves your machine. Does it open in a reasonable time on a typical laptop? Is calculation set to automatic, and does it finish in seconds? Is the file free of unused styles, empty formatted rows and dead links? Have you removed pivot source data that the recipient does not need? Are pictures compressed? Does the recipient need the formulas, or would a values-only copy do? Is there data in it that the recipient should not see, such as cached rows from a database? If the file is large because of real data, would a link to a shared dataset serve better than an attachment?

Each answer that is no is a small improvement waiting to be made, and the recipient will notice the difference.

Reading someone else's big workbook

Often the large file is not yours. It arrives from a vendor, a branch office or a system you do not control, and you have to make sense of it quickly. A short routine saves time.

First, do not open it in Excel on your main machine if it is very large or from an unknown source. Look at it from the outside: the file size, the zip listing, the names of the sheets. The listing shows how many worksheet parts there are and how large each one is, so you know before opening whether you face one huge sheet or twelve moderate ones.

Second, choose the reader to match the size. For a moderate file, pandas with usecols and a sample read of the first fifty thousand rows tells you the structure. For a large one, stream the first few thousand rows with openpyxl in read-only mode, print them and look.

Third, find out what the data is before you convert it. Which sheet holds the table, where the header is, which columns are identifiers, whether there are merged cells or subtotal rows. Make the notes into a small spec, and build the conversion from the spec.

Fourth, convert once and keep the original. Write Parquet or CSV with a streaming job, record the row counts and a control total, and archive the workbook next to the result. From then on, work from the converted copy.

Finally, tell the sender what you learned. If the vendor can send CSV, or a smaller extract, or the same workbook without the pivot caches, ask. A single request often removes the problem for good, and the people who produce the file are usually glad to know that it causes trouble.

Cloud storage and sync clients

Where the file lives changes how big it feels. A workbook on a network drive is read and written over the network, so a 60 MB file takes far longer to open than the same file on a local disk. A workbook in a synced folder (OneDrive, SharePoint, Dropbox) is first written locally and then uploaded, and a large file keeps the sync client busy, can produce conflict copies and, if edited by two people, can end up as two diverging versions. If a file is slow to open only from a particular place, copy it to a local disk and time it there before blaming the workbook.

Co-authoring in the cloud also has size limits and works best with files that are moderate in size and free of features that disable it. A very large workbook may be forced into a read-only or desktop-only mode. When a team relies on a shared file, keeping it small is not a nicety but a condition for the collaboration features to work at all.

Backups matter too. A large workbook that changes daily creates a large daily backup. Version history in a cloud library keeps older copies and counts against storage. Trimming bloat repays itself in storage and in the time it takes to restore a version when something goes wrong.

When the problem is not the size of the file

It is corruption. A file that will not open or opens with a repair prompt has a different problem (Excel cannot open the file because the file is corrupt).

It is the machine. Low memory, 32-bit Excel, antivirus scanning, a slow network drive or a synced folder can make a modest workbook feel huge. Test on another machine.

It is an add-in. A add-in that hooks every calculation or save can dominate. Start Excel in safe mode (excel /safe) and compare.

It is the process. If the workbook is slow because six people use it as a database, the answer is not a smaller file.

The data is large. A few hundred megabytes of real content is not bloat. Choose a container that suits it.

Where to go next

For a heavy file on disk, start with reducing workbook size by removing unused styles and data. For sluggish editing, why Excel is slow with formulas over many rows. For code that runs out of memory, reading a 500 MB Excel file without running out of memory. For analysis beyond a sheet, analyzing more than a million rows without Excel limits. To understand the format that causes some of this, how an xlsx file is a zip of XML is the background.

The references are Microsoft's pages on Excel specifications and limits and Excel performance: improving calculation performance, openpyxl's guide to optimized modes and DuckDB's documentation. To check a small extract as JSON, use the CSV to JSON converter.