• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 18. Building Scenarios and Outli... > Common Problems and Solutions

Common Problems and Solutions

The scenarios and outlining features in Excel may pose concepts that are difficult to grasp initially, but with practice, you will learn to create scenarios and outlines with ease. The following sections discuss problems you may encounter while learning to use these features.

Q1:Do I really need more than 30 variables in a scenario?
A1: It's true that you can only have 30 changing cells in a single scenario. However, many real-life scenarios have far more changing cells than this. How can you deal with this problem?

When you come across situations like this, break up your scenarios into multiple parts. Each part of the scenario controls a different set of changing cells. Then, to completely display a given scenario, use the Show button in the Scenario Manager dialog box for each part.

Recall that Scenario Manager works by inputting cell values into the changing cells. Because of this, there's no problem with using a collection of scenarios where each scenario inputs values into different collections of changing cells.

The danger with this approach is that you have to remember to show each of the component scenarios in order to display the complete scenario. You can make this a bit easier to remember by preceding each scenario name with a number. For example, to completely define a best case scenario, you might create scenarios with these names: 1-Best Case, 2-Best Case, and 3-Best Case. This gives you a reminder as you change complete scenarios that you need to use the Show button with each defined scenario.

Q2:Data is not grouped correctly in outlines.
A2: When you're creating outlines, it's important to keep some points in mind so that they are created correctly.

If you are creating automatic outlines, make sure that the options in the Settings dialog box are set correctly for your worksheet's data. For example, if your summaries are below the detail they summarize, make sure that the appropriate option for the direction of the data is selected.

If you have parts of a list that summarize at the bottom of detail and parts that summarize at the top of detail, and you want to create an automatic outline, you have to break the process into steps. First, select all of the rows or columns where the summarization is in one direction, and then use the Auto Outline command. Then, select the other region where the summarization is in the other direction, and use the Auto Outline command again.

When you select rows or columns prior to using the Auto Outline command, make sure that you include all of the detail and the summarization rows or columns, because those rows and columns are used to determine where the automatic outlines are placed.

Conversely, when creating outlines manually, make sure that you do not select the summarization rows or columns before issuing the Group command. Otherwise, closing that outline will also close the summarization row or column.

Don't forget, as you create and remove outlines, that the Undo command reverses successive Group commands. If you make a mistake when creating an outline, simply click Undo and select the right set of rows or columns.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint