How to Use What-If Analysis in Excel
Excel's What-If Analysis tools are your secret weapon for making smarter business forecasts and decisions. Instead of guessing, you can build dynamic models to see how changing certain numbers impacts your bottom line. This tutorial will walk you through Excel’s three core What-If tools: Scenario Manager, Goal Seek, and Data Tables.
What is "What-If Analysis?"
At its core, What-If Analysis is the process of changing values in cells to see how those changes affect the outcome of formulas on your worksheet. It turns your static spreadsheet into a dynamic financial model, allowing you to explore different possible outcomes without manually re-typing everything.
For example, you can answer questions like:
What if we increase our ad spend by 20%? How will that affect our profit?
What if our conversion rate drops by half a percent? Do we still hit our revenue goals?
How many units do we need to sell to reach a $50,000 profit?
Excel provides three powerful tools designed specifically for this purpose:
Scenario Manager: Lets you create and compare different groups of input values (scenarios) side-by-side, like a "Best Case," "Worst Case," and "Most Likely" budget.
Goal Seek: Works backward. You tell Excel the result you want from a formula, and it figures out the input value you need to achieve that goal.
Data Tables: Lets you see how changing one or two variables impacts a formula, producing a table of all the possible outcomes automatically.
You can find all three of these tools in the Data tab, under the What-If Analysis dropdown in the Forecast group.
Using Scenario Manager for Side-by-Side Comparisons
Scenario Manager is ideal when you have several possible situations and want to see the result of each one. Think of it as creating saved versions of your spreadsheet inputs that you can instantly swap between.
Let's use a simple project budget as an example. You have a few key costs that could change, and you want to see how they impact your total profit.
Step 1: Set Up Your Model
First, create a basic model with your variables (inputs) and your result (output). Our variables will be Marketing Spend, Cost per Material, and Labor Hours. Our result will be the final Project Profit, which is based on a fixed Revenue minus the total costs.
Your setup should look something like this, with simple formulas calculating the costs and the final profit.
For example, the formula in cell C8 for Project Profit might be:
Step 2: Add Your First Scenario
Let's create our "Worst Case" scenario where all the costs are higher than expected.
Go to Data > What-If Analysis > Scenario Manager.
In the pop-up window, click Add.
Name the scenario Worst Case.
For the "Changing cells," select the cells that contain your input values. In our example, that's $B$5:$B$7. Pro tip: you can hold Ctrl to select multiple cells that aren't next to each other.
Click OK.
Step 3: Enter the Scenario Values
A new window will appear asking for the values for your "Worst Case" scenario. Enter the high-cost estimates. For instance:
Marketing Spend (B5): 8000
Cost per Material (B6): 17
Labor Hours (B7): 300
Click OK. You'll now see "Worst Case" in your Scenario Manager list.
Step 4: Create Additional Scenarios
Repeat the process to add a "Best Case" (low costs) and "Most Likely" (expected costs) scenario. Click Add for each, give it a name, and enter the corresponding values.
Once you have all your scenarios set up, you can simply double-click any of them in the Scenario Manager box (or select one and click Show) to instantly update your worksheet with those values and see the resulting profit.
Step 5: Create a Scenario Summary
The real power comes from the summary report. In the Scenario Manager window, click the Summary... button.
Ensure your Result cells box correctly points to your final calculation (in our case, cell C8) and click OK.
Excel will instantly generate a new, perfectly formatted summary report on a new sheet, showing all your scenarios and their results side-by-side. This is fantastic for presentations or reports where you need to showcase different possibilities.
Finding Your Target with Goal Seek
What if you already know the result you need, but you're not sure how to get there? Goal Seek is the tool for you. It solves for a missing input value by working backward from your desired outcome.
Imagine you run an online course and want to earn exactly $10,000 in revenue next month. You know your price per course, but you need to find out how many courses you must sell to hit your goal.
Step 1: Set Up the Formula
Your spreadsheet should be simple. It needs three cells: Courses to Sell (this is your input, you can start with any guess), Price per Course, and Total Revenue (this cell contains the formula).
The formula in B3 would be:
Step 2: Launch Goal Seek
With your formula set up, navigate to Data > What-If Analysis > Goal Seek.
You'll see a simple pop-up box with three fields:
Set cell: This is the cell that contains your final formula. For us, that's B3 (Total Revenue).
To value: This is your target number. Type in 10000.
By changing cell: This is the one input cell Excel is allowed to change to reach the goal. For us, that's B1 (Courses to Sell).
Step 3: Run the Analysis
Click OK. In a fraction of a second, Excel runs hundreds of calculations and finds the value that solves the problem. It will update the "Courses to Sell" cell on your sheet.
Goal Seek is incredibly efficient for solving for a single variable, whether it's figuring out sales targets, break-even points, or the test score needed to pass a class.
Testing Variables with Data Tables
Scenario Manager compares specific, pre-defined sets of inputs, and Goal Seek finds a single input. Data Tables, on the other hand, let you test a whole range of values for one or two variables at the same time.
One-Variable Data Table
Let's say you're taking out a business loan and want to see how different interest rates will impact your monthly payment. A one-variable data table is perfect for this.
How to Set It Up:
Build the Model: Create your loan calculator using Excel's PMT (Payment) formula. Your inputs are Loan Amount, Interest Rate, and Term (in months).
List Your Variable: In a separate column, list all the different interest rates you want to test (e.g., 3.0%, 3.5%, 4.0%, etc.).
Reference the Formula: In the cell above your list of interest rates and one column to the right, create a reference to your original PMT formula result. So, if your payment is calculated in B4, this cell's formula will simply be =B4.
Create the Table: Select the entire range containing your list of variables and the formula reference. Go to Data > What-If Analysis > Data Table.
Set the Input Cell: Since our variables (interest rates) are in a column, we use the "Column input cell" field. Click into the box and select the original Interest Rate cell in your main model (cell B2).
Excel will instantly fill the table with the monthly payment for each corresponding interest rate.
Two-Variable Data Table
You can even take this a step further by testing two variables simultaneously, like both the interest rate and the loan term.
How to Set It Up:
Follow steps 1 & 2 from above, listing your primary variable (Interest Rate) in a column.
Across the top row of your table setup, list your second variable (Loan Term in months, e.g., 180 for 15 years, 360 for 30 years).
Reference the Formula: In the top-left corner cell of your table (where the row and column of variables meet), reference your original PMT formula cell (e.g., =B4).
Create the Table: Select the entire table, including both sets of variables and the corner formula reference. Go to Data > What-If Analysis > Data Table.
Set Input Cells:
For "Row input cell," click the original Loan Term cell (B3) in your model.
For "Column input cell," click the original Interest Rate cell (B2) in your model.
Excel populates the entire matrix, showing you the resulting monthly payment for every combination of rate and term. It's a quick and powerful way to see a vast landscape of possibilities.
Final Thoughts
Mastering Scenario Manager, Goal Seek, and Data Tables moves you from simply recording data in Excel to actively using it for forecasting and strategic planning. Scenario Manager handles comparing distinct cases, Goal Seek works backward to find a specific input, and Data Tables efficiently show how one or two inputs impact your bottom line across a range of values.
While modeling in Excel is powerful, it often relies on manually exporting data, and building these analyses can be time-consuming. At Graphed we see these kinds of "what-if" questions every day in marketing and sales analytics. We designed our tool to automate this process. Rather than building models with stale data, you can connect live sources like Google Analytics, Shopify, and Salesforce and ask your questions in plain English — like, "Create a dashboard showing our projected conversions if website traffic increases by 15%." This turns hours of spreadsheet work into getting instant answers and real-time dashboards that always stay up to date.