To add an objective in Excel Solver, open Data > Solver, enter the single cell you want to optimize in Set Objective, and choose Max, Min, or Value Of. The Solver dialog’s Add button is for constraints, not objectives; the objective is entered in its own field.
What an objective means in Solver
An objective is the measurable result Solver is asked to improve or match. It is usually a worksheet cell containing a formula, such as total profit, total cost, delivery time, or unused capacity. Solver changes other cells, called variable cells or decision variables, and recalculates the objective as it searches for a suitable result.
For example, a business might let Solver choose how many units of two products to make, with total profit as the objective. The changing cells hold the quantities; the objective cell calculates their combined profit. A separate set of constraints can limit labor hours, materials, budgets, or production quantities.
In the standard Solver add-in, you set one objective cell at a time. If a problem has several goals, you can combine them into one meaningful score, or solve them in stages. A combined score needs thoughtful weights: adding dollars, hours, and customer ratings without a defined scale can produce a result that is mathematically valid but not useful.
Enable Solver and open its dialog
In Excel for Microsoft 365 on Windows, Solver is an add-in. If it is not already on the Data tab, enable it with these steps:
- Choose File > Options > Add-ins.
- At the bottom, beside Manage, select Excel Add-ins, then choose Go.
- Check Solver Add-in and select OK.
- Open Data > Solver to display Solver Parameters.
In Excel for Mac, use Tools > Excel Add-ins, select Solver Add-in, and open Solver from the Data tab. Ribbon placement and dialog appearance can differ slightly by platform and update, but the objective field and its purpose are the same. Excel for the web does not provide the desktop Solver add-in for running a Solver model; open the workbook in the desktop app to solve it.
Enter the objective cell
In Solver Parameters, the first field is Set Objective. Enter a cell reference, such as $D$2, or select the field and click the objective cell on the worksheet. The cell should represent the result you care about. In most models it contains a formula that depends, directly or indirectly, on the changing cells. A fixed number cannot meaningfully improve as Solver changes other cells.
Next, select the direction. Choose Max to make the objective as large as possible, Min to make it as small as possible, or Value Of to ask Solver to reach a particular number. Use Value Of when you have a specific target, such as matching a production target or reaching a set cost, rather than simply seeking the highest or lowest result.
Then enter the variable cells in By Changing Variable Cells. These are the input cells Solver is allowed to adjust. If the objective formula does not depend on those cells, changing them cannot improve the objective, so check the worksheet links before solving. You can enter a range or select the relevant cells directly.
Worked example: maximize product profit
Suppose a small workshop makes products A and B. Put the planned quantities in B2 and C2, their profit per unit in B3 and C3, and the total profit in D2. Use 30 and 20 as the respective per-unit profits. In this layout, with quantities in B2:C2 and per-unit profits in B3:C3, enter this in D2:
=SUMPRODUCT(B2:C2,B3:C3)
At 40 units of A and 20 units of B, the objective is 40 × 30 + 20 × 20 = 1,600. The value 1,600 is the total profit in the example’s chosen units, not a guaranteed result for other models.
Now list resource use per unit in B5:C5 for labor, and in B6:C6 for material. For this layout, where B2:C2 are the quantities and B5:C5 are labor hours per unit, enter the total labor formula in D5:
=SUMPRODUCT($B$2:$C$2,B5:C5)
Copy D5 down to D6 to calculate material use from the coefficients in B6:C6. This formula keeps the changing quantities fixed as the formula is copied, while the resource-coefficient row moves. Set Solver’s objective to D2 and choose Max; set the changing cells to B2:C2. Add constraints for D5 being less than or equal to 100 labor hours and D6 being less than or equal to 80 material units. You could also constrain B2 to be at most 40 and C2 to be at most 50. Check Make Unconstrained Variables Non-Negative if quantities cannot be negative.
For whole products, add an integer constraint to B2:C2. With these example limits, quantities of 40 for A and 20 for B use exactly 100 labor hours and 80 material units, respect both quantity caps, and produce profit of 1,600. This illustrates how the objective, changing cells, and constraints work together: the objective says what to optimize, the changing cells say what Solver may adjust, and the constraints define what counts as feasible.
Choose Max, Min, or Value Of
- Max: Use for quantities such as profit, output, or a score that should be as high as possible.
- Min: Use for quantities such as cost, distance, waste, or time that should be as low as possible.
- Value Of: Use when the goal is a specified target. Solver may find inputs that reach it, but a target is not necessarily the best or only solution.
Check that the direction matches the real question. Minimizing profit, for example, is usually the opposite of the intended goal. Also consider whether a maximum or minimum is sensible without constraints: if Solver can increase a decision variable indefinitely and each increase improves the objective, there may be no finite best answer. A capacity, demand, or other realistic limit may be missing.
Add constraints and define allowed decisions
Constraints restrict the values Solver can choose. Select Add in Solver Parameters to open the Add Constraint dialog. Specify a cell reference, a relationship such as <=, =, or >=, and a limit or cell reference. For example, constrain a total-hours cell to be less than or equal to available hours. Select Add to enter another constraint, or OK when finished.
Decision variables also need appropriate rules. Product quantities may need to be nonnegative integers; a yes-or-no choice may need a binary constraint. The Solver dialog offers integer and binary constraint types. Do not add an integer restriction automatically: it can make a model harder to solve, and it is unnecessary when fractional values make sense, such as proportions or continuous measurements.
Constraints should reflect the real problem, not just make Solver return a preferred answer. If a result looks implausible, check units, signs, cell references, and whether a limit has been omitted. For instance, hours per unit should not be confused with total available hours, and a maximum production limit should be attached to the correct product quantity.
Select a solving method
Solver’s method matters because it depends on the shape of the formulas. Simplex LP is appropriate for linear models: the objective and constraints are linear expressions of the changing cells. The product example is linear. GRG Nonlinear is intended for smooth nonlinear models, while Evolutionary can handle some models with discontinuities or non-smooth relationships, though it may take longer and can return a less precise result.
Choose the method in the Solver Parameters dialog before selecting Solve. If the model is linear, use Simplex LP and make sure formulas and constraints truly are linear. A formula involving a product of two changing cells, for example, makes the model nonlinear. The method choice does not replace a sound model: poorly designed formulas or missing constraints can still lead to an unhelpful outcome.
Common objective setup problems
- Using Add for the objective: Add opens the constraint dialog. Put the objective reference in Set Objective instead.
- Selecting a label or input instead of a result: Choose the cell that calculates the outcome, not a heading or one of the variables being changed.
- The objective does not respond to the variables: Check that its formula ultimately refers to the changing cells. If it does not, Solver has no path to improve it.
- Getting an unexpected solution: Verify whether Max, Min, or Value Of is selected, and review every constraint and unit.
- Solver reports no feasible solution: The limits may conflict. Temporarily review or relax constraints one at a time to find the conflict, then restore the real requirements.
- Solver stops without the expected result: Confirm the solving method, formulas, and starting values. Nonlinear models can be sensitive to starting values and may find a local rather than global optimum.
When you change formulas, objective direction, constraints, or variable ranges, run Solver again. A result from an earlier model is not automatically updated to reflect a changed setup.
Review results and compare with alternatives
After Solver finishes, review the result before accepting it. The Solver Results dialog lets you keep the solution or restore the original values, and it can offer reports for supported model types. Check the worksheet itself as well: do the decision values meet every limit, and does the objective have the expected meaning and units? Save a copy of important workbooks before experimenting with substantial model changes.
Solver is useful when several inputs must be chosen together under constraints. Goal Seek, by contrast, adjusts one input to make a formula reach a target; it is a better fit for a single-input question. A data table compares outcomes across specified inputs but does not search for an optimum under a set of constraints. These tools answer different questions, so the simplest one that fits the problem is often the best choice.
Frequently Asked Questions
Where is the objective field in Excel Solver?
Open Data > Solver. In Solver Parameters, the objective is entered in the first field, Set Objective. Select one result cell, then choose Max, Min, or Value Of.
Is Solver’s Add button used to add an objective?
No. The Add button is used to add a constraint, such as a resource limit. Set the objective in the separate Set Objective field.
Can Solver optimize more than one objective cell?
The standard Solver dialog accepts one objective cell for a solve. To represent multiple goals, you can build a single score in a worksheet formula with justified weights, or solve goals in priority order in separate runs.
Does the objective cell have to contain a formula?
It is normally a formula that calculates a result from the changing cells. If the cell is a fixed value or does not depend on those cells, Solver cannot use their changes to improve that result.
Why can’t I see Solver in Excel?
The add-in may not be enabled. On Windows, check it under File > Options > Add-ins, choose Excel Add-ins beside Manage, select Go, and check Solver Add-in. On Mac, enable it through Tools > Excel Add-ins.
Related
- Excel Slicer by Month: Set Up and Troubleshoot Date Filters
- Excel Slicer as a Dropdown: Options and Workarounds
- Excel Sheet Not Responding? Causes and Safe Fixes Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Format Tick Boxes in Excel for Clear ChecklistsTo format a tick box in Excel, select the checkbox cell and use the Home tab to apply…
- Excel Goal Seek Shortcut Key: Open and Use the ToolIn Excel for Microsoft 365 on Windows, press Alt+A, W, G in sequence to open Goal Seek; do…
- Excel Tab Rename Shortcut: Windows and Mac Quick GuideIn Excel for Microsoft 365 on Windows, select the worksheet you want to rename, then press Alt, H,…
- Microsoft Excel Workbook: What It Is and How to Use OneA Microsoft Excel workbook is a file that stores spreadsheet data and the parts used to organize, calculate,…
- Consolidate Excel Sheets with Power Query: A Practical GuideYes. In Excel for Microsoft 365 on Windows, use Power Query to bring sheets or files with similar…
- XLOOKUP in Google Sheets: Formula, Examples & TipsGoogle Sheets has an XLOOKUP function for finding a value in one range and returning the corresponding value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.