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.
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:
- From the ‘Add-ons’ menu, select ‘Get add-ons’.
- This will open Google Workspace Marketplace, from where you can find the apps you need.
- In the search bar on top, type ‘Goal seek’.
- Select the Goal Seek App from the app options that appear.
- Click on the Install button.
- You will be asked for permission to install Goal Seek. Click Continue.
- You will then be asked to confirm if you want to allow Goal Seek to access your Google account. Click Allow.
- You should now see a message that says ‘Goal Seek has been installed’. Click Done.
- 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.
Opening the Goal Seek Window
To open the Goal Seek window from the add-on menu, follow the steps below:
- From the Add-ons menu, navigate to Add-ons -> Goal Seek -> Open.
- Wait for the Goal seek window to open.
- Once the app is loaded you should be able to see the Goal seek window as a sidebar panel of your Google Sheets window.
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.
To Value
Under ‘To Value’, type your target value (7000 in our case). Make sure you don’t include any currency signs or separators.
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.
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:
- Enter your required values in the ‘Set cell’, ‘To value’, and ‘By changing cell’ inputs.
- Click the “Solve” button.
- 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.
- On completion of the Goal Seek, you will get a notification informing you that the required Goal Seek is 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.
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.
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.
