The Goal Seek feature lets you determine the input value in a formula that results in a particular target value. With Goal Seek, you can perform what-if analysis in worksheets, a process in which altering the cell values affect the outcome of the worksheet formulas. For example, consider a scenario where net revenue is analyzed using the number of units sold, retail price per unit, and selling discount. The Goal Seek dialog makes it easy to estimate the number of units sold to reach a particular revenue value.

Let's say, we want to calculate the net revenue using the number of units sold, the retail price of each unit, and the selling discount.

The following image depicts the current figures:

Suppose we want to estimate the number of units sold to reach a revenue of $20,000. For this, we can call the Goal Seek dialog.

To invoke the Goal Seek dialog, follow these steps:

1. Place the cursor on the cell that contains the output you want to change. Here, cell E6.

   

2. From the Data tab of the ribbon, click What-if Analysis, then select Goal Seek dialog option.

   

3. Add the following entries in the Goal Seek Dialog:

   

   • Set cell: The cell which takes the output value that you want to achieve. Here, E6. This cell should have a formula which calculates the value.
   • To value: The output value you want to achieve in numbers. Here, $20,000. The assigned numerical value in this case should be suitable to run the Goal Seek feature, so that it produces a possible result. For example, while trying to compute a target interest rate for a loan payment calculation using the PMT function, we should not specify a 'To Value' less than the loan value as it will give a negative interest rate.
   • By changing cell: The cell that holds the input value that results in the output value. Here, E3. This cell should be a dependent cell of the Set cell formula. That is, it should be a precedent cell for the formula, so that changing its value affects the calculation in the 'Set cell'.

4. After evaluating, the title of the dialog gets changed to Goal Seek Status.

   

5. Click OK to apply the new values in the sheet.

   

You can also invoke the Goal Seek dialog at runtime using the GoalSeek method of the BuiltInDialogs class. To learn more about dialogs in Spread, see Working with Built In Dialogs topic.

Keyboard Shortcut

 The keyboard shortcut to Goal Seek dialog is Alt + A + W + G.