With endless data entry in Excel, columns and rows continue to grow, and so will your file until it’s uncontrollable. If your business spreadsheet has become so large that making changes and sharing the file has become a nightmare, follow these 4 proven strategies for a systemic approach to higher performance enterprise spreadsheets.
1. Reduce formatting and update your file format
First, remove redundant calculations, unused charts, outdated worksheets, and use compressed images and graphics. While formatting is an important organizational tool, too much formatting will slow down your file. Use only what’s necessary for legibility.
Plus, 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 the 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.
2. Condense the amount of data you’re holding on to
While spreadsheets have an almost infinite capacity, too much data slows down your files. 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 sending it via email to your colleagues is a non-starter. Large files are the key factor impeding spreadsheet performance when you need it most. It’s worth taking another look at what’s really needed – something that we have talked about before in one of our earlier blog posts.
Microsoft is making Excel and spreadsheets smarter all the time, helping to manage the size of your workbooks by supporting access to known external data sources, reducing the amount of reference data you need to store and manage in your spreadsheets.
3. Review how the data is organized
Assessing how data is used for calculations or modeling will help identify your actual data inputs and outputs, and how these can be separated from your spreadsheet calculations. Often, inputs, calculations and outputs are all co-mingled, growing the size of your spreadsheets and hindering performance.
Start by identifying your inputs – it’s often possible to get a big win on spreadsheet size by managing how you gather your inputs. In turn, this will more clearly identify your spreadsheet calculations, and for most of us, that's why we have the spreadsheet in the first place.
Reviewing the organization of your data will also lead to a clearer view of the outputs of your spreadsheet, a win for improved data governance. All of this leads to more robust business performance with streamlined spreadsheets, shedding unnecessary data and generating another win for the size of your spreadsheets.
4. Improve data access control and connectivity
Finally, ask yourself “how is my data used and who is dependent on the outputs from my spreadsheets?” This will drive you to look at more effective data access control. It’s okay to say not everyone has access to everything. It’s your data after all. Identifying who is dependent on the data outputs from your spreadsheets, the frequency that they need your team’s data, and what they do with it will also provide a framework to enable a solution around enterprise connectivity for your data. Having a flexible and scale-able framework to think about how to share the outputs of your spreadsheet helps support this process, and drives enterprise spreadsheet performance.
Looking for more ways to improve your enterprise spreadsheet performance or have a spreadsheet performance issue? Send your questions – we’d love to help.