Filtering PivotTable data in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a summary report showing total sales by product category, but only for the 'East' and 'West' regions.

📊 Data: You have a sales dataset with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a PivotTable that sums Sales Amount by Product Category, filtered to show only 'East' and 'West' regions.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	East	Electronics	1200
2024-01-06	West	Clothing	850
2024-01-07	North	Electronics	600
2024-01-08	East	Clothing	700
2024-01-09	South	Furniture	400
2024-01-10	West	Furniture	900
2024-01-11	East	Furniture	500
2024-01-12	North	Clothing	300

1

Step 1: Select the entire sales data range including headers (A1:D8).

No formula needed.

Expected Result

Data range selected for PivotTable creation.

2

Step 2: Insert a PivotTable by clicking Insert > Pivot table, and choose to place it in a new sheet.

No formula needed.

Expected Result

A blank PivotTable is created on a new sheet.

3

Step 3: Add 'Product Category' to Rows area of the PivotTable editor.

No formula needed.

Expected Result

PivotTable rows show each product category: Electronics, Clothing, Furniture.

4

Step 4: Add 'Sales Amount' to Values area and set it to SUM.

No formula needed.

Expected Result

PivotTable shows total sales amount for each product category.

5

Step 5: Add 'Region' to Filters area.

No formula needed.

Expected Result

A filter control for Region appears above the PivotTable.

6

Step 6: Click the Region filter dropdown and select only 'East' and 'West' regions.

No formula needed.

Expected Result

PivotTable updates to show sales totals only for East and West regions.

Final Result

PivotTable Report
-----------------
Product Category | Sum of Sales Amount
---------------------------------------
Electronics     | 1200
Clothing        | 1550
Furniture       | 1400

(Filtered to East and West regions only)

✓Electronics sales in East and West total 1200.

✓Clothing sales in East and West total 1550.

✓Furniture sales in East and West total 1400.

✓Filtering by region helps focus on specific market areas.

Bonus Challenge

Add a slicer for Region to allow easy switching between regions in the PivotTable.

Show Hint

Use Insert > Slicer, select the Region column, and connect it to the PivotTable.