Excel Solver With Integer Values: Setup and Examples

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, open Data > Solver, add an integer constraint to the decision-variable cells, and solve; Solver will return whole-number values for those cells. Integer constraints are useful when a model counts items, people, shifts, or other things that cannot be fractional, but they do not automatically make every other value in the worksheet an integer.

What integer values mean in Solver

Excel Solver changes selected decision cells to maximize, minimize, or reach a target for an objective cell while obeying constraints. An integer constraint tells Solver that a selected decision cell must take a whole-number value, such as 0, 1, 2, or 3, rather than a fraction such as 2.6. The remaining formulas in the workbook calculate the consequences of those choices.

This distinction matters: formatting a cell to show zero decimal places only changes how its value appears. A cell may display 3 while actually containing 2.6. Solver’s integer constraint changes the optimization requirement itself, so the accepted decision value must be an integer. It does not round a fractional solution after the fact; it searches for a feasible whole-number solution to the model.

Use integer decisions for quantities such as the number of machines to buy, cases to ship, or employees assigned. Do not use them just because a result looks neater as a whole number. For example, money totals or hours may legitimately be fractional, depending on the problem. Add the restriction only to cells whose real-world meaning requires it.

Enable the Solver add-in

Solver is an Excel add-in. If Data > Solver is not visible in the Windows desktop app, enable it from File > Options > Add-ins. At the bottom of the Add-ins page, choose Excel Add-ins in the Manage box, select Go, check Solver Add-in, and select OK. The Solver command should then appear on the Data tab, usually in the Analyze group.

In Excel for Mac, enable the add-in through Tools > Excel Add-ins, select Solver Add-in, and choose OK; then look for Solver on the Data tab. Solver is not available as the same desktop add-in in Excel for the web, so open the workbook in the desktop app to use it. Menu placement can vary slightly with ribbon layout and Excel updates.

Build a model before adding integer constraints

A useful Solver model has three clearly identified parts:

  • Decision cells: the values Solver is allowed to change, such as units of two products.
  • Objective cell: a formula measuring what you want to maximize or minimize, such as total profit or total cost.
  • Constraint formulas and limits: worksheet calculations representing resource use, demand, capacity, or other requirements.

Start with a model that calculates the objective and constraints correctly. Solver cannot infer what a variable represents, and an integer restriction cannot fix a wrong formula, omitted limit, or incorrect unit. Label the cells and keep the decision range separate from constants such as prices and capacities. If a variable must be nonnegative, make that explicit with a lower-bound constraint or use the nonnegative-variable setting in Solver Options.

Also distinguish a decision variable from a calculated result. If Solver chooses quantities in B2:B3 and a formula calculates total profit in B5, normally constrain B2:B3 to integers—not B5. Profit may be fractional in another model and is not a choice Solver makes directly.

Add an integer constraint in Solver

On Windows, select Data > Solver to open Solver Parameters. Set the objective cell, choose Max, Min, or a target value, and enter the changing cells. Then select Add under Subject to the Constraints. In the Cell Reference box, select the decision cell or range. Open the relation list and choose int, then select OK. The constraint is usually displayed as a range followed by int.

  1. Select Add again if you need other requirements, such as a capacity limit or a minimum quantity.
  2. Confirm that all integer-only decision cells are covered. A single integer constraint can apply to a contiguous range.
  3. Choose an appropriate solving method, then select Solve.
  4. In the Solver Results dialog, choose Keep Solver Solution if the result is acceptable. Review the changing cells and constraint calculations in the worksheet.

The int relation is not a comparison against a number: it means the selected cells must be integers. To require a variable to be either 0 or 1, use the bin relation instead. Binary is a special, more restrictive kind of integer decision and is useful for yes/no choices, such as whether to open a location. An integer constraint alone does not limit a value to 0 or 1.

Worked example: choose whole units to produce

Suppose a shop makes products X and Y. It earns $40 per unit of X and $30 per unit of Y. X uses two labor hours and Y uses three. The shop has 100 hours, and demand limits production to at most 30 units of X and 25 of Y. Put the quantities to be chosen in B2:B3, the profits per unit in C2:C3, and labor hours per unit in D2:D3. Enter 40 and 30 in C2:C3, and 2 and 3 in D2:D3.

With product quantities in B2:B3 and per-unit profits in C2:C3, put this objective formula in B5:

=SUMPRODUCT(B2:B3,C2:C3)

With quantities in B2:B3 and hours per unit in D2:D3, put this resource-use formula in B6:

=SUMPRODUCT(B2:B3,D2:D3)

In Solver, set the objective to B5 and choose Max. Set the changing cells to B2:B3. Add constraints B6 <= 100, B2 <= 30, and B3 <= 25; also ensure B2:B3 are nonnegative and add the integer constraint B2:B3 int. Choose a method appropriate for the linear model. One optimal result is 30 units of X and 13 of Y: labor use is 2×30 + 3×13 = 99 hours and profit is 40×30 + 30×13 = $1,590. Producing 30 units of X leaves 40 hours, enough for 13 whole units of Y. The 14th unit would exceed the labor limit.

If the integer constraint were omitted, Solver could choose a fractional quantity such as 13⅓ units of Y after making 30 units of X, reaching the 100-hour limit. That may be mathematically valid for a continuous model but impossible if products must be made in complete units. The integer solution can therefore have a lower objective than the unrestricted continuous solution; that is the cost of enforcing a real-world requirement.

Choose a solving method that fits the formulas

Solver’s method should match the model, not simply the fact that it contains integers. For a linear objective and linear constraints, choose Simplex LP. The example above is linear: its formulas use fixed per-unit values multiplied by decision cells and summed. Integer restrictions make the search harder, but do not make those underlying relationships nonlinear.

For smooth nonlinear formulas, GRG Nonlinear may be appropriate. For models with discontinuities, logical switches, or other non-smooth relationships, Evolutionary may be a better fit. Nonlinear integer models can be substantially harder to solve than linear ones, and the chosen method can affect whether Solver finds a solution efficiently. If a model is important, test that the returned values satisfy the intended rules and compare results with a suitable alternative solver when scale or complexity warrants it.

To make a choice, inspect formulas that depend on changing cells. If they are built from linear additions, subtractions, and constant multiples, start with Simplex LP. If they contain nonlinear relationships such as products of decision variables or powers, the model may require a nonlinear method. Do not switch methods blindly: Solver’s result dialog and worksheet constraints are important checks, not proof that the model expresses the problem correctly.

Common problems and how to check results

  • A displayed whole number is not an integer restriction. Number formatting can hide decimals. Check the Solver constraints and inspect the actual cell value or formula bar.
  • Integer does not mean nonnegative. A negative whole number is still an integer. Add a lower bound of zero or confirm that Solver’s option for nonnegative unconstrained variables is selected.
  • Only some variables were constrained. If one decision cell is outside the integer range, Solver may still assign it a fraction. Review the exact cell reference shown in the constraints list.
  • The model may be infeasible. Integer requirements can make a combination of tight limits impossible. For example, an exact target may be reachable with fractional quantities but not with whole units. Temporarily examine which constraints conflict; do not remove a real requirement merely to force an answer.
  • A result may take longer to find. Integer optimization involves searching among discrete choices. More decision cells, tight limits, or nonlinear formulas can increase solve time. Reduce unnecessary changing cells and keep formulas and constraints as simple as the real problem permits.

Solver includes an Integer Optimality setting in its options for controlling how close a result must be to the best integer solution before stopping. A tighter tolerance may require more computation. A result can also depend on the solving method and the model’s structure. For consequential decisions, retain a copy of the original inputs, verify every limit independently, and do not treat a solver status message as a substitute for checking the assumptions.

When to use binary variables or another tool

Use an integer variable when it can take any whole-number count within its bounds; use a binary variable when the choice is strictly yes or no. For example, a planned number of delivery vans might be an integer, while a cell indicating whether a depot is open should usually be binary. A model can combine continuous, integer, and binary decision cells, but each restriction should reflect the real decision being represented.

Excel Solver is convenient for modest worksheet models and familiar spreadsheet workflows. The standard Solver add-in has a limit of 200 changing cells, and discrete or nonlinear problems can become slow well before reaching that limit. For larger or more demanding models, consider a dedicated optimization package, a commercial Solver add-in, or a programming library designed for optimization. Before moving, check that the alternative supports the required model type and that the workbook’s assumptions and units have been documented.

Frequently Asked Questions

Can Excel Solver return whole numbers?
Yes. Add an int constraint to the decision cells that must be whole numbers. Solver then searches for a solution satisfying that condition, along with the objective and all other constraints.

How do I make a Solver variable 0 or 1?
In the Add Constraint dialog, select the variable cell or range and choose bin as the relation. Use bin for yes/no decisions; int would also permit values such as 2 or 3 unless other bounds restrict them.

Does formatting a cell with zero decimal places make it an integer?
No. Formatting changes only the displayed appearance, not the stored value or Solver’s search. Use an integer constraint when whole-number decisions are required.

Why does Solver say it cannot find a feasible integer solution?
The limits may conflict once variables must be whole numbers, or the model may have an incorrect formula or omitted bound. Check the changing-cell range, units, objective, and each constraint; then determine whether the real-world requirements genuinely allow any whole-number combination.

Can Excel Solver handle integer variables in Excel for the web?
The Solver add-in is for the desktop Excel app. Open the workbook in desktop Excel for Windows or Mac, enable Solver if necessary, and add the integer constraint there.

Can I use Solver for a large integer optimization model?
Excel’s standard Solver is limited to 200 changing cells, and solve time depends on model complexity. For larger or difficult models, a specialized optimization tool may be more suitable.

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.