Excel Solver’s Simplex LP method finds the best value for a linear objective while meeting linear constraints. In Excel for Microsoft 365 on Windows, open Data > Solver, set the objective and changing cells, add constraints, and choose Simplex LP under Select a Solving Method.
What Simplex LP Means
LP stands for linear programming: a way to maximize or minimize a linear expression subject to limits expressed as linear relationships. “Linear” means that each decision variable appears as a constant multiple, variables are added or subtracted, and there are no products of decision variables, powers such as x², or nonlinear functions such as a variable divided by another variable.
In Solver, the objective is a worksheet cell containing a formula, such as total profit or total cost. The changing cells hold the decision variables—for example, how many units of each product to make. Constraints set boundaries on those values, such as available labor, a spending limit, or a minimum production requirement. Simplex LP searches for the best feasible combination of changing-cell values for a model with linear formulas.
The method is not a general-purpose “make this formula as large as possible” button. Its usefulness depends on the spreadsheet representing the real problem with formulas that match linear programming assumptions.
When to Use Simplex LP
Choose Simplex LP when the objective formula and every constraint formula are linear in the changing cells. Common examples include allocating labor across products, blending ingredients, planning shipments, assigning production capacity, and selecting an advertising mix under a budget. A basic cost-minimization model might choose quantities to buy while meeting demand and staying within supplier limits.
For example, total contribution from two products is linear if each product has a fixed contribution per unit: 30 dollars per unit of A and 20 dollars per unit of B. Total contribution is 30 times the quantity of A plus 20 times the quantity of B. A labor limit is also linear if each unit takes a fixed number of labor hours. These relationships can be represented with SUMPRODUCT formulas and cell references.
Simplex LP is generally not suitable when the model includes genuinely nonlinear relationships, such as multiplying two changing cells together, a changing-cell exponent, or a formula whose rate changes with the decision variable. A formula may look simple but still be nonlinear: for instance, calculating revenue as price times quantity is nonlinear if both price and quantity are changing cells. If price is fixed, the expression is linear in quantity.
Enable Solver and Choose the Method
Solver is an Excel add-in in the desktop application and may need to be enabled before its command appears. In Excel for Microsoft 365 on Windows, use these steps:
- Choose File > Options > Add-ins.
- At the bottom, set Manage to Excel Add-ins and choose Go.
- Select Solver Add-in and choose OK. If Excel asks to install it, follow the prompt.
- Open the Data tab and select Solver, usually in the Analyze group.
- In Solver Parameters, choose the objective cell, the changing cells, and the constraints. Set Select a Solving Method to Simplex LP.
Solver is not available in Excel for the web in the same way as the desktop add-in, so open the workbook in desktop Excel to use it. On Excel for Mac, enable Solver through Tools > Excel Add-ins, select Solver Add-in, and then use Data > Solver. Ribbon placement and dialog details can differ slightly by platform or update.
Build a Small Linear Model
Consider a workshop making products A and B. Each unit of A contributes $30 and uses two labor hours and one unit of material. Each unit of B contributes $20 and uses one labor hour and two units of material. The workshop has 100 labor hours and 80 units of material available. The goal is to maximize contribution, with no demand limits or setup costs in this simplified example.
Put product names in A2:A3, quantities (the changing cells) in B2:B3, contribution per unit in C2:C3, labor per unit in D2:D3, and material per unit in E2:E3. Enter 30 and 20 in C2:C3, 2 and 1 in D2:D3, and 1 and 2 in E2:E3. Put the available labor, 100, in F6 and available material, 80, in F7.
With quantities in B2:B3 and contribution values in C2:C3, enter this objective formula in B5:
=SUMPRODUCT(B2:B3,C2:C3)
With quantities in B2:B3 and labor use per unit in D2:D3, enter this labor-use formula in B6:
=SUMPRODUCT(B2:B3,D2:D3)
With quantities in B2:B3 and material use per unit in E2:E3, enter this material-use formula in B7:
=SUMPRODUCT(B2:B3,E2:E3)
In Solver, set B5 as the objective and choose Max. Set B2:B3 as the changing variable cells. Add B6 <= F6 and B7 <= F7 as constraints, and ensure quantities cannot go below zero. You can either add B2:B3 >= 0 explicitly or use the nonnegative-variable option. The continuous optimum is 40 units of A and 20 units of B: it uses 2 × 40 + 1 × 20 = 100 labor hours and 1 × 40 + 2 × 20 = 80 material units. The objective is 30 × 40 + 20 × 20 = $1,600.
Check the Model Before Solving
Before selecting Solve, confirm that the worksheet formulas and Solver settings describe the same problem. The objective should point to the cell with the result to optimize, not to a changing cell or a label. The changing-cell range should contain only decision variables, not calculated totals or fixed inputs. Constraints should compare calculated usage or output with the correct limit cells.
For a linear model, each objective and constraint formula should remain linear as the changing cells vary. SUMPRODUCT is often convenient when it multiplies each decision quantity by a fixed coefficient and adds the results. Make sure coefficients are fixed input values, not other decision variables. Also confirm units: hours should be compared with hours, dollars with dollars, and weekly quantities with weekly limits.
Use clear labels and separate input values, decision cells, and calculated totals. This makes it easier to spot an accidental reference to the wrong row or a missing constraint. If a product must be made in whole units, add an integer constraint for its decision cell; nonnegativity alone permits fractions.
Read the Solver Result
When Solver reports a solution, review the values written to the changing cells and check whether the result is feasible and sensible. In the example, the resource totals should not exceed their limits, and the objective should equal $1,600. You can independently verify these calculations with the worksheet formulas rather than relying only on the dialog message.
A constraint that is exactly at its limit is called binding; one with unused capacity is nonbinding. In the example, both labor and material are binding. This means neither resource has spare capacity at the reported solution. A solution can still be optimal when some constraints are nonbinding, because another constraint or the objective’s economics may determine the best mix.
Solver may offer to keep the solution or restore the original values. Choose the option that matches your intent, then save the workbook if you want to retain the result. Solver’s answer depends on the model and its inputs; it does not validate whether the assumptions, data, or business interpretation are appropriate.
Common Problems and Fixes
- Solver says the model is nonlinear. Inspect formulas involving changing cells for multiplication between variables, powers, variable denominators, or nonlinear functions. Replace the relationship with a valid linear formulation only if that simplification reflects the real problem; otherwise try a nonlinear solving method.
- The result violates an expected limit. Check that the relevant constraint was added and that its left- and right-hand cell references point to the intended totals and capacities. Confirm the comparison direction: a capacity is usually usage less than or equal to availability, while a minimum requirement often uses greater than or equal to.
- The result includes negative quantities. Add nonnegativity constraints or enable the nonnegative-variable setting. Do not assume every model automatically prevents negative values.
- Solver reports no feasible solution. The limits may conflict, a minimum may exceed a maximum, or a constraint may have been entered incorrectly. Temporarily review constraints one by one and test whether any combination of values can satisfy them. Do not remove a genuine requirement just to obtain a result.
- The answer looks odd or fractional. LP models allow fractional values unless you impose integer restrictions. If units must be whole items, add an integer constraint to the relevant changing cells and solve again. Integer restrictions can make the search harder and may change the best solution.
Small rounding differences can also make displayed values appear to miss a limit. Keep enough precision in the underlying values and formulas, and distinguish display formatting from actual cell values. Avoid rounding decision variables inside formulas unless rounding is part of the real-world rule.
Simplex LP Compared with Other Solver Methods
Excel Solver provides other methods for problems that do not meet Simplex LP’s linear assumptions. GRG Nonlinear is intended for smooth nonlinear models, while Evolutionary can handle models with more discontinuous or irregular relationships, though it may require more setup and does not guarantee the same kind of result as a linear program. The appropriate choice depends on the structure of the formulas, not on which method sounds more powerful.
A model with integer constraints deserves a careful distinction. Its underlying objective and resource relationships may be linear, but requiring whole-number decisions makes it an integer optimization problem rather than an ordinary continuous LP. Solver can use integer constraints with the Simplex LP selection, but the integrality requirement adds a discrete search component. If a result does not need whole units, leaving variables continuous can make the model simpler; if whole units are essential, do not accept fractional answers as practical recommendations.
Limits and Useful Alternatives
Solver is useful for small and medium-sized spreadsheet models, but a workbook model can become difficult to maintain as decision variables, constraints, and special rules accumulate. Large models may also be limited by the Solver add-in’s capabilities or by the time needed to find a result. Keep the model compact, document assumptions, and test how results change when important inputs change.
For a straightforward allocation problem, Excel Solver is often the most accessible option because the model and its inputs remain visible in worksheet cells. For more extensive optimization work, organizations may use dedicated optimization software or programming tools that provide additional model controls, performance, and reproducibility. Those alternatives still require a correctly specified model; changing software does not fix incorrect assumptions.
Frequently Asked Questions
What does Simplex LP mean in Excel Solver?
It is Solver’s method for linear programming problems: maximizing or minimizing a linear objective while satisfying linear constraints. LP means linear programming.
Where is Simplex LP in Excel?
Open Data > Solver, then choose Simplex LP from Select a Solving Method in the Solver Parameters dialog. If Solver is missing, enable the Solver Add-in through Excel’s add-in settings.
Why does Excel Solver say my model is nonlinear?
One or more formulas may use nonlinear relationships between changing cells, such as multiplying two decision variables or raising a variable to a power. Review the objective and constraint formulas and choose a method suited to the actual model if it cannot be expressed linearly.
Can Simplex LP handle integer constraints?
Solver can apply integer restrictions to changing cells in a model using the Simplex LP method, but the problem then includes discrete decisions. The result may take longer to find, and whole-number restrictions can change the optimum.
Is Excel Solver available in Excel for the web?
The desktop Solver add-in is not available in Excel for the web in the same way. Use desktop Excel for Windows or Mac to enable and run Solver.
Related
- Excel Slicer No Connections Found? Causes and Fixes
- Excel Slicer Not Updating? Fixes for PivotTables and Tables
- Excel Slicer Horizontal: How to Arrange Buttons in Rows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Named Range for a Table Column: How It WorksIf your Excel table is named SalesTable and has a column headed Revenue, refer to the whole column…
- How to Add a Shared Mailbox in New Outlook for WindowsTo add a shared mailbox in New Outlook for Windows, open Mail, right-click your account name in the…
- Excel Not Moving From Cell to Cell? Fixes That WorkIf Excel is not moving from cell to cell when you press the arrow keys, turn off Scroll…
- Goal Seek in Excel: Definition, Steps, and ExamplesGoal Seek in Excel is a What-If Analysis tool that finds the input value needed to make a…
- Can You Make a Flowchart in PowerPoint? Steps and TipsYes, you can make a flowchart in PowerPoint using either individual shapes and connectors or a SmartArt process…
- Excel Named Range Table: How They Work and When to UseIn Excel, a “named range table” is not one combined feature: a named range gives a name to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.