Ravindra BagaleCourses & study guides

8. Charts

8.16 Checkbox to Show/Hide a Series

By Last updated:

Steps in Excel

  1. Turn on the Developer tab (Module 12.1): File › Options › Customize Ribbon › tick Developer.
  2. Developer › Controls › Insert › Form Controls › Check Box › draw it › right-click › Edit Text "Show Nashik".
  3. Right-click › Format Control… › Control tab › Cell link $J$1 › OK. Ticking puts TRUE/FALSE in J1.
  4. Helper column for the chart series: =IF($J$1, ChartData!C2, NA()) copied down. #N/A is not plotted in line/scatter charts, so the series disappears when unticked.
  5. Build the line chart on the helper columns. Repeat for other series.

Newer Microsoft 365 builds also have in-cell checkboxes (Insert › Checkbox – may vary by version); the cell itself holds TRUE/FALSE, so =IF(J1, …) works the same way.

Ravindra Bagale's Tip

Hide karaycha series "" (blank text) kiwa 0 ne bharla tar line zero var padte – khup students cha chart vichitra disato. Line/scatter madhun point kadhaycha asel tar NA() vapra. Checkbox cha cell link ek disnar nahi ashya jagi theva, aani tya cell la naav dya (ShowNashik).

Practice task

Add three checkboxes (Pune, Nashik, Nagpur) that show/hide each city's line in one chart. Add a fourth checkbox that shows a 3-month moving average line.

Thodkyaat sangaycha tar (quick recap)

Aadhi prashna, mag chart: compare = bar/column, trend = line, share = pie (≤5), relationship = scatter, distribution = histogram/box, bridge = waterfall, stages = funnel, hierarchy = treemap/sunburst, geography = filled map; title madhe sandesh; dynamic charts sathi Table, helper range + INDEX/XLOOKUP, OFFSET/INDEX names aani checkbox + NA(). Aata pudhe jaauya – dynamic arrays!