Logo
HOW TO
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
 
 
Windows 7

Microsoft Excel 2010 : Defining Multiple Alternative Data Sets

7/16/2012 3:51:05 PM
One great feature of Excel scenarios is that you’re not limited to creating one alternative data set—you can create as many scenarios as you like and apply them by using the Scenario Manager. To apply more than one scenario by using the Scenario Manager, click the name of the first scenario you want to display, click the Show button, and then do the same for any subsequent scenarios. The values you defined as part of those scenarios will appear in your worksheet, and Excel will update any calculations involving the changed cells.

Tip

If you apply a scenario to a worksheet and then apply another scenario to the same worksheet, both sets of changes appear. If multiple scenarios change the same cell, the cell will contain the value in the most recently applied scenario.

Applying multiple scenarios alters the values in your worksheets. You can see how those changes affect your formulas, but Excel also gives you a way to view the results of all your scenarios in a single, separate worksheet. To create a worksheet in your current workbook that summarizes the changes caused by your scenarios, open the Scenario Manager, and then click the Summary button. When you do, the Scenario Summary dialog box opens.

Tip

From within the dialog box, you can choose the type of summary worksheet you want to create and the cells you want to display in the summary worksheet. To choose the cells to display in the summary, click the Collapse Dialog button in the box, select the cells you want to display, and then expand the dialog box. After you verify that the range in the box represents the cells you want to have included on the summary sheet, click OK to create the new worksheet.

It’s a good idea to create an "undo" scenario named Normal that holds the original values of the cells you’re going to change before they’re changed in other scenarios. For example, if you create a scenario named High Fuel Costs that changes the sales figures in three cells, your Normal scenario restores those cells to their original values. That way, even if you accidentally modify your worksheet, you can apply the Normal scenario and not have to reconstruct the worksheet from scratch.

Tip

Each scenario can change a maximum of 32 cells, so you might need to create more than one scenario to ensure that you can restore a worksheet.

In this exercise, you’ll create scenarios to represent projected revenue increases from two rate changes, view the scenarios, and then summarize the scenario results in a new worksheet.

Set Up

Open the MultipleScenarios_start workbook, and save it as MultipleScenarios. Then follow the steps.

  1. On the Data tab, in the Data Tools group, click What-If Analysis and then, in the list, click Scenario Manager.

    Set Up

    The Scenario Manager dialog box opens.

  2. Click Add.

    The Add Scenario dialog box opens.

  3. In the Scenario name field, type 3DayIncrease.

  4. At the right edge of the Changing cells field, click the Collapse Dialog button.

    Set Up

    The Add Scenario dialog box collapses.

  5. In the worksheet, click cell C4 and then, in the dialog box, click the Expand Dialog button.

    Set Up

    $C$4 appears in the Changing Cells field, and the dialog box title changes to Edit Scenario.

  6. Click OK.

    The Scenario Values dialog box opens.

  7. In the value field, type 11.50.

  8. Click OK.

    The Scenario Values dialog box closes, and the Scenario Manager is displayed again.

  9. Click Add.

    The Add Scenario dialog box opens.

  10. In the Scenario name field, type Ground and Overnight Increase.

  11. At the right edge of the Changing cells field, click the Collapse Dialog button.

    The Add Scenario dialog box collapses.

  12. Click cell C3, hold down the Ctrl key, and click cell C6. Then click the Expand Dialog button.

    $C$3,$C$6 appears in the Changing Cells field, and the dialog box title changes to Edit Scenario.

    Set Up
  13. Click OK.

    The Scenario Values dialog box opens.

  14. In the $C$3 field, type 10.15.

  15. In the $C$6 field, type 18.5.

  16. Click OK.

    The Scenario Values dialog box closes, and the Scenario Manager dialog box is displayed again.

    Set Up
  17. Click Summary.

    The Scenario Summary dialog box opens.

  18. Verify that the Scenario summary option is selected and that cell E8 appears in the Result cells field.

  19. Click OK.

    Excel creates a Scenario Summary worksheet.

    Set Up

Clean Up

Save the MultipleScenarios workbook, and then close it.

Other -----------------
- Microsoft Excel 2010 : Defining an Alternative Data Set
- Tools for Troubleshooting (part 9) - TCPView, Telnet Client
- Tools for Troubleshooting (part 8) - Route & Task Manager
- Tools for Troubleshooting (part 7) - Resource Monitor, Ping & PortQry
- Tools for Troubleshooting (part 6) - Performance Monitor & Data Collector Sets
- Tools for Troubleshooting (part 5) - PathPing
- Tools for Troubleshooting (part 4) - Nslookup
- Tools for Troubleshooting (part 3) - Netstat & Network Monitor
- Tools for Troubleshooting (part 2) - Nblookup, Nbtstat
- Tools for Troubleshooting (part 1) - Arp & IPConfig
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
 
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro