Excel data table sensitivity analysis shows how changing one or two input values affects a formula’s result. In Microsoft 365 for Windows, build the model, place a reference to its result beside the trial inputs, select the full grid, and choose Data > What-If Analysis > Data Table.
What a sensitivity data table does
An Excel Data Table is a What-If Analysis tool. It substitutes a series of trial values into one or two designated input cells, recalculates a formula for each substitution, and displays the results together. This makes it easier to see how sensitive an output is to an assumption such as price, volume, an interest rate, or a cost.
For example, a sales model might calculate revenue from units sold and price. A data table can show how revenue changes at several possible prices while holding the number of units constant. The model remains in its original cells; Excel temporarily supplies each trial value to the input cell and records the resulting output.
This feature is different from an Excel structured Table, which organizes rows and columns of data and can use table names in formulas. It is also not a chart or a forecasting method: it reports the outcomes generated by the formula and assumptions you provide. It does not decide which assumptions are realistic.
Set up the model and a one-variable table
Start with a working formula that calculates the result you want to examine. In this simple revenue example, enter 1,000 in B2 for units sold and 20 in B3 for price per unit. With units sold in B2 and price in B3, enter this formula in B4: =B2*B3. The result in B4 is 20,000.
To test different prices while keeping units sold fixed, arrange the sheet like this:
- Enter the output reference
=B4in E1. - Enter trial prices such as 15, 17.5, 20, 22.5, and 25 in D2:D6.
- Leave D1 blank; the output reference belongs in the top row, one column to the right of the trial-value column.
Select D1:E6, then choose Data > What-If Analysis > Data Table. In the Data Table dialog, leave Row input cell empty and set Column input cell to B3, because the trial values run down a column and replace the model’s price input. Click OK. Excel fills E2:E6 with revenue results: 15,000, 17,500, 20,000, 22,500, and 25,000.
The input cell must be the original model cell that the formula depends on, not the cell containing a trial value. If the output formula does not depend on that input, changing it will not affect the results. You can format the trial values and outputs as currency or numbers after the table is calculated.
Build a two-variable table
A two-variable data table tests two inputs at once and displays the results as a grid. Continue using B2 for units sold, B3 for price, and B4 for revenue. Put =B4 in D1, prices such as 15, 20, and 25 across E1:G1, and unit quantities such as 500, 1,000, and 1,500 down D2:D4. Select D1:G4 and choose Data > What-If Analysis > Data Table.
In the dialog, set Row input cell to B3 because prices are arranged across the top row. Set Column input cell to B2 because quantities are arranged down the first column. After clicking OK, the grid reports revenue for every price-and-volume combination. For instance, the intersection of price 20 in F1 and volume 1,000 in D3 returns 20,000. The intersection of price 25 and volume 1,500 returns 37,500.
Getting the row and column assignments right is important. The row input cell is the model input replaced by values in the table’s top row; the column input cell is replaced by values in its first column. A useful check is to compare one table result with a manual calculation by entering those same assumptions in the model.
Read the results as sensitivity, not prediction
The table answers a conditional question: “What would this formula return if these inputs had these values?” It does not estimate the likelihood of any input, account for events absent from the model, or prove that one outcome will occur. In the revenue example, the model assumes every unit sells at the listed price and ignores discounts, taxes, returns, and capacity limits. Those assumptions may be unsuitable for a real business decision.
Look for the size and direction of changes, not just the largest number. A one-variable table can show whether the output rises steadily with price, while a two-variable grid can reveal whether a price change has a different impact at different sales volumes. If the response is nonlinear, the spacing of trial values matters: a few widely spaced values may miss a sharp change between them. Choose inputs that are meaningful for the model and include a baseline value so you can compare the table with the current result.
For money, loan, or investment models, a sensitivity table is only a mathematical illustration based on stated assumptions. It is not financial, tax, or legal advice; check actual terms with the lender or a qualified professional.
How data tables compare with other What-If tools
Excel’s What-If Analysis tools answer different kinds of questions. Data Tables are best when you want to compare many values for one or two inputs and observe one or more formula outputs. Scenario Manager stores named sets of assumptions, which is useful for comparing cases such as “low,” “base,” and “high,” especially when scenarios involve several changing cells. Goal Seek works in the opposite direction: it finds an input value that makes one formula reach a specified result.
These tools are not interchangeable. A data table is a compact grid of repeated calculations; Scenario Manager organizes alternative input sets; Goal Seek searches for one input that meets one target. For more than two changing inputs, you can make separate tables, use Scenario Manager, or build a custom analysis with formulas. Pick the simplest method that matches the question rather than forcing a two-variable grid to represent a larger model.
Common problems and practical limits
If the results are identical, check that the selected input cell is the same cell referenced by the model’s formulas. In a one-variable table, confirm that the values are arranged vertically and assigned to Column input cell, or horizontally and assigned to Row input cell. In a two-variable table, verify both assignments and confirm that the output reference is in the top-left corner of the selected grid.
Other common issues include selecting an incomplete range, putting the output reference in the wrong corner, or using text-formatted values where numbers are intended. Also check that calculation is enabled and that the model does not contain an error. A table can only report what the underlying formula calculates; it will not repair a broken formula or account for a missing assumption.
Data tables can slow recalculation in a large workbook because each trial combination requires another calculation. If the workbook becomes sluggish, reduce the number of trial values, simplify dependent formulas, or use Formulas > Calculation Options > Automatic Except for Data Tables. With that setting, Excel recalculates data tables when requested rather than recalculating them every time other workbook changes occur. Remember to recalculate before relying on displayed results.
A What-If Data Table is a special calculation range, not an ordinary set of independent formulas. Treat its output cells as results generated by the table, and change its inputs through the trial-value area or the table setup rather than typing over individual results. On Mac, the Data Table command is also available through Data > What-If Analysis; ribbon details can vary slightly by Excel release. Excel for the web does not provide full desktop authoring for What-If Data Tables, so use Excel for Windows or Mac to create or revise one.
Frequently Asked Questions
Is an Excel data table the same as a sensitivity analysis? A Data Table is one built-in way to perform sensitivity analysis. The broader term describes examining how changes in assumptions affect an output; it can also be done with other tools or formulas.
Can an Excel data table change more than two inputs? No. Excel’s Data Table tool substitutes values into one input cell or two input cells at a time. For more changing assumptions, compare separate tables or use another approach such as Scenario Manager.
Why does my data table show the same result in every cell? Often the row or column input cell is wrong, or the output formula does not depend on that input. Confirm the model references the designated cell and that the trial values are placed in the matching row or column.
Can I use multiple output formulas in one data table? Yes. For a one-variable table, place multiple output references across the top row beside the vertical trial values, then select the full range. For a two-variable table, arrange multiple output references in the upper-left area according to Excel’s data-table layout. Check the resulting range carefully, since the layout is less compact than a single-output grid.
Can I create a What-If Data Table in Excel for the web? Excel for the web may display a workbook containing a data table, but it does not offer the same full authoring tools as desktop Excel. Open the workbook in Excel for Windows or Mac to create or change the table.
Related
- Excel Delimited Text to Columns: How to Split Data
- Defining Print Area in Excel: Setup and Printing Tips
- DDSolver Excel Add-in: What It Does and How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Scatter Plot With Dates: Setup and FormattingTo make an Excel scatter plot with dates, put real Excel dates in one column and the corresponding…
- Can You Make a Box and Whisker Plot in Excel?Yes. In Excel for Microsoft 365 on Windows, select your grouped data, then choose Insert > Insert Statistic…
- Excel Consolidate Multiple Tables into One WorkbookYes. To combine the rows from several similarly structured tables into one refreshable table, use Power Query: convert…
- Excel Not Equal Sign: How to Use the <> OperatorExcel’s not equal sign is <>. Put it between two values in a formula to check whether they…
- Google Sheets Dropdown Calendar: How to Add a Date PickerYes—Google Sheets can show a calendar date picker in a cell, but it is not the same as…
- Conditional Formatting Scatter Plots in ExcelExcel does not apply worksheet conditional-formatting rules directly to scatter-plot markers. To color points dynamically, split the data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.