How to Use Goal Seek for What-If Analysis

By | 25th March 2015

Excel Training by Netresult Training

There are many occasions when you need to know the steps to take to reach a goal you already know. The goal could be an amount, of profit for example, or a percentage. A typical example might be “I need to make 45,00 profit this year. What do I need to change my price to in order to reach that figure assuming my sales volume remains the same?”

In this Excel training tutorial I will show you how a simple tool within Excel’s standard functionality allows you to calculate the value you will have to change to achieve the result you want.

What-If Analysis and Goal Seek

Goal Seek is part of the “What-If” analysis series in Excel and is really useful if you know the answer that you want, but need to work backwards to find the input value that gives you that answer. A kind of reverse engineering, if you like.

Goal Seek allows you to specify an answer and to tell Excel which cell to change in order to achieve this answer.  For example, Goal Seek is useful if you need to know how many products you need to sell before you will break even.

First click the Data tab

To use Goal Seek, go to the Data tab, click on the What‐if Analysis button and choose Goal Seek:

goal seek

The Goal Seek dialog box will be displayed

goal seek2

In the Set cell box, enter the reference or name of the cell containing the formula for which you want to find a specific solution, or click on the selector, and then click on the cell in your worksheet…

In the To value box, enter the value that you want the cell to be…

In the By changing cell box, enter the reference of the cell containing the value to be changed in solving for the result that you want, or click on the selector, and then click on the cell in your worksheet…

Click on the OK button.

A dialog box that shows the status of the goal seeking will be displayed

To pause while goal seeking, click on the Pause button

When Goal Seek has finished, Microsoft Excel displays the results on the worksheet. If the specific solution that you want cannot be reached, Microsoft Excel displays a message.

If Microsoft Excel reaches the solution that you want for your formula, click on the OK button to keep the solution values on the worksheet. To restore the original values, click on the Cancel button.

My Excel Training outlines are on this page

See more Microsoft Excel Training tutorials at Excel Tutorial

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.