Accelerate a Slow-Opening Massive Excel File

ACCELERATE A SLOW-OPENING MASSIVE EXCEL FILE

My work requires the use of several very large Excel files, and they take forever to open. In fact, they even take a long time to save and close. I’ve thought of breaking them up into small components, but in most cases having the data distributed over several smaller files will complicate my work. What I’d like to do is compact the files, but I can’t find that function in Excel. Can you help?

There is no compacting function in Excel 2003 or its earlier versions. But Excel 2007, with the new .xlsx file extension, uses file compression, which shrinks spreadsheet files up to 75% of their girth in the earlier .xls format. Of course, that does you no good if you’re not planning to upgrade to Vista.

However, you can buy third-party programs that help. They don’t actually compress, but they do effectively shrink Excel files by eliminating empty cells and unnecessary data. One freeware program, which is available at www.appspro.com/Utilities/Utilities.htm, is especially good at cleaning macro code, which becomes bloated by multiple edits. Several programs claim compacting abilities, and you may want to take advantage of free trial offers. You can locate them by googling Excel file compacting.

Another step you can take is to reset the file’s data range— the area that Excel interprets as containing data, which mistakenly also includes cells that have been cleared of data. To do that, open your workbook and press Ctrl+End. If you’re taken way beyond your data, that indicates Excel considers even those apparently blank cells as part of the workbook’s data range, and those apparently empty cells artificially inflate the file.

To deflate the data range to its proper size, scroll to the end of your data—the last row and column that contains data. In the example below (see screenshots) highlight column AO, hold down the Ctrl button and press on the right arrow until you get to the end of the worksheet, which is column IV. Then delete the columns by right-clicking and selecting Delete. Don’t worry, the columns won’t disappear—only the data they may hold are deleted.

Do the same for the rows: Highlight row 30, hold down Ctrl, and press the down arrow until you get to the end (65,536). Then rightclick and hit Delete.