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.

Practice

1. What is the main purpose of adding a filter to a PivotTable in Google Sheets?

easy

A. To show only specific data that meets certain criteria

B. To change the original data in the spreadsheet

C. To add new rows to the PivotTable

D. To sort the data alphabetically

Solution

1. Step 1: Understand what filtering does in PivotTables

   Filtering lets you focus on parts of your data by showing only what you want.

2. Step 2: Compare filtering with other options

   Filtering does not change original data or add rows; it just hides data that doesn't match criteria.

3. Final Answer:

   To show only specific data that meets certain criteria -> Option A

4. Quick Check:

   Filtering = Show specific data [OK]

Hint: Filters hide unwanted data without changing original data [OK]

Common Mistakes:

• Thinking filters change the original data
• Confusing filters with sorting
• Believing filters add new data

2. Which step correctly describes how to add a filter to a PivotTable in Google Sheets?

easy

A. Change the original data range manually

B. Double-click a cell in the PivotTable to edit it

C. Right-click the PivotTable and select 'Sort'

D. Drag a field into the Filters section in the PivotTable editor

Solution

1. Step 1: Locate the Filters section in the PivotTable editor

   The Filters section is where you add fields to filter your data.

2. Step 2: Add a field by dragging it into Filters

   Dragging a field into Filters applies a filter to the PivotTable data.

3. Final Answer:

   Drag a field into the Filters section in the PivotTable editor -> Option D

4. Quick Check:

   Add filter = Drag field to Filters [OK]

Hint: Drag fields to Filters to filter PivotTable data fast [OK]

Common Mistakes:

• Trying to edit PivotTable cells directly
• Using sort options instead of filters
• Changing original data instead of filtering

3. Given a PivotTable showing sales by region and product, you add a filter on the 'Region' field selecting only 'East'. What will the PivotTable display?

medium

A. Sales data for all regions and products

B. Sales data only for the East region and one product

C. Sales data only for the East region across all products

D. No data will be shown

Solution

1. Step 1: Understand the filter on 'Region' selecting 'East'

   The filter limits data to only rows where Region equals East.

2. Step 2: Check what data remains visible

   All products in the East region remain visible; other regions are hidden.

3. Final Answer:

   Sales data only for the East region across all products -> Option C

4. Quick Check:

   Filter Region=East shows East data only [OK]

Hint: Filter selects matching rows, keeps all other fields intact [OK]

Common Mistakes:

• Thinking filter limits products too
• Expecting all regions to show
• Assuming filter hides all data

4. You added a filter to your PivotTable but it shows no data. What is the most likely reason?

medium

A. The PivotTable source data is empty

B. The filter criteria do not match any data in the source

C. You forgot to refresh the PivotTable after adding data

D. Filters cannot be applied to PivotTables

Solution

1. Step 1: Check the filter criteria

   If the filter criteria do not match any rows, no data will show.

2. Step 2: Verify source data and refresh

   Source data is usually not empty; PivotTables auto-refresh on filter changes.

3. Final Answer:

   The filter criteria do not match any data in the source -> Option B

4. Quick Check:

   No data = filter excludes all rows [OK]

Hint: No data? Check if filter matches any source rows [OK]

Common Mistakes:

• Assuming filters can't hide all data
• Not checking filter values carefully
• Forgetting PivotTables auto-refresh

5. You want to create a PivotTable that shows total sales only for products with sales greater than 1000. How can you apply this filter correctly?

hard

A. Add a filter on the 'Sales' field with condition 'Greater than 1000'

B. Change the original data to remove sales below 1000

C. Sort the PivotTable by sales descending and hide rows manually

D. Add the 'Product' field to Filters and select products manually

Solution

1. Step 1: Use a value filter on the 'Sales' field

   Filters on values like sales allow conditions such as 'Greater than 1000'.

2. Step 2: Apply the condition in the PivotTable filter settings

   Set the filter to show only sales > 1000 to limit data correctly.

3. Final Answer:

   Add a filter on the 'Sales' field with condition 'Greater than 1000' -> Option A

4. Quick Check:

   Value filter > 1000 shows only high sales [OK]

Hint: Use value filters for numeric conditions like > 1000 [OK]

Common Mistakes:

• Filtering products manually instead of by sales value
• Sorting and hiding rows manually (not dynamic)
• Editing original data instead of filtering