Contextures

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.

interactive chart with data

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

interactive chart with data

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 orginal data. When the box is checked, its data will appear in the chart.

To add a check box:

  1. On the Ribbon, click the Developer tab. (If the tab is not visible, follow the instructions here, to add the Developer tab)
  2. Click the Insert command, and in the Form Controls section, click the Check Box

    insert check box

  3. On the worksheet, click in cell A3, to add a check box.
  4. Remove the text from the check box caption, and make the check box narrower, to fit in column A.

    insert check box

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

  1. Right-click on the check box in cell A3, and click Format Control
  2. Click in the Cell Link box, and then on the worksheet, click in cell A9, to link the check box to that cell.

    link check box to cell

  3. Click OK to close the Format Control window.
  4. Link the remaining check boxes to the applicable cells in the duplicate data range.
  5. 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:

  1. Select the data and headings in the duplicate data range -- cells B8:H11
  2. On the Ribbon, click the Insert tab
  3. Click the Insert Line Chart command, then click 2-D Line

    insert line chart

  4. Move and resize the chart, if necessary, to fit on the worksheet.
  5. TIP: Position the chart over the duplicate data range, to hide it.

insert line chart

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:

  1. Click on the chart's legend, to select it.
  2. On the keyboard, press the Delete key

To link the chart title to a cell:

  1. Click on the chart's title, to select it.
  2. Click in the Formula bar, and type an Equal Sign (=)
  3. Click on cell B1, which contains the worksheet title
  4. Press Enter, to complete the formula

chart title cell

To add data labels:

  1. Check the boxes, to show all the series.
  2. Click on the first series, to select it.
  3. Click the last point in the selected series, to select the data point.
  4. Right-click on the selected data point, and click AddData Label, then click Add Data Label

    chart add data label

  5. 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.
  6. Right-click on the selected data label, and click Format Data Label
  7. In the Format Data Label pane, under Label Options, add a check mark to Series Name, and remove the check mark from Values

    chart data label format

  8. Repeat steps 2-7, for the remaining data series.
  9. TIP: If the data labels are too close to the lines, click on the chart's Plot Area, and make it a bit narrower.

chart plot area 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.

interactive chart with data

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.

interactive chart with data

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.

  1. In cell A9, type this formula: =A3<>""
  2. 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 Workbook

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

More Chart Tutorials

Interactive Chart - Totals

Charts, Cluster Stack Utility

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall

VBA Code, Copy to a workbook

Search Contextures Sites

 

excel chart tools

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: December 5, 2016 11:00 AM
Contextures RSS Feed