What-If Analysis in Google Sheets Using Goal Seek

When doing data analysis, there may be times when we know what we want as the result, but don’t really know what input values will help us get there.

Imagine a situation where you have a formula plugged in to calculate annual sales.

You might have a target amount of sales that you want to reach but are not really sure about how many units you will need to produce or sell in order to reach the target.

One option might be to randomly try out different values for the number of units until you get the desired target sales (a brute-force method that can be quite tiresome).

Another option might be to reverse the formula for annual sales, making the units produced the unknown value. If your annual sales formula is rather complex then good luck with that!

A more ideal option would be to use the ‘Goal Seek’ option of Google Sheets.

In this tutorial, we will explain what Goal Seek does, how it works and with an example, we will show you how to install it as a Google Sheets add-on and how to use it.

What is Goal Seek?

Goal seek is a powerful data analysis utility provided by Google Sheets.

It is basically an algorithm that lets you feed the output you want to achieve and then gives you the value of a given input variable that will help provide the given output.

The Goal Seek algorithm essentially performs a series of what-if calculations by gradually plugging in different input values until you get your required output.

For example, a common application of the algorithm is to find out how many units of sales are required to break even, given other fixed variables, like production cost, profit, etc.

A  Goal Seek algorithm consists of three components:

  • An unknown input variable
  • A known output value
  • A formula involving the unknown input to get to the known output

How to Use Goal-Seek in Google Sheets

For simplicity and to help you understand the main idea behind goal seek, let us consider a very simple example where we have the unit prices of a few products, along with the number of units sold.

The last column consists of the total sales of each product, while cell D7 contains the Grand total of all the sales.

Data for Goal Seek

Let us say I want to change the Grand total from $5,728.38 to $7,000 using Goal Seek, and I want to know how many units of Lamps will help me get that.

Let us see step-by-step what you need to do in order to set up and use Goal Seek to solve this problem.

Adding the Goal Seek Add-on to Google Sheets

Unfortunately, the Goal Seek function does not come as a menu item of Google Sheets by default. However, it is available as an official add-on, and you can install it for free from the Google Workspace Marketplace.

Here’s how you can add the Goal seek add-on to your Google Sheets menu:

  1. From the ‘Add-ons’ menu, select ‘Get add-ons’.Click on Get Add-ons
  2. This will open Google Workspace Marketplace, from where you can find the apps you need.
  3. In the search bar on top, type ‘Goal seek’.Search for Goal Seek
  4. Select the Goal Seek App from the app options that appear.Select the Goal Seek for Sheets add-on
  5. Click on the Install button.Install the Add-on to Google Sheets
  6. You will be asked for permission to install Goal Seek. Click Continue.Allow the permission to the add-on
  7. You will then be asked to confirm if you want to allow Goal Seek to access your Google account. Click Allow.Click on the Allow button
  8. You should now see a message that says ‘Goal Seek has been installed’. Click Done.Click on Done
  9. Close the Google Workspace Marketplace window.Close the Google Workspace Marketplace window

You should now see the Goal Seek add-on added as a menu item under the ‘add-ons’ menu.

Note: You only need to follow steps 1 to 5 the first time. Once you’ve installed the add-on, it will remain under the Add-ons menu of Google Sheets, so the next time you want to use Goal seek, all you need to do is select the Add-on from the menu, and this will open the Goal seek window for you.

Opening the Goal Seek Window

To open the Goal Seek window from the add-on menu, follow the steps below:

  1. From the Add-ons menu, navigate to Add-ons -> Goal Seek -> Open.Open the Goal Seek Add-on
  2. Wait for the Goal seek window to open.
  3. Once the app is loaded you should be able to see the Goal seek window as a sidebar panel of your Google Sheets window.Goal seek window as a sidebar panel

Setting up the Goal Seek Options

After your Goal Seek window opens, you can simply plug in your required values and let it work its magic.

For our particular sample problem, here are the settings you can enter:

Set Cell

There are two ways to provide this input. One way is to directly type the cell reference of the cell containing the known output value (D7).

Another way is to select cell D7 and then click on the ‘Capture selected cell’ button under the ‘Set Cell’ field of the Goal Seek pane.

Set the cell for Goal Seek

To Value

Under ‘To Value’, type your target value (7000 in our case). Make sure you don’t include any currency signs or separators.

Set the To Value

By Changing Cell

Here, you need to provide the cell reference for the cell containing the unknown value (Number of lampshade units that need to be sold, which is cell B2).

Similar to the Set cell value, you can provide this reference in two ways. One way is to directly type the cell reference (B2).

Another way is to select cell B2 and then click on the ‘Capture selected cell’ button under the ‘By changing cell’ field of the Goal Seek pane.

Specify the By Changing cell

Note: It is important to ensure that the cell reference entered under the ‘Set cell’ field is dependent on the reference entered under the ‘By changing cell’ field.

Once you know what you want to enter in the input settings of Goal Seek, you can continue with the following steps:

  1. Enter your required values in the ‘Set cell’, ‘To value’, and ‘By changing cell’ inputs.
  2. Click the “Solve” button.Click on the Solve button
  3. Google Sheets will do the required calculation and change the value in both cells B2 and D7 until it arrives at your target value in cell D7.
  4. On completion of the Goal Seek, you will get a notification informing you that the required Goal Seek is completed.Goal Seek completed

At this point, you can also scroll down to the bottom of the Goal Seek panel to see the “Solve Status”. Here you will find all the input values and other information of interest like Goal Seek start time, running time, etc.

Solve Status

Note: You can easily undo the change made by the Goal Seek app by pressing the CTRL+Z shortcut on your keyboard.

In this tutorial, we showed you how to use Goal Seek in Google sheets to perform powerful what-if analysis on your data. We hope you found the explanation useful.

Spreadsheet Expert at Productivity Spot | + posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

Popular Posts