Microsoft Office Tutorials and References
In Depth Information
Hands-On: Creating a Self-Expanding Chart
A chart that displays a series based on a value entered into a cell or an
item chosen from a drop-down list.
A chart that displays a series based on the location of the active cell
This chapter provides the information you need to create several types of charts
that update automatically based on information contained in the workbook. You’ll
also discover how to use dialog box controls (such as check boxes and drop-down
lists) to make your charts interactive.
Another way to create an interactive chart is to use a pivot chart. Refer to
Chapter 9 for information about creating and using pivot charts.
All the examples discussed in this chapter are available on the companion
Hands-On: Creating a
One of the most common questions related to charting is, “How can I create a chart
that will expand automatically when I add new data to the worksheet?”
To understand this issue, examine Figure 7-1, which shows a worksheet set up to
store sales information that is updated daily. The chart displays all the data in the
worksheet. When new data is entered, the chart series must be expanded to include
the new data. On the other hand, if data is deleted, the chart series should also be
contracted to exclude the deleted cells.
Wouldn’t it be nice if the chart series would expand and contract automatically?
Excel doesn’t provide a direct way to create such a self-expanding chart. But, as
you’ll see, it’s certainly possible — if you’re willing to do a little up-front work.
Chapter 3 describes several ways to change the source data used in a chart
series. Although none of those techniques is particularly difficult, each
requires manual intervention. Creating a self-expanding chart makes the
process completely automatic.