If an Excel What-If Analysis data table keeps cycling through different answers, it is usually recalculating a formula or model that changes on recalculation—not cycling by design. Check Formulas > Calculation Options, look for volatile functions such as RAND or NOW, and consider Automatic Except for Data Tables if you need the table to hold its results between deliberate recalculations.
What an Excel data table does
In Excel, a What-If Analysis data table evaluates a formula repeatedly using different values for one or two input cells. For example, a loan model might calculate a payment for several interest rates, or a sales model might calculate profit for combinations of price and units sold. Excel temporarily places each test value in the selected input cell, recalculates the model, and records the result in the corresponding position in the data table.
This feature is different from an Excel table made with Insert > Table. An ordinary Excel table organizes rows of data and may expand as records are added. A What-If Analysis data table is a calculation tool that runs scenarios through a model. Both can appear as grids on a worksheet, which is a common source of confusion.
A What-If Analysis data table does not normally animate or cycle through answers by itself. If its displayed values change, Excel has recalculated the model, the underlying inputs or data have changed, or the results depend on something that changes between calculations.
How one- and two-variable tables work
A one-variable data table tests a series of values in one model input cell. The test values are placed either down a column or across a row. The output formula sits beside or above those values, and you tell Excel which model input cell to substitute. A two-variable table tests combinations: one set of values runs across the top row, and a second set runs down the first column. Excel substitutes both values in turn and records the formula result at each intersection.
For instance, a pricing model could use one input cell for price and another for units sold, with a formula elsewhere calculating revenue. A two-variable table could show the revenue for each price-and-volume combination. The table itself is not a list of saved, independent calculations: it is a set of results produced by recalculating the same model.
That distinction matters when you diagnose changing values. If the model gives a different answer every time it recalculates, the data table will faithfully display those changing answers. The table is often where the change becomes obvious, but the cause may be elsewhere in the workbook.
Common causes of results that change
- Volatile functions: RAND, RANDBETWEEN, NOW, TODAY, OFFSET, and INDIRECT can cause a formula or its dependents to update when Excel recalculates. RAND and RANDBETWEEN intentionally produce new random values.
- External or refreshed data: A linked workbook, connected data source, or query may provide updated values when refreshed or recalculated. Check whether the source has changed and whether a refresh is occurring.
- Iterative calculations: A circular reference with iterative calculation enabled can produce changing or unsettled results if its settings, starting values, or formulas are not appropriate for the model.
- Manual recalculation: Someone may be pressing a calculation shortcut, changing an input, or opening the workbook in a way that triggers a recalculation.
- Randomized model logic: A simulation that uses random numbers is expected to produce a new set of results when recalculated. A data table does not automatically make those results repeatable.
To confirm whether randomness is involved, inspect the formula that supplies the table’s output and the formulas it depends on. A small example: with a formula in cell B2 and no other dependencies, =RAND() returns a decimal from 0 up to, but not including, 1. The value changes when Excel recalculates. If that formula feeds the output used by a data table, the table’s answers can change as well. This example describes a random output, not a fixed scenario result.
Check calculation settings and recalculate deliberately
In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options. The choices include Automatic, Automatic Except for Data Tables, and Manual. Automatic recalculates formulas when relevant changes occur. Automatic Except for Data Tables recalculates the workbook normally but excludes What-If Analysis data tables from automatic recalculation. Manual calculation waits for a calculation command or other events that force calculation.
If the changing values are inconvenient but you still want normal formulas to update, try Automatic Except for Data Tables. This can be useful in a large workbook because data tables may be calculation-intensive. The trade-off is that table results can become out of date until you recalculate them. Do not interpret a temporarily unchanged table as proof that its inputs or formula are unchanged.
On Windows, F9 recalculates open workbooks, while Shift+F9 recalculates the active worksheet. Laptop function-key settings may require holding Fn. In Excel for Mac, the function keys may also require Fn depending on keyboard settings; the Formulas tab provides calculation commands such as Calculate Now and Calculate Sheet. Use these commands to see whether a deliberate recalculation causes the results to change.
Calculation mode is a workbook-level concern in practice: opening another workbook can affect the calculation mode Excel uses for open workbooks. If settings seem to change unexpectedly, check the menu again after opening the files involved. Also remember that manual mode can leave ordinary formula results stale, not just data-table results.
Build the table with the correct input cells
A wrong input-cell selection can make a table appear unreliable because Excel is testing the wrong part of the model. Before creating one, confirm that the model formula actually depends on the input cell you intend to vary. The input cell should be a single cell used by the model, not the output formula cell or the range of test values.
- Arrange the test values in a row or column, and place a reference to the model’s result formula in the corner of the planned table.
- Select the full range, including the result reference and all test values.
- Choose Data > What-If Analysis > Data Table.
- For values listed down a column, set Column input cell to the model cell being varied. For values listed across a row, set Row input cell instead.
- For a two-variable table, set the row input cell to the model input represented by the values across the top, and the column input cell to the model input represented by the values down the side.
After you create the table, change one of its test values or recalculate the workbook and check whether the outputs respond as the model predicts. If every result is identical, the chosen input cell may not affect the formula, or the table may be using the wrong input cell. If results change unexpectedly, trace the output formula’s dependencies rather than assuming the table has a separate calculation error.
Ways to make scenario results stable
First decide whether changing results are actually a problem. A table driven by live dates, refreshed data, or random simulation is expected to update. If you need a stable record of a particular run, preserve the results as values rather than relying on a live calculation table. Copy the relevant result range and use Home > Paste > Paste Values in a separate area or worksheet. That creates a snapshot; it no longer updates when the model changes.
For random models, record the inputs and outputs for each run, and avoid recalculating while you are reviewing a run you intend to keep. Excel’s standard RAND function does not provide a worksheet setting that makes a particular random sequence permanently repeatable. If reproducibility is essential, use a model design that stores the generated values or uses a controlled, documented method for producing them.
For date-dependent models, check whether TODAY or NOW is part of the calculation chain. If the analysis should use a fixed date, put that date in a normal input cell and have the model refer to it, rather than having the model obtain the current date on every recalculation. Likewise, if data comes from a connection, decide whether the table should use the latest refreshed data or a saved snapshot.
Manual calculation can reduce unwanted recalculation while editing, but it is not a safe substitute for understanding the model. It may leave unrelated formulas outdated and can confuse anyone who opens the workbook. If you use it temporarily, document that choice and recalculate before relying on the final outputs.
Excel for the web and Mac considerations
The steps above target Excel for Microsoft 365 on Windows. Excel for the web has more limited support for authoring What-If Analysis tools than the desktop app, so use the Windows desktop version when you need to create or fully troubleshoot a data table. A workbook containing an existing table may still be viewable online, but do not assume its calculation behavior or editing options match desktop Excel.
Excel for Mac has a similar What-If Analysis concept, though menu and keyboard details can vary with the interface version and function-key settings. Look for What-If Analysis on the Data tab and use the calculation commands on the Formulas tab if a keyboard shortcut is inconvenient. If a workbook behaves differently between Mac and Windows, check calculation mode, external connections, and whether both versions have recalculated the same model inputs.
Frequently Asked Questions
Why does my Excel data table change every time I press F9?
F9 recalculates formulas, and a data table may then recalculate its scenarios. If its output depends on RAND, NOW, refreshed data, or another changing input, new results are expected. Inspect the formulas feeding the table and consider whether the model is meant to be dynamic.
How do I stop a What-If Analysis data table from recalculating?
Choose Formulas > Calculation Options > Automatic Except for Data Tables to keep data tables out of automatic recalculation while allowing other formulas to calculate automatically. Recalculate the table when needed, and check that its results are current before using them.
Is an Excel data table the same as an Excel table?
No. An Excel table is a structured range for organizing records and formulas. A What-If Analysis data table repeatedly evaluates a model formula using one or two changing input cells. The similar names refer to different features.
Why are all the results in my data table the same?
Check that the table’s row or column input cell is the model input that the output formula actually uses. Also confirm that the full table range, including the corner formula reference and test values, was selected when the data table was created.
Can I keep a data table’s current results permanently?
Yes. Copy the result cells and paste values into another range or worksheet. The pasted values are a snapshot and will not update with the model, so keep the original live table if you may need to recalculate scenarios later.
Related
- Excel Auto Group Rows by Cell Value: What You Can Do
- Excel Conditional Formatting on All Sheets
- Excel Conditional Formatting: Apply Rules to a Range
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace Shortcut Key: How Ctrl+H WorksIn Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace, then enter the text…
- Google Sheets IFERROR: How It Works and When to Use ItIn Google Sheets, use IFERROR to show a useful alternative whenever a formula returns an error. For example,…
- Excel Shortcut to Edit a Cell: F2 and Other MethodsIn Excel for Microsoft 365 on Windows, select the cell you want to change and press F2 to…
- Excel Freeze Pane Line: How to Remove ItTo get rid of a freeze pane line in Excel, select the affected worksheet, then choose View >…
- Making a Calendar in Google Sheets: A Practical GuideTo make a calendar in Google Sheets, enter a date from the month you want in a cell…
- What Is Power Query? Excel Data Cleanup ExplainedPower Query is Microsoft Excel’s data-import and transformation tool: it connects to files, databases, and other sources, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.