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.

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: Automatically calculates payment for each interest rate

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:
Row 1: Different loan terms (15, 20, 25, 30 years)
Column A: Different rates (4%, 4.5%, 5%, 5.5%, 6%)
Cell A1: =PMT(B2/12, B3*12, -B1)  // Payment formula

Steps:
1. Select entire table (A1:E6)
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

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

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