How to Reduce the Size of an Excel Spreadsheet

Written by ken burnside Google
  • Share
  • Tweet
  • Share
  • Email

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.

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.

Tip

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.

Tips and warnings

  • 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.
Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.