Data Tables run multiple scenarios simultaneously, showing how changing one or two variables affects a formula's result. Perfect for sensitivity analysis and scenario planning.

One-Variable Data Table

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

Two-Variable Data Table

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

Practical Example: Sales Forecast

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

Investment Return Analysis

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

Creating a Data Table

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

Data Table Characteristics

• 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 to Use Data Tables

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