1/8/2011

This tutorial and download workbook  show you how to use checkboxes to let your Users choose which series to show.

1. Data Organization - Range Name Approach

Our data set is weekly chlorine residual data for a number of water quality sampling stations in a water utility.

To make our chart interactive, we will set up a series of data ranges so that the User selections can be automatically transferred to the chart. We will do this in a 4 step process, outlined in the following schematic.

Data Organization Schematic

Step A - Set Up Data Ranges - We set up our data series ranges using Insert > Name > Define  so that we have range names for each data series. We use dynamic offset formulas in the Refers to box so that our charts will be dynamic as well as interactive.

See Dynamic Charts for details on how to make dynamic range names.

Our data set is based on weekly sampling, so we can use the date of the weekly sample as our starter range name. The other range names can be set as offsets of the weekly range.

The Week range name Refers To formula will automatically expand as additional data is added.

Notice that the sample locations are set as Offsets to the Week range name. This simplifies the Refers To formulas and ensures that the location ranges expand as the Week range expands.

Our approach is to use a the range name if the User wants to plot the series or to use a blank series if the User does not want to plot the series. To do this, we add an extra blank series that has no data.

Step B - Add Checkboxes and Linked Cells- To add our checkboxes, we need to have the Control Toolbox visible (View > Toolbars > Control Toolbox).

We add a checkbox to our worksheet by clicking Control toolbar Design Mode,   selecting the Checkbox icon and dragging it to where we want it.

We format the checkbox with a Title and establish the linked cell by right clicking the checkbox and selecting properties.

 

The linked cell will equal True if the check box is selected by the User or false if the checkbox is unchecked.

Step C - Show Series Range Names- Since our approach is to show the data series if the User checks the checkbox or to show blank series if the checkbox is unchecked, we give range names to the checkbox True/False linked cells. 

Step D - Plot Series Formulas - Our final data organization step is to create the plot series range names, one plot series name for each source data series. We want the plot series to point to the source data range if the User has selected the checkbox or to point to the blank series if the User has not checked the box.

 

 

2. Create Charts

We are now ready to create our chart. the simplest way is to create a chart with all data series. With some chart editing, as outlined in the Effective Charting Tips post, this ugly chart can be made to look professional.

3. Apply Series Range Names to Chart

The final steps is to edit the chart series formulas to refer to the plot series range names rather than the cell ranges. This makes the chart dynamic and interactive.