Excel Goal Seek and Solver
Goal Seek and Solver are Excel's what-if tools that work in reverse. Instead of asking "what is the result if I change this input?", they ask "what input do I need to get the result I want?" Both tools are found under the Data tab in the Forecast and Analysis sections.
What Is Goal Seek?
Goal Seek changes one input cell to make a formula cell reach a specific target value. It adjusts a single variable until the formula produces exactly the number you specify.
Real-World Example: Loan Payment
You want your monthly loan payment to be exactly ₹15,000. You already have a formula calculating the payment. Goal Seek finds what loan amount or interest rate makes that payment equal to ₹15,000.
Setup: B1: Interest rate → 8% B2: Loan term → 60 months B3: Loan amount → 700,000 B4: Monthly EMI → =PMT(B1/12, B2, -B3) → ₹14,188 (current result) Goal: Make B4 = ₹15,000 Variable to change: B3 (Loan amount)
How to Use Goal Seek
Go to Data → What-If Analysis → Goal Seek. Fill in three fields:
| Field | What to Enter | In the Example |
|---|---|---|
| Set cell | The formula cell with the result | B4 (the EMI formula) |
| To value | The target result you want | 15000 |
| By changing cell | The single input cell to adjust | B3 (loan amount) |
Click OK. Excel iterates through values and finds the loan amount that produces exactly ₹15,000 per month. Click OK again to keep the new value, or Cancel to restore the original.
Goal Seek Diagram
BEFORE Goal Seek: AFTER Goal Seek: +------------------+ +------------------+ | Loan: 700,000 | | Loan: 740,518 | ← adjusted | Rate: 8% | | Rate: 8% | | Term: 60 mo | | Term: 60 mo | | EMI: ₹14,188 | | EMI: ₹15,000 | ← target reached +------------------+ +------------------+
More Goal Seek Use Cases
| Question | Set Cell | To Value | By Changing Cell |
|---|---|---|---|
| What price gives 20% profit margin? | Margin formula | 20% | Selling price |
| What score is needed to pass? | Average formula | 60 | Last exam score |
| What sales volume breaks even? | Profit formula | 0 | Units sold |
Goal Seek Limitations
Goal Seek changes only one input cell. The formula cell must depend on that input cell. If the formula does not connect to the changing cell, Goal Seek cannot work. For problems with multiple variables or constraints, use Solver instead.
What Is Solver?
Solver is a more powerful optimisation tool. It can change multiple input cells simultaneously, respect constraints (rules the solution must follow), and find either the maximum, minimum, or a specific target value.
Enabling Solver
Solver is an Excel Add-in and is not active by default. Go to File → Options → Add-ins. At the bottom, set "Manage" to Excel Add-ins and click Go. Check the box next to Solver Add-in and click OK. Solver now appears under Data → Solver.
Real-World Example: Maximise Profit
A shop sells two products: Product A (profit ₹200 each) and Product B (profit ₹350 each). The shop can produce a maximum of 100 units total per day. Product A requires 1 hour of machine time and Product B requires 2 hours. Total machine time available is 150 hours per day. Find how many of each product to make to maximise daily profit.
Setup: B2: Units of Product A → (Solver will change this) B3: Units of Product B → (Solver will change this) B4: Total units → =B2+B3 B5: Machine hours used → =B2*1 + B3*2 B6: Total profit → =B2*200 + B3*350
Solver Parameters
| Solver Field | What to Enter | In the Example |
|---|---|---|
| Set Objective | The cell to optimise | B6 (Total profit) |
| To | Max, Min, or Value of | Max |
| By Changing Cells | The input cells Solver adjusts | B2:B3 |
| Subject to Constraints | Rules the solution must obey | B4 ≤ 100, B5 ≤ 150, B2 ≥ 0, B3 ≥ 0 |
Click Solve. Solver finds that making 50 units of Product A and 50 units of Product B gives the maximum profit within the constraints.
Solver Constraint Diagram
CONSTRAINTS: ┌──────────────────────────────────────────┐ │ Total units (B2 + B3) ≤ 100 │ │ Machine hours (B2 + 2×B3) ≤ 150 │ │ Units of A (B2) ≥ 0 │ │ Units of B (B3) ≥ 0 │ └──────────────────────────────────────────┘ SOLVER FINDS: B2 = 50 units of A B3 = 50 units of B Profit = 50×200 + 50×350 = ₹27,500 (maximum possible)
Solver Result Options
| Option | What It Does |
|---|---|
| Keep Solver Solution | Applies the optimised values to your cells |
| Restore Original Values | Puts back the values you had before Solver ran |
| Save Scenario | Saves the solution as a named scenario to compare later |
Goal Seek vs Solver Comparison
| Feature | Goal Seek | Solver |
|---|---|---|
| Number of changing cells | One only | Multiple (up to 200) |
| Constraints | None | Yes — set rules the solution must follow |
| Objective | Reach a specific value | Maximise, minimise, or reach a specific value |
| Speed | Instant | Seconds to minutes depending on complexity |
| Setup required | Minimal | More setup — add-in required |
| Best for | Simple single-variable problems | Multi-variable optimisation problems |
Goal Seek answers quick questions like "what price do I need?" in seconds. Solver tackles complex planning problems — production schedules, budget allocations, staffing rosters — where multiple factors interact under real-world limits. Together, these two tools turn Excel into a practical decision-support system.