Page 41 - Microsoft Office Excel 2003 Programming Inside Out

Part 1: Preliminaries
Exploring Excel

Table 2-1. Excel Worksheets Can Hold a Lot of Data but Have Their Limits
Attribute Limit
Maximum number of scenarios No maximum, but only 251 will be
displayed in a scenario summary
Maximum number of changing cells in a scenario 32
Maximum number of changing cells in Solver 200

One of the most underused capabilities in Excel is the scenario, which lets you define alterna­
tive data sets for a worksheet. As noted in Table 2-1, each scenario can contain up to 32
changes. The advantages of scenarios are that you can define them quickly (by clicking Tools, Chapter 2
Scenarios and using the controls in the Scenario Manager dialog box, shown in Figure 2-1),
and you’re able to switch between alternative data sets without having to create a new work-
sheet to contain the speculative data. If you’re creating one new worksheet that contains 12
values, 3 of which change, scenarios probably won’t save you that much time. However, if you
have a broad range of values (and combinations of values) that could change and you don’t
want to keep track of separate worksheets for each possible combination, you can create a
scenario for each combination and switch within the same worksheet.

Figure 2-1. The Scenario Manager dialog box helps you manage and present alternative
data sets.

If you need to create a scenario with more than 32 changes, you should probably ceate a new
worksheet to hold the data.

Cells and Ranges

At the bottom of the organizational hierarchy in Excel is the cell, which is formed by the
intersection of a column and a row in a worksheet. A cell can contain a value or a formula.
By default, Excel displays the result of a formula in its cell, but you can change that setting by
clicking Tools, Options, clicking the View tab, and selecting Formulas. What’s interesting is

15