How to Create a Solver in Excel

The first time I used Solver, I was trying to figure out the ideal product mix for a small manufacturing budget — how many units of each product to produce to maximize profit without exceeding raw material limits. I had been doing it by trial and error, tweaking numbers for an hour with no real strategy. Then someone told me about Solver, and within five minutes it found the optimal answer I’d been hunting for manually. That’s the power of this tool, and in this guide, I’ll show you exactly how to set it up.

What Is Solver in Excel?

Solver is an Excel add-in used for optimization problems. It finds the best possible value for a target cell (called the “objective”) by adjusting one or more variable cells, while respecting constraints you define. It’s essentially a decision-making engine built right into your spreadsheet — perfect for scenarios like maximizing profit, minimizing cost, or finding the optimal allocation of limited resources.

Unlike Goal Seek, which only adjusts one variable to hit one specific target, Solver can handle multiple variables and multiple constraints simultaneously, making it far more powerful for complex, real-world problems.

Why Use Solver?

• It solves complex optimization problems that would be extremely tedious to do manually.
• It handles multiple variables and multiple constraints at once.
• It’s useful in finance, operations, logistics, staffing, and production planning.
• It removes guesswork from decisions involving limited resources.
• It’s built into Excel for free (as an add-in), so no extra software is needed.

Required Data Before You Start

Before opening Solver, you need a working spreadsheet model that includes:

1. An objective cell – a formula that calculates the value you want to maximize, minimize, or set to a specific value (e.g., total profit).
2. Variable cells – the inputs Solver is allowed to change (e.g., number of units to produce for each product).
3. Constraint cells – formulas representing limits (e.g., total material used must not exceed available stock).

Without a proper formula-based model connecting these pieces, Solver has nothing to work with — it doesn’t guess values from scratch, it manipulates the variables that feed into your existing formulas.

Step-by-Step: How to Enable and Use Solver

Step 1: Enable the Solver Add-In

Solver isn’t turned on by default. To enable it:

1. Go to File > Options > Add-ins.
2. At the bottom, in the “Manage” dropdown, select Excel Add-ins and click Go.
3. Check the box next to Solver Add-in and click OK.
4. You’ll now find Solver under the Data tab, in the Analyze group.

Step 2: Build Your Model

Set up your spreadsheet with:

• A cell for each variable you want Solver to adjust (for example, B2 and B3 for “Units of Product A” and “Units of Product B”).
• A formula cell for your objective (for example, =B2*ProfitA + B3*ProfitB in cell B5, representing total profit).
• Formula cells representing your constraints (for example, total material used based on units produced).

Step 3: Open Solver

Click Data > Solver. A dialog box appears.

Step 4: Set the Objective

In “Set Objective,” select your profit (or cost) cell. Choose whether you want to Max, Min, or set it to a specific Value Of.

Step 5: Define the Variable Cells

In “By Changing Variable Cells,” select the cells Solver is allowed to adjust (like B2:B3).

Step 6: Add Constraints

Click Add to define limits. For example:

• Total material used (cell B6) <= available material (cell C6).
• Units produced (B2:B3) >= 0 (to prevent negative production).

Repeat for each constraint your scenario requires.

Step 7: Choose a Solving Method

At the bottom of the dialog, pick a solving method:

• Simplex LP – for linear problems (most common for business optimization).
• GRG Nonlinear – for nonlinear relationships.
• Evolutionary – for complex, non-smooth problems.

Step 8: Click Solve

Excel will run calculations and display a “Solver Results” dialog. Choose Keep Solver Solution to apply the optimal values directly into your spreadsheet, or Restore Original Values to discard them.

Step 9: Save the Solver Model (Optional)

If you want to reuse the same setup later, click Save Scenario before closing, so you don’t have to rebuild the constraints each time.

Practical Example

Imagine a bakery producing cakes and cookies. Each cake earns $15 profit and uses 3 units of flour and 2 units of sugar. Each batch of cookies earns $8 profit and uses 1 unit of flour and 1 unit of sugar. You have 60 units of flour and 40 units of sugar available.

• Objective: Maximize 15*Cakes + 8*Cookies
• Constraint 1: 3*Cakes + 1*Cookies <= 60 (flour)
• Constraint 2: 2*Cakes + 1*Cookies <= 40 (sugar)
• Constraint 3: Cakes, Cookies >= 0

Set this up in Excel with Cakes and Cookies as variable cells, and Solver will instantly calculate the exact combination that maximizes profit while staying within your ingredient limits.

Common Mistakes to Avoid

1. Forgetting non-negativity constraints – without a >= 0 constraint, Solver might suggest producing a negative quantity, which is meaningless in the real world.
2. Using the wrong solving method – applying Simplex LP to a nonlinear model (or vice versa) can produce incorrect or no results. Match the method to your model type.
3. Incomplete constraints – leaving out a real-world limit means Solver might produce an “optimal” answer that’s actually impossible to execute.
4. Circular references – if your objective cell formula accidentally references itself through the variable cells incorrectly, Solver will fail or give errors.
5. Not checking “Make Unconstrained Variables Non-Negative” – this checkbox in the Solver dialog is easy to overlook and can lead to unrealistic negative results.

Troubleshooting Tips

• “Solver could not find a feasible solution” – this means your constraints contradict each other (e.g., requiring more material than exists). Review each constraint carefully.
• Results seem unrealistic – double-check your formulas in the objective and constraint cells; a small formula error can throw off the entire model.
• Solver button missing from the Data tab – revisit the Add-ins settings in File > Options to confirm Solver is checked and enabled.
• Solving takes too long or times out – for very large or complex models, try the Evolutionary method, or simplify constraints where possible.

Real-World Use Cases

• Manufacturing – determine the optimal product mix given limited raw materials.
• Finance – optimize an investment portfolio for maximum return within risk constraints.
• Logistics – minimize shipping costs across multiple routes and warehouses.
• Staffing – find the most cost-effective employee schedule that still meets coverage requirements.
• Marketing – allocate a limited ad budget across channels to maximize reach or conversions.

Best Practices

• Build and test your formulas manually with sample numbers before running Solver, so you know the logic is correct.
• Label all cells clearly (objective, variables, constraints) so the model is easy to audit later.
• Save your Solver settings as a named scenario if you’ll reuse the model regularly.
• Start with simple constraints and add complexity gradually — it’s easier to debug a small model than a massive one.
• Always sanity-check Solver’s output against real-world logic before implementing the decision.

Final Thoughts

Solver turns Excel from a passive number-cruncher into an active decision-making tool. Once you understand how to define your objective, variables, and constraints, you can tackle optimization problems that would otherwise take hours of manual trial and error. It’s one of the most underrated features in Excel, and once you start using it, you’ll find yourself reaching for it in far more situations than you’d expect.