Skip to content
Request Demo

Data Table

Glossary Term • Beginner • 2 min read

Audience
Model Developers • Auditors
Last Reviewed
July 2026
Updated
Version 1.0

Executive Summary

In Excel, a data table is a range of cells that performs a series of what-if calculations by substituting a set of input values into one or two designated cells and recording the resulting output from a specified formula. Data tables are the standard mechanism for producing sensitivity matrices in financial models. A one-variable data table varies one input and shows the output for each value; a two-variable data table varies two inputs simultaneously. Data table results are stored as array formulas using the TABLE function and update automatically when the model recalculates, unless the workbook's calculation mode excludes data tables from automatic recalculation.

Key Takeaways

  • ✓ A data table is an Excel feature (accessed via Data > What-If Analysis > Data Table) that executes a series of what-if calculations by substituting multiple values into one or two specified input cells and recording the corresponding output from a formula cell.
  • ✓ One-variable data table.
  • ✓ After construction, data table result cells contain the array formula `{=TABLE(row_input_cell, column_input_cell)}`.
  • ✓ The most significant risk associated with data tables is integrity failure: the results displayed in the table do not reflect the current state of the model.

Definition

A data table is an Excel feature (accessed via Data > What-If Analysis > Data Table) that executes a series of what-if calculations by substituting multiple values into one or two specified input cells and recording the corresponding output from a formula cell. The results are displayed in a range of cells, forming a sensitivity matrix.

Data tables are the primary mechanism for sensitivity analysis in Excel-based financial models.

Types of Data Tables

One-variable data table. Varies a single input across a specified range of values and shows the output for each. For example: an IRR sensitivity table showing IRR values for revenue growth rates from 0% to 10% in 1% increments.

Two-variable data table. Varies two inputs simultaneously and shows the output for each combination. For example: an IRR sensitivity matrix showing IRR values for combinations of revenue growth (0% to 10%) and discount rate (8% to 14%).

How Data Tables Work

After construction, data table result cells contain the array formula {=TABLE(row_input_cell, column_input_cell)}. Excel evaluates this formula by: (a) substituting each input value from the header row or column into the specified input cell, (b) recalculating the model, (c) recording the output from the formula cell, and (d) placing the result in the corresponding result cell. This process repeats for every value in the input range.

Data Table Integrity Risk

The most significant risk associated with data tables is integrity failure: the results displayed in the table do not reflect the current state of the model. This occurs when:

  • Automatic recalculation of data tables is disabled (Formulas > Calculation Options > Automatic Except for Data Tables)
  • The input cell specified in the table is not in the dependency chain of the output formula (so varying the input has no effect on the output)
  • The data table results have been converted from array formulas to static values

See Sensitivity Table Integrity for the full guide to detecting and resolving these failures.

Continue Reading

Prerequisites

  • Base Case — the reference scenario around which data table sensitivity is typically constructed

How OXXON tests thisRun a free structural check with FMAE

Related Articles

Sensitivity Table Integrity in Financial Models

Sensitivity table integrity refers to whether the results displayed in an Excel data table in a financial model reflect the current state of the model's calculations or whether they represent stale values from a previous calculation state. An Excel data table runs a series of calculations by substituting different input values into designated cells and recording the outputs. If automatic calculation is disabled, if the data table's input cells are incorrectly specified, or if the data table has been converted from dynamic to static values, the sensitivity results displayed may not correspond to the model as it currently stands. This is a high-risk structural failure because it provides false assurance about the model's sensitivity to changes in key assumptions.

Formula Error Types in Financial Models

Formula errors in financial models fall into four principal categories: visible error values (including #REF!, #VALUE!, #DIV/0!, #NAME?, #N/A, #NULL!, and #NUM!), which display in cells and are immediately apparent; silent formula errors, which produce plausible-looking values but incorrect results; structural formula errors, which arise from incorrect model construction rather than incorrect values; and logic errors, which occur when a formula correctly implements an incorrect financial relationship. Each category requires different detection methods and carries different risk implications.

Base Case

The base case in a financial model is the central scenario that represents the model developer's primary projection of expected outcomes. It uses the most likely or central estimate for each assumption, rather than optimistic or pessimistic values. All other scenarios (upside, downside, stress) are defined in relation to the base case. The base case is the scenario used for investment decisions, credit approvals, and board presentations unless otherwise stated. Its key outputs — typically IRR, NPV, DSCR, and equity returns — are the primary reference metrics for any decision made in reliance on the model.
Request Demo