Filtering PivotTable data in Excel - Real Business Scenario

Scenario Mode

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

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

📊 Data: You have sales data including Date, Region, Product Category, and Sales Amount for various transactions.

🎯 Deliverable: Create a PivotTable that summarizes total sales 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	950
2024-01-10	West	Furniture	1100
2024-01-11	East	Furniture	1300
2024-01-12	North	Clothing	400
2024-01-13	West	Electronics	900
2024-01-14	South	Clothing	500

1

Step 1: Select the entire sales data table including headers.

Select cells A1:D10.

Expected Result

The data range is selected for creating a PivotTable.

2

Step 2: Insert a PivotTable based on the selected data.

Go to Insert tab > PivotTable > Choose 'New Worksheet' > Click OK.

Expected Result

A blank PivotTable is created on a new worksheet.

3

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

Drag 'Product Category' field to Rows area in PivotTable Fields pane.

Expected Result

PivotTable rows show each product category.

4

Step 4: Add 'Sales Amount' to the Values area to sum sales.

Drag 'Sales Amount' field to Values area; ensure it shows 'Sum of Sales Amount'.

Expected Result

PivotTable shows total sales for each product category.

5

Step 5: Add 'Region' to the Filters area to filter data by region.

Drag 'Region' field to Filters area in PivotTable Fields pane.

Expected Result

A filter dropdown for Region appears above the PivotTable.

6

Step 6: Filter the PivotTable to show only 'East' and 'West' regions.

Click the Region filter dropdown > Select only 'East' and 'West' > Click OK.

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      | 2100
Clothing         | 1550
Furniture        | 2400

(Filtered to East and West regions only)

✓Electronics sales total 2100 in East and West regions.

✓Clothing sales total 1550 in East and West regions.

✓Furniture sales total 2400 in East and West regions.

✓Filtering by region helps focus on specific market areas.

Bonus Challenge

Add a slicer for Region to allow easy interactive filtering between regions.

Show Hint

Select the PivotTable, go to PivotTable Analyze tab > Insert Slicer > Choose 'Region' > Use slicer buttons to filter.