Excel Solver Binary Constraint: How to Set It Up

How-to basics · Updated November 18, 2026

Yes. In Excel Solver, add a constraint that sets the decision-variable cells to bin (binary); Solver will then restrict each of those cells to 0 or 1. In Excel for Microsoft 365 on Windows, open Data > Solver, choose Add, select bin in the relation list, and apply the constraint to the changing cells you want to make yes-or-no decisions.

What a binary constraint means

A binary variable has exactly two permitted values: 0 and 1. In a model, those values usually represent a choice. For example, 1 can mean select a project, open a facility, or assign a worker to a shift; 0 means do not. The binary constraint is what makes the cell a decision rather than an unrestricted number Solver can adjust to any value.

This matters because a model without the constraint might return a fractional answer such as 0.63 projects. That may be mathematically useful in a relaxed version of the problem, but it is not a valid yes-or-no decision. Applying bin tells Solver that the final value must be one of the two allowed choices.

A binary constraint is not the same as typing 0 or 1 into a cell before solving. Those are only starting values; Solver can change them unless the model restricts them. Nor is formatting a cell as a whole number enough. A whole-number constraint would allow 2, 3, or other integers, while a binary constraint allows only 0 and 1.

When binary variables are useful

Use binary variables when each decision is indivisible and has two outcomes. Common examples include selecting products, deciding whether to fund proposals, turning machines on or off, and assigning a person to a particular task. Binary choices can also represent logical rules, such as requiring a fixed cost to be paid if a project is selected.

For instance, suppose a business can choose from four projects but has a limited budget. Let one changing cell per project represent whether it is selected. Solver can maximize the total project value while obeying the budget, provided each selection cell is binary. The constraint makes the model reflect the real choice: a project is either included or excluded, not selected at 40 percent.

Binary variables can be used alongside ordinary numeric decision variables. A model might use binary cells to decide which locations to open and other changing cells to determine how much each open location produces. The objective and constraints connect those decisions; the binary restriction alone does not express the whole business rule.

Enable Solver and add the constraint

If Solver is not visible on the Windows ribbon, enable the add-in first. Go to File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Solver Add-in, and select OK. Then find Solver on the Data tab. Solver is a desktop Excel add-in; Excel for the web does not provide the same Solver add-in workflow.

  1. Open Data > Solver and set the objective cell, choosing whether to maximize, minimize, or reach a specified value.
  2. Enter the changing variable cells. These are the cells Solver may alter to find a solution.
  3. Select Add under the constraints. In the constraint dialog, set the cell reference to the decision cells or their range.
  4. Open the relation list and choose bin. The right-hand constraint box is not a number to fill in for a binary restriction; the relation itself defines the allowed values.
  5. Select OK, add any other model constraints, choose a solving method, and select Solve.

For a range of choices, apply the binary relation to the whole range in one constraint when the cells share the same role. If only some changing cells are yes-or-no decisions, constrain those cells separately rather than making every changing cell binary. You can reopen Solver later to review or edit the model’s constraints.

In Excel for Mac, Solver is also available as an add-in in supported desktop versions. The add-in is enabled from Tools > Excel Add-ins; after enabling it, use the Solver command on the Data tab. Ribbon details can vary by release. The binary relation is still entered in Solver’s constraint dialog. Excel for the web is not a substitute for the desktop add-in for this workflow.

Worked example: choosing projects under a budget

Create a small model with project names in A2:A5, costs in B2:B5, values in C2:C5, and decision cells in D2:D5. Enter costs of 40, 30, 25, and 20, and values of 65, 50, 42, and 30, respectively. The values and costs are illustrative units. Put the total value in F2, total cost in F3, and the budget of 70 in F4.

With project values in C2:C5 and selection decisions in D2:D5, enter this formula in F2 to calculate the value of the selected projects: =SUMPRODUCT(C2:C5,D2:D5). With project costs in B2:B5 and the same decision range in D2:D5, enter this formula in F3 to calculate the spending: =SUMPRODUCT(B2:B5,D2:D5). Each formula multiplies each project’s amount by its 0-or-1 selection and adds the results.

In Solver, set the objective to F2 and choose Max. Set the changing cells to D2:D5, add the constraint D2:D5 bin, and add the budget constraint F3 <= F4. The budget formula and constraint ensure spending cannot exceed 70. Because the budget cell contains 70, the Solver condition is effectively total cost less than or equal to 70.

The best selection is project 1 and project 2: their combined cost is 40 + 30 = 70, and their value is 65 + 50 = 115. The other two selection cells are 0. Solver should return D2=1, D3=1, D4=0, and D5=0 for this model, with F2 equal to 115 and F3 equal to 70. For example, projects 1 and 3 have value 107, so they do not beat the selected pair. The example shows why both the binary restriction and the budget rule are needed: one defines valid choices, and the other limits which combination is feasible.

Choose the right solving method

For a model with a linear objective and linear constraints, choose Simplex LP in Solver’s solving method list. The example above is linear: each decision contributes a fixed amount to total cost and value. Solver can account for the binary restrictions while searching for a permitted solution. If the formulas or rules make the model nonlinear, the appropriate method depends on the form of the model; GRG Nonlinear is intended for smooth nonlinear models, while Evolutionary is intended for models that are non-smooth or otherwise unsuitable for the other methods.

Changing the method does not repair an incorrect model. First check that the objective refers to the intended result, every changing cell is included, and each business rule has been entered as a constraint. For a linear model, selecting Simplex LP is a sensible starting point, but read Solver’s completion message rather than assuming that any displayed numbers are an optimal answer.

Binary, integer, and continuous constraints

Solver’s bin constraint is more restrictive than int. An integer constraint permits whole-number values, such as 0, 1, 2, and 3; a binary constraint permits only 0 and 1. Choose int when a decision represents a count that can exceed one, such as the number of vehicles to purchase. Choose bin when the decision is strictly yes or no.

A continuous variable, by contrast, can take fractional values within its bounds. That is appropriate for quantities that can genuinely be split, such as a measured amount of material. A frequent modeling mistake is to use a continuous cell for an all-or-nothing decision and then round the answer afterward. Rounding can violate a budget or another constraint, and it does not guarantee the rounded result is optimal. Put the restriction in Solver instead.

Binary variables can also model some logical rules when combined with other constraints. For example, if opening a site must be represented by a 0-or-1 cell, the rest of the model can link that cell to production or capacity. A binary constraint by itself does not create that link: the model must still specify what selecting the site allows or requires.

Common problems and checks

  • The answer contains a fraction. Confirm that the intended decision cells—not just an adjacent total—are included in the bin constraint. Check the constraint in Solver’s list before solving again.
  • Solver says there is no feasible solution. Check whether the rules can all be satisfied together. In the example, a budget below 30 would still allow one project, but a budget below 20 would allow none; extra requirements could make even that impossible. Review cell references, inequality directions, and any minimum-selection rules.
  • Solver returns an unexpected choice. Check that the objective is set to the correct cell and to Max or Min as intended. Verify that formulas include every relevant row and that the changing cells are the actual decision cells.
  • The model solves slowly or does not finish well. Reduce unnecessary variables and constraints, check for formula errors, and use a suitable solving method. The standard Solver add-in has a limit of 200 changing variable cells, so a large binary model may exceed its capacity or become difficult to solve.
  • Cells show values other than 0 or 1 after solving. Make sure the binary condition is a Solver constraint, not merely a typed label or cell format. Also confirm you are looking at the changing cells, rather than calculated totals that can naturally be larger numbers.

Keep the spreadsheet formulas as simple and transparent as possible. A formula error in an objective or constraint can lead Solver to optimize the wrong calculation even when the binary restriction is correct. It is useful to inspect the final decision cells, recalculate the totals, and confirm each stated rule against the returned solution.

Limits and alternatives for larger models

The built-in Solver add-in is suitable for many small and moderate spreadsheet models, but it is not a general-purpose large-scale optimization platform. Its standard limit of 200 changing variable cells can be restrictive when a problem has hundreds or thousands of yes-or-no decisions. Integer restrictions can also make a model harder to solve than a comparable model with only continuous variables.

For larger problems, consider whether the model can be simplified, split into genuinely independent parts, or solved with an optimization tool designed for larger mixed-integer models. Third-party Solver products and dedicated optimization software are alternatives, but their capabilities, licensing, and Excel integration differ. Before moving tools, verify that the alternative supports the specific constraints and objective your model needs; a larger interface alone does not guarantee the same result.

Frequently Asked Questions

How do I add a binary constraint in Excel Solver?
Open Data > Solver, select Add in the constraints area, choose the decision cell or range, and select bin from the relation list. Confirm the constraint, then solve the model.

Does bin mean the cell can only be 0 or 1?
Yes. A binary constraint restricts each cell in the selected range to 0 or 1. It does not mean any number from 0 to 1 is allowed, and it does not set a fixed value for the cells.

What is the difference between bin and int in Solver?
bin allows only 0 and 1. int allows whole numbers, including values greater than 1. Use binary for a yes-or-no choice and integer for a count that may be larger.

Why is Solver giving me a decimal answer?
The decision cells may not have a binary constraint, or the constraint may refer to the wrong cells. Reopen Solver and check that the changing cells are the same cells covered by the bin condition. A number format that displays no decimal places does not make a value binary.

Can I use a binary constraint with Simplex LP?
Yes, when the model’s objective and constraints are linear, Simplex LP is the appropriate method to try. If your model is nonlinear or non-smooth, select a method suited to its structure and check Solver’s result message.

Can I use Solver with binary constraints in Excel for the web?
The desktop Solver add-in workflow is not available in Excel for the web. Use a supported desktop version of Excel, such as Excel for Microsoft 365 on Windows or Mac, to add and solve this kind of Solver model.

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.