Using a Check Box to Dynamically Show or Hide Trend Lines in Excel Charts

Charts

Showing or hiding a trendline in Excel charts or dashboards with a check box gives your users more power. Some charts have so much data that they may want to show or hide the trend line as they work with the dashboard.

Some Excel dashboards I’ve created have had more than six sets of data in one chart. They were so messy looking and the lines so distracting that I added check boxes so each trend line could be shown or hidden.

Use a check box to show or hide the trend line as needed.

In this example the check box control is selected from the Developer tab, Control group, Insert Form Controls. Click on the Check Box control, then drag across the area on the worksheet where you want the check box. Click on the control and type the instructions, “Show Trend” into the center of check box control.

Add a check box from the Form Control on the Developer tab.

Add a check box from the Form Control on the Developer tab.

If you do not have the Developer tab in Excel, then follow the instructions here to install the Developer tab.

When a check box is selected it creates a TRUE value. Deselecting it creates a FALSE. That value is entered into a cell. In this example that is C25. To specify where the check box puts its TRUE/FALSE value,

1. Right-click the check box control and choose Format Control.

2. Select from the Value options the default setting.

3. Select Cell Link and click on the cell that will receive TRUE/FALSE. In this case C25.

4. Choose Ok.

 

The Format Control sets up your check box.

The Format Control sets up your check box.

Now, enter the following array formula into the range D23:023. This formula will calculate the trend data and will show or hide it depending upon the check box value. If C25 is TRUE, then the TREND function is calculated. If C25 is FALSE, then NA() produces a blank in the chart. To enter the array formula,

1. Select the range D24:O24 and create the formula,

=IF(C25,TREND(D23:O23,D22:O22),NA())

2. Enter the array formula by pressing Shift+Ctrl+Array so the formula enters into the range D24:O24 as a single array formula. The formula should appear enclosed in { } braces in the formula bar.

Now, when you select or deselect the check box the trend line will show or hide on the chart. It always feels a little magic to me when I first create one of these.

You can download the Excel workbook containing these dynamic trend line charts here. Dynamic Trend Charts – Advanced.

Learn how to extend a TREND line to create a forecast.

Learn how to select between different TREND lines with a drop-down menu.

Learn how to edit a TREND or array function.

Share the power...