How to use Goal Seek function in Microsoft Excel

In Excel, we have so many analytical tools, formulas, options, and Goal seek is one of them. Goal seek option allows user to change the value in one cell until the value in a second cell reaches the desire number.

Goal is a part of What-If analysis. You will find the Goal seek option under the Data tab > in the Data tools section >under the What-If analysis.

Goal Seek have 3 options: – 1st Set Cell, 2nd To Value & 3rdand By changing Cell:-

Set Cell:- Here, we enter the cell address of the cell whose value we want to fix and the cell must contain formula; otherwise it will not be linked to cell. We will change it to obtain zero profit.

To Value: - Here, we enter the suitable value which we want to see in “Set Cell”.

By changing cell: -This cell should not contain formulas or function, and here we enter the cell which we want; Goal seek changes to obtain the zero profit.

Let’s take examples to understand the use of Goal seek:-

1st Example:-

We have product’s data, and we have details of product revenue, contribution %age and Contribution amount. Now we want to return how much revenue is required to achieve the target.

• Go to Data Tab > Data tools > What-If analysis > Goal Seek.
• First, we enter the value which we want to set to change till the desired target.
• Select the cell \$F\$8.

• In To value, we enter our target value.

• And then we enter the cell address on the basis of which our whole calculation depends. We enter \$D\$8.

• Click on ok.

2ndExample:-

We have profit summary of cosmetic products, and in this summary, we want to increase total profit amount till 15000 by changing the lower price, highest price, % sold for the highest price.

• Go to Data Tab > Data tools > What-If analysis > Goal Seek.
• First we enter the value which we want to set to change till the desired target.
• Select the cell \$D\$12.
• Enter the value profit value 15000 in “To Value”box.
• Enter \$D\$4 in “By changing cell”.

• Click on Ok.

3rdExample:-

We have detail of loan amount, interest rate, No. of year and EMI value. We want to return the interest rate for EMI value 17000.

• Go to Data Tab > Data tools > What-If analysis > Goal Seek.
• First we enter the value which we want to set for change till the desired target.
• Select the cell \$D\$9.
• Enter the EMI value 17000 in “To Value” box.
• Enter \$D\$4 in “By changing cell”.

• Click on ok.

In this way, we can use Goal seek option in Microsoft Excel for analysis.