Goal Seek is part of the What-If Analysis group built into Excel. It works with values you insert and a formula you enter to calculate those values. This means that a formula is necessary to use Goal Seek.

The tool is especially useful for the situations mentioned above such as savings, investments, and loans, but can be used in other scenarios as well. Once you obtain the results from the Goal Seek tool, you can simply view them or pop them into your sheet.

For a simple way to get familiar with the tool, we’ll start with a basic example. We want to determine how many units of our product we need to sell to reach our goal. So, we have the current Quantity, Unit Price, and Total Sales as shown below.

The Total Sales in cell B3 is a formula that multiples the Quantity by the Unit Price: =B1*B2.

• We want to find out how many units we must sell to reach our goal of $20,000 in total sales. For this, Goal Seek is ideal.
• Go to the Data tab, click the What-If Analysis drop-down arrow, and select “Goal Seek.”

In the small Goal Seek box, insert the following:

• Set Cell: Enter the cell reference containing the formula you want to change. Remember, changing the formula shows us the input we need to reach our goal.
• To Value: Enter the result you want. For us, this is 20,000.
• By Changing Cell: Enter the reference for cell you want to adjust. For us, this is B1 for the Quantity.

When you finish, click “OK.” The Goal Seek Status box shows a solution was found and you’ll see a preview in your spreadsheet.

As you can see, we now know that we must sell 800 units of our product to reach our $20,000 goal. Click “OK” to apply the change to your sheet or “Cancel” to simply close the Goal Seek Status window.

Another good way to use Goal Seek in Excel is for help with loans. You may have the loan amount, term in months, and payment you can afford, but want to know what interest rate you should seek.

Here we have the Loan Amount, Term, and Payment fields filled in. (You can ignore the payment amount displayed for now since the formula still needs the interest rate.) We have the formula for the PMT (Payment) function in cell B4: =PMT(B3/12,B2,B1).

Select Data > What-If Analysis > Goal Seek and insert the following:

Set Cell: Enter the cell reference containing the formula you want to change. For us, this is B4.

To Value: Enter the result you want. For us, this is -800 because we can afford $800 monthly.

By Changing Cell: Enter the reference for cell you want to adjust. For us, this is B3 for the Interest Rate.

Note: Excel uses a negative number for the payment when using the PMT function.

Click “OK” to see the results. It looks like we’ll try for a 4.77% annual interest rate for our loan.

Select “OK” to apply the change to your sheet or “Cancel” to close the box.