How to Reduce the Size of an Excel Spreadsheet
Excel spreadsheets perform a lot of data manipulation and calculations, and they can progress to fairly large file sizes, often for reasons that seem inexplicable. Excel file sizes go up with the total number of cells in use, so most techniques for file size reduction are geared toward eliminating unused cells.
Reducing file sizes can dramatically improve performance for large spreadsheets, and can make them easier to send as attachments.
Make a backup copy of the file you want to reduce.
Open the Excel document and hit "Ctrl" + " End" in each tab of the spreadsheet. You're looking for the last cell with data in it, in effect the lowest, right-most cell in the worksheet. For many sheets, this cell will be well outside of what you might have thought the spreadsheet contained.
- Excel spreadsheets perform a lot of data manipulation and calculations, and they can progress to fairly large file sizes, often for reasons that seem inexplicable.
Select unused columns by clicking on a column header, then holding down the "Ctrl" + "Shift" + "Right Arrow" keys. This will select all columns to the right of the one you selected, including the one you selected. Press the "Delete" key.
Select unused rows by clicking on a row header, then holding down the "Ctrl" + "Shift" + "Down Arrow" keys. This will select all rows beneath of the one you selected, including the one you selected. Press the "Delete" key.
References
Tips
- Wherever possible use INDEX(), MATCH(), FIND(), VLOOKUP() and HLOOKUP() to refer to data tables rather than calculating values on the fly or putting duplicate copies of data on multiple pages. In addition to reducing your spreadsheet's size, it will also make it easier to maintain.
- Do not apply formatting to entire rows or columns; this will automatically create unused cells with that formatting in them to the maximum range allowed in Excel.
- Excel 2007 automatically zips the file as part of the file format. Earlier versions of Excel can have their file sizes reduced by 70% or more by being zipped before e-mailing.
Writer Bio
Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.