This article explains how to use Microsoft Excel's Solver tool, which allows you to alter variables in a spreadsheet to achieve the desired solution. You can use it in the Windows and Mac versions of the program, but you need to enable it first.
Steps
Part 1 of 2: Enable the Solver
Step 1. Open Excel
Click once or twice on the app icon, which looks like a green square with a white "X" inside.
Solver is pre-installed in the Windows and Mac versions of Excel, but you must manually enable it
Step 2. Click on Blank Workbook
This will open the Excel window and you can proceed with activation.
Step 3. Click on File
It is a tab in the upper left part of the Excel window.
On Mac, click instead Tools, then skip the next step.
Step 4. Click Options
This item is one of the last in the menu File. Press it and the Options window will open.
Step 5. Click Add-ons
It is a tab at the bottom left of the Options window.
On Mac, click on Excel add-ins in the menu Tools.
Step 6. Open the "Available Add-ons" window
Make sure "Excel Add-ins" is in the "Manage" text field, then click Go At the bottom of the page.
On Mac, you can open this window by clicking on Excel add-ins in the menu Tools.
Step 7. Install the Solver component
Check the "Solver" box in the center of the page, then click OK. The Solver should appear as a tool on the tab Data at the top of Excel.
Part 2 of 2: Using the Solver
Step 1. Learn how to use Solver
This tool can analyze your spreadsheet data and any constraints you have added to show you possible solutions. It is very useful if you are doing calculations with multiple variables.
Step 2. Add the data to the spreadsheet
To use the Solver, your sheet must contain data with some variables and a solution.
- For example, you can create a sheet that documents all your expenses over the course of a month, where the result is the remaining money.
- You cannot use Solver on a sheet that does not contain solvable data (for example, it will not work if the data does not contain equations).
Step 3. Click on the Data tab at the top of the Excel window
The toolbar will open Data.
Step 4. Click on Solver
You will find this entry on the far right of the toolbar Data. Press it and the Solver window will open.
Step 5. Select the target cell
Click on the cell where the Solver solution should appear. You'll see it appear in the "Set Goal" box.
For example, if you are creating a budget where the ultimate goal is your monthly income, click on the final cell "Income"
Step 6. Set a goal
Check the "Value of" box, then enter your target value in the text field next to it.
- For example, if your goal is to save € 200 at the end of the month, type 200 in the text field.
- You can also check the "Max" or "Min" boxes to instruct the Solver to determine the absolute maximum or minimum value.
- Once a goal has been decided, the Solver will try to achieve it by modifying the variables in the spreadsheet.
Step 7. Add constraints
Constraints impose restrictions on the values that the Solver can use, so that one or more values on the sheet are not accidentally canceled. You can add a constraint as follows:
- Click on add;
- Click on the cell (or select the cells) for which the constraint is to be applied;
- Select a type of constraint from the drop-down menu in the center;
- Enter the value of the constraint (for example a maximum or a minimum);
- Click on OK.
Step 8. Run the Solver
Once you have added all the constraints, click Solve at the bottom of the Solver window. This way the tool will find the optimal solution to the problem.
Step 9. Check the results
When the Solver warns you that it has found a result, you can analyze the spreadsheet to see which values have been changed.
Step 10. Change the Solver criteria
If the result you have obtained is not ideal, click on Cancel in the window that appears, then modify the objective and constraints.
