Data Analysis [Part 4 of 5] – Goal Seek in Excel

This is the fourth article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use Goal Seek in Excel.

Download Example File

Watch Video – Goal Seek in Excel

Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.

For example, I have the data (as shown below), where I calculate the Monthly payment outflow with the given Loan Amount, Interest Rate and Number of Payments. [Monthly payment is in red and in round brackets as it is an outflow].

Goal Seek in Excel - Data Set

I use the following PMT formula in cell B4 to calculate the Monthly payment:

=PMT(B2/12,B3,B1)

Now, I want to calculate the loan amount so that my monthly payment is  $1,000. I can try and use some trial and error by putting random values in Loan amount, but there is a better way to do this – using Goal Seek in Excel.

Using Goal Seek in Excel

  • Go to Data –> Data Tools –> What-If Analysis –> Goal Seek.Goal Seek in Excel
  • In the Goal Seek Dialogue Box, use the following details:
    • Set Cell: B4 (this is the cell with the goal/target).
    • To Value: -1,000 (this is the goal value. Its negative here as it is an outflow).
    • By Changing Cell: B1 (this is the loan amount that we want to change to achieve the goal value).Goal Seek in Excel - Values
  • Click OK. This will open the Goal Seek Status dialogue box.
    • Goal Seek Status dialogue box will inform you when a solution is found. If you wish to accept the solution, click on the OK button, and it will change the cell values. If you do not want to accept the solution, click on Cancel.
    • It may happen that the Goal Seek is not able to find a solution. It will show you the relevant prompt in that case.Goal Seek in Excel - Status Dialogue Box
  • Once you accept the solution, it will be reflected in the cells.Goal Seek in Excel - New Solution

Try it yourself..Download the fileDownload File Pic

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

4 thoughts on “Data Analysis [Part 4 of 5] – Goal Seek in Excel”

  1. what if you enter a negative number in the [to:value] but it doesn’t work? how do I get negatives to work?

    Thank you 🙂

    Reply
  2. Bro it would really help if you explain us with few other examples coz I tried using you given formula but it ain’t working so..

    Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster