What Solver does and when you need it

Solver is a built-in Excel tool that finds the best answer to a problem by testing many combinations of numbers at once. Instead of you manually changing values to see what happens, Solver does the testing automatically and tells you which combination gets you closest to your goal.

You use Solver when you have a spreadsheet with a target number you want to reach (or maximize, or minimize), and you want to know what other numbers need to change to get there. A small business might use it to figure out how many units to produce at each price point to hit a profit target. A household budget might use it to see what combination of spending cuts and income increases would eliminate debt by a certain date. A project manager might use it to find the fastest schedule that stays within a budget.

Solver is different from a straightforward formula because it works backward: you tell it the goal, and it finds the inputs. It is also different from a pivot table or chart, which show you what your data looks like — Solver shows you what your data could look like if you change it the right way.

Key Takeaways

  • Solver lives in the Data tab under Analysis on Windows, and under the Tools menu on Mac; you may need to turn it on first in your Excel settings.
  • You need three things to set up Solver: a target cell with a formula, cells you want Solver to change, and constraints that set limits on what Solver can do.
  • Solver tests combinations of numbers until it finds one that reaches your goal or gets as close as possible, then shows you the answer in your spreadsheet.
  • If Solver cannot find an answer, it usually means your constraints are too strict, your target is impossible, or your spreadsheet has a mistake in the formula.

Where to find Solver and how to turn it on

On Windows, Solver is in the Data tab at the top of Excel. Look for a button labeled "Solver" in the Analysis group on the right side of the ribbon. If you do not see it, you need to turn it on first.

To turn on Solver on Windows: Click File, then Options, then Add-ins. At the bottom of the window, change the dropdown from "Excel Add-ins" to "Manage: Excel Add-ins" and click Go. Check the box next to Solver Add-in and click OK. Solver will now appear in your Data tab.

On Mac, Solver is under the Tools menu at the top. If you do not see it, click Tools, then Excel Add-ins, find Solver in the list, and click OK. On Mac, Solver may take a moment to load the first time you use it.

If you use Excel online (the browser version), Solver is not available. You will need to read and install the desktop version of Excel, or use a different tool like Google Sheets' Solver extension.

The three things Solver needs from you

Before you open Solver, your spreadsheet must have three pieces in place: a target cell, changing cells, and constraints.

The target cell is the cell with the formula that holds your goal. This cell must contain a formula, not just a number. For example, if you want to maximize profit, the target cell would contain a formula that calculates profit (revenue minus costs). Solver will change other numbers in your spreadsheet to make this cell as large, as small, or as close to a specific number as you tell it to.

The changing cells are the cells Solver is allowed to modify. These are usually the inputs to your formula — the numbers that feed into your target cell. If your target cell calculates profit based on price and quantity sold, then price and quantity are your changing cells. You tell Solver which cells to change, and it tests different values in those cells to reach your goal.

The constraints are the rules that limit what Solver can do. A constraint might say "price cannot be less than $10" or "total spending cannot exceed $5,000" or "production cannot be more than 500 units per month." Constraints keep Solver from finding an answer that is technically correct but not realistic. If you do not set constraints, Solver might suggest an answer that breaks your business rules.

How to set up a straightforward Solver problem

Here is a concrete example. Say you run a small bakery and want to know how many loaves and how many pastries to bake each day to hit a profit target of $200, given that loaves cost $2 to make and sell for $6, and pastries cost $1 to make and sell for $4.

First, build your spreadsheet. Put labels in column A: "Loaves made," "Pastries made," "Loaf revenue," "Pastry revenue," "Total revenue," "Loaf cost," "Pastry cost," "Total cost," and "Profit." In column B, put the numbers or formulas. For example, B1 might be 10 (loaves you plan to make), B2 might be 20 (pastries), B3 would be the formula =B1*6 (loaves times selling price), B4 would be =B2*4, B5 would be =B3+B4, B6 would be =B1*2, B7 would be =B2*1, B8 would be =B6+B7, and B9 would be =B5-B8 (profit). Your target cell is B9.

Now open Solver. Click Data, then Solver. In the "Set Objective" field, type B9 (or click the cell). Under "To," select "Value Of" and type 200 (your profit goal). In the "By Changing Variable Cells" field, type B1:B2 (the loaves and pastries you can change). Click "Add" to add a constraint. Set B1 to be greater than or equal to 0 (you cannot bake negative loaves), and add another constraint that B2 is greater than or equal to 0. You can also add constraints like "B1 must be less than or equal to 50" if you cannot bake more than 50 loaves. Click Solve.

Solver will test combinations of loaves and pastries until it finds a mix that produces a profit of $200 (or as close as possible). When it finishes, it shows you the answer. You can accept it (which updates your spreadsheet) or reject it (which leaves your spreadsheet unchanged).

What to do when Solver cannot find an answer

Sometimes Solver will say "Solver could not find a solution" or will give you an answer that does not seem right. This usually means one of three things: your constraints are too strict, your goal is impossible, or your spreadsheet has an error.

If your constraints are too strict, Solver has no room to work. For example, if you tell Solver to make profit equal $200 but also say you can only bake 2 loaves and 2 pastries, the math might not allow it. Loosen your constraints and try again. Remove the ones that are not essential, or make them less restrictive.

If your goal is impossible, Solver will tell you. For example, if you want profit to be $200 but your costs are already $300, no amount of changing quantities will work. Check your formulas and your goal to make sure they make sense together.

If your spreadsheet has an error, Solver may give you a nonsensical answer or fail silently. Check that your target cell contains a formula (not a static number), that your changing cells are referenced in that formula (directly or indirectly), and that all your formulas use the correct cell references. A common mistake is forgetting to link the changing cells to the target cell, so Solver has nothing to optimize.

Solver settings you might need to adjust

When you open Solver, you see a few options beyond the basic setup. The "Select a Solving Method" dropdown at the bottom lets you choose how Solver searches for an answer. The default is "GRG Nonlinear," which works for most business problems. If your spreadsheet uses only straightforward addition, subtraction, multiplication, and division (no complex functions), you can try "Simplex LP" instead, which is faster. Leave this on the default unless you have a reason to change it.

You can also set a time limit under "Engine" or "Options" (the name varies by Excel version). If Solver is taking too long, you can tell it to stop after a certain number of seconds and give you the best answer it has found so far. For most small spreadsheets, Solver finishes in under a second, so you will not need this.

Do not change the other settings unless you are familiar with optimization math. The defaults work for nearly all real-world spreadsheet problems.

Common mistakes and how to avoid them

One mistake is forgetting to make your target cell a formula. Solver needs a formula to optimize. If your target cell is just a number, Solver will not work. Check that the cell starts with an equals sign.

Another mistake is setting constraints that contradict each other. For example, telling Solver that a cell must be greater than 100 and also less than 50 at the same time. Solver will say no solution exists. Review your constraints to make sure they do not conflict.

A third mistake is changing cells that are not actually used in the target formula. If you tell Solver to change cell C5, but C5 does not affect your target cell (either directly or through other formulas), Solver will not change it, because changing it would not move you toward your goal. Make sure every changing cell is connected to your target cell through your formulas.

Finally, do not assume Solver's answer is the only right answer or the best answer in the real world. Solver finds an answer that meets your constraints and reaches your goal mathematically. But it does not know about things like customer demand, supplier limits, or quality concerns that are not in your spreadsheet. Use Solver's answer as a starting point for thinking, not as a final decision.

Frequently Asked Questions

Can I use Solver with multiple target cells?

No, Solver works with one target cell at a time. If you have multiple goals, you need to run Solver multiple times, or combine your goals into a single formula. For example, instead of maximizing profit and minimizing cost separately, you could create a formula that balances both and optimize that one cell.

What if I want Solver to find the smallest or largest number instead of a specific target?

In the Solver dialog, instead of selecting "Value Of" and typing a number, select "Max" to maximize your target cell or "Min" to minimize it. Solver will then find the combination of changing cells that makes your target as large or as small as possible.

Can I save a Solver setup so I do not have to enter it again?

Yes. After you set up Solver the way you want, click the "Save Model" button (or "Save Scenario" in some Excel versions) before you click Solve. Solver will save your target cell, changing cells, and constraints. Later, you can load that setup by clicking "Load Model" and selecting the saved version.

Does Solver work the same way in Google Sheets?

Google Sheets has a similar tool called Solver, but it works slightly differently and has fewer options. The basic idea is the same: you set a target cell, changing cells, and constraints. However, Google Sheets Solver is less powerful and may not solve complex problems. For serious optimization work, use Excel's Solver.

What happens if I run Solver twice with different constraints?

Solver will give you a different answer based on your new constraints. It does not remember the previous run. If you want to compare two different scenarios, save your spreadsheet with different names (like "Scenario A" and "Scenario B") before running Solver each time, so you can look at both answers side by side.