By default, in Microsoft Excel, charts do not display data contained in hidden rows or columns. In this example, we will illustrate a simple way to hide and display data series in a chart. As control elements for visualizing or hiding a data series—both in the worksheet and in the chart we will use checkboxes.
To implement this example, follow these steps:
- Prepare a table on the worksheet showing product sales by months.
- Build a chart for the prepared data: go to the Insert tab on the ribbon, and in the Charts group, choose from the Line list the chart type Line with Markers.
- Format the resulting chart.
- Add five CheckBox controls on the worksheet in sequence and set the Caption property for each of them respectively to: Ruler, Pencil Case, Pen, Pencil, Eraser.
- In the Sheet1 worksheet module, add event-handling procedures for the Click events of the CheckBox controls.