Yes. In Excel for Microsoft 365 on Windows, use the Solver add-in and set the decision-cell range’s constraint to bin; Solver then restricts each cell to 0 or 1. A value of 1 means a choice is selected, and 0 means it is not.
What a binary Solver variable means
A binary variable is a decision cell limited to two possible values: zero and one. This makes it useful when a model represents a yes-or-no decision, rather than a quantity that can take any value. For example, a company might decide whether to open each proposed site, a planner might select a set of projects, or a household might choose which items to include in a delivery.
Excel Solver does not infer that a cell is binary from its contents or formatting. A cell containing a formula that returns 0 or 1 is not automatically treated as a binary decision variable. Instead, the cell must be included in Solver’s “By Changing Variable Cells” range and given a binary constraint. Solver may change those cells while searching, but a valid solution will assign each one either 0 or 1.
Binary is different from integer. An integer constraint permits whole numbers such as 0, 1, 2, and 3; a binary constraint permits only 0 and 1. It is also different from a checkbox or a drop-down list: those controls may help people enter choices, but they do not by themselves create optimization constraints.
Prepare the worksheet
Set up the decision cells, inputs, and objective calculation before opening Solver. Keep the decision cells together in one range when possible, and make the objective and resource totals depend on those cells through formulas. This makes the model easier to inspect and reduces the chance of constraining the wrong cells.
For a project-selection model, use columns for project name, cost, value, and decision. Put costs in B2:B5, values in C2:C5, and the binary choices in D2:D5. Put the available budget, 8, in F1. Leave D2:D5 available for Solver to change. Add a total-cost calculation and a total-value calculation elsewhere on the sheet.
With project costs in B2:B5 and the 0-or-1 decisions in D2:D5, enter this formula in F2 to calculate total selected cost:
=SUMPRODUCT(B2:B5,D2:D5)
With project values in C2:C5 and the same decision cells in D2:D5, enter this formula in F3 to calculate total selected value:
=SUMPRODUCT(C2:C5,D2:D5)
SUMPRODUCT multiplies each project’s input by its decision value and adds the results. A project with a decision value of 1 contributes its full cost and value; a project with 0 contributes nothing. Keep the input ranges the same size as the decision range so each project is paired with the correct choice.
Enable the Solver add-in
If Solver is not visible on the Data tab in Excel for Microsoft 365 on Windows, enable the add-in. Choose File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins and select Go. Check Solver Add-in, then select OK. Return to the worksheet and look for Solver in the Analysis group on the Data tab. If Excel asks to install or enable the add-in, follow its prompt.
In Excel for Mac, enable Solver from Tools > Excel Add-ins by selecting Solver Add-in, then open it from the Data tab. Excel for the web does not provide the same desktop Solver add-in workflow; use the desktop Excel app to create and run this model. Menu placement can vary slightly with Excel updates, but the Windows add-in is managed through File > Options > Add-ins.
Set up a binary Solver model
On the Data tab, select Solver. In the Solver Parameters dialog, enter the objective cell, choose whether to maximize or minimize it, identify the changing cells, and add the model’s constraints. For the project example, configure it as follows:
- Set the objective to F3, the total project value, and choose Max.
- Set By Changing Variable Cells to D2:D5.
- Select Add. Set the cell reference to F2, choose <=, and set the constraint value to F1, the budget. Select Add or OK to keep the constraint.
- Add a second constraint with D2:D5 as the cell reference and
binas the relation. This tells Solver every decision cell must be binary. - Choose Simplex LP as the solving method if all calculations in the objective and constraints are linear, then select Solve.
In the constraint dialog, bin is a special relation selected from the relation list; it is not a formula to type into a worksheet cell. Applying it to the whole range D2:D5 constrains all four cells. If the range is not accepted in your Excel build, add a binary constraint for each decision cell separately.
When Solver finds a solution, review the Solver Results dialog and choose Keep Solver Solution to retain the values. The decision cells should contain only 0s and 1s, the total cost should not exceed the budget, and the objective should reflect the choices shown. Solver can also offer a solution report, depending on the model and available report types.
Worked example: selecting projects under a budget
Suppose four projects have these costs and values: project A costs 4 and is worth 8; B costs 3 and is worth 6; C costs 2 and is worth 5; and D costs 5 and is worth 9. The budget is 8. Each project is either selected or not, so each decision cell in D2:D5 is binary. The objective is to maximize total value while keeping total cost at or below 8.
One best solution is to select A and B: their combined cost is 4 + 3 = 7 and their value is 8 + 6 = 14. Another equally good solution is C and D: their cost is 2 + 5 = 7 and their value is 5 + 9 = 14. Both fit within the budget. Solver may return either one because the objective value is tied; the model does not specify a preference between equally valuable solutions.
This example also shows why a greedy choice—selecting the single project with the highest value, for instance—does not necessarily solve the full problem. Project D is worth 9 on its own, but combining projects can produce a higher total. Solver considers the allowed combinations and evaluates them against the objective and constraints.
Choose the right solving method
For a linear model, Simplex LP is generally the appropriate Solver method, including a model with binary decisions. In the example, the total cost and total value are sums of fixed inputs multiplied by decision cells, and the budget condition is linear. Binary restrictions make the search discrete, but they do not make those sums nonlinear.
GRG Nonlinear is intended for smooth nonlinear models, while Evolutionary is designed for models with more difficult or discontinuous relationships. Do not choose a nonlinear method simply because the decisions are binary. First check whether the formulas in the objective and constraints are linear in the changing cells. If they are, use Simplex LP; if the model includes nonlinear formulas, choose a method suited to those relationships and understand that the search may require more care.
Solver’s result depends on the model that was entered. A solution marked successful is not proof that the worksheet represents the real decision correctly. Verify that all relevant limits are included, that the objective points in the intended direction, and that the changing cells are the actual decisions.
Common setup problems and checks
- Solver returns fractional choices: Confirm that the binary constraint covers every changing decision cell. Merely formatting cells with zero decimal places can hide a fractional value without making it binary.
- Solver says no feasible solution exists: Review the budget and other limits, check that units match, and confirm that constraints do not conflict. For example, requiring three projects with a combined cost above the budget makes the model infeasible.
- The objective does not change: Check that the objective formula refers to the decision cells and that those cells are listed in By Changing Variable Cells. A misspelled range or a formula that points elsewhere can disconnect the choices from the result.
- The answer looks unexpectedly small: Check whether the objective is set to Max rather than Min, whether the value inputs are in the intended cells, and whether an accidental extra constraint limits the choices.
- Solver changes cells you did not expect: Inspect the changing-cell range. It should include the decision cells, not input data or formula cells that must remain fixed.
Before accepting a result, calculate the selected cost and value independently from the displayed choices. Also try a few obvious combinations in a small model. This is a practical way to catch range mistakes and confirm that the constraints match the question being asked.
Limits and alternatives
Excel Solver is useful for small and moderate models, but a workbook can become difficult to manage as the number of decisions, constraints, or formula dependencies grows. The standard Solver add-in also has limits on model size and available solving resources. If a model exceeds those limits, runs slowly, or needs repeatable large-scale optimization, consider a dedicated optimization tool or an Excel add-in designed for larger mixed-integer models. Check the tool’s current compatibility, licensing, and supported model types before moving a workbook.
For a tiny number of binary choices, you can sometimes list every possible combination and calculate each outcome with formulas. This is straightforward for a handful of decisions, but the number of combinations doubles with each additional binary variable: 10 choices already create 1,024 combinations. Solver is more practical when manual enumeration becomes unwieldy. A different alternative is to build the model in a programming or optimization environment, especially when it must be run repeatedly or integrated with other systems.
Use Solver rather than a simple formula when the task is genuinely an optimization problem: there are choices, a measurable objective, and limits that constrain the choices. If the goal is only to flag whether a condition is met, a formula such as IF may be enough; it does not search for the best set of decisions.
Frequently Asked Questions
How do I make a cell binary in Excel Solver?
Open Data > Solver, include the cell in By Changing Variable Cells, add a constraint for that cell, and choose bin as the relation. A binary constraint makes the cell 0 or 1 in the Solver solution.
What is the difference between binary and integer in Solver?
An integer constraint permits whole-number values, including values greater than 1. A binary constraint permits only 0 and 1, which represents a yes-or-no choice.
Can Excel Solver handle multiple binary cells?
Yes. Put the decision cells in a range, include that range as the changing cells, and apply a binary constraint to the range. If Excel does not accept the range constraint, add constraints cell by cell.
Why does Solver give me a decimal answer?
The cells may not have a binary constraint, or the constraint may not cover all changing cells. Number formatting that displays no decimal places does not enforce whole-number or binary values.
Which Solver method should I use for binary decisions?
Use Simplex LP when the objective and constraints are linear in the decision cells, even when those cells are binary. Use another method only when the model’s formulas have nonlinear relationships that require it.
Related
- How to Bold Subtotal Rows in Excel
- Excel Subtotal Visible Rows: Sum Filtered Data Correctly
- Group and Subtotal Rows in Excel: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Unable to Add Page Numbers in Word? Fixes That WorkIf you’re unable to add page numbers in Word, click Insert > Page Number on Windows, choose a…
- How to Insert a Line in Word to Type On: Easy MethodsTo make a line you can type on in Word, insert a one-cell table, remove its borders, and…
- Excel Data Validation List Based on a LookupYes. In Microsoft Excel for Microsoft 365, make a lookup-driven dropdown by using FILTER in a helper cell,…
- Find an Excel Password in a Protected File? Safe OptionsYou usually cannot find or reveal a password from inside a protected Excel file: Excel does not display…
- How to Sort a Row by Date in Google Sheets SafelyTo sort dates arranged left to right in Google Sheets, use a formula that transposes the selected range,…
- Excel Tables: Do They Automatically Add New Rows in Excel?Yes. In Microsoft Excel for Microsoft 365 on Windows, a table will usually expand automatically when you type…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.