Chart interactivity in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants an interactive sales chart that updates based on the selected product category.

📊 Data: You have monthly sales data for different product categories over one year.

🎯 Deliverable: Create an interactive chart in Google Sheets that changes to show sales trends for the selected category using a dropdown menu.

Sample Data

Month	Category	Sales
Jan	Electronics	1200
Jan	Clothing	800
Jan	Home	600
Feb	Electronics	1500
Feb	Clothing	700
Feb	Home	650
Mar	Electronics	1300
Mar	Clothing	900
Mar	Home	700
Apr	Electronics	1600
Apr	Clothing	850
Apr	Home	720

1

Step 1: Create a dropdown menu for product categories.

Select a cell (e.g., E1), then go to Data > Data validation. Choose 'List of items' and enter: Electronics,Clothing,Home. Click Save.

Expected Result

Cell E1 shows a dropdown with Electronics, Clothing, and Home options.

2

Step 2: Create a filtered table that shows sales only for the selected category.

=FILTER({A2:A13, C2:C13}, B2:B13=E1)

Expected Result

A table appears showing Month and Sales only for the category selected in E1.

3

Step 3: Create a chart based on the filtered sales data.

Select the filtered table, then Insert > Chart. Choose a Line chart. Set the X-axis to Month and the Series to Sales.

Expected Result

A line chart appears showing monthly sales for the selected category.

4

Step 4: Test the interactivity by changing the dropdown selection.

Change the value in cell E1 to another category (e.g., Clothing).

Expected Result

The filtered table and chart update automatically to show sales for the new category.

Final Result

Month | Sales
------|-------
Jan   | 1200  
Feb   | 1500  
Mar   | 1300  
Apr   | 1600  

(Line chart showing sales trend for selected category)

✓The interactive chart updates instantly when a different category is selected.

✓This helps the manager quickly compare sales trends across categories.

✓Using FILTER and dropdown menus makes the dashboard dynamic and user-friendly.

Bonus Challenge

Add a second dropdown to select the year and update the chart accordingly.

Show Hint

Add a year column to your data and use FILTER with multiple conditions to filter by both category and year.