Click to Show or Hide Excel Chart Data

If you’re building Excel reports for other people to use, you can add a few interactive chart features, to let people customize the reports.
In this example, there is a check box beside each region name, in the sales summary table.

  • If you add a check mark, that region’s data is shown in the chart.
  • If you clear the check mark, the region’s data disappears from the chart.

This is based on a technique that I learned from Jon Peltier, who creates amazing Excel charting utilities.

No Programming Required

There’s no programming required for this technique – the chart is based on a duplicate set of data, which uses the NA function to prevent data from being displayed.
The technique works best for small to mid-sized tables, if you’re setting it up manually. Each check box is linked to a cell in the duplicate data range, and that would be a pain to set up for a long list.

Test the Interactive Online Chart

To see how the chart works, you can test the interactive online version of the chart and worksheet, shown below. (It might not be visible in all browsers.)
Check boxes aren’t available in the interactive online view, so this example uses "X" marks instead.

  • 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 the detailed instructions, and to download the sample file, please visit my Contextures website: Excel Charts – Add Interactive Features.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.