Excel Solver: How to Add Constraints and Set Limits

How-to basics · Updated November 21, 2026

To add constraints in Excel Solver, open Data > Solver, enter your objective and changing cells, then select Add under Subject to the Constraints and specify the cell reference, relationship, and limit. Constraints tell Solver which solutions are allowed—for example, that production must stay within available labor, quantities cannot be negative, or a decision must be a whole number.

What a Solver constraint does

Solver changes selected worksheet cells to optimize a target, such as maximizing profit or minimizing cost. A constraint restricts the values Solver may use while searching. Without suitable constraints, Solver might return a mathematically attractive answer that is impossible in practice, such as making a negative number of products or using more material than the business has.

A constraint generally compares a cell or range with a limit by using one of three relationships:

  • <= means less than or equal to, often used for a capacity or budget limit.
  • = means exactly equal to, useful for a required total or balance.
  • >= means greater than or equal to, often used for a minimum requirement.

Solver also offers special constraints for integer and binary decisions. These are not ordinary numeric comparisons: an integer constraint requires whole-number values, while a binary constraint restricts a cell to 0 or 1. Constraints are part of the model, not worksheet data validation; they guide Solver during optimization but do not prevent someone from typing a different value into a cell afterward.

Enable Solver in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, Solver is an add-in. If the Solver command is missing from the Data tab, enable it first:

  1. Select File > Options, then choose Add-ins.
  2. At the bottom, choose Excel Add-ins in the Manage box and select Go.
  3. Check Solver Add-in and select OK. If Excel asks to install it, approve the prompt.
  4. Look for Solver in the Analysis group on the Data tab.

Excel for Mac also includes the Solver add-in in supported desktop versions. Enable it through Tools > Excel Add-ins, select Solver Add-in, and then look for Solver on the Data tab. Excel for the web does not provide the desktop Solver add-in for running Solver models; open the workbook in desktop Excel to set up and solve the model. Availability and exact interface details can vary with an organization’s installation and Excel version.

Add a constraint step by step

Prepare the worksheet before opening Solver. The objective cell should calculate the value you want to optimize, and the changing cells should contain the decisions Solver is allowed to adjust. Cells used in constraints should calculate quantities to compare with limits, or contain the decision values themselves.

  1. Choose Data > Solver.
  2. In Set Objective, select the cell containing the result to optimize. Choose Max, Min, or Value Of, depending on the goal.
  3. In By Changing Variable Cells, select the cells Solver may change.
  4. Under Subject to the Constraints, select Add.
  5. In Cell Reference, enter or select the cell or range to restrict. Choose a relationship from the middle list, then enter a number, select a worksheet cell, or choose a special rule in the Constraint box.
  6. Select Add to enter another constraint, or OK when you have finished adding them.
  7. Choose a solving method if needed, then select Solve. Review the result and choose Keep Solver Solution or Restore Original Values.

For a simple limit, a constraint could read D5 <= E5: the calculated amount used in D5 must not exceed the capacity stored in E5. You can type a fixed number as the limit instead, but referencing a worksheet cell makes the model easier to update when the capacity changes. Use cell references from the same worksheet as the Solver model.

Worked example: production, profit, and capacity

Suppose a small workshop makes products A and B. Put the quantities to produce in B2:C2, the profit per unit in B3:C3, and calculate total profit in D2. Put labor required per unit in B5:C5, total labor used in D5, and available labor in E5. Put material required per unit in B6:C6, total material used in D6, and available material in E6. For illustration, the profits per unit are 30 and 50, labor requirements are 2 and 4 hours, labor capacity is 40 hours, material requirements are 3 and 2 units, and material capacity is 30 units.

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

=SUMPRODUCT(B2:C2,B3:C3)

With the same product quantities in B2:C2 and labor requirements in B5:C5, enter this formula in D5 to calculate labor used:

=SUMPRODUCT(B2:C2,B5:C5)

With product quantities in B2:C2 and material requirements in B6:C6, enter this formula in D6 to calculate material used:

=SUMPRODUCT(B2:C2,B6:C6)

In Solver, set the objective to D2 and choose Max. Set B2:C2 as the changing cells. Add D5 <= E5 and D6 <= E6 so production stays within both capacities. Also require B2:C2 to be nonnegative, either by adding a greater-than-or-equal-to-zero constraint or by selecting Make Unconstrained Variables Non-Negative in Solver Options.

With these illustrative numbers and continuous quantities, the best solution is 5 units of A and 7.5 units of B. It uses 40 labor hours and 30 material units, and earns 525 in the example’s unspecified currency units. If products must be whole units, add an int constraint to B2:C2. The best integer solution is 4 units of A and 8 of B, earning 520 while using 40 labor hours and 28 material units. The example shows why the right constraint depends on how decisions work in the real situation: fractional production may be acceptable in a planning model but not for finished items.

Constraint types and when to use them

  • Capacity limits: Use a calculated usage cell with <= and a capacity cell or number. Examples include labor hours, machine time, inventory, and spending limits.
  • Minimums: Use >= for requirements such as producing at least a specified quantity or meeting a minimum service level.
  • Exact totals: Use = when a total must match a target, such as assigning a fixed number of workers across shifts.
  • Nonnegative values: Require decision cells to be at least zero when negative quantities are not meaningful. The nonnegative option is a general Solver setting rather than a separately typed constraint.
  • Integers: Choose int for decisions that must be whole numbers, such as units, staff, or vehicles.
  • Binary choices: Choose bin for yes-or-no decisions represented by 1 and 0, such as whether to open a location or select a project.

For a range of decision cells that all share the same rule, select the range in Cell Reference. For instance, B2:C2 >= 0 applies the nonnegative limit to both cells. Check that the ranges on the two sides of a range-to-range constraint have compatible dimensions. If each product uses a different capacity, it may be clearer to create a separate calculated usage cell and constraint for each resource.

Choose a solving method that fits the model

Solver’s method matters because different models have different mathematical properties. Simplex LP is appropriate for linear models: the objective and constraints are built from linear relationships, without products of decision variables or nonlinear functions of them. The production example is linear, so Simplex LP is a suitable choice.

GRG Nonlinear is intended for smooth nonlinear models, where formulas change continuously but are not linear. Evolutionary can handle models with discontinuities or more complicated relationships, though it may take longer and does not guarantee the same kind of optimum as a linear model. Integer and binary restrictions can also make a model harder to solve. If results seem strange, check that the selected method matches the formulas and decision rules rather than changing constraints at random.

For a linear model, select Simplex LP in the Select a Solving Method list in the Solver Parameters window. In a nonlinear model, use the method suited to its formulas and review Solver Options if the search needs different limits or precision. Solver may find a local result for some nonlinear problems, so a plausible result should be checked against the model and the real-world assumptions.

Common constraint problems and how to fix them

  • Solver says no feasible solution exists: The constraints may conflict. For example, a minimum production target might require more labor than the labor capacity allows. Temporarily inspect the limits and calculations, then determine which assumption needs correction.
  • Solver returns an unexpected answer: Confirm that the objective cell actually calculates the quantity to optimize and that the changing-cell range contains only the decisions. A constraint on an unrelated or incorrectly calculated cell will not enforce the intended rule.
  • A limit does not update when inputs change: If the limit is a typed number, it stays fixed. Use a reference to a worksheet cell when the allowable capacity should change with the data.
  • Solver ignores a negative-value concern: Make sure nonnegative values are enforced for the changing cells, either through the Solver option or explicit constraints. Do not assume all worksheet cells are automatically nonnegative.
  • Whole-number decisions come back as decimals: Check that the int or bin rule was added to the correct changing cells. A regular >= 0 rule does not make a value an integer.
  • A constraint appears to be satisfied but the answer is unrealistic: Check units and formulas. A capacity in minutes compared with usage calculated in hours, for example, can make a correctly entered constraint model the wrong situation.

After solving, inspect the changing cells and the formulas feeding the constraints. Solver optimizes the model as written; it cannot determine whether a missing cost, incorrect unit conversion, or omitted business rule makes the model incomplete.

Editing, removing, and reviewing constraints

To change a rule, open Data > Solver and select the constraint in the Subject to the Constraints list. Choose Change, adjust the cell reference, relationship, or limit, and confirm. Select a listed constraint and choose Delete to remove it. Use Reset All only when you want to clear the Solver setup, including its objective, changing cells, and constraints; it is not a way to remove just one rule.

Keep a note of what each constraint represents, especially in a model with many limits. Descriptive labels beside capacity cells, units in column headings, and a separate section for assumptions make it easier to catch errors later. Save the workbook after building a useful model so the Solver settings and worksheet remain available for future revisions.

Frequently Asked Questions

How many constraints can I add in Excel Solver?
Solver accepts multiple constraints, but practical limits depend on the Solver version and model complexity. If a large model exceeds the available capacity, consider simplifying it or using a purpose-built optimization tool. Check the documentation for the Solver version installed in your Excel environment.

Can I use a cell reference as the constraint limit?
Yes. In the Constraint box, select a worksheet cell instead of typing a fixed number. This is useful when a limit, such as available labor, changes with the data.

How do I make Solver use whole numbers?
Add a constraint for the decision cells and select int in the relationship list. Use bin instead when the decisions must be limited to 0 or 1.

Why is Solver missing from the Data tab?
The Solver Add-in may not be enabled. On Windows, enable it through File > Options > Add-ins, then choose Excel Add-ins in Manage and select Go. On Mac, use Tools > Excel Add-ins. The desktop add-in is not available for solving in Excel for the web.

Can Solver constrain a formula cell?
Yes. A formula cell can be the constrained cell when its value depends on the changing cells. For example, a formula calculating total labor can be constrained to be less than or equal to the labor capacity.

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.