Scenario Analysis with Data Tables

Data Tables are part of Excel’s “What-If Analysis” suite. They allow you to see the results of varying one or two input variables in a formula without creating multiple separate scenarios manually.

One-Variable Data Table

Useful for seeing how changing one input (e.g., Interest Rate) affects a result (e.g., Monthly Payment).

1. Set up your base formula.
2. Create a column of different interest rates.
3. Select the range involving your variables and the output cell.
4. Go to Data > What-If Analysis > Data Table.

Two-Variable Data Table

Allows you to vary two inputs simultaneously—for example, Loan Amount (Top Row) AND Interest Rate (Left Column)—to see a grid of possible Monthly Payments.

Large data tables can slow down workbook calculation. If this happens, set calculation mode to “Automatic except for Data Tables”.