Excel Data Tables for What-If Analysis

An Excel Data Table recalculates a formula for a whole list of substitute input values at once, so you can see every "what-if" scenario's result in a single table instead of changing one cell and checking the answer repeatedly.

What is a data table in Excel's What-If Analysis?

A data table is one of the three commands under Data → What-If Analysis, alongside Scenario Manager and Goal Seek: it takes one formula and fills a grid with that formula's result for every test value (or pair of test values) you list. It is not the same thing as an Excel Table created with Insert → Table or Ctrl+T, which is a formatted, filterable range for storing records. If you click any result cell in a what-if data table, the formula bar shows {=TABLE(row_input, column_input)} — an array Excel writes for you and that you can't type by hand.

For when to pick each of the three What-If Analysis tools, see What-If Analysis in Excel: 3 Tools and When to Use Each. If you meant structured Ctrl+T tables, see Excel Tables: Why You Should Use Them for Everything.

How do I build a one-variable data table?

List the values you want to test in a column, reference your target formula in the cell diagonally adjacent to the first value, select the whole block, then point Data Table at the single input cell your formula depends on.

Example: Loan Payment Analysis

Setup:
Cell B1: Loan Amount = 200000
Cell B2: Interest Rate = 5%
Cell B3: Years = 30
Cell B4: =PMT(B2/12, B3*12, -B1)  // Monthly payment

Data Table Setup:
Column D: Different interest rates (4%, 4.5%, 5%, 5.5%, 6%)
Cell E1: =B4  // Reference to payment formula

Steps:
1. Select range D1:E6
2. Data tab → What-If Analysis → Data Table
3. Column input cell: B2 (interest rate)
4. Click OK

Result: Excel fills E2:E6 with the payment at each rate
D2  4.0%   E2  954.83
D3  4.5%   E3  1,013.37
D4  5.0%   E4  1,073.64
D5  5.5%   E5  1,135.58
D6  6.0%   E6  1,199.10

How do I build a two-variable data table?

Put the formula in the table's top-left cell, one variable's test values down the column beneath it, and the other variable's test values across the row beside it, then give Data Table both a row input cell and a column input cell.

Example: Loan Payment with Rate and Years

Setup (same inputs in B1:B4 as the one-variable example):
Cell D8: =B4  // Corner cell: reference to payment formula
E8:H8: Different loan terms (15, 20, 25, 30 years)
D9:D13: Different rates (4%, 4.5%, 5%, 5.5%, 6%)

Steps:
1. Select entire table (D8:H13)
2. Data → What-If Analysis → Data Table
3. Row input cell: B3 (years)
4. Column input cell: B2 (rate)
5. Click OK

Result: Matrix showing payment for each rate/year combination

Build the table away from its input cells (B1:B3) so the result grid never covers the values Excel is substituting into.

What does a finished Excel data table example look like?

A finished two-variable data table is a plain grid of numbers: every cell is your formula's result for the input values at the head of its row and column, and the corner cell still shows the base-case result. Here is the loan example above after clicking OK, for a $200,000 loan:

        D          E          F          G          H
 8   1,073.64    15 yrs     20 yrs     25 yrs     30 yrs
 9     4.0%     1,479.38   1,211.96   1,055.67     954.83
10     4.5%     1,529.99   1,265.30   1,111.66   1,013.37
11     5.0%     1,581.59   1,319.91   1,169.18   1,073.64
12     5.5%     1,634.17   1,375.77   1,228.17   1,135.58
13     6.0%     1,687.71   1,432.86   1,288.60   1,199.10

The corner (D8) shows 1,073.64 because the base inputs are 5% and 30 years, which is why it matches H11. To sanity-check any cell, type the PMT formula with those inputs somewhere else: =PMT(0.06/12, 15*12, -200000) returns 1,687.71, matching E13. If you don't want the base value visible in the grid, give the corner cell the custom number format ;;; to hide it.

How would a data table model a sales forecast?

Set profit as the formula cell, then test a range of prices in a one-variable table or prices against unit volumes in a two-variable table to see profit across the whole scenario grid.

Base Case:
Units Sold: 1000
Price per Unit: $50
Variable Cost: $30
Fixed Costs: $15,000
Profit = (Units * (Price - Variable Cost)) - Fixed Costs

One-Variable Table:
Test different prices from $40 to $60
Shows profit at each price point

Two-Variable Table:
Rows: Different prices ($40-$60)
Columns: Different unit volumes (800-1200)
Shows profit for all combinations

How do I analyze the results of an Excel data table?

Read the grid for two things: where the result crosses a threshold you care about (such as zero profit), and which input moves the result more per step. Here is the two-variable sales table above filled in, with profit = units × (price − $30) − $15,000:

Price \ Units      800      1,000      1,200
$40             -7,000     -5,000     -3,000
$45             -3,000          0      3,000
$50              1,000      5,000      9,000
$55              5,000     10,000     15,000
$60              9,000     15,000     21,000

• Break-even: at 1,000 units profit is exactly $0 at $45. At 800 units the sign flips between $45 and $50; at 1,200 units, $45 already earns $3,000.
• Which lever matters more: each $5 price step adds units × $5 ($4,000 to $6,000), while each extra 200 units adds 200 × the per-unit margin ($2,000 at $40, $6,000 at $60).
• Make it visible: apply a red-to-green color scale to the result cells with conditional formatting so the loss/profit boundary shows at a glance.
• Get the exact crossover: the grid only brackets the answer. Use Goal Seek to solve for the exact break-even price at a given volume ($48.75 at 800 units).

Can a data table test investment return scenarios?

Yes — build an FV formula, then use a two-variable data table with annual return rate down one axis and monthly contribution across the other to see how each combination compounds over time.

Formula: Future Value
=FV(rate, nper, pmt, pv)

Test Scenarios:
- Different annual returns (4% to 10%)
- Different contribution amounts ($500 to $2000/month)

See how small changes in return rate or contributions
dramatically affect long-term wealth accumulation

What are the exact steps to create a What-If Analysis data table?

The steps differ slightly depending on whether you're testing one variable or two — both start with building the base formula first.

One-Variable Table Steps:

1. Create your formula with inputs
2. List test values in a column (or row)
3. Reference formula in adjacent cell
4. Select entire range
5. Data → What-If Analysis → Data Table
6. Specify column (or row) input cell

Two-Variable Table Steps:

1. Put formula in top-left corner
2. Put first variable values in column below
3. Put second variable values in row to the right
4. Select entire table
5. Data → What-If Analysis → Data Table
6. Specify both row and column input cells

What are the characteristics of a data table's results?

Data table output behaves differently from ordinary formulas because Excel treats the whole result block as a single array.

• Results are array formulas (can't edit individual cells)
• Updates automatically when inputs change
• Can slow down large workbooks (set to manual calc)
• Perfect for scenario comparison at a glance

When should I use a data table instead of Goal Seek or Solver?

Reach for a data table specifically when you want to see results for many scenarios at once, not just find one answer.

• Testing multiple scenarios quickly
• Sensitivity analysis (how sensitive is output to inputs?)
• Break-even analysis
• Financial modeling and forecasting