Ms Office Tune Up

Most chart data consists of an entire range of cells, but it’s sometimes necessary to chart only a portion of a range. If you have stock data, for example, you may want to visualize only a particular 7-, 14-, or 30-day portion of the data. If the chart doesn’t exist yet, just select the portion of the range you want to work with and then insert the chart. If the chart already exists, there are two methods you can use to adjust the data series:

• Click the chart and then choose Design, Select Data to open the Select Data Source dialog box. For the value range, click the Edit button in the Legend Entries (Series) section to display the Edit Series dialog box, adjust the Series Values reference, and then click OK. For the categories, click the Edit button in the Horizontal (Category) Axis Labels section to display the Axis Labels dialog box, adjust the Axis Label Range reference, and then click OK.
• Click the chart, click any point in the data series, and then adjust the range references used in the SERIES() function that displays in the formula bar. I explain the function as follows.

Here’s the syntax for the SERIES() function:

SERIES([name,][ category_labels,] values, order)

name A string or cell reference that specifies the series name. category_labels The range that holds the labels that display on the category (X) axis. If you omit this argument, Excel uses consecutive integers as labels. values The range that holds the values that Excel plots. order The plot order for the series.

For example, here’s the SERIES() function for the line chart that displayed earlier:

=SERIES(‘Worksheet Text’!$B$2,’Worksheet Text’!$A$3:$A$24,

➥’Worksheet Text’!$B$3:$B$24,1)

However, what do you do if the sub-range you want to plot changes over time? For exam­ple, in a stock price worksheet where you enter prices daily, it is useful to use chart that always shows, say, the most recent 7 days of price data. Is it possible to set this up without having to redefine the series ranges by hand? Absolutely! Using a couple of tricks, you can create a chart that automatically plots a dynamic range.

As an example, I show you how to set up a chart that automatically plots the most recent 7 days of stock data. This task is made easier by Excel 2007’s new structured table references. I won’t go into this in detail except to say that when you convert a range to a table, Excel 2007 defines a name for that table. Most importantly for our purposes, as you add data to or remove data from the table, Excel 2007 dynamically adjusts the references associated with the table name.

NOTE

For a more detailed look at Excel 2007’s structured table referencing, see my book Formulas and Functions with Microsoft Excel 2007 (Que 2007; ISBN 0-7897-3668-3).

Therefore, the first thing you need to do is convert your worksheet data to a table. Select your data (including any column headers) and choose Insert, Table to display the Create Table dialog box. Make sure the range is correct, activate the My Table Has Headers check box, and then click OK. Make a note of the table name (select the table data-don’t include the headers-and see the name that displays in Excel’s Name box). The default name is Tablen, where n means this is the nth table you’ve added to the current workbook.

The next stage is to create two dynamic range names-one for the category axis labels and one for the data series values. A dynamic range name is one that automatically adjusts based on the results of the function or functions used to define the name. In this case, you want to create names that dynamically adjust to always return the last seven items in the table you just created.

To make the range name formulas easier to understand, add the range length you want (7, in this case) to a cell and then name that cell Length.

TIP

The easiest way to apply a range name to a cell is to click the cell, type the name in the Name box (the text box that displays to the left of the formula bar), and then press Enter.

There are several methods you can use to create a dynamic range name. I like to use the OFFSET() function, which returns a range offset from some original range by a specified number of rows and columns:

OFFSET(reference, rows, cols[, height][, width])

reference The original range. In this task, this reference must be absolute. rows The number of rows to offset. cols The number of columns to offset. height The number of rows in the new range. If you omit this argument, the new range uses the same number of rows as the reference range. width The number of columns in the new range. If you omit this argu­ment, the new range uses the same number of columns as the ref­erence range.

Our goal is to create an OFFSET() formula that returns the last length number of items in the table, where length is the value you stored in the cell named Length, earlier. You do this by offsetting to the length-last item in the table and then setting the height argument equal to length.

For the category (X) axis labels, assuming the category labels start at cell A3 in the table, then the following OFFSET() formula does the job:

=OFFSET($A$3, ROWS(Table1) – Length, 0, Length, 1)

To use this formula as the basis of a dynamic range name, follow these steps:

1. Choose Formulas, Name Manager to open the Name Manager dialog box.
2. Click New to open the New Name dialog box.
3. Type the range name (for example, Categories) in the Name text box.
4. Type the OFFSET() formula in the Refers To box.
5. Click OK and then click Close.

For the data series values, assuming the series values start at cell B3 in the table, the follow­ing OFFSET() formula does the job:

=OFFSET($B$3, ROWS(Table1) – Length, 0, Length, 1)

To use this formula as the basis of a dynamic range name, follow the same steps that I out­lined previously (entering a different range name, such as Values).

Now, all that remains is to adjust the chart references to point at these two new dynamic range names. Use the techniques I mentioned earlier in this section (using the Select Data Source dialog box or editing the SERIES() function directly). For best results, include the workbook name in the references, as shown here:

=SERIES($B$2,Chapter08.xlsm!Categories,Chapter08.xlsm!Values,1)

Figure 1 shows a chart based on these dynamic named ranges.

Figure 1 A chart based on dynamically named ranges.

Technorati Tags: dynamic range charting, excel 2007

Popularity: 9% [?]