Why would anyone want to reduce Excel file size or to compress such files?
Main Reasons To Reduce Excel File Size
There are multiple reasons for wanting to reduce Excel file size outcomes. Here are a few of them. I’m sure you can find others, if you think harder.
- Smaller files are easier to share via email and cloud storage
- Take up less disk space and memory when open
- Large Excel files slow down performance and increase risk of MS Excel crashes
What Causes Large Excel Files?
To see how to compress excel files to smaller size, it is important to understand what causes some of these files to be that large.
Here are some of the main culprits for large Excel file sizes:
- Lots of data, rows, columns: Files with hundreds of thousands of rows become bloated. Worksheets with hundreds of thousands of rows and columns are far larger than those with only essential data. Every row and cell adds to file size even if blank.
- Complex pivot tables and large pivot caches: Each pivot cache stores a copy of the source data
- High resolution images and pictures: Image rich workbooks take up more space. High quality pictures and photos add significant bulk. Image file size grows exponentially with pixel dimensions.
- Too much formatting (borders, colors, fonts etc.): Formatting data is stored even if invisible. Formatting like colors, borders and styles is saved even if not visible. Too much makes files larger.
- Old edits and revisions still stored: Track Changes and version history build up with ongoing edits. Old drafts lingering in the file inflate size.
Furthermore, if your Excel workbook contains macros, you should strive to prune unnecessary code, as that may also cause bloating and large Excel file size.
Tips to Reduce Excel File Size
- Remove unwanted data, blank rows/columns: Delete hidden sheets with unused data
- Streamline formatting to only essentials: Limit colors, borders etc. to key elements
- Compress images before inserting: Resize to appropriate dimensions, use JPEG compression
- Delete pivot table cache: Refresh cache only when pivot tables need to change
- Clear old revisions: Manage Versions > Purge > Delete All But Current
- Save file as binary .xlsb format: Smaller than .xlsx format due to file compression
Optimize Pivot Tables
- Only keep essential pivot fields: Remove unused fields from Rows/Columns/Filters
- Limit duplicate pivot table instances: Delete excess copies pulling from same data
- Limit pivot table row and column length: Reduce to display only required data
Use XLSB Format
The binary .xlsb format compresses files better than .xlsx format.
Click Save As and choose Excel Binary Workbook.
Limit Use of Images
- Use images only when absolutely necessary: Link to external files rather than embed when possible
- Compress images before inserting: JPEG compression works well for photo resolution
- Set images to low resolution (96 dpi): Higher resolution not needed for on-screen viewing
Only use images sparingly and remember to compress them before you insert them into your Excel worksheet.
Delete Unnecessary Revisions
- Manage Versions > Purge > Delete All But Current: Removes stored changes and edits
- Save and re-open file to completely clear tracked changes: Forced “reset” to eliminate tracking bloat
This will remove all prior edits and revisions, reducing the Excel file size. Maintaining lean clean files ensures the best Excel performance, lowers the risk of crashes and speeds up your work.
Before you go
Regularly optimizing your Excel files by removing unnecessary data, formatting, images and edits will keep file size from ballooning out of control.
This is how to reduce Excel file size without taking a toll on your dashboard or on your pivot tables.
