Filtering with filter views in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Using Filter Views to Show Only Sales Above $500

You have a sales data sheet with columns: Product, Sales, and Date. You want to create a filter view that only shows rows where Sales is greater than 500.

Which step correctly creates this filter view?

A Use conditional formatting to highlight Sales above 500, then filter by color.

B Click Data > Create a filter, then manually hide rows with Sales less than 500.

C Select Sales column, right-click, choose 'Hide column' for values less than 500.

D Click Data > Filter views > Create new filter view, then set filter on Sales column to 'Greater than' 500.

💡 Hint

Filter views allow you to save and reuse filters without changing the original data for others.

Option D correctly creates a filter view and applies a filter condition on the Sales column to show only rows with sales greater than 500. Other options either do not create a filter view or do not filter data properly.

📊 Formula Result

intermediate

Result of Applying a Filter View on Dates

You have a filter view that filters the Date column to show only dates after 2023-01-01. The data has these dates:

• 2022-12-31
• 2023-01-01
• 2023-01-02
• 2023-02-15

How many rows will be visible in the filter view?

A 2

B 3

C 1

D 4

💡 Hint

Remember the filter is for dates strictly after 2023-01-01.

Only dates after 2023-01-01 are shown, so 2023-01-02 and 2023-02-15 are visible. That makes 2 rows.

❓ Function Choice

advanced

Choosing the Right Function to Filter Data in a Filter View

You want to create a filter view that shows only rows where the Status column equals "Completed" and the Amount column is greater than 1000.

Which formula would you use in a helper column to assist filtering?

A =OR(Status="Completed", Amount>1000)

B =AND(Status="Completed", Amount>1000)

C =IF(Status="Completed", Amount>1000, FALSE)

D =NOT(AND(Status="Completed", Amount<=1000))

💡 Hint

Both conditions must be true to show the row.

Option B uses AND to require both conditions: Status is "Completed" and Amount is greater than 1000. Other options either use OR or incorrect logic.

❓ data_analysis

advanced

Analyzing Filter View Impact on Data Summary

You have a dataset with 100 rows. You create a filter view to show only rows where Category is "Electronics". There are 30 such rows.

If you use the SUBTOTAL(9, Amount) function on the Amount column, what will it calculate when the filter view is active?

A Average of Amount for the 30 visible rows.

B Sum of Amount for all 100 rows, ignoring filter view.

C Sum of Amount only for the 30 visible rows in the filter view.

D Count of rows where Amount is not empty.

💡 Hint

SUBTOTAL with function number 9 sums only visible rows, including filtered ones.

SUBTOTAL with 9 sums only visible rows, so it sums Amount for the 30 rows shown by the filter view, ignoring hidden rows.

🧠 Conceptual

expert

Understanding Filter Views and User Collaboration

In Google Sheets, multiple users are working on the same sheet simultaneously. One user creates a filter view to analyze data without affecting others.

Which statement best describes how filter views behave in this scenario?

A Filter views apply only to the user who created them and do not change what others see.

B Filter views change the sheet for all users, hiding rows globally.

C Only one filter view can exist at a time, shared by all users.

D Filter views automatically refresh and merge filters from all users.

💡 Hint

Think about how filter views help individuals without disrupting others.

Filter views are personal views that only affect the user who activates them. Other users see the sheet without those filters unless they activate the same filter view.