Filtering PivotTable data in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Filter PivotTable to show only sales above $500

You have a PivotTable summarizing sales by product. You want to filter the PivotTable to show only products with total sales greater than $500. Which filter setting will achieve this?

A Apply a Value Filter on the sales field with condition 'Greater than' 500

B Apply a Label Filter on the product field with condition 'Contains' 500

C Sort the sales column descending and manually hide rows below 500

D Use a Report Filter to select products with sales less than 500

💡 Hint

Think about filtering based on the numbers in the sales column, not the product names.

Value Filters let you filter PivotTable rows based on the values in the data, such as sales amounts. Using 'Greater than 500' on the sales field shows only products with sales above $500.

❓ Function Choice

intermediate

Choose the correct filter type for PivotTable row labels

You want to filter a PivotTable to show only rows where the product name starts with 'A'. Which filter type should you use?

A Report Filter

B Value Filter

C Label Filter

D Date Filter

💡 Hint

Product names are text labels, not numbers or dates.

Label Filters are used to filter PivotTable rows based on the text in row labels, such as product names starting with a certain letter.

🎯 Scenario

advanced

Filter PivotTable by multiple criteria

You have a PivotTable showing sales by product and region. You want to filter to show only products starting with 'B' and regions that are either 'East' or 'West'. How do you set the filters?

A Apply a Label Filter on product with 'Begins with B' and use Report Filter to select 'East' and 'West' regions

B Apply a Value Filter on product with 'Begins with B' and Label Filter on region for 'East' or 'West'

C Use Report Filters for both product and region, selecting 'B' products and 'East' or 'West' regions

D Apply Label Filters on both product and region fields with 'Begins with B' and 'Equals East or West'

💡 Hint

Remember that product names are labels and region selection can be done via Report Filters.

Label Filter on product lets you filter names starting with 'B'. Report Filters allow selecting multiple regions like 'East' and 'West'. Combining these filters shows the desired data.

❓ data_analysis

advanced

Identify the effect of removing a filter from a PivotTable

You have a PivotTable filtered to show only sales from the 'North' region. You remove this filter. What happens to the PivotTable data?

A The PivotTable shows sales from regions other than 'North'

B The PivotTable shows no data until a new filter is applied

C The PivotTable shows only sales from the 'North' region still

D The PivotTable shows sales from all regions

💡 Hint

Think about what removing a filter means for the data shown.

Removing a filter clears the restriction, so the PivotTable displays all data without filtering by region.

🧠 Conceptual

expert

Understanding filter hierarchy in PivotTables

In a PivotTable with Report Filters, Row Labels, and Value Filters applied, which filter is applied first when displaying data?

A Row Label Filters are applied first, then Value Filters, then Report Filters

B Report Filters are applied first, then Row Label Filters, then Value Filters

C Value Filters are applied first, then Report Filters, then Row Label Filters

D All filters are applied simultaneously with no order

💡 Hint

Consider how filtering narrows down data step-by-step.

Report Filters limit the overall data set first. Then Row Label Filters narrow rows, and finally Value Filters apply conditions on the summarized values.