Microsoft Office Tutorials and References
In Depth Information
Modifying the chart series
Modifying the chart series
The final step is to modify the three chart series, so they use the named formulas for
the values range. The easiest way to do this is to use the Series tab of the Source
Data dialog box. For example, the Values range for the Product A series is
=Sheet1!SeriesA
The Product B and Product C series are modified in a similar manner.
Creating a Very Interactive Chart
The final example, shown in Figure 7-16, is a useful application that allows the
user to choose two U.S. cities (from a list of 284 cities) and view a chart that
compares the cities by month in any of the following categories: average precipitation,
average temperature, percent sunshine, and average wind speed.
The interactivity is provided using by using Excel’s built-in features — no macros
required. The cities are chosen from a drop-down list, using Excel’s Data Validation
feature, and the data option is selected using four Option Button controls. The
pieces are all connected using a few formulas.
This example uses some named ranges. But, unlike the previous examples in
this chapter, it does not use named formulas. Rather, the chart uses data that
is retrieved by using VLOOKUP formulas.
This example demonstrates that it is indeed possible to create a user-friendly,
interactive application without the assistance of macros.
The following sections describe the steps I took to set up this application.
Getting the data
I did a Web search and spent about five minutes locating the data I needed at the
National Climatic Data Center. I copied the data from my browser window, pasted it
to an Excel worksheet, and did a bit of clean up work. The result was four 13-column
tables of data, which I named PrecipitationData, TemperatureData, SunshineData,
and WindData . To keep the interface as clean as possible, I put the data on a separate
sheet (named Data).
Search JabSto ::




Custom Search