What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
To do sensitivity analysis in Excel, build a formula-based model, then use a Data Table to see how an output changes across input values, Scenario Manager to compare named combinations of assumptions, or Goal Seek to find the input needed for a target result. These native What-If Analysis tools are available in desktop Excel; Microsoft’s service description says the desktop app is needed for Data Tables and Goal Seek, so open the workbook in desktop Excel if you cannot find them in the browser version (Microsoft’s Excel for the web service description).
What sensitivity analysis means in Excel
Sensitivity analysis changes one or more assumptions in a model while leaving its formulas and structure intact, then measures the effect on an output. For example, you might test how profit changes as selling price or sales volume changes.
The related Excel tools answer different questions:
- Data Table: How does the output change across a range of one or two inputs?
- Scenario Manager: What is the output under each defined combination of assumptions?
- Goal Seek: What value for one input produces a target output?
Microsoft groups Scenarios, Goal Seek, and Data Tables as Excel’s built-in What-If Analysis tools. Data Tables support one or two changing input cells; a Scenario can store up to 32 changing values; and Goal Seek changes one input cell (Microsoft’s overview of What-If Analysis). A Data Table is the most direct choice for a conventional sensitivity table. Scenario Manager and Goal Seek complement it, but do not display a range of sensitivities in the same way.
Prepare a simple model first
Use a small profit model to make the steps concrete. Enter these labels and values in a blank worksheet:
| Cell | Label | Value or formula |
|---|---|---|
| B2 | Selling price | 50 |
| B3 | Units sold | 1,000 |
| B4 | Variable cost per unit | 30 |
| B5 | Fixed costs | 10,000 |
| B7 | Revenue | =B2*B3 |
| B8 | Variable costs | =B4*B3 |
| B9 | Profit | =B7-B8-B5 |
At these starting assumptions, profit is $10,000. Check that result before building a sensitivity analysis; if the base model is wrong, the what-if results will be wrong too.
Keep inputs and formulas distinct
Put assumptions in dedicated input cells and make formulas refer to those cells rather than hard-coding assumptions inside formulas. Label units clearly—for example, dollars per unit versus total dollars—and visually distinguish inputs from calculated cells. A visible base case makes it easier to compare the model with the tested results. Where useful, add data validation to prevent impossible entries such as negative units or rates outside an allowed range. Named ranges such as SellingPrice, UnitsSold, and Profit can make larger models easier to read. Save a copy before experimenting with What-If tools.
Method 1: Build a one-variable Data Table
Use a one-variable Data Table to test many values for one assumption, such as price, sales volume, interest rate, discount rate, or unit cost. In this example, the table tests selling prices while calculating profit. Excel substitutes each trial value into the selected input cell and records the result; it does not permanently replace the model’s input with each trial.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteArrange the cells
Enter a reference to the output above the list of test prices:
Rank #2
| Cell | Entry |
|---|---|
| D2 | =B9 |
| D3 | 40 |
| D4 | 45 |
| D5 | 50 |
| D6 | 55 |
| D7 | 60 |
The output reference belongs in the cell above the input values. For a column-oriented Data Table, Microsoft’s instructions place the formula one row above and one cell to the right of the input-value column—in this layout, D2 is above the values in D3:D7 (Microsoft’s instructions for calculating multiple results with a Data Table).
Run the table
- Select
D2:D7, including the output reference and every test value. - On the ribbon, choose Data > What-If Analysis > Data Table. The exact ribbon grouping can vary by Excel edition, platform, language, and window size.
- Leave Row input cell blank.
- Set Column input cell to
B2, the selling-price assumption used by the model. - Select OK.
With the example model, the results are:
| Selling price | Profit |
|---|---|
| $40 | $0 |
| $45 | $5,000 |
| $50 | $10,000 |
| $55 | $15,000 |
| $60 | $20,000 |
These values follow from the example’s assumptions: 1,000 units sold, $30 variable cost per unit, and $10,000 fixed costs. Your results will depend on your model.
Extend the Data Table to two variables
Use a two-variable Data Table when you want to see how two assumptions interact—for example, price and sales volume, or interest rate and loan term. The table varies one input across a row and the other down a column, then calculates an output for each combination.
Set up price versus units sold
Keep selling price in B2, units sold in B3, and profit in B9. Create this layout, with unit volumes across the top and prices down the left:
| Cell | Entry |
|---|---|
| F2 | =B9 |
| G2:K2 | 500, 750, 1,000, 1,250, 1,500 |
| F3:F7 | 40, 45, 50, 55, 60 |
Microsoft’s two-variable layout calls for the output formula in the top-left corner, one list of trial values across a row, and the other list down a column. The row and column input cells must match those lists (Microsoft’s Data Table instructions).
Run and format the two-variable table
- Select the complete range
F2:K7. - Choose Data > What-If Analysis > Data Table.
- Set Row input cell to
B3, because the volume values run horizontally across row 2. - Set Column input cell to
B2, because the price values run vertically down column F. - Select OK.
Format the output cells as currency and consider conditional formatting with a clearly explained color scale. Mark the base-case combination—$50 and 1,000 units—so readers can see how nearby cases compare. Use a plausible range of inputs rather than arbitrary extremes.
A Data Table handles no more than two changing input cells. A large grid can also slow a calculation-heavy workbook because Excel recalculates the model for each combination. If your question requires several assumptions to move together as a realistic business case, Scenario Manager may be a better fit.
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 reinstallMethod 2: Compare cases with Scenario Manager
Use Scenario Manager when you want to compare named combinations such as best case, base case, and worst case. Unlike a Data Table, it can change several input cells together. Microsoft says an individual scenario can contain up to 32 changing values (Microsoft’s overview of What-If Analysis).
Define the scenarios
For the profit model, use B2:B4 as changing cells: selling price, units sold, and variable cost per unit. Leave fixed costs at B5 unless you also want that assumption to vary.
| Scenario | Selling price (B2) | Units sold (B3) | Variable cost per unit (B4) |
|---|---|---|---|
| Best case | 60 | 1,500 | 25 |
| Base case | 50 | 1,000 | 30 |
| Worst case | 40 | 700 | 35 |
These are illustrative assumptions, not predictions. A scenario is useful only if its values and the reasoning behind them are credible for the decision you are making.
Add and compare scenarios
- Choose Data > What-If Analysis > Scenario Manager.
- Select Add, enter a scenario name such as
Best case, and selectB2:B4in Changing cells. - Select OK, enter the values for that scenario in the order of the selected cells, and select OK again.
- Repeat for
Base caseandWorst case. - In Scenario Manager, select a scenario and choose Show to apply its values to the worksheet.
- Choose Summary to create a comparison report, and identify
B9as the result cell.
The Scenario Manager wizard is in the What-If Analysis group on the Data tab, according to Microsoft’s instructions for switching between scenarios (Microsoft’s Scenario Manager guide). Selecting Show changes the worksheet’s current input values, so check which scenario is active before continuing work in the model.
Recommended Free Tools
If you edit scenario values after creating a summary report, the existing report does not automatically reflect those edits; create a new summary report to show the revised values (Microsoft’s What-If Analysis overview). Scenario Manager is helpful for a small set of defined cases, but it does not show every intermediate combination between them.
Method 3: Use Goal Seek to find a target input
Use Goal Seek when you know the output you want but need to solve for one input. For example, the profit formula can find how many units must be sold to reach $20,000 profit. Goal Seek is target-seeking, not a table of outcomes: it changes one input cell and does not evaluate multiple input combinations. Microsoft recommends Solver when you need to determine multiple input values (Microsoft’s overview of What-If Analysis).
Find units needed for $20,000 profit
- Choose Data > What-If Analysis > Goal Seek.
- In Set cell, select
B9, the profit formula. - In To value, enter
20000. - In By changing cell, select
B3, the units-sold assumption. - Select OK and review the proposed result.
- Select OK to keep the proposed input, or Cancel to restore the previous value.
For this model, the result is 1,500 units: each unit contributes $20 before fixed costs, so $30,000 in contribution covers $10,000 in fixed costs and leaves $20,000 profit. The calculation assumes price, unit cost, and fixed costs stay unchanged.
A solution is not automatically a sensible business decision. Check whether the required sales volume is within capacity, whether a required price is competitive, and whether the target is achievable under other constraints. If Goal Seek cannot find a result, test low and high inputs manually to see whether the target is feasible. Also check that the changing cell feeds the output formula and that rounding or lookup thresholds are not causing discontinuities. A complex or nonlinear model may have multiple possible solutions or fail to converge reliably.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose the method that matches your question
| Your question | Use |
|---|---|
| How does profit change as price changes? | One-variable Data Table |
| How do price and sales volume interact? | Two-variable Data Table |
| What happens in best, base, and worst cases? | Scenario Manager |
| What input reaches a target result? | Goal Seek |
| What combination optimizes an outcome under constraints? | Solver, rather than one of these three basic approaches |
Excel’s What-If Analysis overview identifies Solver as the option for more advanced optimization problems (Microsoft’s overview of What-If Analysis).
Troubleshoot missing or unexpected results
What-If Analysis is missing
Confirm that you are using desktop Excel. Microsoft’s Excel for the web service description lists the desktop app as necessary for Goal Seek, Data Tables, and Solver (Microsoft’s service description). Excel for the web may display a workbook that contains results, but the dedicated native tools may not be available there for creating or editing the analysis. If you only have browser access, use a manual formula grid as a workaround.
A Data Table gives unexpected results
- Check that the formula reference is in the correct corner cell and that the selected range includes it and all test values.
- Confirm that the chosen input cell is the actual assumption used by the output formula—not an output or calculated cell.
- For a one-variable column table, place the formula above the test values and set the column input cell. For a two-variable table, map the horizontal list to the row input cell and the vertical list to the column input cell.
- Check that the output formula refers to the assumption being varied and does not contain a hard-coded version of it.
- Try a small range first if the workbook is slow or calculation-heavy.
The results appear stale
Check Formulas > Calculation Options > Automatic. Microsoft notes that Data Tables recalculate when automatic workbook recalculation is enabled (Microsoft’s What-If Analysis overview). In manual calculation mode, displayed results may not reflect the latest inputs. Large tables, volatile formulas, external links, simulations, and complex lookup chains can add calculation work; use a smaller test range or calculate manually and refresh only when needed.
A Scenario Manager report is out of date
After changing scenario values, create a new summary report rather than relying on an earlier one. Also check that the changing cells were selected in the intended order and that the model’s formulas use them.
Interpret and present the results responsibly
A sensitivity table shows modeled effects within the ranges and assumptions you chose; it does not establish which events are likely. Most one-at-a-time tests hold other inputs fixed, even though real-world assumptions may move together. A table is not a probability forecast, and a large modeled effect does not by itself prove that an input is the most important business risk.
- Identify which input has the largest effect within the tested range, and state that range.
- Look for thresholds, nonlinear changes, or sudden jumps rather than assuming a straight-line relationship.
- Check whether the base case is close to break-even or another decision threshold.
- Ask whether the result remains material under plausible assumptions and whether the inputs can realistically occur together.
- Document why you chose the ranges and what the model leaves out; formula accuracy cannot make unrealistic assumptions reliable.
For presentation, use a line chart to show a one-variable trend and a heat map or conditional formatting for a two-variable table. A tornado chart can rank one-at-a-time input impacts. Label the assumptions, units, output, and base case, and write a short interpretation that limits its conclusion to the tested model and range.
Make a manual sensitivity grid if needed
If you are working in Excel for the web, need more than two changing inputs, or want a fully visible and editable grid, you can construct a formula-based table manually. For example, with price values across the top, units sold in the column headings, and unit cost and fixed costs held in the model, a cell might use:
=($B$2*G$2)-($B$4*G$2)-$B$5
In that example, G$2 is the units value across the row; copy the formula across and adapt the references to match the grid’s layout. A manual grid can be easier to inspect or export, but you must design and verify its formulas yourself. It is not the native Data Table feature.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Quick Recap
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.
