Excel Solver Syntax: Setup, Constraints, and Examples

How-to basics · Updated November 21, 2026

Excel Solver does not use a special worksheet formula: you define an objective cell, the cells Solver may change, and any constraints, then run the Solver command. In Microsoft Excel for Microsoft 365 on Windows, open Data > Solver; for VBA, the core syntax is SolverOk followed by one or more SolverAdd calls and SolverSolve.

What “Solver syntax” means

People use “Excel Solver syntax” to mean two related things. One is how to specify a model in Solver’s dialog boxes: which cell to optimize, which cells are decision variables, and what limits they must obey. The other is the VBA procedure syntax that builds and runs that model. Neither is a normal Excel function that you type into a worksheet cell.

Solver is an optimization add-in. It searches for values that make a formula in an objective cell as large as possible, as small as possible, or equal to a chosen target, while respecting the constraints you supply. It is useful for allocation, scheduling, production, blending, and other problems with competing limits. Solver returns values in the changing cells; it does not replace the formulas that calculate the model.

Enable Solver in Excel

In Excel for Microsoft 365 on Windows, select File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box, select Go, check Solver Add-in, and select OK. After it loads, Solver appears on the Data tab, usually in the Analysis group. If it is already enabled, go straight to Data > Solver.

Excel for Mac has a different add-in route: use Tools > Excel Add-ins, select Solver Add-in, and confirm. Solver is a desktop Excel feature; Excel for the web does not provide the desktop Solver add-in interface, so open the workbook in the desktop app to use it. Menu wording or placement can vary slightly by platform and build.

Build a worksheet model first

A useful Solver model separates decision variables, input data, calculated results, and constraints. For a production example, put quantities for two products in B2:B3, profit per unit in C2:C3, labor per unit in D2:D3, and material per unit in E2:E3. Put total profit in B6, labor used in B7, and material used in B8. Place the available labor and material limits in separate cells or enter those limits directly when adding constraints.

With product quantities in B2:B3 and per-unit profits in C2:C3, enter this formula in B6 to calculate total profit:

=SUMPRODUCT(B2:B3,C2:C3)

If C2 is 30 and C3 is 20, quantities of 40 and 20 produce a B6 result of 1,600. SUMPRODUCT multiplies corresponding entries and adds the products. The quantities in B2:B3 are the cells Solver will adjust; B6 is a formula cell, not a changing cell.

With quantities in B2:B3 and labor requirements per unit in D2:D3, enter this formula in B7:

=SUMPRODUCT(B2:B3,D2:D3)

With quantities in B2:B3 and material requirements per unit in E2:E3, enter this formula in B8:

=SUMPRODUCT(B2:B3,E2:E3)

For example, if labor requirements are 2 and 1 hours per unit, and material requirements are 1 and 2 units per product, quantities of 40 and 20 use 100 labor hours and 80 material units. Those totals give Solver formula-driven values to compare with the available capacity.

Set the objective and changing cells

Open Data > Solver and fill in the main fields. Set Objective should point to the formula cell, such as $B$6. Choose Max to maximize profit, Min to minimize a cost, or Value Of to make the objective equal a specified number. By Changing Variable Cells should identify the decision cells, such as $B$2:$B$3. Use absolute references in these entries to make the model addresses unambiguous.

Solver can adjust cells only when the model gives it a meaningful objective and the formulas that depend on the changing cells. Do not select the objective formula as a changing cell: doing so lets Solver overwrite the calculation rather than search for inputs that improve it. Likewise, check that every constraint total is calculated from the variables and relevant input data.

Write constraints correctly

A constraint compares a worksheet cell or range with a limit. In the Add Constraint dialog, the Cell Reference box identifies the left side, the middle list selects a relation, and Constraint supplies the right side. For the production model, add $B$7 <= 100 for the labor limit and $B$8 <= 80 for material. This tells Solver that labor use cannot exceed 100 and material use cannot exceed 80.

Constraints can compare a cell with a number or another cell. For example, if the labor limit is stored in B10, use B10 as the constraint’s right-side reference rather than typing 100. A cell reference makes it easier to revise assumptions without reopening Solver. Common relations are less than or equal to, equal to, and greater than or equal to. Use equality when a condition must be met exactly, not merely when it is a maximum or minimum.

Decision variables often need bounds. To keep production quantities nonnegative, add $B$2:$B$3 >= 0. You can also select Make Unconstrained Variables Non-Negative in the Solver Parameters dialog. If quantities must be whole units, add an integer constraint to the relevant changing-cell range; if they must be either zero or one, select binary. Integer and binary restrictions change the nature of the search, so they can take longer than a continuous linear model.

Choose a solving method

The solving method should match the formulas in the model. Solver’s three main methods are Simplex LP, GRG Nonlinear, and Evolutionary:

  • Simplex LP is intended for linear models: the objective and constraints must be linear expressions in the changing cells. It is generally the right choice for product mixes, transportation, and resource allocation when the relationships are linear.
  • GRG Nonlinear is for smooth nonlinear models. It can be suitable when formulas include nonlinear relationships that change gradually, though the result can depend on starting values and may be a local solution.
  • Evolutionary can handle models with discontinuities or formulas that are not smooth, but may require more search time. It does not guarantee that a practical model will be solved quickly.

For the two-product example, profit and resource use are sums of fixed per-unit amounts multiplied by quantities, so they are linear. Choose Simplex LP. If you introduce a formula such as a product of two changing cells, the model is no longer linear; choosing Simplex LP just because it is fast would not be appropriate. Inspect the formulas and the method’s assumptions rather than relying on the method name alone.

Worked example: maximize production profit

Suppose product A earns 30 per unit and uses 2 labor hours and 1 material unit. Product B earns 20 per unit and uses 1 labor hour and 2 material units. There are 100 labor hours and 80 material units available. Put the quantities in B2:B3, the profits in C2:C3, labor coefficients in D2:D3, and material coefficients in E2:E3. Use the SUMPRODUCT formulas above for total profit and resource use.

In Solver, set the objective to B6 and select Max. Set the changing cells to B2:B3. Add B7 less than or equal to 100, B8 less than or equal to 80, and B2:B3 greater than or equal to zero. Choose Simplex LP. At the optimum, Solver should return 40 units of A and 20 units of B: labor use is 2(40) + 1(20) = 100, material use is 1(40) + 2(20) = 80, and profit is 30(40) + 20(20) = 1,600. The result uses both resources fully and is better than producing only A (50 units and 1,500 profit) or only B (40 units and 800 profit).

This result illustrates a key modeling point: Solver optimizes the numbers represented by your spreadsheet, not the real-world situation automatically. If production must be in whole units, add integer restrictions. If there are minimum orders, setup costs, or other rules, model those explicitly; omitted assumptions cannot affect the answer.

VBA Solver syntax

For automation, enable the Solver add-in and, in the Visual Basic Editor, add a reference to Solver under Tools > References if you want to call its procedures directly. The example below assumes the worksheet layout described above and the Solver reference is available. It resets the old model, sets a maximizing objective, adds resource and nonnegative constraints, and solves with Simplex LP.

Save a copy of the workbook before running a macro. Macro changes cannot be undone with Ctrl+Z.

Sub SolveProduction()
    ' Assumes B6 is profit, B7 is labor use, and B8 is material use.
    Dim solveResult As Variant

    SolverReset
    SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:=0, _
        ByChange:="$B$2:$B$3", Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$B$7", Relation:=1, FormulaText:="100"
    SolverAdd CellRef:="$B$8", Relation:=1, FormulaText:="80"
    SolverAdd CellRef:="$B$2:$B$3", Relation:=3, FormulaText:="0"
    solveResult = SolverSolve(UserFinish:=True)
    SolverFinish KeepFinal:=1
End Sub

In this syntax, MaxMinVal 1 means maximize, 2 means minimize, and 3 means set the objective to the ValueOf argument. SolverAdd uses Relation 1 for less than or equal to, 2 for equal to, 3 for greater than or equal to, 4 for integer, and 5 for binary. The Engine value 2 selects Simplex LP; 1 selects GRG Nonlinear and 3 selects Evolutionary. The return value from SolverSolve can indicate the solve status, so automation that depends on success should inspect the status rather than assume every run found an acceptable solution.

Common Solver problems and checks

If Solver reports that it cannot find a feasible solution, first check whether the constraints can all be satisfied at once. For example, a minimum production requirement may conflict with a resource cap. Temporarily review the model’s formulas and limits, and confirm that the left-side cells show the expected totals before solving.

  • Objective does not change: verify that the objective formula depends, directly or indirectly, on the changing cells.
  • Unexpected answer: confirm the units, signs, and cell references in each formula and constraint. A per-hour figure entered as per-unit, for example, changes the model.
  • Wrong method: use Simplex LP only when the model is linear. Nonlinear or discontinuous formulas may require another method.
  • Fractional quantities: Solver treats changing cells as continuous unless you add integer restrictions or another suitable bound.
  • Different results on later runs: check whether the model is nonlinear and whether starting values or the method affect the search.

After a run, read the Solver Results dialog before accepting the solution. Depending on the model and available options, Solver can offer Answer, Sensitivity, or Limits reports. Reports provide additional views of the result; they do not repair an incorrectly structured model. In particular, sensitivity information is meaningful only for suitable linear models, so do not treat it as a universal forecast.

Frequently Asked Questions

Is Solver syntax the same as an Excel formula?
No. You create formulas for the objective and calculated constraints, then specify the objective, changing cells, and limits in the Solver dialog. VBA uses Solver procedures such as SolverOk and SolverAdd.

Where is Solver in Excel?
In desktop Excel for Microsoft 365 on Windows, enable Solver through File > Options > Add-ins, then choose Excel Add-ins and Go. Once enabled, open Data > Solver. On Mac, use Tools > Excel Add-ins to enable it.

What does SolverAdd Relation mean?
In VBA, Relation 1 means less than or equal to, 2 means equal to, and 3 means greater than or equal to. Relation 4 adds an integer condition and 5 adds a binary condition.

Which Solver method should I choose?
Choose Simplex LP for a linear objective and linear constraints, GRG Nonlinear for smooth nonlinear models, and Evolutionary for models with discontinuities or other difficult nonsmooth features. Match the method to the model’s formulas.

Can Solver work in Excel for the web?
The desktop Solver add-in is not available through the Excel for the web interface. Open the workbook in desktop Excel to configure and run Solver.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.