Excel What-If Data Tables: Faster calculation with VBA

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.

WhatIf1First you need to create the outline for the Data Table:

WhatIf2This 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:

WhatIf3You 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 = ""
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
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
' 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)
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
' 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.

WhatIf4The 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


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.




This entry was posted in arrays, Calculation, VBA. Bookmark the permalink.

3 Responses to Excel What-If Data Tables: Faster calculation with VBA

  1. Wow! That is really significant. I’ll definitely be implementing this! …caveat noted

  2. Levi Bailey says:

    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.

  3. John Broderick says:

    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?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s