Don’t forget to share this post

Goal seeking is a popular and powerful technique in data analysis, frequently used to perform break-even and what-if analyses. Google’s Goal Seek is a free add-on that allows you to use this technique directly in Google Sheets. It can be used for simple tasks - like figuring out how many items you need to sell to break even - or more complicated tasks, such as choosing between different investment opportunities.

In this article, you will learn how to install the Goal Seek add-on in Google Sheets and how to use it to perform a break-even analysis. Additionally, you will learn how to deal with common errors when using Goal Seek. 

What is Goal Seek?

Goal Seek is a free add-on for Google Sheets that allows you to carry out goal-seeking analysis directly within your spreadsheet. This will save you from spending hours using trial and error to guess at the answers. 

As the name suggests, goal-seeking techniques focus on obtaining the desired value in a target cell, by trying different inputs in other cells; this is a process you’ve probably attempted manually at some point. If there’s only one changing cell in a simple formula, you can do it relatively quickly through guesswork. Then again, why waste even a few minutes of your time if they can be saved?

If you want to perform what-if analysis, stay away from manual methods to save yourself from frustration and a guaranteed headache. Using Google’s Goal Seek, you can quickly and easily analyze multiple scenarios, so you can make informed decisions.

How to Track Changes in Google Sheets
How to Track Changes in Google Sheets?

There are several ways to track changes in Google Sheets to make collaboration in your team even easier. Here'show to track changes in Google Sheets.

READ MORE

How do I add Goal Seek in Google Sheets?

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

  1. 1. Open Google Sheets and go to Extension > Add-ons > Get add-ons.
How To Use Goal Seek in Google Sheets Get Add ons
How To Use Goal Seek in Google Sheets - Get Add-ons
  1. 2. In the Google Workspace Marketplace, search for Goal Seek, click on the add-on, and then “Continue”.
How To Use Goal Seek in Google Sheets Goal Seek Add on
How To Use Goal Seek in Google Sheets - Goal Seek Add-on
  1. 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.
How To Use Goal Seek in Google Sheets Allow Access
How To Use Goal Seek in Google Sheets - Allow Access
  1. 4. Once the add-on is successfully installed, you’ll see a confirmation message like the one below.
How To Use Goal Seek in Google Sheets Add on Installed
How To Use Goal Seek in Google Sheets - Add-on Installed

How to use Goal Seek in Google Sheets?

Now that you have installed the add-on, let’s see how easy it is to use Goal Seek in Sheets.

There are three required parameters that you must provide to 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.

In the example below, I’d like to use Goal Seek to figure out what the selling price for a new product should be. Since it is a new product, I want to figure out a price that will give me a profit of $0 so that I break even.

  1. 1. In your spreadsheet, go to Extensions > Goal Seek > Open.
How To Use Goal Seek in Google Sheets Open Goal Seek
How To Use Goal Seek in Google Sheets - Open Goal Seek
  1. 2. A Goal Seek “Settings” sidebar should now appear to the far right of your spreadsheet. As you can see, there are three fields for each parameter we should enter.
How To Use Goal Seek in Google Sheets Goal Seek Settings
How To Use Goal Seek in Google Sheets - Goal Seek Settings

Linking Google Sheets: How to Reference Another Sheet?

Sometimes you have to reference or merge data from multiple sheets or spreadsheets. Here's how to easily link multiple Google Sheets

READ MORE
Linking Google Sheets How to Reference Another Sheet
  1. 3. Enter the formula in the “Set Cell” field. The “To Value” should be set to “0”, since here I want to obtain the break even point. The “By Changing Cell” field, corresponds to the cell that will change once the “Target value” is found. Once you finish, click “Solve”.
How To Use Goal Seek in Google Sheets Goal Seek Parameters Solve
How To Use Goal Seek in Google Sheets - Goal Seek Parameters Solve
  1. 4. While it’s working, you can see the numbers changing in your spreadsheet. This process can take a few seconds or a few minutes, so make sure you don’t make changes while it’s working.
How To Use Goal Seek in Google Sheets Solving
How To Use Goal Seek in Google Sheets - Solving
  1. 5. Once Goal Seek has found a solution, you will see a pop-up informing you that the process is complete. On the lower half of the Goal Seek sidebar, you will see a summary of the attempt, including the number of iterations, the time it took to complete, and the solution itself.
How To Use Goal Seek in Google Sheets Solve Status
How To Use Goal Seek in Google Sheets - Solve Status

Now that you’ve seen how easy it is to use Goal Seek, you can quickly duplicate the dataset and check out different scenarios in the same spreadsheet. For example, you can reach the same goal - break even - by changing different cells, like the number of units sold. You can also perform more complex what-if analyses on larger and more complex datasets.

How to Troubleshoot a Goal Seek Error?

As with any tool or add-on, there’s a chance that you’ll come across an error at some point. If you do encounter an error, it is very likely to be related to one of the following problems:

  • The target cell does not contain a formula: Make sure you have set the right cell as the target, as it has to contain a formula, not a value. Goal Seek ensures that the result of this formula is the target value that you have set.
  • The target value is not numerical: Check that the target value is numerical. If you try to use a formula or non-numerical value, the add-on won’t work.
  • The formula does not use the changing cell: Select the right changing cell and ensure that it’s used in the formula. If it isn’t, Goal Seek can’t run.

For more support, you can search for specific Goal Seek issues using Google’s support page. Here, you can access help center articles and community posts to help you.

How to manage your Google Sheets with Layer?

Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.

Using Layer, you can:

  • Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
  • Review & Track: Consolidate input, and easily track changes.
  • Collaborate: Define, assign, and automate tasks and set deadlines.

Sign up for early access and start automating your Google Sheets workflows with Layer!

Conclusion

As you can see, using Google’s Goal Seek add-on for Google Sheets is a very easy, powerful tool to help calculate complex data scenarios directly within your spreadsheet. Whether you’re trying to figure out your personal expenses or deciding on an investment strategy for your company, Goal Seek will help you quickly and easily analyze different scenarios directly from your spreadsheet.

You now know how to install the Goal Seek add-on in Google Sheets and how to use it to help you make decisions, as well as how to avoid common problems. For more information, you can head to Google’s support page.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jun 27 2022, Updated Jun 28 2022