Ravindra BagaleCourses & study guides मराठी Track your progress

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

If you fill a series you want to hide with "" (blank text) or 0, the line drops to zero – many students' charts look strange. To remove a point from a line/scatter chart, use NA(). Keep the checkbox's cell link somewhere out of sight, and give that cell a name (ShowNashik).

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!

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of checkbox to show hide a series on a blank sheet for QuickCart Delivery.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Sana Sheikh and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for James O'Neill.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply checkbox to show hide a series to a 5-row made-up QuickCart Delivery table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with checkbox to show hide a series.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as QuickCartDe_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Total billable hours (Easy): Add up Billable hours in D2:D11.
  2. Average rate (Easy): Average of the Rate per hour (₹) column, rounded to 2 decimals.
  3. Rows in Cloud (Easy): How many rows have Category/group "Cloud"? (column B)
  4. Largest billable hours (Easy): The largest value in D2:D11.
  5. Billable hours for Cloud (Medium): Total of the Billable hours column where Practice is "Cloud".
  6. Cloud in Delhi (Medium): Total of the Billable hours column where Practice is "Cloud" and City is "Delhi".
  7. Best in Cloud (Medium): Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 (Medium): Multiply the first-row Billable hours by the first-row Rate per hour (₹).
  9. Look up Hannah Lee (Medium): The Billable hours for "Hannah Lee" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Cloud (Hard): Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. How many rows are above the average "Rate per hour (₹)" (Hard): Count the rows where "Rate per hour (₹)" is above the average "Rate per hour (₹)".
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Checkbox To Show Hide A Series?
Card 1: What is Checkbox To Show Hide A Series? · Download PNG
Revision card 2: Tiny example — Checkbox To Show Hide A Series
Card 2: Tiny example — Checkbox To Show Hide A Series · Download PNG
Revision card 3: Checklist — Checkbox To Show Hide A Series
Card 3: Checklist — Checkbox To Show Hide A Series · Download PNG