Get help

Help center

Guides and answers

Contact support

Write to our team

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Finance

Turn manual finance work into reports that update themselves.

What Finance teams automate

  • Track payables and receivables
  • Build cash flow statements
  • Compare budget against actuals

Inventory and manufacturing

See stock levels and automate ordering.

What inventory and manufacturing teams automate

  • Track inventory across multiple sites
  • Connect sales, purchasing, and warehouse data
  • Link a barcode scanner to your sheet

HR

Run recruitment, attendance, and OKR tracking from your sheets.

What HR teams automate

  • Track recruitment and onboarding
  • Record team OKRs and results
  • Track check-ins, check-outs, and leave

Project management

Assign tasks and track hours without manual updates.

What project teams automate

  • Assign tasks and track status changes
  • Estimate effort and log hours worked
  • Track milestones across projects

Sales and CRM

Track leads and sales performance with automated reports.

What sales teams automate

  • Track and convert leads
  • Set sales goals and track team metrics
  • Monitor sales KPIs on dashboards

Education

Automate attendance, grades, and school purchasing.

What schools automate

  • Track attendance and notify parents
  • Record and compare class grades
  • Track inventory lent to students and staff

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Get started

Templates

Ready-made workflows to start from

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Help center

Guides and answers

Contact support

Write to our team

Workflows

Build processes with your team

Automations

Connect files and automate tasks

How to perform what-if analysis in Google Sheets

Google Sheets has no built-in what-if analysis tool like Excel. However, this doesn’t mean you can’t perform this type of analysis in Google Sheets. Using a free add-on like Goal Seek, you can carry out what-if analyses to explore and anticipate the consequences of possible future changes. If you are looking for the IF function instead, see this guide to IF functions in Google Sheets.

In this article, you will learn the difference between what-if and sensitivity analyses and how to use Google Sheets’ Goal Seek add-on to run what-if analyses on multiple scenarios. To learn more about the Goal Seek add-on, check out our article on how to use goal seek in Google Sheets.

What’s the difference between what-if and sensitivity analysis?

In what-if analysis, various situations or “scenarios” are contemplated through the manipulation of variables. Among others, there are two very popular types of what-if analysis: scenario and sensitivity analysis.

In sensitivity analysis, the focus is on the effects of changes to one specific variable; this type of analysis explains how variables (dependent) are affected based on changes made in another variable (independent). In scenario analysis, the focus is on the effects of changes to multiple variables; here, the different scenarios correspond to these changing variables. For example, a worst-case scenario could involve higher-than-average costs and minimum sales.

As you can see, both types of what-if analysis can be very valuable, allowing you to prepare for change or take calculated risks. In the next section, you will learn how to carry out what-if analyses in Google Sheets.

What-if analysis with Goal Seek in Google Sheets

By setting up different scenarios in your spreadsheet, you can use Goal Seek for what-if analysis. Whether you carry out sensitivity or scenario analysis will depend on how many variables you manipulate and to what extent you test individual ones.

Follow the steps below to install the Goal Seek add-on for Google Sheets, set up various scenarios, and use Goal Seek to modify multiple variables.

Install the Goal Seek add-on in Google Sheets

Like with most Google products and features, you simply need a Google account and a few minutes. Follow the steps below to find and install Goal Seek in Google Sheets.

1. Open Google Sheets and go to Extensions > Add-ons > Get add-ons.

What-if analysis in Google Sheets — Extensions menu open at Add-ons with Get add-ons

2. In the Google Workspace Marketplace, search for Goal Seek, click on the add-on, and then “Continue.”

Goal Seek add-on for Google Sheets — Google Workspace Marketplace search results for Goal Seek

3. You will see a pop-up asking you to grant Goal Seek permission to access your account. Click “Allow” to finish installing the add-on.

Goal Seek add-on for Google Sheets — permission request with the Cancel and Allow buttons

4. Once the add-on is successfully installed, you’ll see a confirmation message like the one below.

Goal Seek add-on for Google Sheets — Goal Seek has been installed message pointing to Add-ons

Set up what-if scenarios

In the example spreadsheet below, you can see three copies of the initial dataset. Here, the goal is to reach profits of $20000 and to analyze different scenarios.

In the first, you’ll see the effects of changing the selling price; in the second, the consequences of changing the number of units sold; and, in the third, the effects of changing the unit cost.

If you want to compare a base, a best, and a worst case side by side, our guide to scenario analysis shows how to set them up.

What-if scenarios in Google Sheets — initial data and three scenario copies with a profit goal of 20000

What-if analysis in Google Sheets with Goal Seek

You can use Goal Seek to carry out a what-if analysis of multiple scenarios by running it on multiple copies of your data, where you can set your goal and specify which variables to change.

Goal Seek required parameters

There are three required parameters in Goal Seek:

  • Target cell: This is the cell containing the formula that will link other cells together. Goal Seek will ensure that the result obtained from this formula corresponds to the value you set as your target.
  • Target value: This is the value we want to obtain in our target cell.
  • Changing cell: This is the cell that Goal Seek can manipulate to reach the target value in the target cell.

Goal Seek optional parameters

You can also configure some optional parameters in Goal Seek:

  • Maximum iterations: the default value is 200. You can increase this number, so that Goal Seek makes more attempts to find a solution, but this will also depend on the time limit you set.
  • Tolerance: the default value is 0.0001. You can increase this number if your solution doesn’t need to be this precise.
  • Maximum time limit: the default value is 120 seconds. You can increase this number for more complex calculations. Calculations will stop when this limit is reached, regardless of the “Max Iterations” setting.

1. Go to Extensions > Goal Seek > Open.

What-if analysis in Google Sheets — Extensions menu open at Goal Seek with Open

2. You will see the Goal Seek sidebar on the right of your spreadsheet, including “Settings”, “Solve Status”, and “History”.

What-if analysis in Google Sheets — empty Goal Seek sidebar with Settings, Solve Status and History

3. Click on the cell containing the profit formula, then click on the grid icon beside “Set Cell” in the sidebar. If you place your cursor over the grid icon, you will see the text “Capture selected cell”, as in the screenshot below.

What-if analysis in Google Sheets — profit cell B20 selected and Capture selected cell next to Set Cell

4. Next, type in the “Target value” for the “Set Cell”. Here, “20000”.

What-if analysis in Google Sheets — 20000 typed in the To Value box of the Goal Seek sidebar

5. Click on the cell containing the value you want Goal Seek to change, then click on the grid icon beside “By Changing Cell” in the sidebar. Here, the selling price for Scenario 1, cell B14.

What-if analysis in Google Sheets — selling price cell B14 captured as the By Changing Cell

6. To change the number of iterations, the tolerance, and/or the time limit, click on “Options”. Here, I will use the default values.

What-if analysis in Google Sheets — Options open with 200 iterations, 0.0001 tolerance and 120 seconds

7. Once everything is ready, click “Solve.” With the default options used above, it will take a maximum of two minutes to calculate the solution. If Goal Seek cannot find a solution, try increasing the tolerance or the time limit and number of iterations.

What-if analysis in Google Sheets — Set Cell, To Value and By Changing Cell filled in above the Solve button

8. When Goal Seek finds a solution, you will see a pop-up like the one below. You can see the solution directly in the cell or the “Solve Status” at the bottom of the sidebar.

What-if analysis in Google Sheets — Goal Seek Complete pop-up with the selling price at 5.41 and profit at 20000

9. Next, let’s configure the settings for Scenario 2. First, select the “Set Cell” and the “Target Value”. Here, the “Set Cell” contains the profit formula in cell E20, and the target value is shown in “To Value”, i.e. 20000.

What-if analysis in Google Sheets — Scenario 2 profit cell E20 set with 20000 in To Value

10. Once you have set both values, click on the cell with the changing value. Click “Solve” when you’re happy with the settings.

What-if analysis in Google Sheets — Scenario 2 units sold cell E15 captured as the changing cell

11. You will see the “Goal Seek Complete” pop-up again.

What-if analysis in Google Sheets — Goal Seek Complete pop-up for Scenario 2 with units sold changed

12. Finally, configure the settings for Scenario 3, following the same steps as before, and click “Solve” to finish.

What-if analysis in Google Sheets — Scenario 3 settings with profit cell H20 and unit cost cell H16

13. That’s it. The solution for Scenario 3 is available, as shown below.

What-if analysis in Google Sheets — Goal Seek Complete pop-up for Scenario 3 with unit cost at 0.88

14. You should see the results obtained with Goal Seek for all three scenarios; the changing cells have a green background, and the target cells have a red one.

What-if analysis in Google Sheets — three scenario results with changing cells in green and profit cells in red

Conclusion

You can perform what-if analysis directly in Google Sheets using Goal Seek. You can set up different scenarios with different goals and choose any variable you want as the changing cell. As Goal Seek keeps a history of attempts, you can always return to previous solutions.

After reading this article, you know how to install the Goal Seek add-on, set up scenarios in Sheets, and analyze them using Goal Seek. To learn more about Goal Seek, check out how to use goal seek in Google Sheets.

For statistical tools such as regression, try the XLMiner Analysis ToolPak add-on. To check your scenarios against real results, read how to find and analyze budget vs. actual variances, or start from a profit and loss statement template.

You may also like…

google sheets features and formulas

Top 5 dynamic array formulas in Google Sheets 

Google Sheets has evolved beyond basic spreadsheets. With the introduction of dynamic array formulas, users can now manipulate and analyze...
google sheets features and formulas

Mastering the FILTER Formula: 4 Use Cases with Examples

The FILTER formula in Google Sheets is a versatile tool for extracting data that meets specific conditions. Unlike the QUERY formula,...
google sheets features and formulas

Unlocking the Power of SUMIF and SUMIFS in Google Sheets: 4 real-life use cases

The SUMIF and SUMIFS formulas in Google Sheets are indispensable tools for performing conditional summations. They simplify complex...