An Excel What-If data table lets one formula run against many possible inputs. Instead of retyping a payment, price, or margin formula repeatedly, you give Excel a list of inputs and it fills the results automatically.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows. Excel for Mac has a similar What-If Analysis feature available from the Data menu. This is different from a normal formatted Excel table.
Build a one-variable data table
Use this when only one input changes, such as interest rate, price, discount, or quantity.
- Create the formula you want to test in one cell, such as a loan payment formula that points to an interest-rate cell, term cell, and loan amount cell.
- List the possible input values in a column or row near the formula.
- Select the whole setup: the formula cell, the input values, and the empty cells where results should appear.
- Open Data > What-If Analysis > Data Table.
- If your values run down a column, leave Row input cell blank and set Column input cell to the original input cell the values should replace.
- If your values run across a row, use Row input cell instead.
- Click OK.
Excel calculates one result for every test value, so you can compare all outcomes in one compact block.
Build a two-variable data table
Use this when two inputs change at the same time, such as loan term across the top and interest rate down the side.
- Place the formula in the top-left corner of the table area.
- Enter one set of test values down the first column below the formula.
- Enter the second set of test values across the first row to the right of the formula.
- Select the entire rectangle, including the formula, both value lists, and the blank grid.
- Open Data > What-If Analysis > Data Table.
- Set Row input cell to the original cell represented by the across-the-top values.
- Set Column input cell to the original cell represented by the down-the-side values.
- Click OK.
Excel fills the full grid with every combination of the two inputs. This is useful for comparing loans, pricing, budgets, and other models with several possible scenarios.
More control
Speed up large workbooks
Data tables can recalculate many combinations every time the workbook changes. If that slows Excel down, open Formulas > Calculation Options and choose Automatic Except for Data Tables, then press F9 when you want them recalculated.
Do not edit one result cell
The result range behaves like a single array formula. If you need to remove or redo it, clear the whole table result area rather than trying to change one calculated cell.
Change the scenarios
Edit the row or column of input values. Excel recalculates the results using the same table setup, so you usually do not need to rebuild it.
Use Goal Seek for one target result
A data table shows many results from many inputs. If you know the exact result you want and need Excel to find one input that produces it, use Goal Seek from the same What-If Analysis menu.
Sources
- Microsoft Support – Calculate multiple results by using a data table (2025)
- Microsoft Support – What-If Analysis in Excel (2025)
- Microsoft Support – Goal Seek (2024)