Goal Seek in Google Sheets helps users determine the needed input value in a formula to achieve a specific outcome. Useful for scenarios where the desired result is known but the required initial value is not, Goal Seek reverse-calculates by modifying one cell's value until the dependent formula matches the desired result.
In this example, we will be using Goal Seek in Google Sheets to find the necessary sales amount per unit to achieve a desired profit goal. We'll use a simple dataset that calculates profit based on sales amount, cost per unit, and units sold and apply Goal Seek to determine the optimal sales price per unit.
With your Google Sheets open, navigate to the "Extensions" menu in the top bar. From here, select "Add-ons" and then "Get add-ons."
In the G Suite Marketplace dialog that appears, search for "Goal Seek" in the search bar. Find the Goal Seek add-on in the search results and click on it. Follow the prompts to install the add-on to your Google Sheets.
First, determine your specific profit goal, for example, $3000, using the formula in cell F2, which calculates profit as Profit = Revenue - Total Costs. You should know the target profit and where the formula that needs to meet this target is located.
After installing, to use Goal Seek, go to the top menu of Google Sheets, click on Extensions > Add-ons > View document add-ons.
Find "Goal Seek" within the document add-ons and select "Use." This step will bring up the Goal Seek dialog box on the right side of your screen, ready for input.
In the Goal Seek dialog, input three critical pieces of information: 'Set cell' to F2 where the profit formula is located, 'To value' to your profit goal of $3000, and 'By changing cell' to A2, the cell (Sales Amount) that Goal Seek will adjust to try reaching your profit goal. Click the "Solve" button within the Goal Seek dialog to initiate the process.
Goal Seek will now iterate, adjusting the Sales Amount in cell A2. It works towards making the profit calculated in cell F2 equal to your target of $3000.
After Goal Seek finishes, examine the adjusted Sales Amount in A2 to see if it aligns with achieving a $3000 profit in F2. If successful, the adjusted Sales Amount in A2 is what you need to achieve your profit goal.
If the first outcome doesn’t meet your expectations or you're curious about how different sales amounts affect profit, adjust your profit goal in the 'To value' field and/or change other variables. Run Goal Seek again to see how these adjustments impact the required sales amount.
We hope that you now have a better understanding of how to use Goal Seek in Google Sheets. If you enjoyed this article, you might also like our article on how to move cells in Google Sheets without overwriting or our article on the NPV formula in Google Sheets.
