Chart interactivity in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

Monthly sales data for 5 months used to create an interactive chart.

Cell	Value
A1	Month
B1	Sales
A2	Jan
B2	100
A3	Feb
B3	150
A4	Mar
B4	120
A5	Apr
B5	170
A6	May
B6	160

Formula Trace

FILTER(B2:B6, A2:A6 = D1)

Step 1: A2:A6 = D1

Step 2: FILTER(B2:B6, [FALSE, TRUE, FALSE, FALSE, FALSE])

Cell Reference Map

     A       B       C       D
1  Month   Sales           Selected Month
2  Jan     100
3  Feb     150
4  Mar     120
5  Apr     170
6  May     160

D1 is the input cell where user types a month to filter sales.

The formula uses the month names in A2:A6 and sales in B2:B6. It filters sales based on the month typed in D1.

Result

     A       B       C       D
1  Month   Sales           Selected Month
2  Jan     100
3  Feb     150
4  Mar     120
5  Apr     170
6  May     160

Result of FILTER formula (e.g. in E1): 150 (sales for Feb)

When user types 'Feb' in D1, the formula returns 150, the sales for February. This value can be linked to a chart to update interactively.

Sheet Trace Quiz - 3 Questions

What does the expression 'A2:A6 = D1' return if D1 contains 'Mar'?

A [FALSE, FALSE, TRUE, FALSE, FALSE]

B [TRUE, FALSE, FALSE, FALSE, FALSE]

C [FALSE, TRUE, FALSE, FALSE, FALSE]

D [FALSE, FALSE, FALSE, TRUE, FALSE]

Key Result

FILTER(range_to_return, condition_range = user_input) filters data dynamically based on user input.