We’ve all been there: your Excel® workbook is so large that you might as well take a coffee break while you wait for it to open. And forget about sending it via email to your colleagues.
If your workbook has become so large that making changes and sharing the file has become a nightmare, follow these three tips to slim down the size of your workbook.
But before we jump in, common sense applies. Be sure to first remove redundant calculations, unused charts, outdated worksheets, and use compressed images and graphics.
Tip 1: Reduce formatting.
While formatting is an important organizational tool, too much formatting will slow down your file. Use only what’s necessary for legibility.
Test to see if over-formatting is slowing down your file:
1. Make a copy of the Excel file, then open it.
2. Select the entire worksheet with the keys Ctrl + A. If the workbook has more than one worksheet, hold down the Shift key while you click the last tab at the bottom of the window so that you select all the worksheets.
3. At the top of the window in the Home ribbon’s editing pane, click the drop-down arrow by the Clear icon (which looks like an eraser) and select Clear Formats. Save the file.
If this new file is significantly faster than the old one, you over-formatted the original file.
Tip 2: Save in a different file format.
Files that use the .xls extension that were created with Excel versions prior to 2007 can take up more than double the space of those created with the newer versions using the .xlsx extension. If you don’t need compatibility with old versions of Excel, convert the file by performing a Save As and selecting Excel Workbook (.xlsx) from file format drop-down menu. You can further reduce files and increase speed by using the Excel Binary Workbook (.xlsb) file type, but keep in mind that alternative spreadsheet programs will not be able to load the file.
Tip 3: Put a database behind it.
With endless data entry in Excel, columns and rows continue to grow, and so will your file until it’s uncontrollable. The best way to manage data over time is with a database. But if you don’t want to purchase, install and configure expensive database software or lose the ability to work with data so easily in Excel, consider xOverTime. xOverTime makes it easy for users to leverage powerful database capabilities to store key data and manage the distribution of that data without having to share the entire workbooks.
Looking for more ways to improve your Excel-based business process? Send your questions – we’d love to help.