PivotTable layouts in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a clear summary of sales by product category and region, showing total sales and quantity sold. They also want the PivotTable to be easy to read with categories and regions clearly separated.

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

🎯 Deliverable: Create a PivotTable that shows Product Category as rows, Region as columns, and displays the sum of Sales Amount and Quantity Sold. Format the PivotTable using the Tabular layout for clarity.

Sample Data

Date	Region	Product Category	Product	Quantity Sold	Sales Amount
2024-01-05	North	Electronics	Smartphone	10	5000
2024-01-07	South	Electronics	Laptop	5	7000
2024-01-10	East	Furniture	Desk	3	900
2024-01-12	West	Furniture	Chair	7	1400
2024-01-15	North	Clothing	Jacket	20	2000
2024-01-18	South	Clothing	T-Shirt	30	900
2024-01-20	East	Electronics	Tablet	8	3200
2024-01-22	West	Clothing	Jeans	15	1500
2024-01-25	North	Furniture	Shelf	4	1200
2024-01-28	South	Furniture	Table	2	600

1

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

Select range A1:F11.

Expected Result

All data rows and headers are selected.

2

Step 2: Insert a PivotTable using the selected data.

Go to Insert > 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.

Expected Result

PivotTable rows show each product category.

4

Step 4: Add 'Region' to the Columns area of the PivotTable.

Drag 'Region' field to Columns area.

Expected Result

PivotTable columns show each region.

5

Step 5: Add 'Sales Amount' to the Values area and set it to sum.

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

Expected Result

PivotTable shows total sales amount for each category and region.

6

Step 6: Add 'Quantity Sold' to the Values area and set it to sum.

Drag 'Quantity Sold' to Values area; ensure it shows 'Sum of Quantity Sold'.

Expected Result

PivotTable shows total quantity sold alongside sales amount.

7

Step 7: Change the PivotTable layout to Tabular form for better readability.

Go to PivotTable Design tab > Report Layout > Show in Tabular Form.

Expected Result

PivotTable displays each field in its own column with clear headers.

8

Step 8: Turn off subtotals for 'Product Category' to simplify the table.

PivotTable Design tab > Subtotals > Do Not Show Subtotals.

Expected Result

No subtotals appear, making the table cleaner.

9

Step 9: Format the Sales Amount and Quantity Sold values as numbers with no decimals.

Right-click values > Number Format > Number > Decimal places: 0 > OK.

Expected Result

Values are easier to read without decimals.

Final Result

PivotTable Report

Product Category | North | South | East | West
------------------------------------------------
Electronics      | 5000  | 7000  | 3200 | 0
Furniture        | 2400  | 600   | 900  | 1400
Clothing        | 2000  | 900   | 0    | 1500

Sum of Quantity Sold

Product Category | North | South | East | West
------------------------------------------------
Electronics      | 10    | 5     | 8    | 0
Furniture        | 14    | 2     | 3    | 7
Clothing        | 20    | 30    | 0    | 15

✓Electronics have the highest sales in the South region.

✓Furniture sales are more evenly spread across regions.

✓Clothing has strong sales quantity in the South and North regions.

Bonus Challenge

Add a slicer to filter the PivotTable by Region so the manager can view sales for one region at a time.

Show Hint

Select the PivotTable, go to PivotTable Analyze tab > Insert Slicer > Choose 'Region' > Click OK.