Plotting Every nth Data Point in a Series . . . . . . . . . . . . . . 226
Using AutoFiltering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Using array formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Creating named formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Plotting a Series Based on the Active Cell . . . . . . . . . . . . . 229
Creating the chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Defining the names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Using a macro to force a recalculation . . . . . . . . . . . . . . . . . . 231
Defining a Series Based on the Active Cell . . . . . . . . . . . . . 231
Creating the chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Defining the names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Using a macro to force a recalculation . . . . . . . . . . . . . . . . . 233
Using Check Boxes to Select Series to Plot . . . . . . . . . . . . 233
Creating the chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Adding the Check Box controls . . . . . . . . . . . . . . . . . . . . . . 234
Defining the names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Modifying the chart series . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Creating a Very Interactive Chart . . . . . . . . . . . . . . . . . . . . 236
Getting the data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Creating the Option Button controls . . . . . . . . . . . . . . . . . . . 237
Creating the city lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Creating the chart’s data range . . . . . . . . . . . . . . . . . . . . . . . 238
Creating the chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Chapter 8 Charting Techniques and Tricks . . . . . . . . . . . . . . . . . 241
Adding Lines and Backgrounds to a Chart . . . . . . . . . . . . . 241
Adding horizontal reference lines to a chart . . . . . . . . . . . . . . 242
Adding a vertical line to a chart with an XY series . . . . . . . . . 242
Using background columns to represent a vertical line . . . . . . 244
Adding horizontal or vertical “bands” . . . . . . . . . . . . . . . . . . 245
Creating an XY chart with colored quadrants . . . . . . . . . . . . 247
Charts That Use a Single Data Point . . . . . . . . . . . . . . . . . 249
Creating a thermometer chart . . . . . . . . . . . . . . . . . . . . . . . . 249
Creating a gauge chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Using a Dummy Axis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
An introductory example . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Labeling an axis with non-equal intervals . . . . . . . . . . . . . . . 254
Column and Bar Chart Variations . . . . . . . . . . . . . . . . . . . 256
Stacked-column chart variations . . . . . . . . . . . . . . . . . . . . . 256
Creating a step chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
A bar-line combination chart . . . . . . . . . . . . . . . . . . . . . . . . 260
Varying column widths . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
Conditional colors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Creating a population pyramid chart . . . . . . . . . . . . . . . . . . . 264
Creating Gantt charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Identifying the maximum and minimum values in a series . . . . 266
Shading between two series in a line chart . . . . . . . . . . . . . . . 268
xviii Contents
a517643 FM.F 10/14/02 8:38 AM Page xviii