Ms Office Tune Up

Earlier in this chapter, you learned how to create a chart that expands automatically as you add new data points (see “Automatically Expanding a Chart to Include New Data”). It’s a useful trick that I use all the time, but it does lead to one problem: You can end up with a lot of data points in the chart, and after awhile you have to resize the chart horizontally to maintain the visual integrity of the plot. Of course, that leads to an entirely different prob­lem: Eventually your chart becomes wider than the Excel window, so you need to use the horizontal scroll bar to see different parts of the chart.

A better solution is to keep your chart the same size but add a scrolling element to it that enables you to move back and forth through the data. You set this up by adding a scroll bar to the worksheet and using the values generated by the scroll bar to create dynamically named ranges that change as the scroll bar value changes. Then, as you saw earlier in this chapter (see “Charting a Dynamic Range”), you configure your chart to use these dynami­cally named ranges. The result is that as you scroll forward and backward in the scroll bar, the data plotted on the charts moves forward and backward by the same amount.

You begin by converting your worksheet data to a table. Figure 1 shows the example data that I use in this section. It’s a table (named Table2) of monthly sales that runs from January, 1998 to December, 2007. I plot the Month and Actual columns in the chart.

Figure 1 As an example in this section, I create a scrolling chart for 10­year’s worth of monthly sales data.

Next, you add the scroll bar. First, insert two values in the worksheet:

• The initial value of the cell that you’ll link to the scroll bar-Start off this cell with the value 1. This cell’s value changes as you scroll, and you set things up so that this cell represents the starting value Excel plots on the chart. For example, the initial value is 1, so the chart initially begins with the first value in the table. Later, if this cell displays, say, 50, then the chart shows the 50th value in the table as its starting point. You should also name this cell to make your dynamically named range formulas easier to read. In the monthly sales example, I named this cell Starting_Month.
• The number of values to display in the chart-This tells Excel how many data points to display at a time. For the monthly sales, I want to see a year’s worth of data at a time, so I enter 12 in this cell. In the monthly sales example, I named this cell Months_to_Display.

You are now ready to add the scroll bar to the worksheet. Here are the steps to follow:

1. Choose Developer, Insert, and in the Form Controls gallery, click the Scroll Bar control.

NOTE

If you don’t see the Developer tab, choose Office, Excel Options to open the Excel Options dialog box. In the Popular tab, click to activate the Show Developer tab in the Ribbon check box and then click OK.

2. At the point on the worksheet where you want the scroll bar to display, click and drag a thin rectangle that is the size and shape you want for your scroll bar and then release the mouse button. Drag to the right to create a horizontal scroll bar; drag down to create a vertical scroll bar.

TIP

If you want to resize the scroll bar or make other changes to it, you first need to select it.To do this, hold down Ctrl and then click the scroll bar.

3. Choose Developer, Properties to display the scroll bar’s Format Control dialog box.
4. Modify the following values:
   • Current Value-Set this to 1.
   • Minimum Value-Set this to 1.
   • Maximum Value-Set this to the number of records in the table you chart.
   • Incremental Change-Set this to the number of data points that you want Excel to scroll when you click the scroll bar arrows. In most cases, you should leave this value at 1.
   • Page Change-Set this to the number of data points that you want Excel to scroll when you click between the scroll bar and a scroll bar arrow. For the monthly sales example, I set this value to 12.
   • Cell Link-Click inside this range box and then click the cell that you set up earlier to hold the current value of the scroll bar (this is $G$3 in the monthly sales example).
5. Click OK.

Figure 2 shows the Monthly Sales worksheet with the scroll bar added and its filled in Format Control dialog box. For future reference, note that I have applied the named Months_to_Display to cell G2 and the name Starting_Month to cell G3.

Next, you need to define your dynamically named ranges. As before, you need to use the OFFSET() function, which you should set up as follows:

• The reference point (the OFFSET() function’s reference argument) is the table header.
• The number of rows to offset (the OFFSET() function’s rows argument) is the current value of the scroll bar. That is, this should be a reference to the scroll bar’s linked cell. In the monthly sales example, this is the Starting_Month named range (cell G3).
• The number of rows in the offset range (the OFFSET() function’s height argument) is the cell value that defines the number of data points you want to display in the chart. In the monthly sales example, this is the Months_to_Display named range (cell G2).

Figure 2 A scroll bar added to the worksheet and config­ured for the monthly sales data.

In the monthly sales example, I created the name Monthly_Sales_Categories and assigned it the following formula:

=OFFSET(Table2[[#Headers],[Month]],Starting_Month, 0, Months_to_Display, 1)

I also created the name Monthly_Sales_Values and assigned it the following formula:

=OFFSET(Table2[[#Headers],[Actual]],Starting_Month, 0, Months_to_Display, 1)

Finally, create a chart and set up the type and formatting you want to use. It’s important, however, to not plot the entire table. Instead, just plot the same number of points that you want to display in the chart. In the monthly sales example, I want to display 12 months at a time, so I plotted only the first 12 months of data in the table. When that’s done, modify the references to the category labels and series values, as described in this chapter’s “Charting a Dynamic Range” section.

Figure 3 shows the scrolling chart for the monthly sales data. Notice that the current scroll bar value is 13 (see cell G3) and that the chart plot begins with January, 1999, which is the 13th value in the table.

Figure 3 The scrolling chart for the monthly sales table.

Technorati Tags: scrolling chart, excel 2007

Popularity: 9% [?]