Chart interactivity in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of FILTER formula linked to chart

You have a data table with sales in column A and months in column B. You use =FILTER(A2:A13, B2:B13=E1) to show sales for the month typed in cell E1. If E1 contains "March", what will the FILTER formula output?

A Only the first sales value from column A

B An error because FILTER cannot use text criteria

C All sales values from column A where the month in column B is "March"

D All sales values from column A regardless of month

💡 Hint

FILTER returns rows matching the condition you give it.

FILTER returns only the sales values where the month matches the text in E1. So if E1 is "March", only sales for March appear.

❓ Function Choice

intermediate

Best function to create interactive chart data

You want to create a chart that updates automatically when you select a category from a dropdown in cell D1. Which function is best to use to filter data dynamically for the chart?

A FILTER

B VLOOKUP

C SUM

D ARRAYFORMULA

💡 Hint

You need to return multiple rows based on a condition.

FILTER returns multiple rows that meet a condition, perfect for dynamic chart data based on dropdown selection.

🎯 Scenario

advanced

Chart does not update when dropdown changes

You created a chart based on a FILTER formula that depends on a dropdown in cell B1. But when you change the dropdown, the chart does not update. What is the most likely reason?

A The chart is linked to the original data, not the FILTER formula output

B The dropdown cell B1 is locked and cannot change

C FILTER formula has a syntax error

D Charts never update automatically in Google Sheets

💡 Hint

Check what data the chart is using.

If the chart is linked to the original data range, it won't reflect changes in the FILTER output. The chart must use the FILTER formula output range.

❓ data_analysis

advanced

Number of data points in interactive chart

You have a data table with 100 rows. You use =FILTER(A2:A101, C2:C101=F1) where F1 is a dropdown with 5 categories. If category "X" appears 20 times in column C, how many data points will the chart show when "X" is selected?

A 0

B 5

C 100

D 20

💡 Hint

FILTER returns only rows matching the condition.

FILTER returns all rows where column C equals "X". Since "X" appears 20 times, the chart will show 20 data points.

📊 Formula Result

expert

Output of dynamic chart data with multiple conditions

Given data in columns A (Sales), B (Month), and C (Region), what is the output of this formula?

=FILTER(A2:A20, (B2:B20=E1)*(C2:C20=F1))

If E1 is "April" and F1 is "East", what does this formula return?

A An error because FILTER cannot handle multiple conditions

B Sales values where Month is "April" AND Region is "East"

C All sales values regardless of Month or Region

D Sales values where Month is "April" OR Region is "East"

💡 Hint

Multiplying conditions means both must be true.

The formula filters sales where both conditions are true: Month equals E1 and Region equals F1. Multiplying conditions acts like AND.