Goal Seek is part of the Data menu what-if analysis tools in Excel. But you won’t find the Goal Seek in Google Sheets under any menu items. Because it’s only available as an official Google Sheets add-on. Install the add-on to use it as a menu item in Docs Sheets.
I will give you the link to install the official (yes, it’s from Google) Goal Seek add-on in Google Sheets. Before that let me introduce, for those who are unfamiliar with it, the purpose of Goal Seek in Google Sheets.
Purpose:
Use Goal Seek to tune/get the result you need from a formula by finding the missing value or modifying the existing value.
In certain cases, we know the result that we want from a formula like the total invoice value (in case you set a target to invoice) but don’t know how to get that required value by changing the quantity or rate.
For example, let’s consider the below-unformatted invoice.
| A | B | C | D | |
| 1 | Description of Goods | Qty (MT) | Unit Price | Amount (USD) |
| 2 | Dolomite in Bulk, Size 40-80 mm | 30050 | 12 | $360,600.00 |
| 3 | Dolomite in Bulk, Size 0-6 mm | 7500 | 9 | $67,500.00 |
| 4 | Dispatch Earned | $7,000.00 | ||
| 5 | Gross Amount | $435,100.00 |
In this invoice, I want to reduce the Gross Amount to the tune of $430,000.00 (in cell D5) by changing the quantity (in cell B2) of the item ‘Dolomite in Bulk, Size 40-80 mm’.
You can easily do that using the Goal Seek in Google Sheets. It’s similar to the Goal Seek under the What-If Analysis in Excel but maybe a little slow if you go for performance comparison.
How to Get Excel Similar Goal Seek in Google Sheets
I have already made it clear that to get the Goal Seek, you need to install an official Google add-on for Sheets.
You can install the Goal Seek plugin two ways in Google Sheets.
- Within your Sheet, go to the menu ‘Add-ons’ and click ‘Get add-ons’. In the opening window, you can search the term ‘Goal Seek’ in the search field to find the add-on.
- From G Suite Marketplace. Link here.
Marketplace Add-on Installation
Here is the step by step instructions to install the Goal Seek add-on in Sheets. I am installing the plugin from withing Google Sheets (option 1 above).
Steps:
- Go to the menu ‘Add-ons’ in Google Sheets.
- Click on ‘Get add-ons’.
- On the window (G Suite Marketplace) that opens, search ‘Goal Seek’.
- Click on the correct plugin and follow the simple onscreen instructions to install it.
Goal Seek Official Sheets Add-on Settings and Usage Tips
Once installed any add-on in Sheets, it resides as a menu item under the ‘Add-ons’ menu. The same happens here also.
To open the Goal Seek window, first, go to the menu Add-ons > Document add-ons. Click on the “Use” button.
Note: In my case, the above action didn’t complete 🙁 I restarted my Sheets and simply followed the below steps.
Then go to Add-on > Goal Seek > open the Goal Seek window (actually a sidebar panel) in Google Sheets.
Experimenting with Sample Data
See the table given a few paras above. I am using that data for my Goal Seek experiment in Sheets.
As I’ve mentioned earlier I want to change the value in cell D5 from $435,100.00 to $430,000.00 using Goal Seek. Here are the required settings.
Set Cell
Click on cell D5 and under Goal Seek settings either type cell D5 against “Set Cell” or click on the “Select capture cell button” (the small square box) to automatically capture the cell reference (you can refer to the screenshot given at the end part of this post).
To Value
Then under “To Value” type the value of $430,000.00 without currency sign and thousand separators.
By Changing Cell
This is the most important part. It means by changing which cell, you want to change the D5 value to your required output. I am referring to cell B2.
Here you should take care of one thing. The cell D5 must dependent on cell B2. That means if you manually change the value in cell B2, it should affect the formula output in cell D5.
Click the “Solve” button. Google Sheets will do the required calculation and change the value in both the cells B2 and D5.
Upon completion “Goal Seek” will notify you that the required Goal Seek is completed.
Don’t forget to scroll down to the bottom of the Goal Seek panel to see the “Solve Status”. You will see all the input values and other pieces of information like Goal Seek start time, running time, etc.
I hope you will like this useful Google Sheets add-on.
