While the impact on the performance may be negligible when there is less data, it becomes more profound as you add more and more data/calculations to the workbook. 9 out of 10 times, an Excel user would complain about the slow Excel spreadsheets. And there is hardly anything you can do about it. Well, that’s NOT completely true. The way Excel has been made, it does get slow with large data sets. However, there are many speed-up tricks you can use to improve the performance of a slow Excel spreadsheet.
10 Tips to Handle Slow Excel Spreadsheets
Here are 10 tips to give your slow Excel spreadsheet a little speed boost, and save you some time and frustration (click to jump to that specific section).
1. Avoid Volatile Formulas
Volatile formulas are called so because of a reason. Functions such as NOW, TODAY, INDIRECT, RAND, OFFSET etc. recalculate every time there is a change in the workbook. For example, if you use NOW function in a cell, every time there is a change in the worksheet, the formula would be recalculated and the cell value would update. This takes additional processing speed and you end up with a slow excel workbook. As a rule of thumb, avoid volatile formulas. And if you can’t, try and minimize its use.
2. Use Helper Columns
Helper columns are one of the most under-rated design constructs in Excel. I have seen many people shy away from creating helper columns. DON’T DO That. The biggest benefit of using ‘Helper Columns’ is that it may help you avoid array formulas. Now don’t get me wrong. I am not against array formulas. Rather I believe these could be awesome in some situations. But it when you try to do it all with one long formula, it does impact the performance of your Excel workbook. A couple of array formulas here and there shouldn’t hurt, but in case you need to use it in many places, consider using helper columns. Here are some Examples where helper columns are used:
Automatically Sort Data in Alphabetical Order using Formula. Dynamic Excel Filter – Extract Data as you Type. Creating Multiple Drop-down Lists in Excel without Repetition.
3. Avoid Array Formulas
Array formulas have its own merits – but speed is not one of those. As explained above, array formulas can take up a lot of data (cell references), analyze it, and give you the result. But doing that takes time. If there is a way to avoid array formulas (such as using helper column), always take that road.
4. Use Conditional Formatting with Caution
I absolutely love conditional formatting. It makes bland data look so beautiful. Instead of doing the comparison yourself, now you can simply look at a cell’s color or icon and you’d know how it compares with others. But.. here is the problem. Not many Excel users know that Excel Conditional Formatting is volatile. While you may not notice the difference with small data sets, it can result in a slow excel spreadsheet if applied on large data sets, or applied multiple times. Word of advice – Use it Cautiously.
5. Use Excel Tables and Named Ranges
Excel Table and Named Ranges are two amazing features that hold your data and makes referencing super easy. It may take a while to get used to it, but when you start using it, life becomes easy and fast. In creating data-driven dashboards, it is almost always a good idea to convert your data into an Excel Table. It also has an added advantage of making your formulas more comprehensible. For example, what’s easier to understand? =Sales Price-Cost Price OR =SUM(A1:A10)-SUM(G1:G10)
6. Convert Unused Formulas to Static Values
This is a no-brainer. When you don’t need it, don’t keep it. Lots of formulas would result in a slow Excel workbook. And if you have formulas that are not even being used – you know who to blame. As a rule of thumb, if you don’t need formulas, it’s better to convert them into a static value (by pasting as values).
7. Keep All Referenced Data in One Sheet
This may not always be possible, but if you can do this, I guarantee your Excel sheet would become faster. The logic is simple – formulas in your worksheet don’t have to go far to get the data when it is right next to it in the same sheet.
8. Avoid Using the Entire Row/Column as Reference (A:A)
The only reason I have this one on the list is that I see a lot of people using the entire row/column reference in formulas. This is a bad practice and should be avoided. While you may think that the row/column only has a few cells with data, Excel doesn’t think that way. When you reference an entire row/column, Excel acts it as a good servant and check it anyways. That takes more time for calculations.
9. Use Manual Calculation Mode
I am just repeating what million people have already said in various forums and blogs. Using Manual calculation gives you the flexibility to tell excel when to calculate, rather than Excel taking its own decisions. This is not something that speeds up your Excel workbook, but if you have a slow Excel spreadsheet, it definitely saves time by not making Excel recalculate again and again.
To switch to manual mode, go to Formula Tab –> Calculation Options –> Manual (press F9 key to recalculate)
10. Use Faster Formulas Techniques
Excel gives you a lot of formulas and formula-combos to do the same thing. It is best to identify and use the fastest ones. Here are a couple of examples:
Use IFERROR instead of IF and ISERROR combo (unless you are using Excel 2003 or earlier, which does not have IFERROR). Use MAX(A1,0) instead do IF(A1>0,A1,0) – This is a cool tip that I learned from Mr. Excel aka Bill Jelen. His research shows that MAX option is 40% faster than IF option (and I am ready to take this stat on his face value). Use the INDEX/MATCH combo, instead of VLOOKUP – This may raise a lot of eyebrows, but the truth is, there is no way VLOOKUP can be faster if you have 100’s of columns of data. The world is moving towards INDEX/MATCH, and you should make the shift too. [If you are still confused about what to use, here is a head-on-head comparison of VLOOKUP Vs. INDEX/MATCH]. Use — (double negatives) to convert TRUE’s and FALSE’s to 1’s and 0’s (instead of multiplying it by 1 or adding 0 to it). The speed improvement is noticeable in large data sets.
Is this an exhaustive list? Absolutely NOT. These are some good ones that I think are worth sharing as a starting point. If you are looking to master Excel-Speed-Up techniques, there is some good work done by a lot of Excel experts. Here are some sources you may find useful:
75 Speed-up tips by Chandoo (smartly done by crowdsourcing). Decision Models Website. Mr. Excel Message Board (explore this and you would find tons of tips).
I am sure you also have many tips that can help tackle slow excel spreadsheets. Do share it with us here in the comment section. I also have one request. The pain of working with a slow excel spreadsheet is something many of us experience on a daily basis. If you find these techniques useful. share it with others. Ease their pain, and earn some goodness 🙂
24 Excel Tricks to Make You Sail through Day-to-day work. 10 Super Neat Ways to Clean Data in Excel Spreadsheets. 10 Excel Data Entry Tips You Can’t Afford to Miss. Creating and Using a drop-down List in Excel. Reduce Excel File Size. How to Recover Unsaved Excel Files. Free Online Excel Training (7-part video course) Arrow Keys not Working in Excel | Moving Pages Instead of Cells
Kanhaiyalal Newaskar. In regards to #8, I recently learned that whole column/row references are ok for some of the functions. Some of the functions like SUM or SUMIF will automatically recognize the last used row/column. The following Microsoft article explains this in more detail. http://msdn.microsoft.com/en-us/library/ff726673(v=office.14).aspx I still agree with your suggestion though. As a best practice I still don’t recommend referencing whole rows/columns because I am not exactly sure how the function determines the “last used row/column”. As we know with VBA, this can sometimes be misleading. The last used row/column could be much farther down on the sheet if data has been deleted. And this means the function could be including unused rows/columns in the calculation. However, I think it’s good to know that some of the functions do consider this. I will have to see if there is a full list of the functions that do recognize the last used row/column. Thanks for the great tips! As you’ve stated this list is not exhaustive, but it is a very good start. Microsoft has several very good articles about performance optimization and calculation speed (written by the team at Decision Models) that are worth reading. Having said that, I think this article could be improved with some explanation as to WHY each of these ideas make a difference to speed. For example: 1. Helper columns (and rows) » breaks up formulae that can save recalculation due to the way Excel builds the ‘calculation tree’. 2. Array formulae » typically reference large ranges of cells and therefore need to evaluate each one, when this can be avoided with a different choice of formula. 3. Volatile functions » by definition must be recalculated each, but the real issue is related to where they appear in the calculation tree. If at the bottom then no big deal, but if right at the top, then very dependent formula must also be recalculated. (See the Decision Models website for discussion about this.) 7. It’s not clear what you mean by this one. 8. Whole columns/rows » Ditto re calculation tree issue. Regards
