Optimization in Excel (A Complete Guide)

In this article, we serve as your guide to optimization in Excel. We will discuss the Solver and its activation in Excel. We will explore how to solve linear optimization models in Excel. Additionally, we will dive into examples that show the application of Excel’s Solver tool for optimization tasks.

With Excel’s Solver, users can optimize a wide range of parameters, such as production quantities, investment portfolios, resource allocations, scheduling, and more.

Here, we’ll show 6 different aspects of optimization in Excel through examples. So, come along as we explore the world of optimization in Excel one by one.

Download Practice Workbook

You may download the following attached workbook and follow it while going through the article. It’s also helpful for your practice.

What Is Solver in Excel?

Solver is like a special helper that you can add to your computer program like Excel. Actually, it’s an add-in. It’s really good at solving tricky problems by finding the best answer using math. This is made to solve problems where you want to find the maximum or minimum value while following some restrictions.

It is particularly good at solving linear programming problems, which are all about finding the best solution to maximize or minimize something. That’s why some people also refer to it as a linear programming solver.

But that’s not all it can do! It can also handle other types of problems that involve curves and bumpy situations. So, whether your problem is straightforward or a bit more complicated, Excel Solver is there to assist you.

How to Enable Solver in Excel?

• First, open the Excel Options window by clicking File >> Options.
• Select Add-ins on the left side panel.
• Then, choose Excel Add-ins in the Manage option and click on Go….

• In the Add-ins dialog box, check the box of Solver Add-in and click OK.

6 Examples to Use Solver for Optimization in Excel

1. Use Solver for Optimization Without Constraints

Here, we’ll minimize the value of f(x) where f(x) = x³+3x²-5. In this example, there are no constraints that we are using.

• In cell C7, we entered the following formula to replicate the equation of f(x).

Here, C4 denotes the value of x.

• Then, navigate to the Data tab >> Solver option on the Analyze group.

It’ll open the Solver Parameters dialog box.

• In the Set Objective box, select cell C7 and choose Min in the below section as we want to minimize its value.
• Select cell C4 in the By Changing Variable Cells box and click Solve.

By the end, the value of x in cell C4 is converted to 0 and the minimum value of f(x) is achieved.

2. Optimization Under Constraints with Solver in Excel

In the previous example, there was no constraint. Here, we’ll see the usage of constraints in the solver for optimization in Exel.

In this case, we have 2300 cm² of metal sheet in our hand. From this, we have to make a cylinder whose Height should be double its Radius. We have to optimize the system so that the Volume gets maximized.

• The given data and constraints are present in cells C9, and C10.

• To determine the Volume, we input the formula below in cell C7. The mathematical form of the formula is, V = πr²h.

Here, the PI function means π which value is 3.1416. C4 and C5 cells denote the values of height and radius respectively.

• In cell C8, we insert the formula of the total surface area of a cylindrical object. The mathematical form of the formula is, A = 2πr(h+r). Here A serves as the total surface area.

In the Solver Parameters dialog box,

• Select cell C7 in the Set Objective box and choose Min in the To section.
• In the By Changing Variable Cells box, select cells in the C4:C5 range. Then, click on Add to add constraints.

• In the Add Constraint dialog box, select C4 as Cell Reference, select the Equal sign, and write down the following in the Constraint box.

• Then, click on Add for adding more constraints.

• Again, select C8 as Cell Reference, select the Equal sign, and select cell C9 in the Constraint box. Lastly, click OK.

• You can find the added constraints in the Subject to the Constraints box in the following image.
• Select GRG Nonlinear in the Select a Solving Method section and click Solve.

• In the Solver Results dialog box, check the Keep Solver Solution option and click OK.

Your expected result is here. The maximum achievable Volume with this area of the metal sheet is visible in cell C7 and its regarding Height and Radius are also here.

3. Maximizing Profit by Optimization in Product Mix

Here, we have the profit for each product of 4 types in the C6:F6 range. The required amount of material for each product type is available in the C9:F12 range. And the available amount of each material is here under the Available Resource column (Column H).

Now, we’ll calculate the quantity of each product to be produced using the given resources. And our prime objective is to maximize the profit from this production.

• To calculate the Total Profit in cell G6, enter the formula below.

The SUMPRODUCT function returns the sum of the products of the corresponding values from all the arrays.

• Then, enter the following formula in cell G9 to calculate the amount of used resources for each material available.

In the Solver Parameters dialog box,

• Select cell G6 in the Set Objective box and choose Max in the To section.
• In the By Changing Variable Cells box, select cells in the C5:F5 range.
• The constraints are described in the image and we selected the Simplex LP method for solving.
• Click Solve.

Excel calculated the product quantity, used resources, and maximum total profit at a glance.

Read More: How to Calculate Optimal Product Mix in Excel

4. Profit Maximization in Retail Stores with Optimization

We have several products from a retail electronics store. There are cost/unit, inventory/unit, and profit/unit are available under columns D, E, and F. Also, the total capacity of inventory and the total capital (cash in hand) are given in the D11:D12 range.

Our aim is to maximize the total profit by using these resources.

• The total unit of products is calculated by the following formula in cell C9.

The SUM function adds all the numbers in each range of cells.

• We calculated the total cost with the following formula in cell D9.

Similarly, we calculated the total inventory and total profit in cells E9 and F9.

In the Solver Parameters dialog box,

• Select cell F9 in the Set Objective box and choose Max in the To section.
• In the By Changing Variable Cells box, select cells in the C5:C8 range.
• Constraints are: every product quantity should be equal to or greater than zero. So, it must be non-negative. The total cost of buying products should be less than the total capital (cash in hand). The total occupied inventory should be less than the total available inventory.
• We selected the Simplex LP method for solving.
• Click Solve.

So, we can see that, only by buying ACs he can make the most profit. But he has to expand his inventory as he could spend only one-third of his capital.

5. Logistics Cost Optimization with Excel Solver

In the C6:F7 range, we have the transportation fees for 4 different stores from 2 different storage houses. The available products in each storage are also available in the H11:H12 range. We can also find the demand for each store (retail store) in the C14:F14 range.

We should meet the demand of each store and make the logistics cost the bare minimum.

• The following formula in cell G11 determines the total shipped products from Storage 1.

• A similar thing can be done for Storage 2 in cell G12.

• For calculating the total received products of Store 1 from both storages, enter the following formula in cell C13.

• Do the same thing for the 3 remaining stores in cells D13:F13.

In the Solver Parameters dialog box,

• Select cell D16 in the Set Objective box and choose Min in the To section.
• In the By Changing Variable Cells box, select cells in the C11:F12 range.
• Constraints are described in the following image and we selected the Simplex LP method for solving.
• Click Solve.

Our optimization in Excel is done.

Read More: How to Perform Route Optimization in Excel

6. Create Magic Square with Solver in Excel

What is Magic Square?

A magic square is like a puzzle that consists of a square grid. You can imagine it as a square box divided into smaller boxes or cells. Each cell in the grid contains a different number, usually a whole number.

The remarkable thing about a magic square is that if you add up the numbers in any row (going from left to right), any column (going from top to bottom), or either of the two main diagonals (the diagonal lines that go from one corner of the square to the opposite corner), the total will always be the same.

It’s like magic because the sum will always be equal no matter which row, column, or diagonal you choose.

How to Create Magic Square

Here, we opt to create a 4*4 magic square. So there would be 1 to 16 numbers in the 16 different cells. We calculated the sum of rows in the H6:H9 range. Also, there is the sum of columns in the C11:F11 range. And, the diagonal sums are in the C13:C14 range.

For a 4*4 magic square, the sum for any columns or rows has to be,

Here, n represents the size of the magic square, which is the number of cells in each row or column.

In the Solver Parameters dialog box, we inserted the following constraints as in the image below.

• Click Solve.

Magically, Excel created the magic square. You can apply the same procedure for magic squares of any dimension.

Algorithms of Excel Solver

When you’re working with the Excel Solver and need to define a problem, you have the option to choose from different solving methods in the dropdown box called “Select a Solving Method“. Let me explain what each of these methods does:

GRG Nonlinear: This method utilizes the Generalized Reduced Gradient (GRG) Nonlinear algorithm. It is specifically designed for tackling problems that involve smooth nonlinear functions. In other words, if your problem includes at least one constraint that is a smooth nonlinear function of the decision variables, this method can be useful.

LP Simplex: The LP Simplex method is based on the Simplex algorithm, which was developed by an American mathematician named George Dantzig. It is employed for solving Linear Programming (LP) problems. Linear Programming deals with mathematical models where the requirements can be described by linear relationships. These models usually consist of a single objective represented by a linear equation that needs to be either maximized or minimized.

Evolutionary: The Evolutionary method is suited for non-smooth problems, which are generally the most challenging type of optimization problems to solve. Non-smooth problems often involve functions that are not smooth or even discontinuous. In such cases, determining the direction in which a function is increasing or decreasing becomes difficult. The Evolutionary method is tailored to handle these complex optimization problems.

By selecting the appropriate solving method based on the nature of your problem, you can leverage the power of Excel Solver to find optimal solutions efficiently.

Things to Remember

• Enable the Solver Add-in before starting work on optimization.
• Make sure to select the appropriate solving method for your problem. Otherwise, the result may differ.
• If you find that the Excel Solver has been working on a specific problem for a while and you want to stop it, you can simply press the Esc key to interrupt the process.

Frequently Asked Questions

1. What is the objective function in optimization?

The objective function in optimization is a mathematical expression that defines the quantity you want to maximize or minimize. It represents the goal you are trying to achieve.

2. What are the constraints in optimization?

Constraints are the limitations or restrictions imposed on the decision variables in an optimization problem. They define the feasible region or the set of valid solutions.

3. Can Excel Solver handle large-scale optimization problems?

While Excel Solver is capable of solving moderately large optimization problems, its performance may be limited when dealing with very large-scale or complex problems. In such cases, specialized optimization software or programming languages designed for optimization, like MATLAB or Python, may be more suitable.

Conclusion

In conclusion, we tried to cover the definition of optimization in Excel, what is solver, and how to enable it in Excel. Also, we showed 6 different examples of using a solver in an optimization problem in Excel.

Excel’s optimization capabilities extend beyond Solver, with additional features like Goal Seek, Scenario Manager, and Data Tables. These tools allow users to analyze sensitivity, perform what-if analysis, and understand how changes in variables impact outcomes.

So, that’s all for today. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Optimization in Excel: Knowledge Hub

<< Go Back to Solver in Excel | Learn Excel