Create an interactive chart with series-selection checkbox in Excel
In Excel, we usually insert a chart for better displaying data, sometimes, the chart with more than one series selections. In this case, you may want to show the series by checking the checkboxes. Supposing there are two series in the chart, check checkbox1 to display series 1, check checkbox2 to display series 2, and both checked, display two series as below screenshot shown.
Create an interactive chart with series-selection checkbox by Bulit-in features
Create an interactive chart with series-selection checkbox by Kutools for Excel
| Feature | Checkboxes + Formulas | Kutools for Excel |
|---|---|---|
| Best for | Users who want to build a checkbox-controlled interactive chart manually with Excel’s built-in tools | Quickly creating an interactive line chart with checkboxes |
| Control type | Form Control Checkboxes | Automatically generated checkboxes |
| Multiple series at once | Yes; each series can be shown or hidden independently | Yes; multiple series can be displayed together |
| Helper data required | Yes; requires linked cells and a separate helper range | No extra helper range required |
| Formula required | Yes; uses formulas such as IF and NA() | No |
| Linked cell required | Yes; each checkbox must be linked to a TRUE/FALSE cell | No manual linked-cell setup |
| Setup complexity | Advanced; requires multiple manual steps | Very simple |
| Main advantage | Uses Excel’s built-in tools and offers full control over the setup | Creates an interactive checkbox chart in only a few steps |
| Main limitation | Requires checkboxes, linked cells, helper data, formulas, and considerable setup | Requires Kutools for Excel |
Create an interactive chart with series-selection checkbox by Bulit-in features
1. Firstly, please arrange the data range as below screenshot shown:
2. Then type below data into a blank range, supposing here, type 2018 into cell A6, TRUE in the cell B6, 2019 into cell A7, TURE in cell B7.
2018 and 2019 are the row header of the data that will be used as series in the chart.
3. Then insert two checkboxes. Click Developer > Insert > Check Box (From Control). Then draw a checkbox in a blank place. Repeat this step to draw another checkbox.
4. Then right click at the first checkbox, choose Edit Text from the context menu, then change the name of the checkbox 1 to 2018 (the first series name you will use in chart). Repeat this step to change the name of checkbox 2.
5. Then right click at checkbox 2018, select Format Control from the context menu, in the Format Control dialog, in Cell link textbox, choose the cell B6 that you typed TRUE in step 2, and click OK.
Repeat this step to link the checkbox 2019 to cell B7.
Now when the checkboxes are checked, the text in B6 or B7 will display TRUE, or display FALSE.
6. Copy the data range, here is A2:M4, and paste them to a blank cell, for instance cell A10. Then delete all data range excluding the headers. See screenshot:
7. In the cell B11, the first blank cell of the pasted data range, type this formula =IF($B$6,B3,NA()), then drag the auto fill handle right to fill all cells of the data range with this formula.
8. In the cell B12, type this formula =IF($B$7,B4,NA()), then drag the auto fill handle right to fill all cells of the data range with this formula.
9. Now select the pasted data range excluding first column data, for instance, B10:M12, click Insert tab and choose a type of chart in Chart group.
Now a blank chart inserted.
You can check the checkboxes to display the data as you need.
Create an interactive chart with series-selection checkbox by Kutools for Excel
If you want an easier way to create an interactive chart without setting up formulas, helper columns, or complex controls, Kutools for Excel provides a convenient solution. With its Check Box Line Chart feature, you can quickly create a dynamic chart with checkboxes and freely show or hide multiple data series with just a few clicks.
- Go to Kutools > Charts > Category Comparison > Check Box Line Chart.
- In the Check Box Line Chart dialog box, specify the required ranges:
- Data range: Select the values you want to plot in the chart.
- Axis Labels: Select the cells containing the category labels for the horizontal axis.
- Legend Entries (Series): Select the series names that will appear beside the checkboxes.
- Click OK to create the chart.
Conclusion
Interactive charts with checkboxes make it easier to compare different data series in Excel by allowing users to show or hide each series as needed.
The Checkboxes + Formulas method uses Excel’s built-in Form Controls and gives you full control over the chart setup, but it requires linked cells, helper data, and formulas. For a simpler approach, Kutools for Excel can create a checkbox-controlled interactive chart in just a few steps without manually building helper ranges or linked-cell formulas.
Choose the manual method when you need a highly customized setup, or use Kutools when you want to build an interactive checkbox chart more quickly with fewer configuration steps.
Click to download the sample file
Dynamic highlight data point on Excel chart
If a chart with multiple series and a lot of data plotted on it, which will be difficult to read or find only relevant data in one series you use.
Conditional formatting stacked bar chart in Excel
This tutorial, it introduces how to create conditional formatting stacked bar chart as below screenshot shown step by step in Excel.
Creating an actual vs budget chart in Excel step by step
This tutorial, it introduces how to create conditional formatting stacked bar chart as below screenshot shown step by step in Excel.
Create a chart with date and time on X axis in Excel
in this article, I introduce the way for how to show the date and time on X axis correctly in the Chart.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
