Skip to content
Tutorials Center
My learning

Topics

Lesson 66 of 93 ยท PivotTables and Data Analysis

What-If Analysis: Goal Seek, Scenarios and Data Tables

Learn Excel What-If Analysis: Goal Seek, Scenario Manager, one- and two-variable data tables and Solver, with JavaScript and Python stand-ins.

A good model answers questions, and the best questions start with "what if". What if the interest rate rises by one point? What payment do I need to reach a target? What happens to profit in the best, expected and worst case? Excel's What-If Analysis tools let you ask these questions without rebuilding the sheet by hand.

This lesson covers four tools on the Data > What-If Analysis menu (Goal Seek, Scenario Manager and Data Table) plus the Solver add-in. Excel cannot run on this site, so the steps are text. The live examples are stand-ins: a tiny JavaScript model that searches for answers the way Goal Seek does, and Python code that mimics scenarios and data tables. They show the idea, but they are not Excel's own engine.

Four what-if tools

Goal Seek Find the one input that gives a target.
Scenarios Save named sets of inputs.
Data Table Show results for many inputs.
Solver Optimize with constraints.
1 / 2

Start with a model

Every what-if tool needs a model: input cells that you can change, and a formula cell that depends on them. Suppose you take a loan:

Cell Content
B1 Loan amount: 20000
B2 Yearly interest rate: 6%
B3 Years: 5
B4 Monthly payment: =-PMT(B2/12, B3*12, B1)

The inputs are B1:B3 and the result is B4. Keep inputs as typed values, never buried inside formulas, because the what-if tools change input cells directly. For more on PMT see Financial Functions.

Goal Seek

Goal Seek answers: "Which value of one input makes this formula give a target result?" You open Data > What-If Analysis > Goal Seek and fill three boxes:

  • Set cell: the formula cell, B4.
  • To value: the target, for example 300.
  • By changing cell: the input to adjust, B1.

Click OK and Excel changes B1 until B4 is (almost) 300, then asks whether to keep the answer. Here it tells you how big a loan a 300 monthly payment supports.

Facts to remember:

  • The Set cell must contain a formula, and the changing cell must contain a constant, not a formula.
  • Goal Seek changes one input only, and it overwrites the input cell. Use Undo (Ctrl+Z) or copy the old value first if you want to go back.
  • It is an iterative numeric search with limits (about 100 iterations and a small tolerance by default; see File > Options > Formulas > Enable iterative calculation). It may stop at a close value, such as 299.9999, rather than the exact target.
  • It can fail for formulas with no solution or several. Try another starting value if it reports it may not have found a solution.
  • The answer is static. If inputs change later, run Goal Seek again, or use a formula when an algebraic solution exists.

Excel does not publish its exact algorithm, so we cannot reproduce its steps. A simple search that shows the same idea is bisection: if the result is too low at one end and too high at the other, the answer lies in between; check the middle and keep the half that still brackets the target.

JS Goal Seek stand-in: find the loan for a target payment

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Scenario Manager

A scenario is a named set of values for chosen input cells. Use Data > What-If Analysis > Scenario Manager > Add and give each scenario a name ("Best", "Expected", "Worst"), then pick the Changing cells (up to 32) and type the values for that case. After saving a few, select one and press Show to put its values into the sheet; the whole model recalculates.

The most useful button is Summary. It creates a new worksheet listing every scenario side by side for the result cells you choose. This sheet is a static snapshot, so run Summary again after you change the model.

A few warnings:

  • Scenarios are stored with the worksheet. Inserting or deleting cells can break the links, so build them after the layout is final.
  • The Show button overwrites the live input cells, so remember which scenario is on screen. Many models add a visible label or a cell holding the scenario name.
  • For more than a handful of cases, a table of scenarios plus CHOOSE or INDEX is easier to maintain: put each case in a column, pick the active column with a dropdown (see Data Validation), and let the model read the chosen column. That version updates live and needs no menu.

A stand-in: scenario table

The pure-Python sketch below does the same as the Summary sheet: it runs one model for several named input sets and prints them side by side.

Python Scenarios side by side

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

One-variable data tables

A data table recalculates a formula for a whole list of inputs and shows all results at once. It is not an Excel Table (see Excel Tables); it is a special range filled by the array formula {=TABLE(...)}.

To build a one-variable table with the loan model:

  1. In a column, list the interest rates to test, for example 4%, 5%, 6%, 7%, 8% in D2:D6.
  2. In E1, one row above and one column right of the list, type a formula that points at the result: =B4.
  3. Select D1:E6, open Data > What-If Analysis > Data Table.
  4. Leave Row input cell empty and set Column input cell to B2 (the cell holding the rate, because the values run down a column).
  5. Press OK. Excel fills E2:E6 with the monthly payment for each rate.

You can add more result columns by putting more formulas in F1, G1 and so on. If your input values run across a row instead, put the formulas down the first column and use the Row input cell.

Notes you will thank yourself for later:

  • The input cell must be on the same sheet as the data table.
  • You cannot edit or delete part of the result range, only all of it.
  • Data tables recalculate often and can slow a large workbook. Formulas > Calculation Options > Automatic Except for Data Tables turns that off; press F9 to refresh them.

Two-variable data tables

A two-variable table shows one result for every combination of two inputs. Put one list across the top row and another down the first column, and put the formula in the corner cell where the two meet. For a payment table with rates down the side and years across the top:

3 4 5
=B4 (corner)
4%
6%

Select the whole block including the corner, open Data Table, set Row input cell to B3 (years run across) and Column input cell to B2 (rates run down). The grid fills with payments. Mixing up the two boxes is the most common mistake: the row input cell is for the values in the row along the top.

A stand-in: building the grid yourself

A data table is just the model run once per input combination. This script does it with loops:

JS A two-variable data table in JavaScript

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Solver

Solver handles what Goal Seek cannot: several changing cells, constraints, and maximizing or minimizing a result. Switch it on once under File > Options > Add-ins > Manage: Excel Add-ins > Go > Solver Add-in. It then appears at the right end of the Data tab.

A Solver problem has four parts:

  • Objective: a formula cell to maximize, minimize or set to a value (for example total profit).
  • Variable cells: the inputs Solver may change (for example how many of each product to make).
  • Constraints: rules such as units <= capacity, B5:B7 >= 0, or "must be an integer".
  • Method: Simplex LP for linear models, GRG Nonlinear for smooth curves, Evolutionary for irregular ones.

Typical uses are product mix, staff scheduling, budget allocation and route planning. Solver can return a Solver Results report and save a setup as a scenario. Always read its message: it can say a solution was found, or that constraints cannot be satisfied, or that the objective is unbounded. Nonlinear methods may stop at a local best rather than the global best, so try different starting values.

We cannot run Solver here, but the idea behind it is searching. This brute-force stand-in tries every whole-number product mix within limits and keeps the best one.

JS Solver stand-in: best product mix

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Real Solver is far smarter than looping over every case, which would be hopeless with dozens of variables; it uses mathematical methods that scale.

Which tool when?

  • You need one input value that reaches a target: Goal Seek.
  • You want to compare a few named cases: Scenario Manager, or a scenario table with a dropdown.
  • You want a grid of outputs for one or two inputs: Data Table.
  • You need to choose several inputs under limits: Solver.
  • For simple cases, a direct formula (such as =target/rate) beats all of them because it stays live.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which cells must a Goal Seek Set cell and By changing cell contain?

    Goal Seek evaluates a formula (the Set cell) while changing one typed input value (the changing cell). It cannot overwrite a formula.
  2. 2. In a one-variable data table whose input values are listed down a column, which box do you fill in?

    Values listed down a column go into the Column input cell. The Row input cell is for values listed across a row.
  3. 3. What does the Scenario Manager Summary button create?

    Summary writes a static report sheet with the scenarios side by side. It does not update, so run it again after changes.
  4. 4. When would you choose Solver instead of Goal Seek?

    Solver supports many variable cells, constraints and maximize or minimize goals. Goal Seek changes a single input to reach a given value.

Summary

  • What-If tools need a model: input cells that a formula depends on.
  • Goal Seek changes one constant to make a formula reach a target; its answer is static and approximate.
  • Scenario Manager stores named input sets; Summary makes a snapshot comparison. A scenario table with a dropdown stays live.
  • Data Tables fill a grid of results for one or two inputs; mind Row versus Column input cells and the same-sheet rule.
  • Solver optimizes with several variables and constraints. Enable it as an add-in.

Next: Importing Data from CSV, Text and the Web.