Solver in Excel (Everything You Need to Know)

In the image below, we have enabled the Solver Add-in.

What is an Excel Solver?

The Excel Solver is an add-in tool in Microsoft Excel that helps you find an optimal solution for complex problems involving mathematical equations and constraints. It is primarily used for optimization and solving linear programming problems.

Definitions of terms:

Objective Cell: A single cell with a formula in it. The constraint cells’ limitations are applied to the decision-based formula in the cell. The objective cell’s value can be decreased, increased, or fixed at the provided threshold.

Variable Cells: These are made up of variable data that the Solver modifies to accomplish the goal.

Constraint Cells: These are the prerequisites that must be met.

How to Add Solver in Excel

• Go to the File tab >> select Options.

• Select Solver Add-in >> click OK.

Types of Solving Methods

1. GRG Nonlinear

Nonlinear problems can exhibit multiple feasible regions or a range of variable values that satisfy all the constraints.

2. Simplex LP

In a linear programming problem, the goal is to maximize or minimize a single objective while considering specific conditions.

The simplex LP and GRG nonlinear methods are employed to solve smooth problems.

3. Evolutionary

This approach is useful for resolving problems with jagged patterns, which involve functions that have breaks.

Further Examples of the Excel Solver

1.  Finding Maximum (GRG Nonlinear)

• Go to the Data tab >> Select Solver.

• In the Set Objective box, select cell G10 >> click on Max.
• Select cells F5:F9 in the By Changing Variables
• Click on Add >> give the Constraints.
• Click on Mark Unconstrained Variable Non-Negative >> select GRG Nonlinear.
• Click on Solve.

• In the Solver Result dialog box, select Answer >> click OK.

You can see the result in a different Excel sheet.

2. Finding Magic Square (GRG Nonlinear)

• Go to the Data tab >> Select Solver.

• Keep the Set Objective box empty >> click on Max.
• Select cells C5:E7 in the By Changing Variables
• Click on Add >> give the Constraints.
• Click on Mark Unconstrained Variable Non-Negative >> select GRG Nonlinear.
• Click on Solve.

You can see the rearranged magic square.

3. Interpreting Linear Programming (Simplex LP)

• Select Solver from the Data

• In the Set Objective box, select cell H10 >> click on Max.
• Select cells C10:E10 in the By Changing Variables
• Click on Add >> give the Constrain.
• Click on Mark Unconstrained Variable Non-Negative >> select Simple LP.
• Click on Solve.

• Select Answer >> click OK.

You can see the answer report.

4. Coordinates and Mapping with Solver (Evolutionary Solver)

• Enter the following formula in cell F5:
=SQRT((C5-OFFSET(\$C\$13,E5,0))^2+(D5-OFFSET(\$D\$13,E5,0))^2)

• Enter the following formula in cell F14:
=SUM(F5:F10)

• From the Data tab >> select Solver.
• In the Set Objective box, select cell F14 >> click on Min.
• Select cells in the By Changing Variables
• Click on Add >> give the Constrain.
• Click on Mark Unconstrained Variable Non-Negative >> select Evolutionary.
• Click on Solve.

• Click OK.

You can see the result in cell F14.

How to Load/Save Solver Solution in Excel

• To load/save a solver solution, select Load/Save in the Solver Parameters dialog box.

• Select a cell >> click on Save.

Solver in Excel: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Afia Kona

Afia Aziz Kona, a graduate of NAME from Bangladesh University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also interested... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF