have several trend data series? Would you like to be able to pick and choose
which data series to plot?
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.
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.
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 >
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
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.