Data Tables are a feature built into Microsoft Excel that provides some limited sensitivity analysis capabilities.   This feature can be accessed from the What-If Analysis group of the Data tab of the Excel ribbon.

Effectively, when Excel encounters a data table in your model, it will internally use multiple recalculations to provide a table of output results for all the input values you provide in the table.

Data Table Limitations

Excel’s Data Tables have several drawbacks and limitations:

• The interface for creating them is very counter-intuitive.
• The data tables created by the Excel UI are unformatted, and hence often difficult to interpret.
• You must manually construct a table of input values to use.
• The results are tabular, and not graphical.
• You are limited to one or two inputs, and those inputs must all be on the same worksheet as the data table itself.
• If you have a large number of data tables, or a particularly large data table, Excel’s responsivity can be negatively impacted.

While in general we recommend you use the other Sensitivity Explorer tools to perform your analyses, there are some cases where a Data Table is appropriate, especially if you need a table of values that will automatically update as you make changes to your model.

Sensitivity Explorer Improvements

While Sensitivity Explorer cannot remove all the limitations of Data Table (e.g. the number of inputs allowed, or the fact that they must all be on the same worksheet as the table itself) it improves upon the built-in Excel feature in several important ways:

• The interface for creating them is very intuitive.
• The tables created are nicely formatted.
• The input value table is automatically generated by the software.

Sensitivity Explorer Data Table Features

Sensitivity Explorer offers two closely related features for creating Data Tables:

The One-Variable Data Table Creator facilitates creating a data table with one input and one or more outputs.

The The-Variable Data Table Creator helps you create a data table with two inputs and one output.