First of all, let’s have a look at what makes spreadsheets slow
Invalid references in Name manager
This is my “usual” find. People use names in name manager and then copy tabs from one Excel workbook to another and bring over the names as links to the “old” file. As these spreadsheets grow, they end up with more and more names referencing old or already deleted files. The names can no longer be resolved by Excel. And Excel doesn’t like not resolving links, so it really tries and ends up taking a lot of time for this. So the first thing I tell someone when they have a slow spreadsheet, is go to name manager and delete any invalid names. This alone, can do miracles.
Unresolved Links and formulas
This is similar to the above. Check if your file has links to anything it shouldn’t have links to, by going to Data, Connections, Edit links. Also if you have a lot of cells that are returning invalid or #ref, then delete these. I’ve seen a lot of people keep these, because they are working with files that were created by their predecessor and are afraid of destroying stuff. Be assured that if a cell has #ref and you delete it, you’re just doing your file a favor. There is nothing you will ruin.
Number of cell references in formulas
If you can avoid referencing the entire column in your sum or vlookups then do so. Instead of (C:C) do (C1:C3000) or until wherever your data is.
Efficiency of functions used
If you use too many IFs and SUMs in one formula instead of using SUMIFS(), or use VLOOKUP() on a huge matrix, instead of using INDEX & MATCH, you can impact the speed of your calculations.
Too many volatile functions
These type of functions get calculated every time you make a move in Excel. No matter what you calculate or scroll or type, they get calculated. An example is the Now() or Today() formula, but the major one that most people have in complicated files is OFFSET(). So be aware of this if you have very slow files.
Too much conditional formatting
This is similar to the above. Conditional formatting, actually gets applied to only the area that is visible on the screen. So every time you scroll down or up, Excel “paints” the conditional formatting. Too much of this can cost you speed.
Steps to fix slow spreadsheets
Now, let’s have a look at the steps we need to take to make our spreadsheets faster. Some of these are really simple to do, especially number 2.
Optimize your workbook by:
- Keeping as much as you can in the same workbook
- Deleting all invalid references in name manager
- Deleting any invalid links and formulas
- Deleting anything you no longer use. Press Ctrl + END on each sheet to determine the actual used range of your sheets. If your used range includes empty cells delete any rows or columns that you don’t need
Optimize your formulas by:
- Using the most efficient function possible to get your results. Reduce workarounds. Use the IFERROR() formula instead of the IF(ISERROR(),,)
- Minimizing the number of cells you are referencing. Instead of referencing (D:D) use (D1:D200)
Best is of course, to never end-up with a slow spreadsheet by following these tips from the start. However, in many cases you might not be the creator of the original file. Put some of these tips to practice and you’ll surely see an improvement in speed.
Leila Gharani
I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
