Yes. Excel Solver can handle binary options by assigning a binary constraint to decision cells, forcing each one to be either 0 or 1. In Excel for Microsoft 365 on Windows, open Data > Solver, set the objective and changing cells, then add a constraint using the Bin relation for the decision-cell range.
What binary options mean in Solver
A binary variable represents a yes-or-no decision. Its only permitted values are 0 and 1: for example, 1 can mean choose a project, schedule a worker, or open a warehouse, while 0 means do not. In a spreadsheet model, a cell containing a binary decision lets Solver evaluate combinations while obeying the restrictions you specify.
This is different from simply entering a zero or one yourself. If a decision cell is not constrained, Solver may assign it a fractional value such as 0.6. A fractional answer could be meaningless if the cell represents whether to buy an entire machine or accept a whole project. The binary constraint prevents that by restricting the cell to the two allowed values.
Binary variables are a special case of integer variables. They are useful for selection, assignment, scheduling, and fixed-charge problems. They do not, by themselves, make a model accurate: the worksheet still needs formulas that correctly calculate the objective and represent every relevant limit.
A small project-selection example
Suppose a business has four possible projects and a budget of 100 units. Each project has a value and a cost, and the business must decide which projects to fund. The figures below are illustrative and are not financial advice.
- Project A: value 70, cost 60.
- Project B: value 60, cost 50.
- Project C: value 50, cost 40.
- Project D: value 40, cost 30.
Set up the worksheet with project names in A2:A5, values in B2:B5, costs in C2:C5, and decision cells in D2:D5. Put a total-value formula in F2 and a total-cost formula in F3. Initially enter 0 in each decision cell; Solver will change them. The formulas below assume exactly this layout.
With values in B2:B5 and binary decisions in D2:D5, enter this in F2: =SUMPRODUCT(B2:B5,D2:D5). It calculates the total value of the selected projects.
With costs in C2:C5 and the same decisions in D2:D5, enter this in F3: =SUMPRODUCT(C2:C5,D2:D5). It calculates total cost. The multiplication in each SUMPRODUCT means a project contributes its value or cost only when its decision cell is 1.
For example, decisions of 0, 1, 1, 0 select B and C. The total value is 60 + 50 = 110, and the total cost is 50 + 40 = 90. Another optimal choice is A and D, also with value 110 and cost 90. Both meet the budget. Solver may return either because the objective value is tied.
Enable Solver and open its settings
In desktop Excel for Microsoft 365 on Windows, Solver is an add-in that may need to be enabled before it appears on the Data tab:
- Choose File > Options > Add-ins.
- At the bottom, set Manage to Excel Add-ins and select Go.
- Check Solver Add-in, then select OK. If Excel asks to install it, follow the prompt.
- Open the Data tab and select Solver, usually in the Analysis group.
After enabling it, you can reopen the Solver Parameters dialog from Data > Solver. Excel for the web does not provide the desktop Solver add-in in the same way; use desktop Excel for these steps. In Excel for Mac, Solver is also available as an add-in, but add-in management is under Tools > Excel Add-ins; after enabling it, look for Solver on the Data tab. Menu placement can vary slightly between releases.
Set binary decision cells and constraints
In the Solver Parameters dialog, configure the example as follows:
- Set Objective: select F2, the total-value formula cell, and choose Max.
- By Changing Variable Cells: select D2:D5.
- Select Add to create a constraint. Set the cell reference to D2:D5, choose Bin from the relation list, and confirm the constraint.
- Add another constraint with F3 as the cell reference, <= as the relation, and 100 as the limit.
- Choose a solving method appropriate to the model, then select Solve.
The Bin relation applies to every cell in the selected range. You do not need a separate binary constraint for each project if the whole range is selected. The decision cells should be ordinary worksheet cells that Solver is allowed to change, not formula cells. The total formulas, by contrast, should be formulas and should not be included among the changing cells.
For this example, choose Simplex LP because both the objective and the budget calculation are linear in the decision variables. If Solver finds a solution, review the results dialog and choose to keep the solution if you want the values left in the worksheet. The resulting total value should be 110 and the total cost should be 90, although either of the two tied project combinations may appear.
Choosing the solving method
Solver’s method should match the structure of the formulas, not just the fact that some cells are binary. Simplex LP is appropriate when the objective and constraints are linear. A SUMPRODUCT of fixed values and decision variables, as used above, is linear. Binary restrictions make the search discrete, but the underlying expressions can still be linear.
When a linear model includes binary or other integer restrictions, Solver must search among discrete combinations rather than accept fractional values. This can take longer as the number of decisions grows. GRG Nonlinear is intended for smooth nonlinear models, while Evolutionary is designed for models that are non-smooth or otherwise unsuitable for the other methods. Selecting a nonlinear method does not repair a wrongly built model, and it can make a simple linear model harder to solve. If the model is linear, start with Simplex LP.
Look for a model that genuinely matches the chosen method. For instance, multiplying two decision cells together creates a nonlinear expression; adding fixed costs with binary variables can also require care in how the formulas are represented. If you add terms or formulas beyond straightforward totals, check Solver’s method guidance and verify the returned solution independently.
Common problems and practical checks
If Solver returns fractions in cells that should be yes-or-no choices, first check that the Bin constraint is present and covers the intended range. Also confirm that you selected the correct changing cells. A constraint on D2:D4 will not make D5 binary.
- Solver is missing: enable Solver Add-in through Excel Add-ins, then check the Data tab again.
- No feasible solution: inspect the limits for contradictions. For example, requiring a cost below 50 while also requiring two projects that each cost at least 30 would make the model infeasible.
- The answer looks wrong: verify that objective and constraint formulas include every relevant row and that the objective is set to Max rather than Min.
- Solver selects nothing: check that selecting a project increases the objective, that decision cells are linked to the totals, and that constraints do not rule out every selection.
- It takes too long: simplify unnecessary formulas, reduce the number of decision cells where possible, and check for accidental nonlinear calculations.
It is good practice to validate a result outside Solver. For a small model, calculate the totals from the selected rows and compare them with the objective and constraint cells. For a larger model, test a few alternative combinations or use a separate check formula. A solver status message is not a substitute for confirming that the worksheet expresses the real problem correctly.
Binary versus integer and changing-cell choices
Use Bin when the only legal values are 0 and 1. Use an integer restriction when a cell represents a whole-number quantity that may be greater than 1, such as the number of vans to purchase. For instance, a count could be 0, 1, 2, or 3, whereas a yes-or-no purchase decision should be binary. If the model needs a whole-number count with a maximum, combine an integer restriction with an upper bound.
Not every choice needs a separate binary variable. If a choice is naturally represented by a quantity that can vary continuously, a binary cell may add unnecessary complexity. Conversely, if a project must be accepted whole, allowing a continuous decision variable from 0 to 1 models a different problem: it permits partial selection. Choosing the right variable type is a modeling decision, not merely a Solver setting.
Limits and alternatives for larger models
Excel’s built-in Solver is useful for compact planning problems and learning optimization, but it has practical limits. Models with many binary decisions can require a large search, may take a long time, and can exceed the capacity of the standard Solver add-in. Microsoft’s Solver documentation describes a limit of 200 changing variable cells for the standard Solver. The exact practical speed depends on the model and computer; a model with fewer variables can still be difficult if its logic is complex.
For a workbook-scale problem, reduce needless decision variables, keep formulas transparent, and avoid volatile or unnecessarily complicated calculations. If the model is too large or needs advanced optimization features, consider a dedicated optimization tool or a programming environment with an optimization library. That is not automatically better for every user: a separate tool involves learning its workflow and ensuring the data and model are transferred correctly.
Frequently Asked Questions
How do I make a cell binary in Excel Solver?
Open Data > Solver, select the decision cells as the changing variable cells, add a constraint for that range, and choose Bin as the relation. The constraint restricts each selected cell to 0 or 1.
Can Excel Solver choose yes or no?
Yes. Represent each yes-or-no choice with a cell and define which value means yes, usually 1, and which means no, usually 0. Add a binary constraint and connect those cells to the objective and limits with worksheet formulas.
Which Solver method should I use for binary variables?
For a linear model with binary decisions, use Simplex LP. The binary restriction still requires discrete choices, but linear formulas such as totals of fixed costs or values multiplied by binary cells remain a linear model.
Why does Solver give me a decimal instead of 0 or 1?
The binary constraint may be missing, applied to the wrong cells, or omitted from part of the range. Confirm that the cells are included in By Changing Variable Cells and that the Bin constraint covers all of them.
Can Excel Solver handle multiple binary choices?
Yes. Apply one Bin constraint to a range of decision cells to make all cells in that range binary. The standard Solver add-in has a documented limit of 200 changing variable cells, and solving time can rise substantially as the number and complexity of choices increase.
Does Excel for the web have Solver?
The desktop Solver add-in workflow described here is for Excel on Windows or Mac. Excel for the web does not offer that same built-in desktop add-in experience, so open the workbook in desktop Excel to use Solver.
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 Double Space in Word and OutlookTo double-space text in Word, select it and choose Home > Line and Paragraph Spacing > 2.0. In…
- Excel Insert Picture Shortcut: Windows, Mac, and WebIn Excel for Microsoft 365 on Windows, press Alt, N, P, D in sequence to open the dialog…
- How to Enable the Text Import Wizard in Excel for CSV FilesTo enable the legacy Text Import Wizard in Excel for Microsoft 365 on Windows, open File > Options…
- Protect a Sheet in Excel: Steps, Options, and LimitsIn Excel for Microsoft 365 on Windows, protect a sheet by opening the worksheet, choosing Review > Protect…
- How to Fill Area Under a Scatter Plot in ExcelExcel does not provide a direct “fill under” option for an XY scatter series. For evenly spaced X…
- Excel Print Area Button Greyed Out? Causes and FixesIf Excel’s Print Area button is greyed out, first exit cell-editing mode and make sure only one worksheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.