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, 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.






Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.