Charting a filtered table

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. You filter a table to show only the 'Tech' category. What happens to a column chart built from that table?

A The chart updates to plot only the visible Tech rows

B The chart keeps plotting every row, including the hidden ones

2. Which action makes the chart show all the data again?

A Clear the filter so the hidden rows come back

B Delete the chart and leave the filter on

3. How does a filter button on a table help make a chart interactive?

A The filter button changes the chart colours but never the data

B Choosing a different value in the filter changes which rows the chart plots

4. Which statement about hidden rows is true with default chart settings?

A Hidden rows are plotted but shown in grey

B Hidden rows are not plotted on the chart

5. Which setting makes a chart keep plotting rows that a filter has hidden?

A Show data in hidden rows

B Show data in filtered labels

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Plot visible cells only		ASUM counts hidden rows too, so the total will not match the filtered chart. Use =SUBTOTAL(109,B2:B50) to add visible rows only.
2Show data in hidden rows and columns		BFilter arrow on the House heading, tick only Red
3A filter button on a table heading lets the user choose which rows to show, which updates the chart		CData > Clear (or Clear Filter From House)
4Clearing the filter brings the hidden rows back, so the chart plots all the data again		DShow data in hidden rows is turned on, so hidden Year 10 rows are still plotted. Untick it so only visible rows appear.
5Filter the table to Year 9, but the chart still shows Year 10 columns (Show data in hidden rows is ticked)		EChart setting that leaves out filtered-out rows, so the chart shows only the rows still visible in the table
6=SUM(B2:B50) used for the chart total after filtering to Red house		FChart setting that makes the chart plot hidden rows too, so filtering no longer changes the chart

Definitions

Plot visible cells only

Fill in the Blanks

Complete each line of code by filling in the missing parts.

chart.source ← filteredTable chart.showHiddenRows ← _____

___________(table, "Region", "North") chart.update()

clearFilter(table) chart.plot(table._______)

IF showHiddenRows = FALSE THEN plot(visibleRows) ____ plot(allRows)

ON _____ northButton applyFilter(table, "Region", "North") chart.refresh()

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Select A1:B9

B Insert column chart

C Filter House to Red

D Chart still shows all rows

Question 2

A Delete rows not in North

B Chart plots visible rows only

C Clear the filter

D Chart plots all rows again

Question 3

A Create chart from table A1:C9

B Re-insert the chart each time

C Tick North only

D Chart updates automatically

Question 4

A Click the chart

B Chart Title > Hidden and Empty

C Tick Show data in hidden rows

D Hidden rows are now plotted

Question 5

A Filter Year to 2024

B Other years' rows are hidden

C Chart still plots hidden rows

D Clear filter: all rows return

Answer Key

Charting a filtered table

Section A: Quick Questions

A — The chart updates to plot only the visible Tech rows

A — Clear the filter so the hidden rows come back

B — Choosing a different value in the filter changes which rows the chart plots

B — Hidden rows are not plotted on the chart

A — Show data in hidden rows

Section B: Match the Pairs

Plot visible cells only → Chart setting that leaves out filtered-out rows, so the chart shows only the rows still visible in the table

Show data in hidden rows and columns → Chart setting that makes the chart plot hidden rows too, so filtering no longer changes the chart

A filter button on a table heading lets the user choose which rows to show, which updates the chart → Filter arrow on the House heading, tick only Red

Clearing the filter brings the hidden rows back, so the chart plots all the data again → Data > Clear (or Clear Filter From House)

Filter the table to Year 9, but the chart still shows Year 10 columns (Show data in hidden rows is ticked) → Show data in hidden rows is turned on, so hidden Year 10 rows are still plotted. Untick it so only visible rows appear.

=SUM(B2:B50) used for the chart total after filtering to Red house → SUM counts hidden rows too, so the total will not match the filtered chart. Use =SUBTOTAL(109,B2:B50) to add visible rows only.

Section C: Definitions

Plot visible cells only — A chart built from a filtered table plots only the visible rows, so applying or clearing a filter updates the chart. Hidden rows stay out unless Show data in hidden rows is on.

Section D: Fill in the Blanks

chart.source ← filteredTable
chart.showHiddenRows ← FALSE

applyFilter(table, "Region", "North")
chart.update()

clearFilter(table)
chart.plot(table.allRows)

IF showHiddenRows = FALSE THEN
  plot(visibleRows)
ELSE plot(allRows)

ON CLICK northButton
  applyFilter(table, "Region", "North")
  chart.refresh()

Section E: Spot the Error

D — A chart built from a filtered table plots only the visible rows. After filtering to Red, the chart shows only the Red rows.

A — Filtering hides rows without deleting them, so clearing the filter brings them back. Deleting rows removes the data. The line should be 'Filter Region to North'.

B — The chart is linked to the table, so using the filter button updates it automatically. You do not need to rebuild it. The line should be 'Use filter button on Region'.

B — The Show data in hidden rows setting is found under Select Data > Hidden and Empty Cells, not in the chart title options.

C — By default a chart ignores hidden rows. After filtering, the hidden years are not plotted. The line should be 'Chart skips the hidden rows'.