Why Your Excel File Crashes (and 12 Ways to Make It 10x Lighter)
A 100 MB workbook that takes 60 seconds to open, freezes when you scroll, and crashes on save is a workbook that got away from you. The good news: every bloated Excel file has the same six underlying causes, and the fixes are quick. This guide covers all 12 real-world techniques that drop file size from 100 MB to 10 MB — usually in under an hour.
What's in this guide
- The 6 real causes of bloat
- Diagnose first
- 1. Shrink the used range
- 2. Save as .xlsb
- 3. Compress images
- 4. Kill whole-column formatting
- 5. Remove volatile formulas
- 6. Clean conditional formatting
- 7. Delete hidden sheets
- 8. Clear data from pivot caches
- 9. Break external links
- 10. Move to Power Query / Power Pivot
- 11. Convert formulas to values
- 12. Nuclear option
- How to prevent future bloat
The 6 real causes of Excel bloat
Every 100 MB workbook has at least one of these:
- Massive used range — Excel thinks your sheet extends to row 1,000,000 because you once typed something way down there
- Whole-column formatting — you applied a format to column A entirely (all 1M cells) instead of just the data rows
- Unoptimized images — screenshots pasted at full resolution
- Volatile formulas — NOW, TODAY, INDIRECT, OFFSET recalculating constantly across thousands of cells
- Multiple pivot caches — each pivot table stores its own copy of the source data
- Broken external links — formulas pointing to files that no longer exist, each one slowing load dramatically
Fix these six and your file will usually drop 70-90% in size. The remaining 10-30% is legitimate data.
Diagnose before you fix
Check per-sheet size
The quickest way to find which sheet is the culprit: save a copy of the file, delete sheets one at a time, save after each delete, note the file size drop. The sheet whose deletion drops size most is the bloated one.
Press Ctrl+End
Go to each sheet, press Ctrl+End. This jumps to the "last used cell". If it lands far beyond your actual data (say, column ZZ row 60000 when your data ends at column F row 500), you've found a used-range problem.
Check for corrupted XML
For files over 50MB: rename the file to .zip and open it. Browse to the xl/worksheets folder. If any sheetN.xml is above 10MB, that sheet has a serious problem.
1. Shrink the used range
Most common fix. Most dramatic impact.
- Go to the sheet. Press Ctrl+End. Note where it lands.
- If it's beyond your data: select the first row below your data (click row header)
- Press Ctrl+Shift+End (selects everything from here to the phantom used range)
- Right-click → Delete → select Entire Row → OK
- Do the same for columns past your data
- Save the file
- Press Ctrl+End again — should now land at your real last cell
Typical savings: 30-50 MB on affected files. Takes under 2 minutes per sheet.
Excel stores formatting, cell styles, and empty-cell metadata for every cell inside the used range. When the used range is 100× bigger than your real data, you're storing 100× the overhead.
2. Save as .xlsb (binary format)
File → Save As → change format to Excel Binary Workbook (.xlsb).
XLSB stores data in binary instead of XML. Benefits:
- 30-50% smaller files
- Faster open, save, and recalc
- All features work identically (formulas, pivot tables, VBA macros)
Downsides:
- Some corporate security tools block binary formats
- Third-party integrations may expect XLSX
- Can't be edited by Google Sheets natively (download-upload round trip only)
For internal workbooks, XLSB is usually the better default. One-click 40% size reduction.
3. Compress embedded images
- Click any image in the workbook
- Picture Format tab → Compress Pictures
- Uncheck "Apply only to this picture" (so it affects all images)
- Choose "Email (96 ppi)" for maximum compression
- OK → save
A workbook with 20 screenshots at full resolution easily holds 50 MB of image data. Compressing to 96 ppi drops this to 2-3 MB with no visible quality loss at screen size.
4. Kill whole-column formatting
This is the sneakiest cause of bloat. Someone selected column A and applied a fill color or number format. Now Excel stores that formatting for all 1,048,576 cells in column A.
Fix:
- Select the actual data range (not the whole column)
- Copy its formatting with Format Painter
- Select the whole column with format applied
- Press Delete to clear everything
- Reapply formatting to just the data range
Or use a nuclear approach:
- Select all empty rows below your data (Ctrl+Shift+End from one row below data)
- Home → Clear → Clear All
- Same for columns to the right
- Save
5. Remove volatile formulas
Volatile formulas recalculate every time anything changes anywhere in the workbook. The usual suspects:
- NOW() and TODAY() — recalc on every change
- INDIRECT() — recalc on every change
- OFFSET() — recalc on every change
- RAND() and RANDBETWEEN() — recalc on every change
- INFO() and CELL() in some forms — recalc on every change
A workbook with 10,000 cells containing INDIRECT will recalculate all 10,000 every time you type anything. This isn't a file-size issue — it's a performance killer that eventually causes crashes.
Audit your volatile formulas
Press Ctrl+F → search for "INDIRECT" → Options → Within: Workbook → Find All. Count how many cells contain it. If more than a few hundred, consider rewriting with INDEX (non-volatile).
6. Clean conditional formatting
Conditional formatting rules applied to entire columns (A:A) instead of actual data ranges (A2:A1000) are a huge source of bloat and slowdown. Each rule evaluates against every cell in its range.
Fix:
- Home → Conditional Formatting → Manage Rules
- Change "Show formatting rules for" to "This Worksheet"
- For every rule, check its "Applies to" range
- If it says $A:$A, change to $A$2:$A$1000 (your actual data range)
- Delete duplicate rules (common — they accumulate over edits)
- Apply
For aggressively broken workbooks: select the whole sheet, Home → Conditional Formatting → Clear Rules → From Entire Sheet. Then reapply only the rules you actually need.
7. Delete hidden sheets
Workbooks often accumulate hidden sheets — prior-version backups, test data, scratch calculations.
- Right-click any sheet tab → Unhide
- Review every hidden sheet
- Delete any you don't need
- Check for very hidden sheets too (VBA → Project Explorer → Sheet properties → Visible)
Very hidden sheets can't be unhidden through the regular menu — only VBA. They're a common place for bloat to hide, literally.
8. Clear data from pivot caches
Every pivot table stores a cached copy of its source data. If you have 10 pivot tables from the same source, you may have 10 copies of that data embedded in the file.
Share one cache across pivots
When creating pivot tables from the same source, use "Insert Pivot Table from existing connection" instead of rebuilding from scratch. This shares one cache.
Refresh behavior
Right-click pivot → PivotTable Options → Data tab → uncheck "Save source data with file" → Refresh data when opening the file. This replaces the cache with a live refresh, dropping the cache's weight from the file.
9. Break broken external links
Formulas like =[OldFile.xlsx]Sheet1!A1 point to another workbook. If that workbook no longer exists or isn't found, Excel still tries to resolve the link on every open, slowing load dramatically.
- Data → Edit Links (grey if no external links exist)
- For each broken link: click Change Source (point to the new file) or Break Link (replaces formulas with their last cached values)
- Save
Break Link is irreversible — the formulas become static values. For broken links to files you don't need, this is the right choice.
10. Move large data to Power Query / Power Pivot
If your workbook has 500,000+ rows of raw data that drives pivot tables or lookups, storing that data in worksheet cells is the wrong approach.
Better architecture:
- Data → Get Data → connect to your source (CSV, database, another file)
- In Power Query, filter/transform as needed
- Load to the Data Model (not to a worksheet)
- Build pivot tables from the Data Model
Data Model storage uses columnar compression — 10 million rows can live in a 20 MB file. Same data in worksheet cells would be hundreds of MB and unusable.
11. Convert finished formulas to values
For cells whose formulas you'll never need to recalculate (historical data, finalized reports):
- Select the range
- Ctrl+C to copy
- Right-click → Paste Special → Values
- Save
Static values are smaller and much faster than live formulas. Best for archival worksheets.
12. Nuclear option: rebuild the workbook
When nothing else works, the file may be corrupted or structurally damaged. The rebuild:
- Create a new blank workbook
- Copy data (not formulas, not formatting — just values) from the old workbook, sheet by sheet
- Rebuild pivot tables and formulas from scratch
- Save as .xlsb
Time-intensive but sometimes the only cure for mysterious corruption. The resulting file is often 95% smaller than the original.
How to prevent future bloat
- Never apply formatting to entire columns — only to data ranges
- Audit conditional formatting monthly — delete duplicates and consolidate rules
- Use .xlsb by default for any non-public workbook
- Compress images on insert — set default compression quality in File → Options → Advanced → Image Size and Quality
- Avoid INDIRECT when possible — INDEX is almost always a non-volatile replacement
- Use Tables (Ctrl+T) — auto-expanding references prevent whole-column formulas
- Move large data to Power Query from day one if you expect 100K+ rows
- Review Edit Links quarterly — break any you don't need
- Delete scratch sheets as soon as you're done with them
- Close workbooks you're not actively using — reduces memory pressure that triggers crashes
Several of these fixes (especially #11 and #12) are destructive. Save a copy of your workbook before starting. If something goes wrong, you have the original.
Typical real-world results
| Starting | Fix applied | Result |
|---|---|---|
| 100 MB finance model | Shrink used range + .xlsb + compress images | 8 MB |
| 60 MB sales dashboard | Clean conditional formatting + share pivot cache | 12 MB |
| 45 MB inventory tracker | Fix whole-column formatting + .xlsb | 4 MB |
| 80 MB report with images | Compress images + delete hidden sheets | 15 MB |
| 200 MB data warehouse | Move to Power Query Data Model | 25 MB |
The 10x reduction headline isn't marketing. Most bloated workbooks hide 90% of their size in fixable waste.
One-click workbook analysis and cleanup
Finding every used-range problem, broken link, and whole-column formatting rule by hand is tedious. Excel Wizard scans your workbook, surfaces every bloat issue by size impact, and applies the fixes you approve — in seconds.
Install Excel Wizard →Frequently asked questions
Why does my Excel file keep crashing?
Six usual causes: volatile formulas, massive used ranges, whole-column conditional formatting, uncompressed images, redundant pivot caches, broken external links. Fixing one typically drops size by 50%+.
How do I reduce Excel file size?
Save as .xlsb, compress images, delete unused sheets, clear whole-column formatting, convert volatile formulas to values. These five steps cut most bloated files by 70-90%.
What is the Excel used range and why does it matter?
The rectangular area Excel considers "in use". If you ever typed in AZ5000 and deleted it, Excel still extends the sheet there. Ctrl+End reveals where Excel thinks the sheet ends — if far beyond your data, you have a used-range problem.
Should I use .xlsb instead of .xlsx?
For files over 10MB or with heavy calculations, yes. XLSB is 30-50% smaller and faster. Downsides: less compatible with third-party tools, blocked by some corporate security policies.
How many rows can Excel handle before crashing?
Technical limit is 1,048,576 rows. Practical limit depends on formula complexity — performance degrades around 100K rows, stability degrades past 500K. For bigger data, use Power Query + Power Pivot Data Model.