Excel Solver for Data Analysis: Setup, Models, and Limits

How-to basics · Updated November 18, 2026

Excel Solver is an optimization add-in: it changes selected input cells to maximize, minimize, or reach a target value in a formula while respecting constraints. In Microsoft 365 for Windows, enable it through File > Options > Add-ins, choose Excel Add-ins beside Manage, select Go, and check Solver Add-in; then open Data > Solver.

What Excel Solver does

Solver answers questions that involve finding the best combination of values, rather than simply calculating a result from values you already know. You define an objective cell containing a formula, identify the cells Solver may change, and specify limits those values must obey. Solver searches for a set of inputs that satisfies the limits and improves the objective.

For example, a business might want to choose how many units of several products to make in order to maximize contribution, without using more labor or materials than it has. A household might compare combinations of tasks under a time limit, or an analyst might find a mix that meets requirements at the lowest cost. Solver does not decide what counts as best: the objective formula and constraints you provide define the question.

Solver is not the same as Excel’s Analysis ToolPak. The ToolPak offers statistical and engineering analyses, such as regression and descriptive statistics. Solver is for optimization. It can use formulas in a workbook, but it does not automatically interpret a data table or infer your business rules.

When Solver is useful

Use Solver when there are multiple possible input values, a measurable goal, and restrictions that can be expressed mathematically. Typical situations include product mix, staffing, delivery allocation, budget allocation, and selecting quantities subject to capacity limits. It can also solve some nonlinear models, such as maximizing a formula that includes a product of decision variables.

It is usually unnecessary for a simple one-input question. If you need to find one input that makes one formula equal a target, Goal Seek is simpler. Solver becomes useful when more than one input can change or when several constraints must be met. Before building a model, check that each requirement can be represented as a formula or a constraint; a vague preference such as “keep the plan balanced” needs a numerical definition before Solver can use it.

Enable Solver and open it

On Excel for Microsoft 365 for Windows, use these steps:

  1. Select File > Options > Add-ins.
  2. At the bottom, set Manage to Excel Add-ins and select Go.
  3. Check Solver Add-in and select OK. If Windows asks to install the add-in, allow it to complete.
  4. Open the Data tab and select Solver, usually in the Analysis group.

In Excel for Mac, use Tools > Excel Add-ins, select Solver Add-in, and then open Solver from the Data tab. The labels and placement can vary slightly by Mac release. Solver is not available as a working Solver add-in in Excel for the web; open the workbook in the desktop app to use it. Once enabled, the add-in is available for Excel workbooks on that desktop installation.

Build a small product-mix model

A useful model separates decision variables, input data, calculated results, and limits. Suppose a shop makes products A and B. Each A earns $40 and uses two labor hours and one unit of material. Each B earns $30 and uses one labor hour and two units of material. The shop has 100 labor hours and 80 material units. The goal is to maximize total contribution.

Set up the worksheet with product names in A2:A3, the quantities to produce in B2:B3, contribution per unit in C2:C3, labor per unit in D2:D3, and material per unit in E2:E3. Put totals in F5:F7: total contribution in F5, labor used in F6, and material used in F7. Enter starting values such as zero in B2:B3; these are the changing cells Solver will adjust.

With quantities in B2:B3 and contribution values in C2:C3, enter this in F5 to calculate total contribution:

=SUMPRODUCT(B2:B3,C2:C3)

With quantities in B2:B3 and labor requirements in D2:D3, enter this in F6 to calculate labor used:

=SUMPRODUCT(B2:B3,D2:D3)

With quantities in B2:B3 and material requirements in E2:E3, enter this in F7 to calculate material used:

=SUMPRODUCT(B2:B3,E2:E3)

These formulas return zero when both quantities start at zero. The Solver model will maximize F5 by changing B2:B3, subject to F6 being no greater than 100, F7 being no greater than 80, and both production quantities being nonnegative. If quantities must be whole products, add an integer constraint for B2:B3. For this example, the best whole-number solution is 40 units of A and 20 of B: it earns $2,200, uses all 100 labor hours, and uses all 80 material units. The contribution calculation is 40 × $40 plus 20 × $30.

Set the objective, changing cells, and constraints

Choose Data > Solver to open Solver Parameters. In Set Objective, select F5. Choose Max because the goal is to maximize contribution. In By Changing Variable Cells, select B2:B3. Then add constraints using Add:

  • F6 <= 100, for the labor-hour limit.
  • F7 <= 80, for the material limit.
  • B2:B3 >= 0, to prevent negative production.
  • Optionally, set B2:B3 to int if production must be whole units.

Solver can also apply a nonnegative restriction through the Make Unconstrained Variables Non-Negative option. Explicit constraints are useful when you want the model’s assumptions to be visible in the constraint list. Choose a solving method, select Solve, and review the result dialog. If the result is suitable, choose Keep Solver Solution; otherwise choose Restore Original Values and revise the model. Save the workbook if you want to preserve the worksheet and its Solver setup.

Choose a solving method

The solving method matters because different methods suit different formula shapes. For a linear model like the product-mix example, choose Simplex LP. A model is linear when its objective and constraints use linear relationships between changing cells—for instance, fixed per-unit contributions and resource use. Multiplying two changing cells together, or using a changing cell as an exponent, makes the model nonlinear.

  • Simplex LP: for linear optimization models. It is generally the right choice for allocation models with fixed coefficients and linear limits.
  • GRG Nonlinear: for many smooth nonlinear models, where formulas change continuously as inputs change.
  • Evolutionary: for models with discontinuities, non-smooth formulas, or certain logical relationships that are difficult for the other methods.

These methods search in different ways, so the first result is not automatically proof that a complicated model has found the best possible answer. For nonlinear or Evolutionary models, try reasonable starting values and review whether the solution makes practical sense. Do not select a method just because it sounds more powerful; a model that is genuinely linear is usually clearer and easier to solve with Simplex LP.

Check and interpret the result

After Solver reports a solution, verify the formulas and constraints in the worksheet. Confirm that quantities are sensible, units match, and all limits are met. A result can be mathematically valid but operationally unrealistic—for example, if the model permits fractional people, products, or machine runs. Add integer or binary constraints where the real decision requires whole numbers or yes/no choices.

For a successful linear model, Solver can offer reports such as Answer and Sensitivity. A Sensitivity report can help explain how changes in certain coefficients or resource limits affect the solution, subject to the assumptions of the model; it is not a forecast and does not repair incorrect input data. Sensitivity analysis is not available for every method or model type. Treat any report as a way to inspect the mathematical model, not as a substitute for checking the source data and assumptions.

Common Solver problems and limits

If Solver is missing from the Data tab, the add-in may not be enabled in desktop Excel. If a model reports that it cannot find a feasible solution, inspect the constraints for contradictions. For instance, requiring at least 60 labor hours of work when the model’s maximum possible labor use is 50 makes the conditions impossible. Check cell references too: a constraint tied to the wrong total can make Solver solve a different problem than the one you intended.

If Solver returns an unexpected answer, confirm that the objective cell contains a formula linked to the changing cells. Check whether nonnegative, integer, or binary restrictions are needed, and make sure the selected solving method fits the model. A formula error, text value, or accidental blank in an input range can also disrupt calculations. For nonlinear models, different starting values may lead to different results, so compare plausible solutions and review the formulas.

The standard Excel Solver add-in supports up to 200 changing variable cells. That can be sufficient for a compact planning model but restrictive for larger optimization problems. Large models may need specialist optimization software or a coding-based solver. Solver also optimizes only the model you give it: omitted costs, constraints, or real-world conditions will not be considered unless you represent them.

Solver compared with other Excel tools

Goal Seek changes one input cell to make a formula reach a specified value; it does not manage a set of multiple decision variables and constraints like Solver. Data Tables are useful for comparing formula outcomes across sets of input values, but they do not search for the best feasible combination. PivotTables summarize and explore data; they do not optimize decisions. The Analysis ToolPak performs statistical or engineering procedures rather than constrained optimization.

For work that exceeds Solver’s size or modeling capabilities, alternatives include dedicated optimization software and programming tools with optimization libraries. Those options can handle larger or more specialized problems, but usually require more setup and model knowledge. For a modest workbook where the decision variables, objective, and constraints are transparent, Solver is often a convenient place to start.

Frequently Asked Questions

Is Excel Solver free?
Solver is included as an add-in with desktop Excel; it does not require a separate Solver purchase. Access depends on having a compatible desktop Excel installation and enabling the add-in. It is not available as a usable Solver add-in in Excel for the web.

Is Solver the same as the Analysis ToolPak?
No. Solver searches for input values that optimize an objective under constraints. The Analysis ToolPak provides statistical and engineering analysis tools. They address different kinds of questions, though both are Excel add-ins.

Can Excel Solver handle integer or yes/no decisions?
Yes. Add an int constraint for whole-number decisions or a bin constraint for binary decisions, using the relevant changing cells. Binary decisions represent choices such as whether to open a location, where zero means no and one means yes.

Why does Solver say it cannot find a feasible solution?
Usually the constraints cannot all be satisfied together, or a formula or cell reference is incorrect. Check each requirement against the formulas and look for conflicting minimums, maximums, or resource limits. Confirm that Solver is changing the intended cells.

Can Solver find the best answer every time?
It can solve many well-formed models, but results depend on the model structure and selected method. Linear models using Simplex LP have different solving characteristics from nonlinear models, where starting values and the shape of formulas can matter. Always validate the result against the real-world assumptions.

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.