How to create interactive chart with checkboxes in Excel

Excel 2016
Excel has many different useful features that help you present data. One popular way to present data is to create an interactive chart with checkboxes. The Check Box control allows you to define which data series to display on the chart.

Like any other form control, each Check Box needs a target cell on the worksheet that stores the value of that control - TRUE if the checkbox is selected and FALSE for the empty one. See other techniques for creating interactive charts.

Interactive chart with check boxes in Excel 365

To create an interactive chart with checkboxes, do the following:

   1.   Prepare the data that will be shown in the chart

Add additional data to your spreadsheet for checkbox values:

For example, enter in the cells G2 and G3 the values TRUE :

Additional data for check boxes in Excel 365

   2.   Define names

Create data ranges, which will be used for the interactive chart, on the Formulas tab, in the Defined Names group, click Define Name (see Creating and using named ranges for more details):

Define Name in Excel 365

   2.1.   Define whether to display the first data series:

In the New Name dialog box:

  • In the Name field, enter the name of this new range, for example, Series1,
  • In the Refers to field, enter this formula:

= IF (<condition>, <series data>),

where the <condition> is the option to show this data series.

In this example:

= IF ($G$2, $C$3:$C$14):

New Name dialog box in Excel 365

   2.2.   Add the new data range for the other series.

In this example:

  • Name: Series2,
  • Refers to: = IF ($G$3, $D$3:$D$14).

   3.   Create a chart that includes all data series

Chart with new data in Excel 365

See how to create a combination chart for more details.

   4.   Change the data series

   4.1.   Do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click on the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

   4.2.   On the Select Data Source dialog box, for the Legend Entries (Series), select the first data series that you would like to hide and show using the checkboxes and click the Edit button:

Select Data Source in Excel 365

   4.3.   In the Edit Series dialog box, change Series Y values (or Series values) to the appropriate names:

Edit Series in Excel 365

   4.4.   Repeat the previous steps for all other data series you will show and hide using the checkboxes.

   5.   Add the checkboxes to the chart

   5.1.   On the Developer tab (see Show the Developer tab), in the Controls group, click the Insert dropdown list and then choose Check Box (Form Controls):

Controls, Check box in Excel 365

Note: You can also add to the Quick Access Toolbar (see how to add commands to the Quick Access Toolbar):

  • The entire Insert controls dropdown list,
  • The Check Box control.

Add as many checkboxes as the data series will be selected, and place them where you want.

In this example, just two checkboxes were used:

Chart with option buttons in Excel 365

   5.2.   Format checkboxes:

   5.2.1.   Double-click on every checkbox and change the label.

   5.2.2.   Right-click on each checkbox and choose Format Control... in the popup menu:

Format Control in Excel 365

   5.2.3.   In the Format Control dialog box, on the Control tab, choose the appropriate cell in the Cell link field (in this example, G2):

Format Control Check box in Excel 365

   5.2.4.   Format other checkboxes (in this example, for cell G3).

Make any other adjustments to get the look you desire.

Note: Excel will use the colors from the theme by default to show the data series. To use your colors for checked data series, create a new theme with custom colors. For example:

Custom colors for chart in Excel 365

See how to change the default colors that Excel uses for chart series for more details.

See also this tip in French: Comment créer un graphique interactif avec des cases à cocher dans Excel.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.