# How does Excel Solver work

Excel, a versatile and comprehensive spreadsheet software, offers much more than simple calculations and data analysis. Within Excel’s vast array of tools, Excel Solver stands out as a powerful feature that enables users to unlock optimization capabilities. With Solver, users can tackle complex problems by fine-tuning variables, adhering to constraints, and optimizing objectives. In this blog post, we will delve into the inner workings of Excel Solver, exploring its core components and uncovering its immense potential in addressing real-world optimization challenges.

Here we have a dataset that contains Grocery Items, Quantity (Kg), Cost (\$), and Total Amount. We are also given the Budget to spend on groceries but our Total Bill is going over the Budget. To get our bill equal to our budget we’ll be making some changes in the Quantity of each grocery item. We can do this by simply using the Solver feature of Excel. Follow the simple steps below to do it.

## Step – 1 Open Excel Options.

• Click on File on the top left of Excel.
• Click on Options.
• Excel Options will open.

## Step – 2 Add the solver option to the ribbon.

• In the Excel Options dialog box, click on Add-Ins.
• After that at the bottom you’ll see ‘Manage:‘, click on ‘Go’ in front of ‘Manage:‘, as shown in the gif below.
• At last click OK.

## Step – 3 Open the Solver menu.

• Open the Data tab.
• In the Analysis group click on Solver
• This will open the Solver Parameters dialog box.

## Step – 4 Defining Parameters for Solver.

• First of all in the Set Objective blank space select the target cell that we are focusing on.
• In BY Changing Variable Cells select the range of cells, where changes will be made to get the desired results.
• Now we will be adding conditions/formulas which will be used by the solver to produce the desired results, for that click on Add.

## Step – 5 Adding the conditions.

• In Cell Reference select the cell that you want to be changed (Total Bill).
• In Constraint blank space select the cell (Budget) that will be compared with the Reference value.
• Choose the condition that will be used to compare the two cells, as we wanted the Total Bill and Budget to be equal that’s why we choose the ‘=’ relational operator.
• Condition will look something like this

Total Bill = Budget

• Now we will add a second condition that will restrict Solver to change values in Quantity to less than zero to obtain the desired results.
• In the Cell Reference select the range of values in the Quantity column and in the Constraint section type 0
• In the middle choose the condition ‘>=’, as we want values in the Quantity column to be at least zero
• Second condition will look something like this:

\$B\$2:\$B\$6 >= 0

The range is of the Quantity column.