In This Chapter
Introducing the concept of interactive charts
Creating a self–expanding chart — a chart that updates automatically when data is added or deleted
Using a scroll bar to specify the data in a series
Using a drop–down list to choose a beginning point and an end point for a series
Plotting the last n data points in a series
Plotting every nth data point in a series
Using a check box or a drop–down list to select a series to plot
The term interactive chart, as used in this book, refers to a chart that changes automatically, based on the worksheet environment. In a sense, all charts are interactive because chart series are linked to ranges, and the chart updates automatically when the data is changed. This is not the type of interactivity covered in this chapter.
If you create spreadsheets that are used by others, you'll probably find several useful techniquesin this chapter. Many of these examples have a single goal: to make it easier for users (especially novice users) to deal with a workbook that contains charts.
A few examples of interactive charts include the following:
A chart that updates itself to use data added to the end of the series
A chart that updates itself when data is deleted
A chart that limits the amount of data displayed in a series (for example, only the last 12 data points)
A chart that displays a series based on a value entered into a cell or an item chosenfrom a drop–down list.
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 discoverhow to use dialog box controls (such as check boxes and drop–down lists) to make your charts interactive. None of the examples in this chapter uses macros.
Note
Another way to create an interactive chart is to use a pivot chart. Refer to Chapter 11 for information about creating and using pivot charts.
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?
The good news is that the table feature in Excel 2007 is "chart–aware." If your chart data resides in a table (created by choosing Insert
Figure 7-1. If this were a self–expanding chart, it would update automatically when new data is entered.
Note
If you plan to share your workbook with someone who uses an earlier version of Excel, things get a bit complicated. Excel 2003 can handle an Excel 2007 table, and the software treats it as a list (charts that use data in an Excel 2003 list are self–expanding). But if you open the file with a version prior to Excel 2003, the chart is not self–expanding. In other words, you need to use the technique described in this section to create a selfexpanding chart.
Note
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 requires a bit of extra effort, but the result is a chart that updates automatically, with no user intervention required.
One option, of course, is to specify a larger–than–required range for the data series. Figure 7-2 shows an example in which the data series includes empty cells that will eventually be filled. The result is a lopsided chart that displays lots of empty space. In the majority of situations, this solution is not satisfactory.
As an introduction to the world of interactive charts, the following sections present a hands–on, step–by–step example. You'll create a standard chart and then make the changes necessary to make the chart expand automatically when new data is added, and contract when data is deleted.
The example makes use of a simple worksheet that has dates in column A and sales amounts in column B. The assumption is that a new date and sales figure are each added daily, and the chart should display all the data.
The first step is to create a standard chart, using the data that currently exists. Figure 7-1, presented earlier, shows the data and a column chart created from the data.
The chart contains a single series, and its SERIES formula is as follows:
=SERIES(Sheet1!$B$1, Sheet1!$A$2:$A$9, Sheet1!$B$2:$B$9, 1)
This SERIES formula specifies that:
The series name is in cell B1.
The category labels are in A2:A9.
The values are in B2:B9.
So far, this is just a common chart. If you add a new date and value, the chart will not display the new data. But that will soon change.
In this step, you create two named formulas. The names will eventually serve as arguments in the SERIES formula. In case you're not familiar with the concept of a named formula, it is explained later in this section. To create the named formulas, follow these steps:
Choose Formulas
In the Name field, enter Date. In the Refers To field, enter this formula:
=OFFSET(Sheet1!$A$2, 0,0, COUNTA(Sheet1!$A:$A)–1, 1)
Click the OK button to create the formula named Date.
Notice that the OFFSET function refers to the first category label (cell A2) and uses the COUNTA function to determine the number of labels in the column. Because column A has a heading in row 1, the formula subtracts 1 from the number.
Next, create a name for the sales data as follows:
Choose Formulas
Type Sales in the Name field. Enter this formula in the Refers To field:
=OFFSET(Sheet1!$B$2, 0,0, COUNTA(Sheet1!$B:$B)–1, 1)
In this case, the OFFSET function refers to the first data point (cell B2). Again, the COUNTA function is used to get the number of data points, and it is adjusted to account for the label in cell B1.
Click the Add button to create the formula named Sales.
After you perform these steps, the workbook contains two new names, Date and Sales.
The final step is to modify the chart so that it makes use of the two new names rather than the hard–coded range references. Follow these steps:
Activate the chart and choose Chart Tools
Click Sales in the Series list and then click the Edit button to display the Edit Series dialog box.
In the Series Values field, enter Sheet1!Sales.
Click the OK button to close the Edit Series dialog box and return to the Select Data Source dialog box.
Click the Edit button for the Horizontal (Category) Axis Labels to display the Axis Labels dialog box.
In the Axis Label Range field, enter Sheet1!Date.
Click the OK button to return to the Select Data Source dialog box.
Click the OK button to close the Select Data Source dialog box.
In Steps 3 and 6, note that the name was preceded by the worksheet name and an exclamation point. Because named formulas are, by default, workbook–level names (not sheet–level names), you should (technically) enter the workbook name, an exclamation point, and the name. However, Excel is very accommodating in this regard, and changes it for you. If you look at the SERIES formula (or access the Select Data Source dialog box again), you'll discover that Excel substituted the workbook's name for the sheet reference you entered:
=daily.xls!Sales
Figure 7-3. Using the Select Data Source dialog box to change the references used in a chart series.
Bottom line? When using these named formulas, you can precede the name with either the worksheet name or the workbook name. (I find it easier to use the worksheet name.) Butkeep in mind that if the sheet name or workbook name includes a space character, you must enclose it in single quotation marks, like this:
='daily sales.xls'!Sales
or
='sales data'!sales
For more information about names, refer to the nearby sidebar, "How Excel handles names."
Tip
An alternative to using the Select Data Source dialog box is to edit the chart's SERIES formula directly. The modified SERIES formula is the following:
=SERIES(Sheet1!$B$1, daily.xlsx!Date, daily.xlsx!Sales, 1)
To test the results of your efforts, enter new data in columns A and B, or delete data from the bottom of the columns. If you performed the preceding steps correctly, the chart will update automatically. If you receive an error message or the chart doesn't update itself, review the preceding steps carefully.
Warning
If you adjust the range used by the chart by dragging the range highlights, the names (Date and Sales) will be replaced by actual cell references, and the chart will no longer be a self–updating chart.
Many people use this self–expanding chart technique without fully understanding how it works. There's certainly nothing wrong with that. If you go through the hands–on exercise described previously, you should be able to adapt the procedures to your own charts. But understanding how it works will make it possible to go beyond the basic concept and create more powerful types of dynamic charts.
ABOUT NAMED FORMULAS
Many of the interactive chart techniques described in this chapter take advantage of a powerful feature called named formulas. You're probably familiar with the concept of named cells and ranges. But did you know that naming cells and ranges is really a misnomer? When you create a name for a range, you are really creating a named formula.
When you work with the New Name dialog box, the Refers To field contains the formula, and the Name field contains the formula's name. You'll find that the contents of the Refers To field always begin with an equal sign — a sure sign that it's a formula.
Unlike a normal formula, a named formula doesn't exist in a cell. Rather, it exists in Excel'semory and does not have a cell address. But you can access the result of a named formula by referring to its name, either in a standard formula or in a chart's SERIES formula.
After defining the two named formulas, Excel evaluates these formulas every time the worksheet is calculated. But these named formulas aren't used in any cells, so there is no visible effect of creating these named formulas — until you use them to define the chart series.
To get a better handle on named formulas, use the New Name dialog box to create the following formula, and name it Sum12Cells.
=SUM($A$1:$A$12)
After you've created the named formula, enter the following formula into any cell:
=Sum12Cells
This formula will return the sum of A1:A12.
About The OFFSET Function
The key to mastering self–expanding charts lies in understanding the OFFSET function. This function returns a range that is "offset" from a specified reference cell. Arguments for the OFFSET function let you specify the distance from the reference cell and the dimensions of the range (the number of rows and columns).
The OFFSET function has five arguments, as follows:
reference: The first argument for the OFFSET function is essentially the "anchor" cell, used by the second and third argument.
rows: This argument indicates how many rows to move from the reference address to begin the range.
cols: This argument indicates how many columns to move from the reference address tobegin the range.
height: This argument indicates the number of rows to be included in the range.
width: The final argument indicates the number of columns to be included in the range.
Note
If the columns used for the data contain any other entries, COUNTA will return an incorrect value. To keep things simple, don't put any other data in the column. If the column contains additional information, you'll need to adjust the height argument in the COUNTA function.
Recall that the named formula Sales was defined as follows:
=OFFSET(Sheet1!$B$2, 0,0, COUNTA(Sheet1!$B:$B)–1, 1)
If 9 entries are in column B (in range B1:B9), the COUNTA function returns 9. This result is reduced by 1 to account for the column heading. Therefore, the named formula can be expressed as follows:
=OFFSET(Sheet1!$B$2, 0,0, 8,1)
This formula uses cell B2 as the anchor cell and returns a reference to the range that is as follows:
Offset from cell B2 by 0 rows (second argument, rows)
Offset from cell B2 by 0 columns (third argument, cols)
Eight cells high (fourth argument, height)
One cell wide (fifth argument, width)
In other words, the OFFSET function returns a reference to range B2:B9, and this is the range used by the chart series. When a new data point is added, the OFFSET function returns a reference to range B2:B10.
Subsequent examples in this chapter use the same basic concept but vary in the arguments supplied to the OFFSET function.
Note
To keep things simple, the charts in this chapter make use of a single data series. However, these techniques can be applied to charts with any number of data series. You will, however, have to make the necessary adjustments for each series.
The example in the following sections demonstrates another type of interactivity. Figure 7-4 shows a chart that uses a Scroll Bar control to specify the number of months (from 1 to 12) to display in the chart.
Figure 7-4. The Scroll Bar control at the top of the chart determines how many months are displayed.
Note
This workbook, named chart with scrollbar.xlsx, is available on the companion CD–ROM.
Refer to Figure 7-4 and use the data in columns A:B to create a chart. (The example uses a 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 vertical axis. Set the Maximum scale value to a large enough value to accommodate all the data (25, 000 in this case). Doing this keeps the value axis constant, regardless of the number of data points displayed.
This example uses several names, which are described in this section.
Cell E1 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. The Month formula is defined as follows:
=OFFSET(Sheet1!$A$2, 0,0, NumMonths,1)
The Balance formula is defined as follows:
=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.
Tip
Another approach, which is a bit simpler, is to define Balance as an offset from the Month range. Using this approach, the definition for Balance would be as follows:
=OFFSET(Month, 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 Select Data Source dialog box. The net effect? Change the value in cell E1, and the chart updates immediately.
The Scroll Bar control isn't really necessary, but it adds a touch of convenience. Moving the scroll bar with the mouse is a bit easier than changing the value in cell E1.
Note
Previous versions of Excel allowed you to add a control to the embedded chart itself. This was convenient because the control moves with the chart. Unfortunately, you can't add a control to an Excel 2007 embedded chart. Therefore, the Scroll Bar control must be added to the worksheet. Also, note that Excel does not allow you to group a chart with a control.
Note
To add a control to a worksheet, Excel must display its Developer tab. If this tab is not visible, choose Office.Excel Options, click the Popular tab, and select the Show Developer Tab in the Ribbon check box.
The following instructions add a Scroll Bar control to the worksheet and link the control to cell E1:
Choose Developer
Click and drag in the worksheet to create the control. You can size and position it just as you can any other graphic object.
Right–click the Scroll Bar control and choose Format Control from the shortcut menu. This displays the Format at Control dialog box.
In the Format Control dialog box, click the Control tab (see Figure 7-5).
Enter 1 in the Minimum Value field. In the Maximum Value field, enter 12 (the maximum number of data points for the chart).
Set the Incremental Change field to 1 and the Page Change field to 3.
In the Cell Link field, enter NumMonths. This links the Scroll Bar control with cell E1 (which is named NumMonths).
Click the OK button to close the dialog box.
After performing these steps, the value in cell E1 is controlled by the scroll bar and will have a numeric range of 1-12. This value, in turn, will control the number of data points shown on the chart.
Note
Excel offers two general types of controls: Forms controls and ActiveX controls. Controls in the Forms category are easier to use, but they don't offer as much flexibility as ActiveX controls. For example, the Forms controls offer virtually no formatting options. To keep it simple, all the examples in this chapter use controls from the Forms category.
If a chart uses a lot of data, you may want to be able to limit the data that's displayed in the chart. Figure 7-6 shows an example.
Cell E2 contains a value that represents the first row to be plotted, and cell E4 contains a value that represents the last row to be plotted. The chart is displaying the data in rows 6 through 13. If cell E2 or E4 is changed, the chart adjusts accordingly. This example uses Spinner controls linked to cells E2 and E4. These controls make it easy to change the values in these cells.
Note
This workbook, named first and last point in series.xlsx, is available on the companion CD–ROM.
Refer to Figure 7-6 and create a chart from the data in columns A:C. The chart in this example is a standard column chart. It uses two columns (A:B) for the category axis labels, which results in having two rows of category labels in the chart.
For convenience, cell E2 is named FirstRow and cell E4 is named LastRow.
In addition, the workbook has two named formulas. The Date formula is defined as follows:
=OFFSET(Sheet1!$A$2, FirstRow–2, 0,LastRow–FirstRow+1, 2)
Because the category labels occupy two columns, the OFFSET function uses 2 as its final argument. In other words, the function returns a range that's two columns wide.
The Sales formula is defined as follows:
=OFFSET(Sheet1!$C$2, FirstRow–2, 0,LastRow–FirstRow+1, 1)
As an alternative, you can define the Sales formula in terms of the Date formula:
=OFFSET(Date, 0,2, 1)
After creating these named formulas, they are then specified as the category labels and values range for the chart's series, using the Select Data Source dialog box (or by editing the SERIES formula directly). For more information about using named formulas for a chart series, refer to the section "Modifying the Series," earlier in this chapter.
Warning
There's a serious bug in the initial release of Excel 2007. If you enter a non–numeric value in cell E2 or E4, the named formulas return error values. Excel displays the rather uninformative error message shown in Figure 7-7, and the chart series will probably disappear! To make the series reappear, correct the erroneous entry and save the workbook using a different name. Hopefully, this problem will be fixed in a subsequent update.
For additional convenience, you may wish to add Spinner controls to the worksheet to make adjusting the FirstRow and LastRow values easier. To do so, follow these steps:
Choose Developer
Click and drag in the worksheet to create the control. You can size and position it just as you can any other graphic object.
Right–click the Spinner control and choose Format Control from the shortcut menu. This displays the Format Control dialog box.
In the Format Control dialog box, click the Control tab.
In the Minimum Value field, enter 2.
In the Maximum Value field, enter 25 (or a number that corresponds to the row that contains the last data point for the chart).
In the Cell Link field, enter FirstRow. This links the Spinner control with cell D2.
Click the OK button to close the dialog box.
Repeat Steps 3-8 to add another Spinner control for the LastRow cell. In Step 7, specify LastRow as the Cell Link.
After performing these steps, you can use the linked Spinners to quickly adjust the values that control the first and last data points on the chart.
The example in the following sections is similar to the previous example. Rather than enabling the user to specify the first row and last row to be plotted, this example allows the user to specify the first row (as a meaningful date) and the number of data points.
Figure 7-8 shows a worksheet that contains daily sales information. Cell D2 contains the irst date to be plotted, and cell D4 contains the number of data points to appear in the hart.
This example utilizes two (optional) user interface enhancements: a drop–down list to select the start day and a Spinner control to specify the number of days. The drop–down list(not visible in the figure) is accomplished with Excel's Data Validation feature.
Note
This workbook, named first point and number of points.xlsx, is available on the companion CD–ROM.
Use the data in columns A:B to create a chart. The chart in the figure is a standard line chart but this technique will work with any chart type.
In this example, cell C2 is named StartDay and cell C4 is named NumDays. The workbook has two named formulas. The Date formula is defined as follows:
=OFFSET(Sheet1!$A$2, MATCH(StartDay, Sheet1!$A:$A, 1)–2, 0,NumDays, 1)
The Sales formula is defined as follows:
=OFFSET(Sheet1!$A$2, MATCH(StartDay, Sheet1!$A:$A, 1)–2, 1,NumDays, 1)
The second argument for the OFFSET function uses the MATCH function. The MATCH function returns the relative position of an item in a range. In this case, it returns the positionof the date in column A that matches the date in the StartDay cell. This, of course, is just another way of determining the first row to include in the chart.
As in the previous example, these two named formulas are then used for the category labels and values range for the chart series. For more information about using named formulas for a chart series, refer to the section "Modifying the Series," earlier in this chapter.
The NumDays cell has a linked Spinner control to make it easier to specify the number of days to include in the chart. (See the previous section for information about adding a linked Spinner control.)
You can't use a Spinner control for the StartDay cell because it needs to display dates, and the Spinner control has a maximum value of 30, 000. (The date serial number values exceedthis number.) A scroll bar is an option, but a drop–down list of available dates would be perfect. Fortunately, Excel's Data Validation feature makes adding a drop–down list to a cell very easy. To do so, follow these steps:
Select cell D2, and make sure that it's formatted to display a date.
Choose Data
In the Data Validation dialog box, click the Settings tab.
In the Allow field, choose List.
In the Source field, enter =$A$2:$A$60, which is the worksheet range that contains the dates. (See Figure 7-9.)
Click the OK button to close the Data Validation dialog box.
After entering the data validation settings, you can then select a date when cell D2 is activated. The selected date will be the first date in the chart. The Spinner control determines how many total data points appear in the chart.
Tip
In Step 5 in the preceding list, you can take a different approach. Rather than enter the range address into the Source field, you can enter the following formula, which adjusts automatically if additional dates are added:
=OFFSET($A2, 0,0, COUNTA($A:$A)–1, 1)
Another interactive chart variation is to make a chart show only the most recent n data points in a column. For example, you can create a chart that always displays the most recent six months of data (see Figure 7-10). In this example, cell F1 holds the number of data points to display in the chart.
Figure 7-10. This chart displays the six most recent data points. The number plotted is controlled by the value in cell F1.
Note
This workbook, named plot last n data points.xlsx, is available on the companion CD–ROM.
Create a chart using the data in columns A:C. The chart in this example is a standard column chart, but this technique will work with any chart type. The horizontal category axis uses two columns (A and B).
In this example, cell F1 is named NumMonths. The workbook has two other named formulas. The Date formula is defined as follows:
=OFFSET(Sheet1!$A$2, COUNTA(Sheet1!$B:$B)–NumMonths–1, 0,NumMonths, 2)
The Sales formula is defined as follows:
=OFFSET(Sheet1!$C$2, COUNTA(Sheet1!$C:$C)–NumMonths–1, 0,NumMonths, 1)
The chart title uses a link to cell H1, which contains the following formula:
="Sales by Month (Last " & NumMonths &" Months)"
This formula uses the cell name Num Months to ensure that the chart title always displays he number of months plotted.
After you create the names, you use these two named formulas for the category labels and values range for the chart series. For more information about using named formulas for a chart series, refer to the section "Modifying the Series," earlier in this chapter. The numberof data points in the chart will then be controlled by the value in cell F1. New data added to the worksheet will be accommodated automatically.
Suppose that you have a large amount of data in a column, and you want to plot only every 10th tenth data point. The following sections present two techniques that enable you to do just that. This technique is most useful for large data sets in which the data varies smoothly and continuously. In some cases, important data may be obscured if some of the data points are not displayed.
Note
A workbook that contains the two examples in these sections is available on the companion CD–ROM. The file is named plot every nth value.xlsx.
One way to plot every nth data point in a range is to use filtering in conjunction with a formula. Filtering allows you to hide rows that don't meet specified criteria. Excel, by default, doesn't plot data that resides in a hidden row. Therefore, the trick is to create formulas that return a specific value based on the data's row number and then use the results of these formulas as the basis for filtering the list.
Figure 7-11 shows a worksheet with filtering in effect (note the hidden rows). Cell F1 contains a value that represents n. For example, when F1 contains 5, the chart displays every 5th data point: the value in rows 2, 7, 12, and so on.
Figure 7-11. This chart plots every nth data point (specified in cell F1) by ignoring data in the rows hidden by filtering.
Note
If your data is in a table (created by choosing Insert
Column A contains 365 dates, and column B contains 365 corresponding data points. Column C contains formulas that return a value which is used to determine whether the row should be hidden. The formula in cell C2, which is copied to the cells below, is as follows:
=MOD(ROW()–ROW($B$2),$F$1)
This formula uses the MOD function to calculate the remainder when the row number minus the row number of the first row is divided by the value in F1. As a result, every nth cell in column C contains 0.
Use the drop–down arrow in cell C1 to display only the rows that contain a 0 in column C. This technique does not work if the Show Data in Hidden Rows and Columns option is in effect for the chart. By default, this setting is disabled. To check (or change) this setting, select the chart and choose Chart Tools
Tip
If the chart is next to the data, you'll find that the chart's height is reduced when rows are hidden. To prevent this, activate the chart and display the Size and Properties dialog box. To display this dialog box, click the dialog box launcher in the Chart Tools
Note
The main problem with this technique is that it's not fully automatic. When you change the value in cell F1, you need to respecify the filter criteria for column C. The rows do not hide automatically.
The preceding technique works well, but it would be nice to make it fully automated. Tushar Mehta, an Excel charting expert, developed a clever technique that uses named formulas. The example in this section is an adaptation of his method.
Figure 7-12 shows the same data used in the previous example. This workbook uses three named ranges: Nth (cell H1), Dates (range A2:A366), and Data (range B2:B366). The Nthcell is linked to a Spinner control.
Notice two additional columns of formulas (columns D and E). Both of these are multicell array formulas. The array formula in D2:D366 is as follows:
=N(OFFSET(Dates,(ROW(OFFSET($A$1, 0,0, ROWS(Dates)/Nth))–1)*Nth, 0))
This formula returns an array that consists of every nth row in the Dates range.
The array formula in E2:E366, as follows, is very similar and returns an array that consists of every nth row in the Data range:
=N(OFFSET(Data,(ROW(OFFSET($A$1, 0,0, ROWS(Data)/Nth))–1)*Nth, 0))
When you enter a multicell array formula, select all the cells first. Then type the formula and press Ctrl+Shift+Enter.
Note
These array formulas are complex, and a complete explanation is beyond the scope of this book. However, you don't have to fully understand them to use them. They can easily be adapted to other data sets. Be aware that the reference to cell $A$1 must remain intact. This cell is used to generate a series of offsets that reference cells within the original range.
If you create a chart from the data in columns D:E, the result would not be very satisfactory. Every nth value would be plotted, but the chart would display a lengthy series of empty (#NA) cells.
The solution is to call upon the named formulas technique to substitute for the two array formulas. These named formulas are identical to the array formulas listed in the previous section.
Define NewDates as follows:
=N(OFFSET(Dates,(ROW(OFFSET(Sheet2!$A$1, 0,0, ROWS(Dates)/Nth))–1)*Nth, 0))
Define NewData as follows:
=N(OFFSET(Data,(ROW(OFFSET(Sheet2!$A$1, 0,0, ROWS(Data)/Nth))–1)*Nth, 0))
After you create the names, you use these two named formulas for the category labels and values range for the chart series. For more information about using named formulas for achart series, refer to the section "Modifying the Series," earlier in this chapter. The result? The arrays used by the charts consist only of the values (no #NA values).
Because the named formulas substitute for the array formulas, the formulas in columns D:E are no longer needed.
The example shown in Figure 7-13 displays a line chart with three series. The number of series that are actually displayed is controlled by three Check Box controls. When all three check boxes are selected, the chart displays data for Product A, Product B, and Product C. Deselect a check box, and the corresponding series disappears from the chart.
Note
This workbook, named select series with checkboxes.xlsx, is available on the companion CD–ROM.
This section describes how to add the Check Box controls and link each of them to a cell.
Note
To add a control to a worksheet, Excel must display its Developer tab. If this tab is not visible, choose Office
To add the check boxes to the worksheet, follow these steps:
Choose Developer
Click and drag in the worksheet to create the control. You can size and position it just as you can any other graphic object.
Right–click the Check Box control and choose Format Control from the shortcut menu. This displays the Format Control dialog box.
In the Format Control dialog box, click the Control tab.
In the Cell Link field, enter $G$4. This links the Check Box control with cell G4, which will display either TRUE or FALSE, depending on the state of the Check Box control.
Click the OK button to close the dialog box.
Repeat Steps 2-6 to add two more Check Box controls, linked to cells G5 and G6.
This example uses quite a few names, which are listed in Table 7-1. Note that SeriesA, SeriesB, and SeriesC are named formulas. The other names all refer to cells or ranges. Also, note that range E2:E13 is empty. This is the range that will be used if a series is not selected.
Table 7-1. DEFINED NAMES
Name | Refers To |
|---|---|
Month | =Sheet1!$A$2:$A$13 |
ProductA | =Sheet1!$B$2:$B$13 |
ProductB | =Sheet1!$C$2:$C$13 |
ProductC | =Sheet1!$D$2:$D$13 |
BlankRange | =Sheet1!$E$2:$E$13 |
ShowProductA | =Sheet1!$G$4 |
ShowProductB | =Sheet1!$G$5 |
ShowProductC | =Sheet1!$G$6 |
SeriesA | =IF(ShowProductA, ProductA,BlankRange) |
SeriesB | =IF(ShowProductB, ProductB,BlankRange) |
SeriesC | =IF(ShowProductC, ProductC,BlankRange) |
The three named formulas are quite a bit different from the previous examples in the chapter. These formulas use an IF function that checks the corresponding check box value (stored in a cell in column G). If it's TRUE, the named formula returns the range reference for the corresponding product's data. If the check box is not selected, the named formula returns a reference to the blank range (E2:E13).
The final step is to modify the three chart series so that they use the named formulas for the values range. The easiest way to do this is to edit the SERIES formulas. For example, the SERIES formula for Product A is as follows:
=SERIES(Sheet1!$B$1, Sheet1!Month, Sheet1!SeriesA, 1)
Or, you can use the Select Data Source dialog box.
The Product B and Product C series are modified in a similar manner.
Note
I used data labels (one per series) in lieu of a chart legend because the legend always shows all three series — even if a series is not actually displayed on the chart.
The final example, shown in Figure 7-14, is a useful application that allows the user to choose two U.S. cities (from a list of 284 cities) and view a chart that compares the cities by month in any of the following categories: average precipitation, average temperature, percent sunshine, and average wind speed.
Note
This workbook, named climate data chart.xlsx, is available on the companion CD–ROM.
The interactivity is provided by using Excel's built–in features — no macros required. The cities are chosen from a drop–down list, using Excel's Data Validation feature, and the dataoption is selected using four Option Button controls. The pieces are all connected using a few formulas.
Note
This example uses some named ranges. But, unlike the previous examples in this chapter, it does not use named formulas. Rather, the chart uses data that is retrieved by using VLOOKUP formulas.
This example demonstrates that it is indeed possible to create a user–friendly, interactive application without the assistance of macros.
The following sections describe the steps I took to set up this application.
Figure 7-14. This application uses a variety of techniques to plot monthly climate data for two selected U.S. cities.
I did a Web search and spent about five minutes locating the data I needed at the National Climatic Data Center. I copied the data from my browser window, pasted it into an Excel worksheet, and did a bit of cleanup work. The result was four 13–column tables of data, which I named PrecipitationData, TemperatureData, SunshineData, and WindData. To keep the interface as clean as possible, I put the data on a separate sheet (named Data).
I needed a way to allow the user to select the data to plot and decided to use Option Button controls from the Forms toolbar. Because option buttons work as a group, the four Option Button controls are all linked to the same cell (cell Q2). Cell Q2, therefore, contains a value from 1 to 4, depending on which option button is selected.
I needed a way to obtain the name of the data table based on the numeric value in cell O3. The solution was to write a formula (in cell Q3) that uses Excel's CHOOSE function:
=CHOOSE(Q2,"TemperatureData","PrecipitationData","SunshineData","WindData")
Therefore, cell Q3 (which is named DataToUse) displays the name of one of the four named data ranges.
Next step in setting up the application: Create drop–down lists to enable the user to choose the cities to be compared on the chart. Excel's Data Validation feature makes creating a drop–down list in a cell very easy. Cell B4 contains the first city list and is named City1. Cell B5, which is named City2, contains the second city list.
To make working with the list of cities easier, I created a named range, CityList, which refers to the first column in the PrecipitationData table.
Following are the steps I used to create the drop–down lists:
The key to this application is that the chart uses data in a specific range. The data in thisrange is retrieved from the appropriate data range using formulas that utilize the VLOOKUP function. Figure 7-16 shows the range of data that is used by the chart.
The formula in cell D4, which looks up data based on the contents of City1, is as follows:
=VLOOKUP(City1, INDIRECT(DataToUse),COLUMN()–2, FALSE)
For example, assume that City1 contains Aberdeen, SD, and the Precipitation option button is selected. That option button causes the cell named DataToUse to display the text PrecipitationData. Therefore, the formula in cell D4 looks for the row in the PrecipitationData range that contains the text Aberdeen, SD. Because cell D4 is in column 4, the function retrieves the data in the column number minus 2 (that is, column 2) of the PrecipitationData range. That column contains the precipitation data for January.
Cell D4 is copied to the other cells in the row, and each formula returns the data for its month.
The formula in cell D5 is the same, except that it is looking up data based on the contents of City2, as follows:
=VLOOKUP(City2, INDIRECT(DataToUse),COLUMN()–2, FALSE)
The label above the months (in merged cells D2:O2) is generated by a formula that refers to the DataToUse cell and constructs a descriptive title. The formula is as follows:
="Average " &LEFT(DataToUse, LEN(DataToUse)–4)
After completing the previous tasks, the final step — creating the actual chart — is a breeze. The line chart has two data series and uses the data in D4:O5, and category labels in D3:O3. The chart title is linked to cell D2. The data in rows 4 and 5 changes whenever an Option Button control is selected or a new city is selected from either of the data validation lists.