Why Is My Excel File So Large?
Short answer
Press Ctrl+End on each sheet. If the cursor jumps thousands of rows past your data, Excel is storing a huge block of empty-but-formatted cells, which is the most common cause of a bloated workbook. Delete those rows and columns entirely, save, and the file frequently drops by 80% or more. Images and pivot caches account for most of the rest.
On this page
A workbook with 3,000 rows of numbers has no business being 40MB. When it is, something is in there that you did not knowingly put there, and it is almost never the data.
Excel stores more than the values you see. It stores formatting for every cell it believes is in use, a cached copy of the source data behind every pivot table, every image at full resolution regardless of how small you dragged it, and in some cases a record of edits nobody asked it to keep. Any one of those can dwarf the actual content.
Find out what is in there first
Before deleting anything, see where the weight is. An .xlsx file is a ZIP
archive with a different extension, and you can look inside it.
- Copy the workbook to your desktop and work on the copy.
- Rename the copy so it ends in
.zipinstead of.xlsx. - Open it. Windows and macOS both browse ZIP files natively.
What you see tells you the cause immediately:
| Largest folder or file | What it means |
|---|---|
xl/media |
Images. Compress or remove them |
xl/worksheets/sheet1.xml very large |
Formatted empty cells, or huge used range |
xl/pivotCache |
Pivot table caches holding duplicate copies of source data |
xl/sharedStrings.xml |
Enormous number of distinct text values |
customXml or long docProps |
Residue from another system that exported it |
Rename it back to .xlsx when you are done. This takes two minutes and it tells
you which of the sections below is worth your time.
The used range — the cause in most workbooks
Open each sheet and press Ctrl+End. That takes the cursor to the bottom-right of what Excel considers the used range.
If your data ends at row 3,000 and the cursor lands at row 847,000 or column
XFD, that is your answer. Excel is storing formatting state for every one of
those cells, and empty formatted cells cost nearly as much as full ones.
This happens when someone selects an entire column and applies a fill colour, a border, or a number format. It also happens when data is pasted in from a web page, and when a file has been through a few rounds of import and export.
To fix it:
- Click the row header one below your last row of real data.
- Press Ctrl+Shift+Down to select every row to the bottom of the sheet.
- Right-click a selected row header and choose Delete. Not Clear Contents, not the Delete key on the keyboard — Delete from the right-click menu, which removes the rows themselves.
- Repeat horizontally: click the column header one right of your data, press Ctrl+Shift+Right, right-click, Delete.
- Save, close the file completely, and reopen it.
The last step matters. The used range is only recalculated when the workbook is saved and reopened, so Ctrl+End will still show the old boundary until you do. Files that drop from 35MB to under 2MB on this step alone are routine.
Images
Excel stores pasted images at their original resolution. Dragging a photo’s corner to make it small on screen changes nothing about what is stored. A dozen phone photos pasted into a sheet is 40–80MB before you have typed anything.
Use Compress Pictures from the Picture Format tab that appears when an image is selected. Untick Apply only to this picture to catch all of them, choose a sensible resolution such as Email or Web, and tick Delete cropped areas of pictures. Cropping in Office only hides the removed part; it stays in the file until this option strips it.
If the images are the point of the file rather than decoration, compress them before inserting instead. Our image compression guide covers getting the size down without visible quality loss, and the image compressor tool does it in the browser.
Pivot tables and their caches
Every pivot table keeps a cached copy of its source data inside the workbook. A pivot built from 200,000 rows carries those 200,000 rows a second time, and several pivots from the same source can each carry their own copy.
Right-click the pivot table, choose PivotTable Options, then on the Data tab untick Save source data with file and tick Refresh data when opening the file. The cache is dropped on save and rebuilt when the file opens. The trade-off is an obvious one: the pivot is empty until that refresh runs, and it only works if the source data lives in the same workbook or a reachable connection.
Conditional formatting that has bred
Go to Home → Conditional Formatting → Manage Rules, and set the dropdown to This Worksheet. If you expected five rules and the list has four hundred, you have found something.
Copying and pasting rows duplicates and fragments conditional formatting rules,
so a rule that once covered A1:A5000 becomes several hundred rules covering a
few rows each. Delete them all and reapply the handful you actually want to the
full range in one go.
Excessive conditional formatting also makes the file slow to scroll, which is often the first symptom people notice.
The smaller contributors
- Unused styles. Files that have passed through other systems accumulate thousands of named cell styles. There is no good interface for clearing them; copying your sheets into a brand-new workbook is the practical fix.
- Hidden sheets. Right-click any sheet tab and choose Unhide to see whether the workbook is carrying sheets you forgot about. Very hidden sheets set through VBA will not appear here.
- Defined names. Formulas → Name Manager sometimes holds thousands of
broken range names imported from elsewhere. Sort by the Refers To column and
delete anything showing
#REF!. - Shapes and controls. Press F5, choose Special, then Objects to select every object on the sheet at once. Pasting from web pages can leave hundreds of invisible one-pixel shapes.
Changing the format
If the file is still large after all of that, and it genuinely contains a lot of data, the format itself is worth a look.
| Format | When it is right |
|---|---|
.xlsx |
The default. Zipped XML, good compatibility |
.xlsb |
Formula-heavy workbooks. Smaller and faster, weaker compatibility |
.xls |
Never, unless something old demands it. Uncompressed and size-limited |
.csv |
Data only, one sheet, no formatting or formulas |
Converting a legacy .xls to .xlsx alone often halves the size, because the
old binary format has no compression at all.
The nuclear option, and what to expect
When nothing identifiable accounts for the size, create a new blank workbook and copy your sheets into it one at a time, saving after each. Workbook-level corruption and accumulated junk do not survive the move. Check formulas afterwards, because cross-sheet references can break in transit.
Realistically, the used-range fix resolves most cases and resolves them completely. A clean workbook of 10,000 rows with formulas and modest formatting should sit in the low single-digit megabytes. If yours does after this, there is nothing left to fix.
One honest limit: a workbook that genuinely holds half a million rows of data will be large no matter what you do, and that is Excel telling you the data belongs in a database rather than a spreadsheet. No amount of tidying changes that arithmetic.