An Excel file with a few tables should not take up 30 MB. Yet it is very common to find workbooks that take forever to open, freeze the computer when saving or cannot be emailed because they exceed the attachment limit. The good news is that the size is almost always caused by elements that add nothing and can be removed. In this article you will see the most common causes and how to reduce the weight of your files.
How to find out what takes up space
Before you start deleting, identify the culprit. A simple trick for XLSX files: make a copy, change the extension to .zip and open the compressed file. You will see folders like xl/worksheets (the sheets), xl/media (images) or xl/pivotCache (PivotTable caches). The size of each item tells you where the problem is.
Cause 1: the phantom used range
Excel remembers the last cell used on each sheet. If you once formatted an entire column or pasted something in row 500,000 and then deleted it, Excel may still consider the sheet to extend that far and store information about all those cells.
How to spot it: press Ctrl+End. If the cursor jumps far beyond your data, you have a phantom range.
How to fix it:
- Select the first empty row below your data.
- Press Ctrl+Shift+Down Arrow to select to the end.
- Right-click > Delete (pressing Delete on the keyboard is not enough).
- Repeat with the columns to the right of your data.
- Save, close and reopen the file.
Cause 2: uncompressed images
A photo pasted straight from a camera or phone can be several megabytes. If the workbook has logos repeated on every sheet or screenshots, the size shoots up.
Fix: select an image, go to Picture Format > Compress Pictures, untick “Apply only to this picture” and choose a resolution of 150 ppi or less. Delete images that are not needed as well.
Cause 3: excessive formatting
Formatting entire rows or columns, using lots of different styles or having hundreds of conditional formatting rules increases size and slows the file down.
Fix:
- Apply formatting only to the range with data, not whole columns.
- Check Home > Conditional Formatting > Manage Rules and delete duplicated or unused rules.
- Remove custom styles you do not use.
Cause 4: PivotTable caches
Every PivotTable stores a copy of its source data. If you have several PivotTables based on different data, or if the cache holds old data, the file grows.
Fix: in the PivotTable options, Data tab, untick “Save source data with file” (it is recalculated on refresh) and set “Number of items to retain per field” to None.
Cause 5: unnecessary formulas
Thousands of formulas referring to entire columns, or calculations that no longer change, take up space and calculation time.
Fix: if a result is final, copy the range and paste it as values. Limit references to the real range (A2:A5000 instead of A:A).
Cause 6: the XLS format
XLS files are not compressed. The same workbook saved as XLSX is usually much smaller. If you still work in XLS, convert to XLSX.
Cause 7: hidden sheets and names
Forgotten helper sheets, defined names pointing to ranges that no longer exist or old connection data also add up. Check Formulas > Name Manager and delete names showing #REF! errors.
The quick fix: an online compressor
If you do not want to go through everything by hand, an Excel compressor applies many of these optimizations automatically. With the Excel size reducer on our Excel tools page:
- Upload the heavy file.
- The tool optimizes images, removes unnecessary data and recompresses the workbook.
- Download the lighter version.
Afterwards, open it and check everything is in order. If the data is important, compare the original with the compressed version using our Excel comparison tool.
Other alternatives
- Convert to PDF if the recipient only needs to see the data.
- Export to CSV if you only need the data from one table.
- Save as XLSB if the file is huge and will only be used in Excel.
- Split the workbook into several files by year, region or department.
Frequently asked questions
Does compressing an Excel file lose data?
A good compression only removes redundant information, such as empty ranges or images with more resolution than needed. Cell values do not change. Still, always keep the original.
Why is the file still large after deleting data?
Because the used range is not updated until you save, close and reopen the file, and because clearing content with the Delete key does not remove cell formatting.
Does saving as XLSB make a big difference?
For very large workbooks, yes: XLSB files often open and save faster and can be noticeably smaller. The trade-off is compatibility, as some programs and online tools do not read XLSB.
Conclusion
A heavy Excel file can almost always be fixed. Remove phantom ranges, compress images, clean up formatting and caches, convert to XLSX and replace formulas with values where possible. If you would rather do it in one click, use the compressor on our Excel tools page and get your workbooks moving quickly again.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
