Excel Charts Interactive Features
Add interactive features for Excel charts, such as check boxes or cell entries, to show and hide data
Show Chart Series With Check Boxes
Add check boxes on a worksheet, to control which series will be shown
in a chart. There is no programming required for this technique, and
it works best for small to mid-sized tables.
This tutorial is based on a technique that I learned from Jon Peltier,
who creates amazing Excel charting utilities.
Create a Duplicate Data Range
Instead of using the original data range to create the chart, you'll
create a duplicate range, linked to the original data. In the duplicate
range, formulas will show the series' data, only if the check box
is checked. Otherwise, it will show #N/A, and the series will be omitted
from the chart, because #N/A values are not plotted in an Excel chart.
In this example, the original data is in cells B2:H5, and the duplicate
range, shown below, is in cells B8:H11
To create the Month column headings:
- In cell C8, type this formula: =C2
- Copy the formula across to cell H8
To create the Region row headings:
- In cell B9, type this formula: =C3
- Copy the formula down to cell B11
To create series data:
- In cell C9, type this formula: =IF($A9=TRUE,C3,NA())
- Copy the formula across to cell H9, and then copy down to row
Add the Check Boxes
Next, you'll add a check box to the left of each region name in the
orginal data. When the box is checked, its data will appear in the
To add a check box:
- On the Ribbon, click the Developer tab. (If the tab is not visible,
follow the instructions here, to add
the Developer tab)
- Click the Insert command, and in the Form Controls section, click
the Check Box
- On the worksheet, click in cell A3, to add a check box.
- Remove the text from the check box caption, and make the check
box narrower, to fit in column A.
- Copy the check box, and paste copies in cells A4 and A5.
Link the Check Boxes to Worksheet Cells
The next step is to connect each check box to a cell in the duplicate
- Right-click on the check box in cell A3, and click Format Control
- Click in the Cell Link box, and then on the worksheet, click in
cell A9, to link the check box to that cell.
- Click OK to close the Format Control window.
- Link the remaining check boxes to the applicable cells in the
duplicate data range.
- Click each of the check boxes, to add a check mark, and to add
TRUE to its linked cell.
Create a Line Chart
To create a line chart from the duplicate data range:
- Select the data and headings in the duplicate data range -- cells
- On the Ribbon, click the Insert tab
- Click the Insert Line Chart command, then click 2-D Line
- Move and resize the chart, if necessary, to fit on the worksheet.
- TIP: Position the chart over the duplicate data range, to hide
Format the Line Chart
To finish the line chart, you'll remove the legend, and add a label
to the last data point in each series. For the chart title, you'll
link to a heading cell on the worksheet.
To remove the chart's legend:
- Click on the chart's legend, to select it.
- On the keyboard, press the Delete key
To link the chart title to a cell:
- Click on the chart's title, to select it.
- Click in the Formula bar, and type an Equal Sign (=)
- Click on cell B1, which contains the worksheet title
- Press Enter, to complete the formula
To add data labels:
- Check the boxes, to show all the series.
- Click on the first series, to select it.
- Click the last point in the selected series, to select the data
- Right-click on the selected data point, and click AddData Label,
then click Add Data Label
- Click on the data label twice -- the first click selects all of
the data labels for the series, and the second click selects just
one data label.
- Right-click on the selected data label, and click Format Data
- In the Format Data Label pane, under Label Options, add a check
mark to Series Name, and remove the check mark from Values
- Repeat steps 2-7, for the remaining data series.
- TIP: If the data labels are too close to the lines, click on the
chart's Plot Area, and make it a bit narrower.
Test the Interactive Line Chart
To test the interactive chart, click one of the check boxes, to remove
its check mark.
The value in its linked cell will change to FALSE, and the data for
that series disappears from the chart.
Show Chart Series With X Marks
Instead of using check boxes, you can mark a series with an "X",
to make it appear in a chart. Clear the "X", to remove the
series from the chart.
Follow the setup instructions for the check box interactive example
above, but don't add check boxes in cells A3:A5.
Then, add a formula in column A of the duplicate data range, to see
if there is a mark in the main data. If there is a mark, the result
is TRUE, and if not, the result is FALSE.
- In cell A9, type this formula: =A3<>""
- Copy the formula down to cell A11.
Test the Interactive Online Chart
Below is an interactive online version of the chart and worksheet.
(It might not be visible in all browsers.)
Check boxes are not available in the interactive online view, so
this example uses "X" marks n column A.
- Type an X in column A, to show a region.
- Clear the cell in column A, to hide a region.
Download the Sample File
To see both versions of the chart setup, you can download the sample
file. The file is zipped, and in 2007/2010 format (xlsx), with no
macros. Sample Interactive
More Chart Tutorials
Charts, Cluster Stack
VBA Code, Copy to a workbook