To create a Solver Answer Report in Excel, set up and solve your model, then select Answer in the Reports list in Solver Results and click OK. Excel adds a report worksheet showing the target cell, changing cells, and constraint results. You need the Solver Add-in and a working model first; the report summarizes the solution Solver found rather than building or checking the model for you.
What You’ll Need
- Microsoft Excel for Microsoft 365 desktop on Windows. Excel for the web does not run the Solver Add-in; open the workbook in desktop Excel to solve it and create reports.
- The Solver Add-in enabled. If Solver is missing from the Data tab, use File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, click Go, check Solver Add-in, and click OK.
- A spreadsheet model with a formula in the objective cell, decision-variable cells that Solver can change, and formulas for any constraints.
- A saved copy of the workbook. A report is useful for documenting results, but it is not a substitute for keeping the original model and assumptions.
On a Mac, enable Solver through Tools > Excel Add-ins, select Solver Add-in, and click OK; then look for Solver on the Data tab. The wording or placement can vary slightly by Excel release.
Step-by-Step
- Prepare a small, clearly labeled model. For example, suppose B2:B3 contain unit profits of 30 and 20, while C2:C3 will hold the quantities Solver chooses. Label the columns Unit profit, Quantity, and Total profit so a reader can identify each value in the report. Enter starting quantities, such as zero, in C2 and C3. Solver will adjust these cells when it searches for a solution.
- Calculate the objective and any totals. With unit profits in B2:B3 and quantities in C2:C3, enter
=B2*C2in D2 for the first product’s profit. With the same layout, enter=B3*C3in D3 for the second product’s profit. With product profits in D2:D3, enter=SUM(D2:D3)in D4 to calculate total profit. With quantities in C2:C3, enter=SUM(C2:C3)in C4 to calculate total units. If Solver assigns all 100 units to the product earning 30 per unit, D4 returns 3000 and C4 returns 100. This example assumes no other limits or costs. - Open Solver. Select Data > Solver. If the command is not there, enable the add-in as described above, then reopen the workbook or check the Data tab again. Solver opens its Parameters dialog, where you specify the target, variables, and limits. Keep the model and its labels visible while setting it up so you can check that each reference points to the intended cells.
- Set the objective cell and goal. In Set Objective, select D4, the cell containing total profit. Choose Max to find the largest possible profit. Choose Min when the goal is to reduce a quantity such as cost, or Value Of when the objective must reach a particular number. The objective must be a formula connected to the changing cells; selecting a typed constant will not give Solver a meaningful result to optimize.
- Choose the decision-variable cells. In By Changing Variable Cells, enter C2:C3. These are the quantities Solver is allowed to change. Do not select the objective formula or unrelated input cells. If the model has separate decision cells in nonadjacent ranges, enter the ranges separated by commas, or select each range in the box.
- Add constraints that reflect the real limits. Click Add. For this example, set Cell Reference to C4, choose <=, and enter 100 as the Constraint, then click Add or OK. This means the total quantity cannot exceed 100. Use additional constraints for other real limits, such as available materials or minimum production. If quantities must be whole numbers, add an integer constraint; if a cell must be binary, use the binary option. Do not add restrictions just because they seem convenient: each one changes which solutions are allowed.
- Choose a suitable solving method. Use Simplex LP when the objective and constraints are linear. Use GRG Nonlinear for smooth nonlinear models, and Evolutionary for models with discontinuities or relationships that the other methods cannot handle well. For the simple example, Simplex LP is appropriate. Confirm that nonnegative quantities are intended: Solver commonly has a Make Unconstrained Variables Non-Negative setting, but check the setting and the model rather than assuming it matches your requirements.
- Run Solver and request the report. Click Solve. When Solver finishes, the Solver Results dialog appears. Select Keep Solver Solution if you want the worksheet to retain the values from the solution. In the Reports list, select Answer, then click OK. Excel creates a new worksheet, often named Answer Report 1, alongside the model. If Solver reports that it could not find a feasible solution, or the Reports list is unavailable, see Troubleshooting below instead of treating the current values as a successful report.
- Review and save the report. Read the report alongside the original model. Check the objective value, the final values of the changing cells, and the status of each constraint. In the example, the objective should show 3000 and the quantity total should not exceed 100. Rename the report tab with a useful date or model label, then save the workbook. If you run Solver again, Excel may create another report sheet; keep the relevant version and label it so it is clear which assumptions and solution it records.
An Answer Report is a generated worksheet, not a live dashboard that recalculates Solver’s optimization whenever inputs change. If you alter the model and want a report for the new result, run Solver again and create a fresh report. Retain the original report if you need a record of the earlier result.
Common Mistakes
- Choosing the wrong objective cell. Selecting a nearby input instead of the formula that measures the goal can produce a meaningless result. Confirm the objective cell address and inspect its formula before solving.
- Leaving out a necessary constraint. If Solver is allowed to use unlimited resources or negative quantities, it may return an unrealistic answer. Translate every genuine business or mathematical limit into a cell reference and constraint, and check its direction: a maximum is usually <=, while a minimum is usually >=.
- Using the wrong solving method. A nonlinear or discontinuous model may not work as expected with Simplex LP. Conversely, using a more complex method for a linear model can make the setup harder to diagnose. Match the method to the formulas and relationships in the model.
- Assuming Answer means optimal or correct. The report documents Solver’s outcome and model status; it does not prove the assumptions, formulas, or source data are correct. Validate the model independently, especially when a result will inform an important decision.
- Overwriting or confusing report versions. A newly created report can be mistaken for an earlier run. Give report tabs descriptive names and keep a note of important input assumptions, dates, and model versions.
Tips
- Use clear labels next to objective, variable, and constraint cells. Solver reports use cell references, and labels make it much easier to map those references back to the model.
- Before solving a large model, test it with a small example whose correct answer you can calculate or reason through. This can expose a reversed inequality or an incorrect formula early.
- Keep the objective and constraint formulas separate from the Solver settings. The worksheet should show how results are calculated, while Solver records which cells it can change and what limits apply.
- Review the report’s original and final values, not just its headline result. A large change from the starting values can be reasonable, but it can also reveal an unintended variable range or missing bound.
- For a repeatable analysis, record the Solver method, relevant constraints, and assumptions in nearby worksheet notes. The Answer Report helps document the result but may not capture every modeling choice in a reader-friendly way.
Troubleshooting
- Solver is not on the Data tab: Enable the Solver Add-in using File > Options > Add-ins, set Manage to Excel Add-ins, click Go, and check Solver Add-in. On Mac, use Tools > Excel Add-ins. If you are in Excel for the web, open the file in desktop Excel; Solver does not run there.
- Solver says it cannot find a feasible solution: Check whether the constraints conflict. For example, a required minimum may be higher than a permitted maximum. Confirm that each constraint references the intended formula cell and that the changing cells are included in the model. Temporarily inspect or relax one constraint at a time to locate the conflict, then restore the real limit before relying on the result.
- The result looks unreasonable or Solver reports an unbounded solution: Look for missing upper limits, incorrect signs in formulas, or changing cells that should have been fixed. Check whether nonnegative values are required and whether that option is selected. A maximum objective with no effective limit can grow without bound, so add only the real limits that apply.
- No Answer report appears: In the Solver Results dialog, look for Answer in Reports after a solve completes. If Solver has not produced a solution or the dialog offers no report, fix the model or solver outcome first. If the dialog appeared but the worksheet is hard to spot, check the sheet tabs for a newly added Answer Report tab; it may be off-screen among many tabs.
- The report and model seem inconsistent: Check whether you are viewing the report from an earlier run. Compare its final values with the current variable cells, then run Solver again if the inputs have changed. Keep or rename the report that matches the version you intend to document.
Frequently Asked Questions
What does an Excel Solver Answer Report show?
The report summarizes the objective cell, changing cells, and constraint results for a Solver run. It typically includes original and final values and indicates whether constraints are satisfied or have slack.
Can I create the report without solving the model?
No. The report is offered after Solver runs. Set up the model, click Solve, and choose Answer in the Solver Results dialog when it is available.
Does the Answer Report update when I change worksheet inputs?
No. Treat it as a record of a particular Solver run. Change the inputs, solve again, and create a new report if you need documentation for the updated model.
Why is the Answer option missing?
Check that the Solver Add-in is enabled, the run has reached the Solver Results dialog, and Solver has produced a usable result. A model that stops with an error or infeasible outcome may not offer the expected report choices.
Can I create a Solver Answer Report in Excel for the web?
Solver is a desktop Excel add-in and does not run in Excel for the web. Use Microsoft Excel for Microsoft 365 desktop on Windows, or desktop Excel on Mac with the Solver Add-in enabled.
Related
- Extract Email Addresses From Text in Excel 365 Easily
- Remove Spaces in an Excel Column: Step-by-Step
- How to Remove Spaces and Dashes in Excel Without Errors
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Check Track Changes in Word: Review Edits with ConfidenceTo check Track Changes in Word, open the Review tab, set Display for Review to All Markup, and…
- Make a Word Form Fillable but Not EditableTo make a Word form fillable but keep its layout and instructions from being edited, add form controls,…
- Excel Data Table Image: Convert Pictures to CellsTo turn a picture of a table into editable Excel data in Microsoft 365 for Windows, choose Data…
- Google Sheets: How to Lock Cells From EditingTo lock cells in Google Sheets, select the cells, then choose Data > Protect sheets and ranges. In…
- ISNUMBER in Excel: Check Whether a Cell Holds a NumberTo check whether a cell contains a number, enter =ISNUMBER(A2) in another cell, replacing A2 with the cell…
- How to Create Blank Address Labels in Microsoft WordTo create a sheet of blank address labels in Word, open Mailings > Labels, choose the label brand…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.