Chart interactivity in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a chart that updates when the data changes.

Google Sheets

Insert a chart by selecting the data range and then clicking on [1].

Drag options to blanks, or click blank then click option'

AFormat > Chart

BInsert > Chart

CData > Chart

DTools > Chart

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing the wrong menu like Format or Data.

Trying to create a chart from Tools menu.

To create a chart in Google Sheets, you use the Insert menu and select Chart.

2fill in blank

medium

Complete the code to link the chart to the data range A1:B5.

Google Sheets

Select the chart, then in the Chart editor, set the Data range to [1].

Drag options to blanks, or click blank then click option'

AA1:B5

BA1:B10

CB1:C5

DA2:B6

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting a range that is too big or too small.

Choosing the wrong columns or rows.

The data range must match the cells containing your data. Here, it's A1:B5.

3fill in blank

hard

Fix the error in the formula that dynamically updates the chart range when rows are added.

Google Sheets

Use the formula =[1](A1:B) to select all rows in columns A and B.

Drag options to blanks, or click blank then click option'

ASORT

BFILTER

CIMPORTRANGE

DARRAYFORMULA

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER which requires criteria.

Using IMPORTRANGE which imports from another sheet.

ARRAYFORMULA allows the formula to cover all rows dynamically in the range A1:B.

4fill in blank

hard

Fill both blanks to create a dropdown filter that updates the chart based on the selected category.

Google Sheets

Create a dropdown in cell D1 using Data > Data validation with criteria [1], then use =FILTER(A2:B10, A2:A10 [2] D1) to filter data.

Drag options to blanks, or click blank then click option'

A"List of items"

B"Number"

C=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Number' as dropdown criteria for categories.

Using '>' instead of '=' in the filter condition.

The dropdown uses a list of items, and the FILTER formula uses '=' to match the selected category.

5fill in blank

hard

Fill all three blanks to create a dynamic chart title that shows the selected category from cell D1.

Google Sheets

Set the chart title formula to ="Sales for " & [1](D1) & " in " & [2](D2) & "."

Drag options to blanks, or click blank then click option'

AUPPER

BLOWER

CTEXT

DCONCATENATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOWER instead of UPPER for emphasis.

Not formatting the date properly.

Not joining strings correctly.

UPPER converts the category to uppercase, TEXT formats the date in D2, and CONCATENATE joins the strings.

Practice

1. What is the main purpose of adding a slicer to a chart in Google Sheets?

easy

A. To add colors to the chart

B. To change the chart type from bar to line

C. To filter data interactively and update the chart automatically

D. To export the chart as an image

Solution

1. Step 1: Understand what a slicer does

   A slicer lets you filter data easily by clicking options without changing the original data.

2. Step 2: Connect slicer to chart

   When you use a slicer, the chart updates automatically to show only the filtered data.

3. Final Answer:

   To filter data interactively and update the chart automatically -> Option C

4. Quick Check:

   Slicer filters data and updates chart [OK]

Hint: Slicers filter data and update charts live [OK]

Common Mistakes:

• Thinking slicers change chart type
• Confusing slicers with color formatting
• Believing slicers export charts

2. Which of the following is the correct way to add a slicer in Google Sheets?

easy

A. Data > Slicer > Add slicer

B. Insert > Chart > Add Slicer

C. Format > Slicer > New slicer

D. Tools > Slicer > Create slicer

Solution

1. Step 1: Locate the slicer menu

   In Google Sheets, slicers are found under the Data menu, not Insert, Format, or Tools.

2. Step 2: Confirm correct menu path

   The correct path is Data > Slicer > Add slicer to create a new slicer control.

3. Final Answer:

   Data > Slicer > Add slicer -> Option A

4. Quick Check:

   Slicer is under Data menu [OK]

Hint: Find slicers under Data menu in Google Sheets [OK]

Common Mistakes:

• Looking for slicers under Insert or Format menus
• Trying to add slicer from Tools menu
• Assuming slicers are part of chart insertion

3. Given a chart linked to a data range A1:B5 and a slicer filtering column A with values {"East", "West"}, what will the chart display if you select only "East" in the slicer?

medium

A. All data rows regardless of filter

B. Only data rows where column A is "East"

C. Only data rows where column B is "East"

D. An empty chart with no data

Solution

1. Step 1: Understand slicer filtering

   The slicer filters the data range by the selected values in column A, so only rows with "East" remain visible.

2. Step 2: Effect on chart display

   The chart updates to show only the filtered rows matching "East" in column A, ignoring other rows.

3. Final Answer:

   Only data rows where column A is "East" -> Option B

4. Quick Check:

   Slicer filters rows by selected value [OK]

Hint: Slicer filters rows by selected column values [OK]

Common Mistakes:

• Thinking slicer filters column B instead of A
• Assuming chart ignores slicer filters
• Believing chart shows empty if filter applied

4. You added a slicer to filter a chart, but the chart does not update when you change slicer selections. What is the most likely cause?

medium

A. The chart is locked and cannot update

B. The chart type does not support filtering

C. The slicer is set to filter a different sheet

D. The slicer is not connected to the chart's data range

Solution

1. Step 1: Check slicer connection

   If the slicer is not linked to the same data range as the chart, changes in slicer won't affect the chart.

2. Step 2: Verify chart and slicer data range match

   Ensure both slicer and chart use the same data range so filtering works correctly.

3. Final Answer:

   The slicer is not connected to the chart's data range -> Option D

4. Quick Check:

   Slicer must link to chart data range [OK]

Hint: Ensure slicer and chart share the same data range [OK]

Common Mistakes:

• Assuming chart type blocks filtering
• Thinking slicer filters different sheets automatically
• Believing charts can be locked to prevent updates

5. You want to create an interactive dashboard in Google Sheets where users can filter sales data by region and product category, and see the chart update instantly. Which combination of features should you use?

hard

A. Add two slicers: one for region and one for product category, linked to the chart's data range

B. Create two separate charts, one filtered by region and one by product category

C. Use filter views to manually filter data before viewing the chart

D. Manually change the chart data range each time filters are needed

Solution

1. Step 1: Identify interactive filtering needs

   Users want to filter by multiple criteria (region and product category) and see one chart update instantly.

2. Step 2: Use slicers for multiple filters

   Adding two slicers linked to the same data range allows filtering by both criteria interactively, updating the chart live.

3. Step 3: Why other options fail

   Separate charts don't combine filters; filter views are manual and not interactive; manual range changes are slow and error-prone.

4. Final Answer:

   Add two slicers: one for region and one for product category, linked to the chart's data range -> Option A

5. Quick Check:

   Multiple slicers update chart interactively [OK]

Hint: Use multiple slicers linked to one chart for interactive filtering [OK]

Common Mistakes:

• Using separate charts instead of one interactive chart
• Relying on manual filter views for interactivity
• Changing data range manually instead of slicers