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

Goal Seek: Find the Input for a Desired Output

You have a formula that calculates profit. You want to know what price yields $100,000 profit.

Data tab → What-If Analysis → Goal Seek:

• Set cell: The cell with your profit formula
• To value: 100000
• By changing cell: The price cell

Excel iterates until it finds the price that produces the target profit.

Scenarios: Compare Multiple What-If Cases

You want to compare three scenarios: pessimistic, baseline, and optimistic — each with different growth rates and costs.

Data tab → What-If Analysis → Scenario Manager:

• Add scenarios with different input values
• Show any scenario to update your model
• Create a summary report comparing all scenarios

Data Tables: Sensitivity Analysis

Data Tables show how results change across a range of inputs. Perfect for sensitivity analysis.

One-variable Data Table: Shows output for different values of one input (e.g., profit at prices from $10-$20)

Two-variable Data Table: Shows output for combinations of two inputs (e.g., profit at different prices AND volumes)

Set up your input values in a row or column, reference your formula, then Data → What-If Analysis → Data Table.

When to Use Each Tool

• Goal Seek: Finding a specific target value
• Scenarios: Comparing named sets of assumptions
• Data Tables: Seeing how results vary across input ranges

Official Resources

• What-If Analysis intro — Microsoft's overview
• Goal Seek — finding target values