Excel Charts Interactive Features
Add interactive features for Excel charts, such as check boxes or cell entries, to show and hide data. This technique uses formulas, and there are no macros used in the Excel spreadsheet.
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.
People can click the check boxes, to quickly show or hide region data in the interactive chart.
Note: This tutorial is based on a technique that I learned from Jon Peltier,
who creates time-saving Excel charting utilities.
Create a Duplicate Data Range
In this example, the original data for the Microsoft Excel chart is in cells B2:H5
Note: Cell B2 is left empty
Instead of using the original data range to create the chart, formulas are used to
create a duplicate range, linked to the original data.
- In the duplicate
range, formulas will show the series' data, only if the check box
- 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.
chart data range, shown below, is in cells B8:H11
How to Create the Duplicate Data Range
Follow these steps to create the headings and data for the duplicate data range:
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
When the box is checked, the data for that region will appear in the
To add a check box, follow these steps:
- On the Ribbon, click the Developer tab.
- If the Developer 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 related 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
This tutorial uses line graphs, but you could use another type of graph, if you prefer. For example, the technique would also work with a bar graph, or with column charts.
To create a line chart from the duplicate data range, follow these steps:
- Select the data and headings in the duplicate data range -- cells
- On the Excel Ribbon, click the Insert tab
- In the group of chart types, click the Insert Line Chart command
- In the list of Line charts, click the 2-D Line option
- 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 changes to FALSE
- The data for
that region 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 a region's data appear in a chart.
Later, clear the "X", to remove the
series from the chart.
Follow the setup instructions for the check box interactive example
above, but do not add check boxes in cells A3:A5.
Instead, follow these steps to add a formula in column A of the duplicate data range:
- In cell A9, type this formula: =A3<>""
- Copy the formula down to cell A11
The formula in cell A9 checks cell A3 (main data range), to see
if it is not equal to an empty string (not empty)
- If cell A3 is not empty (if it contains X or any other character), the formula result in cell A9 result
- If cell A3 IS empty (contains no characters), the formula result in cell A9 result
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 in column A.
- Type an X in column A, to show a region.
- Clear the cell in column A, to hide a region.
Download the Workbook
To see both versions of the chart setup, you can download the sample
file. The Excel workbook file is zipped, and in xlsx format with no
macros. Sample Interactive
More Chart Resources
Interactive Chart - Totals
Show Target Range in Line Chart
Excel Gantt Chart
Charts, Cluster Stack
Charts, Line-Column 2 Axes
VBA Code, Copy to a workbook