Ravindra BagaleCourses & study guides Track your progress

Guides

Goal Seek in Excel: Work Backwards to the Number You Need

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Shraddha Bagale runs an evening Excel batch in Pune. The fee is ₹4,500, each student costs ₹1,500 in material and lab time, and the hall and trainer cost ₹1,20,000 a batch. With 40 students she only breaks even. How many students does she need to make ₹60,000? Instead of trying 50, 55, 58... by hand, she asks Goal Seek.

Goal Seek (Data > What-If Analysis > Goal Seek) changes one input cell until a formula cell reaches the value you want. It is trial and error done by Excel. The formula must depend on the input cell, directly or through other formulas.

Download the practice file (goal-seek.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Build the model with formulas first (the profit cell must use the students cell).
  2. Click Data > What-If Analysis > Goal Seek.
  3. Set cell: the profit cell, B8.
  4. To value: 60000. By changing cell: the students cell, B5.
  5. Click OK. Excel puts 60 in B5. Click OK to keep it or Cancel to go back.

Example 1: students needed for a profit target

CellItemValue or formula
B2Fee per student₹4,500
B3Cost per student₹1,500
B4Fixed cost per batch₹1,20,000
B5Students (input)40
B6Revenue=B2*B5
B7Total cost=B4+B3*B5
B8Profit=B6-B7

With 40 students, profit is ₹0 (break-even). Goal Seek with Set cell B8, To value 60000, By changing B5 returns 60 students.

Check it by hand: each student adds 4,500 − 1,500 = ₹3,000. To cover ₹1,20,000 and earn ₹60,000 you need 1,80,000 ÷ 3,000 = 60. The practice file has this check formula: =(B10+B4)/(B2-B3).

Example 2: how big a loan an EMI allows

  1. On the Loan sheet, B2 holds the loan amount, B3 the yearly rate 9%, B4 the years 5.
  2. B5 holds the EMI: =PMT(B3/12,B4*12,-B2).
  3. Goal Seek: Set cell B5, To value 15000, By changing B2.
  4. Excel finds a loan of about ₹7,22,601.

Here a direct formula also exists: =PV(B3/12,B4*12,-15000). Goal Seek is useful when no simple reverse formula exists, for example when the model has many steps, slabs or IFs.

Things that trip people up

  • Set cell must contain a formula; By changing cell must contain a plain number, not a formula.
  • Goal Seek changes only one input. For several inputs or limits (like "students at most 70"), use Solver.
  • The answer may have decimals such as 59.9999. Round it, or set File > Options > Formulas > Maximum Change smaller.
  • Goal Seek overwrites the input. Note the old value or press Cancel to undo.

Ravindra Bagale's Tip

Run Goal Seek, write the answer in a separate "result" cell with a note, and then put the input back. The model stays as it was, and the meeting gets a clear sentence: 60 students needed for ₹60,000 profit.

Practice task

  1. Open the Batch sheet. Check B8 shows 0 with 40 students.
  2. Run Goal Seek: Set cell B8, To value 60000, By changing B5. Expect 60.
  3. Compare with the check formula in B11.
  4. Open the Loan sheet and Goal Seek the loan for an EMI of 15000.
  5. Compare with the PV check in B8.

Learn it properly

Course lessons:

Related guides: Excel Data Tables: See Many What-If Answers at Once · Scenario Manager in Excel: Compare Best, Likely and Worst Case · PMT, IPMT and PPMT: Calculate EMI and Build a Loan Schedule

Frequently asked questions

Where is Goal Seek in Excel?

Data > What-If Analysis > Goal Seek (in the Forecast group).

Why does Goal Seek say it may not have found a solution?

The target may be impossible, or the set cell does not depend on the changing cell. Check the formulas link them.

Can Goal Seek change two cells?

No. Use Solver (Data > Solver, after enabling the add-in) for several changing cells and limits.

Is Goal Seek available on Mac and the web?

Yes on Excel for Mac. Excel for the web has Goal Seek in current Microsoft 365 versions; older web versions did not.