Filtering PivotTable data in Excel - 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. Which filter setting will show only products with total sales greater than $500?

A Apply a Label Filter on the product field with condition 'Begins With' 'S'

B Apply a Value Filter on the sales field with condition 'Greater Than' 500

C Apply a Report Filter selecting only products with sales over 500

D Sort the sales column in descending order

💡 Hint

Think about filtering based on numbers, not text labels.

Value Filters let you filter PivotTable data based on the values in the data area, such as sales amounts. Label Filters filter based on text labels, which won't work for numeric sales filtering.

❓ Function Choice

intermediate

Choose the correct filter type for dates in a PivotTable

You want to filter a PivotTable to show data only from the year 2023. Which filter type should you use?

A Date Filter

B Value Filter

C Label Filter

D Manual Filter

💡 Hint

Dates have a special filter category in PivotTables.

Date Filters allow you to filter PivotTable data by specific date ranges or years easily.

🎯 Scenario

advanced

Filter PivotTable to exclude specific categories

You have a PivotTable showing sales by category. You want to exclude the categories 'Electronics' and 'Furniture' from the view. What is the best way to do this?

A Use the Label Filter to exclude 'Electronics' and 'Furniture'

B Sort the categories and delete the rows for 'Electronics' and 'Furniture'

C Apply a Value Filter to exclude categories with sales less than 1000

D Manually uncheck 'Electronics' and 'Furniture' in the Row Labels filter dropdown

💡 Hint

Think about quick ways to exclude specific items without deleting data.

Manually unchecking items in the filter dropdown is the simplest way to exclude specific categories in a PivotTable.

❓ data_analysis

advanced

Analyze effect of multiple filters on PivotTable data

A PivotTable has filters on Region and Product Category. Region filter is set to 'East' and Product Category filter is set to 'Office Supplies'. What data will the PivotTable show?

A All sales data for Office Supplies category regardless of region

B All sales data for the East region regardless of category

C All sales data for the East region and Office Supplies category only

D All sales data for all regions and categories

💡 Hint

Filters combine to narrow down data shown.

When multiple filters are applied, the PivotTable shows data that meets all filter conditions simultaneously.

🧠 Conceptual

expert

Understanding Slicer impact on PivotTable filtering

You add a Slicer connected to a PivotTable for the 'Salesperson' field. Selecting multiple salespersons in the Slicer filters the PivotTable. What happens if you clear all selections in the Slicer?

A The PivotTable shows data for all salespersons (no filter applied)

B The PivotTable shows no data because no salesperson is selected

C The PivotTable crashes and shows an error

D The PivotTable shows data only for the last selected salesperson before clearing

💡 Hint

Think about what clearing a filter means.

Clearing all selections in a Slicer removes the filter, so the PivotTable shows all data for that field.