Creating a PivotTable in Excel - Business Scenario Walkthrough

Scenario Mode

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

📋 Request: Your manager wants a summary report showing total sales by region and product category for the last quarter.

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

🎯 Deliverable: Create a PivotTable that shows Regions as rows, Product Categories as columns, and the sum of Sales Amount as values.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1200
2024-01-12	South	Clothing	850
2024-02-03	East	Electronics	950
2024-02-15	West	Clothing	700
2024-03-10	North	Clothing	650
2024-03-22	South	Electronics	1100
2024-03-28	East	Clothing	400
2024-03-30	West	Electronics	1300

1

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

Select cells A1:D9.

Expected Result

The data table is highlighted and ready for PivotTable creation.

2

Step 2: Insert a PivotTable using 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 'Region' field to the Rows area of the PivotTable.

Drag 'Region' from the field list to the Rows box.

Expected Result

PivotTable rows show each unique Region: East, North, South, West.

4

Step 4: Add 'Product Category' field to the Columns area of the PivotTable.

Drag 'Product Category' from the field list to the Columns box.

Expected Result

PivotTable columns show each unique Product Category: Clothing, Electronics.

5

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

Drag 'Sales Amount' to the Values box; ensure it shows 'Sum of Sales Amount'.

Expected Result

PivotTable displays total sales amounts for each Region and Product Category.

6

Step 6: Format the PivotTable for clarity.

Apply 'Currency' number format to the Sum of Sales Amount values.

Expected Result

Sales amounts are shown with currency formatting, e.g., $1,200.00.

Final Result

PivotTable Report

Region    | Clothing | Electronics | Grand Total
---------------------------------------------
East      | $400     | $950        | $1,350
North     | $650     | $1,200      | $1,850
South     | $850     | $1,100      | $1,950
West      | $700     | $1,300      | $2,000
---------------------------------------------
Grand Total| $2,600  | $4,550      | $7,150

✓West region has the highest total sales at $2,000.

✓Electronics sales ($4,550) are higher than Clothing sales ($2,600).

✓South region has strong sales in both categories.

Bonus Challenge

Add a filter to the PivotTable to show sales only for February and March.

Show Hint

Add the 'Date' field to the Filters area and select dates from 2024-02-01 to 2024-03-31.