How to Fix an Excel Workbook That Is Larger Than 200 MB
Question details
The user needs to understand why an Excel workbook remains over 200 MB after clearing unused cells, and how to properly reduce its file size.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Trying to reduce the file size of an excessively large Excel workbook after clearing contents did not work.
- Observed behavior
- The Excel workbook file size remains beyond 205 MB despite clearing unused cells, but significantly drops to 8 MB when saved as an XLSB file.
Before deleting rows, columns, or altering the structure of your large spreadsheet, save a backup copy of the 200 MB workbook to prevent any accidental data loss.
Delete Unused Rows and Columns to Reset the Used Range
Using 'Clear' on cells only removes the visible content. To truly reduce file size, you must delete the entire rows and columns to remove underlying formatting and reset Excel's memory of the used range.
Excel keeps track of the 'used range' of your worksheet. If a cell at the far bottom or right edge gets accidentally formatted, Excel will save millions of blank cells as part of your file, causing severe file bloat.
Click the first row number immediately below your actual data. Press 'Ctrl + Shift + Down Arrow' to select all the way to the bottom of the worksheet.
Right-click anywhere on the selected row numbers and choose 'Delete' from the context menu.
Click the first column letter immediately to the right of your data. Press 'Ctrl + Shift + Right Arrow' to select all columns to the edge of the worksheet.
Right-click on the selected column headers and choose 'Delete'.
Press 'Ctrl + S' to save the file. Excel will recalculate the used range, which should significantly drop the workbook size.
Copy Your Data to a Fresh Workbook
If deleting ranges doesn't resolve the bloat, copying only the required data to a new workbook leaves the corrupted or hidden formatting behind.
Save the File as an Excel Binary Workbook (XLSB)
Saving as an XLSB compresses the file substantially by utilizing a binary format instead of XML, drastically reducing size and improving load times.
Easily Optimize Large Spreadsheets with WPS Office
WPS Spreadsheet is engineered to handle massive datasets smoothly. It offers powerful, built-in tools to clear redundant formatting, correctly reset your used range, and compress files into lightweight formats.
- 1. Open the large file in WPS: Launch WPS Spreadsheet and open your oversized Excel workbook.
- 2. Highlight excess ranges: Select the empty rows below and columns beside your data using 'Ctrl + Shift + Arrow Keys'.
- 3. Delete and clear formatting: Right-click the headers and select 'Delete' to instantly wipe out hidden formatting inflating the file.
- 4. Save and compress: Click 'Menu' > 'Save As' to easily overwrite the file or save it as an .xlsb for maximum compression.
Frequently Asked Questions
Why didn't clearing cells reduce my workbook's file size?
Using the 'Clear' or 'Delete' key on your keyboard only removes the text or values inside the cells. It does not erase background colors, borders, conditional formatting, or hidden formulas. Excel continues to treat those cells as 'in use,' which keeps the file size massively bloated.
What is an inflated used range in a spreadsheet?
Excel memorizes the last cell in your sheet that contains data or formatting. If you accidentally apply formatting to a cell at row 1,000,000, Excel includes all the millions of empty cells above it as part of your active 'used range'. This drastically increases the amount of background data saved in the file.
Are there downsides to saving a workbook as XLSB?
XLSB (Excel Binary Workbook) is highly efficient, saving space and loading faster. The main downside is that because it is saved in binary rather than XML, third-party applications that scrape or extract data from Excel files might not be able to read an XLSB file. However, for everyday user editing and macro support, it functions identically to XLSX.
