How to Use What-If Analysis in Google Sheets

Changing one number in a spreadsheet to see its impact on your bottom line is one of the most powerful tools in business analysis. Known as what-if analysis, this process lets you see the future outcomes of different decisions without any real-world risk. This article will walk you through several practical methods for performing what-if analysis in Google Sheets, from simple manual adjustments to more automated scenario modeling.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is What-If Analysis?

What-if analysis is the process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. It’s a way to model different possibilities and understand the potential impact of your decisions. You’re essentially asking your data questions like:

• “What if our ad spend increases by 15%? How would that affect our customer acquisition cost?”
• “What if we decrease the price of our product by $5? How many more units do we need to sell to make the same profit?”
• “What if our website conversion rate improves from 2% to 3%? What would be the impact on our total revenue?”

By building a small model in Google Sheets, you can explore these questions and prepare for a range of potential outcomes, giving you the confidence to make smarter, data-driven decisions.

Technique 1: Simple Manual Adjustments for Quick Forecasts

The simplest form of what-if analysis doesn't require any special functions or add-ons. It's all about structuring your spreadsheet so you can easily change key variables and watch the results update instantly. Think of these variables as the "levers" you can pull.

Let's use a basic example of projecting revenue for a new product.

Step 1: Set Up Your Model and Define Inputs

First, create a clean structure. It's a best practice to keep your input variables separate from your calculations. This makes your model easy to understand and use.

Let's assume we want to calculate our monthly revenue. Our key inputs would be:

• Price per Unit
• Advertising Spend
• Cost per Website Visitor
• Website Conversion Rate

Set these up in a clearly labeled section of your sheet. Using a distinct background color for input cells is a great way to signal that these are the numbers you're intended to change.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Create Your Formulas

Now, build the formulas that will calculate your outcomes based on those inputs. For our example, the calculations would be:

1. Website Visitors: Advertising Spend / Cost per Visitor
2. Units Sold: Website Visitors * Website Conversion Rate
3. Total Revenue: Units Sold * Price per Unit

Your sheet might look something like this:

Inputs:

• Price per Unit: $50
• Advertising Spend: $2,000
• Cost per Visitor: $1.00
• Website Conversion Rate: 2%

Calculations:

• Website Visitors: =B1/B2
• Units Sold: =B3*B4
• Total Revenue: =B5*B1

Step 3: Ask "What If?" and Change a Variable

Now for the analysis. What if you could improve your website conversion rate from 2% to 3.5% through better landing page design?

Simply change the "Website Conversion Rate" input cell from 2% to 3.5%. Instantly, all your other formulas update:

• Units Sold becomes 70.
• Total Revenue becomes $3,500.

Just like that, you've quantified the value of improving your conversion rate. This simple manual method is fantastic for quick, back-of-the-napkin calculations that guide day-to-day decisions.

Technique 2: Create a Scenario Comparison Table

Manually changing one value at a time is useful, but what if you want to compare three different schemas side by side? For example, a "Worst Case," "Most Likely Case," and "Best Case" scenario for next quarter's sales. While Google Sheets lacks the one-click "Scenario Manager" found in Excel, you can easily build your own summary table.

Step 1: Keep Your Core Model

Use the same sales model from the previous technique. This will act as our "live" calculator.

Step 2: Build a Scenario Table

Somewhere else on your sheet, create a new table to hold your results. Create a row for each scenario you want to test. Your columns should include the scenario name, the input variables you plan to change, and the key outputs you want to measure.

Here’s an example structure:

• Column A: Scenario Name (e.g., "Worst Case", "Most Likely", "Best Case")
• Column B: Advertising Spend (The input we'll change)
• Column C: Website Conversion Rate (Another input we'll change)
• Column D: Total Revenue (The key output we'll record)

Step 3: Populate Your Scenario Table Manually

This part is a simple copy-and-paste job. For each scenario:

1. Enter the variables for that scenario into your main model. For your "Worst Case" scenario, you might plug in $1,500 for Advertising Spend and 1.5% for Conversion Rate.
2. Note the "Total Revenue" your model calculates.
3. Copy and paste that Total Revenue outcome into the corresponding row of your scenario table.

Repeat for your "Most Likely" and "Best Case" scenarios. When you're done, you'll have a clear, static table that summarizes every possible path—ideal info to add to a presentation or report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Technique 3: Build a Dynamic Scenario Selector with a Drop-Down Menu

If you want a more interactive model, you can use a drop-down list to switch between predefined scenarios dynamically. Others—like team members or clients—can then explore the possibilities without having to edit formulas.

Step 1: Organize Your Scenarios

First, create a structured table of your different scenarios and their corresponding input values. This differs from the previous method because this table will feed your model, not just summarize its results.

Let's map out three scenarios—Low Growth, Moderate Growth, and High Growth—and define two variables for each: Monthly New Users and Conversion Rate.

Your table might appear similar to:

• Cell A1: Scenarios, Cell B1: Monthly New Users, Cell C1: Conversion Rate
• Cell A2: Low Growth, Cell B2: 500, Cell C2: 1.5%
• Cell A3: Moderate Growth, Cell B3: 1,000, Cell C3: 2.0%
• Cell A4: High Growth, Cell B4: 1,800, Cell C4: 2.5%

Step 2: Create a Drop-Down Menu

Pick a cell where you will choose your active scenario. Let’s say cell F2.

1. Select cell F2.
2. Go to Data > Data validation.
3. In the "Criteria" dropdown, choose "List from a range."
4. Click the grid icon and select your scenario names (cells A2:A4 in our table).
5. Click "Save." You now have a drop-down menu in cell F2 with your three scenarios.

Step 3: Use VLOOKUP to Pull in Data

Now, you need your core financial model to grab the correct values based on what you select in the drop-down. The VLOOKUP function is perfect for this.

In your model's input cells, instead of hard-coded numbers, you’ll use a formula.

• To get the Monthly New Users value:

=VLOOKUP($F$2, $A$2:$C$4, 2, FALSE)

This formula looks for the value placed in cell $F$2 (the chosen scenario name) within your scenario table ($A$2:$C$4), and returns the result from the second column.

• For the Conversion Rate:

=VLOOKUP($F$2, $A$2:$C$4, 3, FALSE)

This is the same logic as above but returns the result from the third column (the conversion rate).

Now, whenever you select a new scenario from your drop-down list, your entire model will dynamically update. This technique is fantastic for creating interactive dashboards for your team to use.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Technique 4: Work Backwards with the Goal Seek Add-on

Sometimes, your what-if question is reversed. You already know the outcome you want to achieve, but you need to figure out what input is required to get there. For instance: "Exactly how many units must we sell to achieve a break-even point?" This is where Goal Seek comes in handy.

While Excel includes a built-in Goal Seek function, Google Sheets requires a free add-on to do the same task. The most popular one is called "Goal Seek for Sheets."

Step 1: Install "Goal Seek for Sheets"

1. In your Google Sheet, navigate to Extensions > Add-ons > Get add-ons.
2. Search for "Goal Seek" and install the official add-on offered by Google Cloud.
3. You may need to grant it permission to access your sheets.

Step 2: Set Up Your Break-Even Model

Goal Seek needs a formula cell to target. Let's create a simple break-even analysis model.

Inputs:

• Units Sold (this is the cell we will have Goal Seek change)
• Price per Unit: $75
• Cost per Unit: $40
• Fixed Costs: $50,000

Calculations:

• Total Revenue: Units Sold * Price per Unit
• Total Variable Costs: Units Sold * Cost per Unit
• Profit / Loss: Total Revenue - Total Variable Costs - Fixed Costs

Step 3: Run Goal Seek

Let's say a profit/loss sum currently displays a negative number, and we need to determine our break-even target (a profit/loss of zero).

1. Go to Extensions > Goal Seek > Open. A sidebar will appear on your right.
2. Set cell: Select the cell with your Profit/Loss formula.
3. To value: Enter 0. (Break-even means zero profit/loss).
4. By changing cell: Select the cell for "Units Sold".
5. Click "Solve".

Goal Seek will now run hundreds—or even thousands—of calculations automatically, adjusting the "Units Sold" until your profit/loss becomes exactly $0. This saves you several grueling hours you might have previously spent tinkering with your inputs to get the answers you were hoping for.

Final Thoughts

From manually adjusting inputs to building dynamic scenario selectors and working backwards with Goal Seek, Google Sheets offers surprisingly robust options for what-if analysis. Mastering these techniques empowers you to move beyond simply reporting on what has happened and start building reliable forecasts for what could happen, turning your spreadsheets into powerful decision-making engines.

While these methods are powerful, setting them up still requires manually gathering data from various sources and structuring them correctly in your sheet. That's why we built Graphed . We wanted to eliminate the painstaking process of exporting CSVs and wrestling with VLOOKUPs. Once your marketing and sales data sources like Google Analytics, Shopify, or Facebook Ads are connected, you can simply ask for the scenarios you want to see - "Show me a forecast comparing revenue if we increase ad spend by 10% vs. 20%" - and get a live, interactive dashboard in seconds, not hours.