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 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.

The duplicate 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:

1. In cell C8, type this formula: =C2
2. Copy the formula across to cell H8

To create the Region row headings:

1. In cell B9, type this formula: =C3
2. Copy the formula down to cell B11

To create series data:

1. In cell C9, type this formula: =IF($A9=TRUE,C3,NA())
2. Copy the formula across to cell H9, and then copy down to row 11.

Add the Check Boxes

Next, you'll add a check box to the left of each region name in the original data.

When the box is checked, the data for that region will appear in the chart.

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 data range.

• 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 data and headings in the duplicate data range -- cells B8:H11
• 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 chart styles, 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 it.

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 point.
• 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 Label
• 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:

1. In cell A9, type this formula: =A3<>""
2. 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 is TRUE
• If cell A3 IS empty (contains no characters), the formula result in cell A9 result is FALSE

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 Chart File

More Chart Resources

Interactive Chart - Totals

Pie Charts

Show Target Range in Line Chart

Excel Gantt Chart

Charts, Cluster Stack Utility

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall

VBA Code, Copy to a workbook