Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Scenarios

| | Information Helpful? Why Not Donate.

 

Excel Scenario Manager to Project Various Outcomes

Excel Scenarios

Download Scenario example workbook

Excel's Scenario Manager is a tool that can be used to determine different projected outcomes of data by changing different cells within a Worksheet model.

A scenario is a specific set of values that Excel can save for you and automatically substitute into your Worksheet. This means that you could have a spreadsheet displaying numerical data that is relevant to a certain date, month, topic or whatever and using the Scenario Manager you can enter different values into the worksheet to forecast the outcome of the data. These values (or Scenarios) can be retained for future use and are stored in a hidden part of the workbook which can be retrieved by asking the Scenario Manager to show the Scenario that uses those specific values.

Remember, all that Scenarios are are just a different version of the same worksheet with each different version using a different set of input values in the nominated cells.

For Scenarios to work correctly, you should first set up a base or default Scenario, on a worksheet in Excel. It is from this default Scenario that all other Scenarios are defined.

  1. Go to Tools>Scenarios to activate the Scenario Manager. You will see a message telling you "No Scenarios are defined".
  2. Choose Add to add your default Scenario.
  3. Give your Scenario a name, Base, Original or Default or a name you can easily identify will be fine.
  4. Click in the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook will be referenced here.
  5. Click the collapse dialog button to collapse this dialog box up so that you can easily select the cells from the Worksheet that you wish to reference.
  6. Click on the collapse dialog button again to expand the full Scenario Manager box.
  7. If you wish to type a comment in, then click into the Comment: box and do so.

There are two options at the bottom of this dialog box. They are Prevent changes and Hide, with Prevent changes being the default. If you select Prevent changes, then all your Scenarios will be locked and will be unable to be edited. It is important to note here that you MUST also protect your Worksheet via the Tools>Protection>Protect sheet option for this option to take effect. If you then with to edit your Scenarios, you must first unprotect your Worksheet, then de-select the Prevent changes option to proceed. The Hide option when selected will do exactly as said and Hide your Scenarios. This option also requires sheet protection for it to take effect.

  1. Click the OK button. Once you click on the OK button the Scenario Values dialog box will appear. This is where you must enter values into the scenario cells. As the first scenario is your default Scenario, the values in the cells that we specified in the Changing Cells: box have been picked up so we need to make no changes here, we need only click the OK button. This will now take us back to the Scenario Manager and you will see the name of your Scenario.

Adding Scenarios

There is no limit to the number of Scenarios that you can apply to your worksheet model. Adding a scenario is done in basically the same way as creating a default scenario.

  1. Go to Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box.
  2. Under Scenario name: give your new Scenario a name.  You need make no changes to the Changing cells: box as the cells we nominated in your Default scenario show here.
  3. Click OK. This will show the Scenario values dialog box and this time you will need to change the values.
  4. Click OK.

You will now have two Scenarios available you. 

Displaying Scenarios

Now you can display your Scenarios to show how they change the outcome of your data by asking the Scenario Manager to show a particular scenario.

  1. Select Tools>Scenarios
  2. Click on the Scenario name you want to see
  3. Click Show.
  4. Drag the dialog box out of the way and have a look at the values in the worksheet. One you have done this, click on the next Scenario name and then Show, and again peruse the values in the worksheet.
  5. Click on Close when you no longer wish to view your Scenarios.

Download Scenario example workbook

Learn More About Scenarios Page 2

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates