Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallExcel’s Data Table feature shows how a formula’s result changes across trial values for one or two input cells. Set up a formula linked to the assumptions you want to test, arrange the trial values in a row or column, then use Data > What-If Analysis > Data Table to map them to the model’s input cells.
Set up the model and identify its input cells
Keep the assumptions you want to test in separate worksheet cells, and make sure the result formula refers to those cells. A Data Table temporarily substitutes trial values into the input cell or cells you assign, evaluates the formula, and displays the outcomes. It does not create a new model or test an arbitrary number of variables.
As an Amazon Associate I earn from qualifying purchases.
For example, if a payment formula depends on an interest rate, put the rate in its own cell and have the payment formula reference it. Preserve the original assumption in the model so you can tell which cell is being varied and what the baseline value is.
Build a one-variable Data Table
Use a one-variable table to compare many candidate values for a single model input. The trial values can run down a column or across a row.
- Enter the trial values in one column or one row.
- Place the result formula beside the list’s starting cell. For a vertical list, put the formula one row above and one cell to the right. For a horizontal list, put it one column to the left and one cell below.
- Select the entire rectangle containing the formula, trial values, and cells where Excel will return the results.
- Choose Data > What-If Analysis > Data Table.
- If the trial values run down a column, set Column input cell to the model input cell Excel should substitute. If the trial values run across a row, set Row input cell to that model input cell.
- Confirm the dialog. Format the output cells as needed to make the results and their units legible.
You can include more than one output formula in adjacent header cells for a one-variable table. Each formula must depend on the same input cell being tested.
Build a two-variable Data Table
Use a two-variable table to see results for combinations of two assumptions. One trial list runs down a column, and the other runs across a row.
- Enter the first set of trial values down a column and the second set across a row.
- Place one formula in the corner of the grid. It must refer to both model input cells.
- Select the complete rectangle: the corner formula, both trial-value lists, and the empty result area.
- Choose Data > What-If Analysis > Data Table.
- Set Row input cell to the model input represented by the values across the row. Set Column input cell to the model input represented by the values down the column.
- Confirm the dialog, then check that the row and column labels, result values, and output units match the assumptions being tested.
The mapping follows the orientation of the trial lists, not the position of the corresponding input cells in your model. A swapped row and column assignment can produce a plausible-looking grid whose results answer the wrong question.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Check calculation settings if results seem stale or slow
Microsoft says Data Tables recalculate when the worksheet recalculates, even if the table’s inputs have not changed. A workbook with large tables or formula-heavy models can therefore take longer to calculate.
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Microsoft documents a calculation option that recalculates the worksheet while excluding Data Tables. The general calculation guidance describes Automatic, Partial (excluding Data Tables), and Manual modes. In desktop Excel, calculation-option changes affect all open workbooks, so check the workbook’s calculation mode before relying on a table’s displayed results. If Data Tables are excluded from recalculation, refresh the table before using its results. Microsoft’s Excel for Mac instructions say to select the table formulas and press F9 to recalculate them manually.
Choose the right What-If Analysis tool
| Question | Excel tool | What it does |
|---|---|---|
| How does one or two assumptions affect a formula across many trial values? | Data Table | Displays formula results for one or two changing input cells. Microsoft’s What-If Analysis overview. |
| What happens under a few saved combinations of several changing values? | Scenario Manager | Stores sets of values; each scenario can contain up to 32 variable values. A scenario summary does not automatically update when scenario values change. Microsoft’s Scenario Manager guidance. |
| What single input produces a target formula result? | Goal Seek | Works backward from a desired result by changing one variable input. Microsoft’s What-If Analysis overview. |
| Which values optimize an objective while meeting constraints? | Solver | Adjusts decision-variable cells to optimize an objective subject to constraints. Microsoft’s Solver guidance. |
Interpret the results as scenarios, not forecasts
A Data Table reports what the worksheet formula returns for the inputs you chose. It does not establish that those inputs are likely, nor does it validate the assumptions or the formula. Label the trial values and results with their units, and explain any assumptions needed to interpret the table. Treat the outputs as conditional comparisons unless you have separate evidence that supports a probability or forecast.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Excel versions and menu availability
Microsoft’s documentation lists the feature for Microsoft 365 and Excel 2024 and 2021, including Mac editions; its general Windows guidance also covers Excel 2019 and Excel 2016. The documented menu path is Data > What-If Analysis > Data Table, though ribbon grouping can vary by platform and version. See Microsoft’s Data Table instructions for Excel for Mac and its general Data Table instructions.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
