Recently, I wrote a series of blogs that dealt with slow calculations on Excel and how to improve calculation speed.
A question I did not address is: Why should you care? The answer is that obviously, if calculations take a long time, you would prefer if they take less time.
What I did not clarify is when Excel actually calculates a formula. The simple explanation is that a formula is calculated when one of its arguments changes or is calculated. In addition, some formulas are considered volatile and are calculated anytime anything changes in any open workbook. See more about that here.
If these were the only cases in which a formula was calculated, our lives as Excel users would be much easier. The reality is that calculations occur much more frequently that that.
Simple Operations That Cause Multiple Calculations
The case that triggers calculations for no apparent reason is when we use filter/sort/refresh on a table or just a range of data. Every filter or sort operation triggers a full calculation of all formulas in the range/table. When you refresh the data, every formula in the range will calculate twice.
In my opinion, there is no good reason for this behavior, as all formulas will return the same result after the filter or sort. However, don’t expect this behavior to change anytime soon, as it has been this way forever.
What Can We Do to Alleviate the Pain?
Knowing that formulas will be calculated again and again during filters and that sometimes we may need to filter many times, we need to find some workarounds.
Manual Calculation
Before running multiple filter/sort operations to a range that contains formulas, you may wish to switch to manual calculation, and then switch back to automatic calculation after you are done. The way to switch to manual calculation and back is from Formulas/Calculation Options. Alternatively, you may write a small macro to toggle the calculation mode.
When you are in manual calculation, Excel will not attempt to calculate any other cells except the one you just edited. No formulas will run during filter/sort/refresh.
The big disadvantage of using manual calculation is that you must be aware at all times that you are in this mode and click F9 to apply all necessary calculations. If you send a file to someone else and the file was saved in manual mode, it will be opened in manual and the recipient may be very confused.
If you have queries in your file bringing data into tables and the slow calculations are in columns in the same tables, the situation can be even worse. During refresh, all calculations in the tables are calculated twice. This may slow down the refresh time significantly.
A solution is to refresh using a VBA macro that switches to manual calculation before refresh and restores the calculation mode after refresh.
Improve the Speed of Any Calculations in the Table
You may choose to implement everything you have learned in previous posts or from any other source to use the best Excel methods for the task. For example, use Match/Index instead of Vlookup, use the much faster non-exact Vlookup, etc. If the functions are calculating fast, you can hopefully tolerate the fact that they calculate on every filter/sort/refresh.
The only downside to this solution is that you have to know all the alternative solutions, and it still may be slow, even when it is fully optimized.
Copy/Paste Values
This method works well if you plan to send the file to other colleagues and they are interested in the data for exploration and reporting. You can copy and paste values on all cells in the table before saving a new version and sending it.
Again, this method has the disadvantage of requiring extra steps every time before you share your file. Also, if you mix data/formula changes with exploring the data, it may not be practical to wipe all formulas just to do some filtering.
Use Pivot Tables for Exploration
Pivot tables can rapidly summarize data and can be filtered without causing any calculations.
If you need the data in a table-like format, you can create a flat pivot table that will look very similar to a table.
This method has its limitations. It is not practical to have a pivot with 50 fields nested in rows. Sometimes tables can have over 100 columns, and a pivot cannot contain all of the data. In addition, using such a wide table is not a fun experience.
Move Calculations to the Query
If possible, try to move calculated columns away from the query table to the query that imports the data into Excel in the first place. The advantage of this technique is that calculations are done once—and never again—once the data is in Excel. This can also be a disadvantage because calculations are not dynamic and are not affected by changes in other cells.
One disadvantage is that calculations in Power Query are not as flexible as in Excel, and for some calculations, you may need to resort to writing M code, which is a new skill.
Conclusion
If you have a table/range with thousands of rows and you have columns with calculations like Vlookup, simple operations on these tables will be anywhere between slightly annoying to impossible. Your best options are to either improve the calculation speed by using better functions or use some other method explained in this article.
