For some reason this year I keep on meeting Excel’s What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly, in fact more slowly than I think they should.
Using What-If Data Tables
What-If Data Tables can be either 1-dimensional or 2-dimensional. You create them using the What-If pull-down on the Data tab.
First you need to create the outline for the Data Table:
This is a 2-dimensional data table. The idea is that you have a single formula (in cell E7) that shows the results of a set of calculations that are dependent on 2 constants (in F3 and G3). We want to recalculate the workbook for every combination of the values in the range (row) F7:H7 and the column E8:E12, and place the corresponding result in F87:H12.
Start by selecting the range E7:H12 and click the What-If Data Table button:
You are asked to pick the Row Input Cell and Column Input Cell, and when you click OK the range F8:H12 gets filled with a TABLE array function formula {=TABLE(F3,G3)}
Note that for some strange reason Excel requires the row input and column input cells to be on the same worksheet as the data table.
What-If data tables calculate slowly because Excel has to recalculate many times (in this case 3 x 5 = 15 times. So you need to be in Manual calculation mode, or possibly in Automatic except Data Tables mode (this makes calculation automatic but does not do the iterative calculation of the data table(s)).
Speeding Up What-If Data Table Calculation.
I did some experiments to see if the data table calculation could be speeded up and found:
- Each iteration is calculated using single-threaded calculation.
- The process starts with a multi-threaded recalculation, then iterates using single-threaded recalculation, then finishes with an additional recalculation.
- There seems to be quite a large overhead to this overall process.
- If the one combination of values in the table is the same as the initial set of values it gets recalculated anyway even if not needed.
The VBA Solution
So I wrote some VBA to see if by eliminating these problems I could make it run faster.
Note that the VBA is written as a proof-of-concept demonstration rather than production level code.
Sub IterateTables() ' ' Proof-of-Concept code ' for faster calculation of a 2-D What-If data table ' Copyright Charles Williams, Decision Models 16-December-2015 ' Dim rngTable As Range Dim rngRowCell As Range Dim rngColCell As Range Dim varRowSet As Variant Dim varColSet As Variant Dim varResults() As Variant Dim rngFormula As Range Dim nRows As Long Dim nCols As Long Dim lCalcMode As Long Dim j As Long Dim k As Long Dim varStartRowVal As Variant Dim varStartColVal As Variant Dim varFirstVal As Variant Dim blCalculated As Boolean Dim dTime As Double ' ' get the what-if table and its formula ' Set rngTable = ActiveCell.CurrentRegion ' expand active cell to the current region ' Set rngFormula = rngTable.Cells(1, 1) ' Top-Left corner nRows = rngTable.Rows.Count - 1 ' number of rows in the Column of what-if values nCols = rngTable.Columns.Count - 1 ' number of columns in the row of what-if values ' ' get the row and column input cells using RefEdit ' With ufIterTable .RefEditRow.Value = "" .RefEditCol.Value = "" .Show If ufIterTable.RefEditRow.Value "" Then Set rngRowCell = Range(.RefEditRow.Value) If ufIterTable.RefEditCol.Value "" Then Set rngColCell = Range(.RefEditCol.Value) End With ' ' if 2-D and we have got the row and column cells then proceed ' If nRows > 0 And nCols > 0 And Not rngRowCell Is Nothing And Not rngColCell Is Nothing Then dTime = Microtimer ' ' create output results array ' ReDim varResults(1 To nRows, 1 To nCols) ' ' get row and column arrays of what-if values ' varRowSet = rngFormula.Offset(0, 1).Resize(1, nCols).Value2 varColSet = rngFormula.Offset(1, 0).Resize(nRows, 1).Value2 ' ' set environment ' Application.ScreenUpdating = False lCalcMode = Application.Calculation If Application.Calculation xlCalculationManual Then Application.Calculation = xlCalculationManual ' ' can only skip initial values if workbook is calculated at start ' If Application.CalculationState = xlDone Then blCalculated = True Else blCalculated = False End If ' ' initial start values ' varStartRowVal = rngRowCell.Value2 varStartColVal = rngColCell.Value2 varFirstVal = rngFormula.Value2 ' ' calculate result for each what-if values pair ' For j = 1 To nRows For k = 1 To nCols If blCalculated And varRowSet(1, k) = varStartRowVal And varColSet(j, 1) = varStartColVal Then ' ' if whatis value pair is the same as the start values then skip recalc ' varResults(j, k) = varFirstVal Else ' ' show calc status in the status bar ' Application.StatusBar = "What-If Table Row " & j & " Column " & k ' ' set values for this iteration, recalc, store result ' rngRowCell.Value2 = varRowSet(1, k) rngColCell.Value2 = varColSet(j, 1) Application.Calculate varResults(j, k) = rngFormula.Value2 End If Next k Next j ' ' reset status bar ' Application.StatusBar = False ' ' put results back ' rngFormula.Offset(1, 1).Resize(nRows, nCols) = varResults ' ' reset back to initial values & recalc ' rngRowCell.Value2 = varStartRowVal rngColCell.Value2 = varStartColVal Application.Calculation = lCalcMode Application.Calculate ' ' timer message ' dTime = Int((Microtimer - dTime) * 1000) / 1000 MsgBox "Time for " & nRows * nCols & " Iterations: " & dTime & " Seconds" End If End Sub
Timing Results
I ran some timing tests using a 4-core I7 with hyper-threading so that Excel tries to use 8 cores for multi-threaded calculation.
The first row gives the time for a single calculation in multi-threaded and single-threaded mode.
The second row gives the time to calculate using Excel’s What-If data table method. You can see that difference between single-threaded and multi-threaded is equal to 2*(Single-thread time – multi-thread time).
The third row gives the time to calculate using my VBA routine: a lot faster in multi-threading mode and slightly faster in single-threading mode.
A timing breakdown for XL Data Tables could be:
2 * multi-threaded Calc + 15 * single-threaded Calc + overhead
2*0.65 + 15 * 2.8 + 0.3 = 43.6
And for the VBA:
15 * multi-threaded calc + overhead
15 * 0.65 + 0.15 = 9.9
Conclusion
You can download the test workbook from
I have made a suggestion in Excel User Voice to use these findings to speed up Excel’s What-If Data Tables. Please vote for it if you think it has merit:
Also it would be great if existing data table users could try out my VBA code to see how it works for them.
I plan to build a more generalised solution into FastExcel Calc.
http://www.decisionmodels.com/fastexcelV3SpeedTools_Calc.htm
Wow! That is really significant. I’ll definitely be implementing this! …caveat noted
Great post, thanks Charles! I use data tables fairly regularly and had suspected they were slow but didn’t realise it could be so drastic.
I was using your example file and found something interesting that I can’t explain.
I added a UserForm with just a single CommandButton.
The UserForm has ShowModal=False.
The CommandButton calls the iterTables routine.
I modified your iterTables routine to remove the ufIterTable functionality and instead hard code them:
Set rngRowCell = Range(“F3”)
Set rngColCell = Range(“G3”)
If I click on the button in the UserForm and don’t move the mouse everything calculates fine.
It takes about 17 seconds on my machine.
However if I click move the mouse after pressing the button and before the usual 17 seconds has elapsed it will stop calculating properly from that point on.
Do you have any idea why this would happen?
Thanks,
John
Hi, it seams that the test workbook is no longer available? I’d like to use it if possible!
Thanks, this looks very promising!
Fixed the download link
https://1drv.ms/x/s!AozctK2jVaC0_BBqxXCCaVkLI-W1?e=CGBL6s
I wrote my own macro to simulate a slow-as-molasses data table and this is indeed MUCH faster than the regular data table… Many thanks for pushing me in this direction!
Wonderful post! I’ve been struggling with excel and performing scenario analysis on complex data sets. Some minor adaptations for my use case and this significantly out-performs the in-built tool. Cheers!