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*^{3}*+3x*^{2}*-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)*.

`=C4^3+3*C4^2-5`

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**.

*Note:**We selected the solving method as*.

**GRG Nonlinear**as itâ€™s a non-linear equationBy 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** ^{2}* 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*^{2}*h*.

`=PI()*C5^2*C4`

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.

`=2*PI()*C5*(C4+C5)`

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.

`2*$C$5`

- 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.

`=SUMPRODUCT(C5:F5,C6:F6)`

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.

`=SUMPRODUCT(C9:F9,$C$5:$F$5)`

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**.

`=SUM(C5:C8)`

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

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

`=SUMPRODUCT(C5:C8,D5:D8)`

**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 *AC*s 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*.

`=SUM(C11:F11)`

- 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**.

`=SUM(C11:C12)`

- 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,

**Sum = (n * (n^2 + 1)) / 2**

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**