What-if Data Table calc should use multi-threading
Calculation Performance of iterative what-if Data Tables is unnecessarily slow. The reasons seem to be (in priority sequence):
- each iteration is using single-threaded recalculation rather than multi-threaded
- the overhead for the whole table calculation looks larger than it should be
- the process starts with a recalc, then does each iteration, then ends with a recalc: the first recalc is unnecessary
- if the values to be used in one of the iterations are the same as the start values the recalc for that set of values could be skipped
Many thanks to Charles for taking the time to analyze this in such detail and start the discussion about improving the efficiency of data tables. Please vote for this issue if it’s important to you so we can prioritize it appropriately.
Steve (MS Excel)
Raul Sanchez commented
Please. I usually have to do my scenario "data tables" in Visual Basic because that's the only way it uses more than 10% of the CPU.
This would be very helpful. I use many data tables and quickly becomes bloated and slow.
This issue was compounded by formulas switching to excel 2013 or later as the formulas must take care not to include the new limit of rows.
Charles Williams commented
See my blog post for more details