Excel 2016 enables you to create and save sets of input values that produce different results as scenarios with the Scenario Manager option on the What-If Analysis button's drop-down menu on the Data tab of the Ribbon. A scenario consists of a group of input values in a worksheet to which you assign a name, such as Best Case, Worst Case, Most Likely Case, and so on.
Then, to reuse the input data and view the results that they produce in the worksheet, you simply select the name of the scenario that you want to use, and Excel applies the input values stored in that scenario to the appropriate cells in the worksheet. After creating your different scenarios for a worksheet, you can also use the Scenario Manager to create a summary report showing both the input values stored in each scenario and the key results produced by each.
When creating a scenario for your worksheet, you create a spreadsheet that uses certain cells that change in each scenario (appropriately enough, called changing cells). To make it easier to identify the changing cells in each scenario that you create (especially in any scenario summary reports that you generate), you should assign range names to the variables in the spreadsheet with the Name a Range or Create from Selection command buttons on the Formulas tab of the Ribbon before you create your scenarios.
To create your scenarios with the Scenario Manager, follow these steps:
When you return to the Scenario Manager dialog box, the names of all the scenarios that you added appear in the Scenarios list box. For example, in the figure, you see that three scenarios — Most Likely, Best Case, and Worst Case — are now listed in the Scenarios list box.
To show a particular scenario in the worksheet that uses the values you entered for the changing cells, you simply double-click the scenario name in this list box or click the name and then click the Show command button. The figure shows the results in the sample forecast worksheet after showing the Worst Case scenario.
If, after creating the scenarios for your worksheet, you find that you need to use different input values or you want to add or remove scenarios, you can edit the scenarios in the Scenario Manager dialog box.
To modify the scenario's name and/or the input values assigned to the changing cells of that scenario, click the scenario name in the Scenarios list box and then click the Edit button so that you can make the appropriate changes in the Edit Scenario dialog box.
To remove a scenario from a worksheet, select the scenario's name in the Scenarios list box and then click the Delete button. Note, however, that if you delete a scenario in error, you can't restore it with the Undo command. Instead, you must re-create the scenario by using the Add command button as outlined previously.
You can also merge scenarios from other Excel workbook files that are open. (Of course, the workbooks must share the same spreadsheet layout and changing cells.) To merge a scenario into the current worksheet from another workbook, click the Merge button in the Scenario Manager dialog box and then select the workbook from the Book drop-down list box and the worksheet from the Sheet drop-down list box before you click OK. Excel then copies all the scenarios defined for that worksheet and merges them with any scenarios that you've defined for the current worksheet.
Greg Harvey, PhD, is the president of Mind Over Media, LLC., and a bestselling author of books on Excel, including all editions of Excel For Dummies and Excel Workbook For Dummies. He began teaching business users about computers back in the 1980s, and has been a dedicated educator ever since.
