Excel Solver parameters tell the Solver add-in what result to optimize, which cells it may change, and what limits the solution must obey. In Microsoft 365 for Windows, open Data > Solver, then set the objective cell, variable cells, constraints, and an appropriate solving method before selecting Solve.
What Solver parameters mean
Solver is an Excel add-in for optimization: it searches for values in selected worksheet cells that maximize, minimize, or reach a target in a formula cell while satisfying constraints. The Solver Parameters dialog is where you describe that problem. It does not guess what your business goal or restrictions are; the worksheet formulas and the entries in the dialog must represent them correctly.
The main parameters are the objective, the changing variable cells, constraints, and the solving method. Additional options control matters such as calculation precision, iteration limits, and how Solver handles integer restrictions. A well-constructed model is more important than changing advanced options at random. If the worksheet does not calculate the quantity you care about, Solver cannot optimize it correctly.
Enable the Solver add-in and open its dialog
If Solver is missing from the Data tab in Excel for Microsoft 365 on Windows, enable the add-in first:
- Select File > Options > Add-ins.
- At the bottom, choose Excel Add-ins in the Manage box and select Go.
- Check Solver Add-in, select OK, and allow Excel to install or enable it if prompted.
- Open the Data tab and select Solver, usually in the Analysis group.
In Excel for Mac, the add-in is enabled through Tools > Excel Add-ins; select Solver Add-in and confirm. The exact placement of Solver controls can differ by Excel release. Solver is not available in Excel for the web in the same way as the desktop add-in, so use the desktop app to configure and run this Solver feature.
Set the objective and decision cells
Set Objective must refer to one cell containing a formula that measures the outcome you want to improve. Choose Max to find the largest value, Min for the smallest, or Value Of to make the formula equal a specified number. The target should usually be a calculated result, such as total profit, total cost, or a difference from a desired value—not a cell that Solver is allowed to overwrite.
By Changing Variable Cells identifies the cells Solver may adjust. These are decision variables: quantities, allocations, or other inputs whose values determine the objective. Start them with plausible values, often zero or a reasonable estimate, and make sure the objective and constraint formulas depend on them. You can select a range or multiple cells; keep the model understandable and avoid including formulas or labels in the changing range.
For example, suppose a small workshop makes two products. Put the units to produce in B2:B3, profit per unit in C2:C3, labor per unit in D2:D3, and material per unit in E2:E3. Enter 30 and 20 as the two unit profits, 2 and 1 as labor requirements, and 1 and 2 as material requirements. The changing cells B2:B3 are the production quantities.
With profit data in C2:C3 and decision quantities in B2:B3, put this formula in C5 to calculate total profit: =SUMPRODUCT(B2:B3,C2:C3). At 40 units of the first product and 20 of the second, it returns 1,600 because 40 × 30 plus 20 × 20 equals 1,600.
Add constraints that reflect real limits
Constraints define which values count as feasible. Select Add in the Solver Parameters dialog, set a cell reference or range on the left, choose a comparison such as <=, =, or >=, and enter a cell reference, number, or range on the right. Use Change to edit a selected constraint and Delete to remove one. Check every constraint against the actual problem: an incorrect inequality can make a sensible model impossible or permit an unrealistic answer.
Continuing the workshop example, put labor used in D5 and material used in E5. With quantities in B2:B3 and per-unit labor in D2:D3, enter =SUMPRODUCT(B2:B3,D2:D3) in D5. With quantities in B2:B3 and per-unit material in E2:E3, enter =SUMPRODUCT(B2:B3,E2:E3) in E5. At 40 and 20 units, these formulas return 100 labor units and 80 material units, respectively.
If the workshop has at most 100 labor units and 80 material units, add D5 <= 100 and E5 <= 80. Also require B2:B3 >= 0, either with an explicit constraint or the nonnegative setting described below. The resulting model can produce 40 of the first product and 20 of the second for a profit of 1,600. That uses both resources fully; for this example, it is the best feasible mix under the stated linear rates and limits.
Use integer constraints when a decision must be a whole number, such as the count of machines or people. Binary constraints are for yes/no decisions represented by 0 or 1. These restrictions can make a problem harder to solve, so add them only when the real-world decision requires them. A fractional result is not necessarily an error if the model has not prohibited fractions.
Choose the solving method
Solver offers methods for different mathematical shapes. Selecting the wrong method can lead to poor results or a message that Solver cannot find a solution. The method dropdown is part of the Solver Parameters dialog:
- Simplex LP is appropriate when the objective and constraints are linear functions of the changing cells. The workshop example is linear: each extra unit adds a fixed profit and uses fixed amounts of labor and material.
- GRG Nonlinear is for smooth nonlinear models, where formulas may include curves or products of changing values. Its result may depend on the starting values, and it can find a local optimum rather than the best possible value across every feasible point.
- Evolutionary is designed for models with discontinuities or non-smooth relationships that are not handled well by the other methods. It uses a search process and may take longer; results can depend on options and search time.
For a linear model, use Simplex LP rather than choosing a method just because it is the default. For a nonlinear model, determine whether the formulas are smooth and whether integer or yes/no decisions are involved before settling on a method. Always inspect the final worksheet and Solver report; a successful status does not verify that the model represents the intended problem.
Understand the remaining options
The checkbox to make unconstrained variables non-negative tells Solver to treat changing cells as zero or greater unless you specify otherwise. This is useful for quantities such as production or shipments, but it is inappropriate when a variable may legitimately be negative, such as a signed adjustment. You can instead add explicit lower-bound constraints where they make the model clearer.
Select Options in the Solver dialog to adjust method-specific settings. Depending on the method, the dialog can include limits for time or iterations, precision for constraint satisfaction, convergence for nonlinear calculations, automatic scaling, and integer optimality. These settings influence the search or stopping criteria; they do not repair an incorrect objective formula or missing constraint. Leave defaults in place unless you have a reason to change a particular setting, and change one setting at a time so the effect is easier to assess.
Precision is not the same as rounding the displayed answer. A cell formatted to show two decimal places may store more digits, and Solver can use those underlying values. If the real decision needs whole units, add an integer constraint rather than relying on number formatting. Likewise, use realistic units and sensible formula scales; very different magnitudes can make numerical solving less reliable.
Common setup problems and how to diagnose them
If Solver reports that it cannot find a feasible solution, the constraints may conflict. For instance, requiring a minimum output that consumes more labor than the available capacity makes the model infeasible. Temporarily review constraints one by one, verify units, and check whether equality constraints are too strict. Do not delete a real-world limit merely to obtain an answer; identify which assumption or input needs correction.
If Solver returns an implausible solution, check whether the objective is set to Max or Min as intended, whether changing cells are the actual decisions, and whether every important limit is represented. A missing nonnegative constraint can allow negative production. A misplaced cell reference can make a formula ignore some products. Use worksheet labels and inspect formulas directly before increasing iteration limits.
If Solver stops at a local solution in a nonlinear problem, try reasonable alternative starting values and compare outcomes. If it is a linear model, verify linearity and use Simplex LP. If integer restrictions are involved, allow adequate solve time and inspect the integer-related options rather than expecting an instantaneous result. Save a copy before substantial changes, especially when testing different model structures.
What to review after selecting Solve
When Solver finishes, read the status message and choose whether to keep the solution or restore the original values. Review the changing cells, objective value, and each constrained total on the sheet. For the workshop, confirm that quantities are nonnegative, labor is no more than 100, material is no more than 80, and total profit matches the objective formula.
Solver can offer Answer, Sensitivity, or Limits reports for supported problem types. Report availability depends on the model and selected method; for example, a sensitivity report is associated with suitable linear models and is not a general-purpose explanation for every nonlinear or integer result. Reports summarize the current solution but cannot tell whether the assumptions are realistic. Keep a record of the input values, constraints, and chosen method when the result needs to be reviewed later.
Frequently Asked Questions
What does Set Objective mean in Excel Solver?
It is the formula cell whose result Solver will maximize, minimize, or set to a specified value. It should calculate the outcome from the changing cells rather than act as a decision cell itself.
What is the difference between objective cells and changing cells?
The objective cell measures the result, such as total cost. Changing cells are the inputs Solver is permitted to alter, such as quantities to produce. Formulas connect the changing cells to the objective and constraint totals.
Which Solver method should I use?
Use Simplex LP for a linear objective and linear constraints, GRG Nonlinear for smooth nonlinear models, and Evolutionary for many non-smooth or discontinuous models. The model structure—not a general preference—should determine the choice.
Why is Solver missing from Excel?
The Solver Add-in may not be enabled. On Windows, open File > Options > Add-ins, choose Excel Add-ins beside Manage, select Go, and check Solver Add-in. On Mac, open Tools > Excel Add-ins and enable it there.
Can Excel Solver handle whole-number decisions?
Yes. Add an integer constraint for whole-number decisions or a binary constraint for 0-or-1 decisions. These restrictions can make solving more demanding, and you should confirm the selected method and review the final values.
Related
- Excel Split Cell Into Columns: Methods and Tips
- Excel Set Print Area: Steps, Tips, and Fixes
- MS Excel Solver: How It Works, Setup, and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace Shortcut: Keys, Options, and TipsIn Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace. Enter the text to…
- Outlook Signature Card: Create and Add It to EmailTo create a signature card in Outlook, make or update your contact in classic Outlook for Windows, format…
- Excel Power Query Merge vs Append: Which Should You Use?In Excel Power Query, Merge joins two queries side by side using one or more matching columns; Append…
- Excel Redo Shortcut: How to Reapply an ActionIn Excel for Microsoft 365 on Windows, press Ctrl+Y to redo the last action you undid. On a…
- Monthly Budget Tracker Template for Google Sheets?A monthly budget tracker template for Google Sheets can be built from a transaction log, a monthly category…
- How to Insert a Page Break in Excel for Better PrintingTo insert a manual page break in Excel for Microsoft 365 on Windows, select the row below or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.