Microsoft Office Tutorials and References
In Depth Information
Creating the chart
Creating the chart
Refer to Figure 7-4 and use the data in columns A:B to create a chart (the example
uses standard 3-D column chart). Because the number of data points will vary, it’s
a good idea to turn off automatic scaling for the value axis. Set the Maximum scale
value to a large enough value to accommodate all the data. Doing this keeps the
value axis constant, regardless of the number of data points displayed.
Defining the names
This example uses several names, which are described in this section.
Cell D1 contains a value that determines the number of months displayed in the
chart. For convenience, this cell is named NumMonths .
In addition, the workbook has two named formulas, which are used in the chart’s
series. Months is defined as
=OFFSET(Sheet1!$A$2,0,0, NumMonths,1)
Balance is defined as
=OFFSET(Sheet1!$B$2,0,0,NumMonths,1)
If you understand how the named formulas worked in the previous example, you
should have no problem understanding this variation. As you can see, the OFFSET
functions use NumMonths for their height argument. The result is that the
NumMonths cell controls how many data points are displayed in the chart.
Another approach, which is a bit simpler, is to define Balance as an offset
from the Months range. Using this approach, the definition for Balance
would be:
=OFFSET(Months,0,1)
As in the previous example, these two named formulas are then used for the
category labels and values range for the chart series. This is done by using the Series
tab of the Source Data dialog box. The net effect? Change the value in cell D1, and
the chart updates immediately.
Adding the Scroll Bar control
The Scroll Bar control isn’t really necessary, but it does add a touch of convenience.
Moving the scroll bar with the mouse is a bit easier than changing the value in
cell D1.
Search JabSto ::




Custom Search