Row and column fields 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 report showing total sales by product category and by region for the last quarter.

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

🎯 Deliverable: Create a pivot table that uses Product Category as row fields and Region as column fields, showing the sum of Sales Amount.

Sample Data

Date	Region	Product Category	Product Name	Units Sold	Sales Amount
2024-01-05	North	Electronics	Headphones	10	500
2024-01-12	South	Electronics	Speakers	5	300
2024-02-03	East	Furniture	Desk	2	400
2024-02-15	West	Furniture	Chair	4	200
2024-03-10	North	Clothing	Jacket	7	350
2024-03-18	South	Clothing	T-Shirt	15	225
2024-03-22	East	Electronics	Camera	3	900
2024-01-25	West	Clothing	Hat	8	120
2024-02-28	North	Furniture	Shelf	1	150
2024-03-30	South	Furniture	Table	3	600

1

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

No formula needed; just select the range A1:F11.

Expected Result

The data range is selected for creating the pivot table.

2

Step 2: Insert a pivot table using the selected data.

Go to Insert > PivotTable, choose to place it in a new worksheet.

Expected Result

A blank pivot table is created on a new sheet.

3

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

Drag 'Product Category' field to Rows.

Expected Result

Pivot table rows show Electronics, Furniture, Clothing.

4

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

Drag 'Region' field to Columns.

Expected Result

Pivot table columns show North, South, East, West.

5

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

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

Expected Result

Pivot table shows total sales amount for each product category by region.

6

Step 6: Format the pivot table for clarity.

Use PivotTable Design tab to apply a simple style and add grand totals.

Expected Result

Pivot table is easy to read with totals for rows and columns.

Final Result

                 North   South   East   West   Grand Total
Electronics      500     300     900    0      1700
Furniture        150     600     400    200    1350
Clothing        350      225     0      120    695
Grand Total     1000     1125    1300   320    3745

✓Electronics had the highest total sales amount of $1700.

✓East region had strong sales in Electronics and Furniture.

✓West region had the lowest total sales amount of $320.

✓Clothing sales were strongest in the North region.

Bonus Challenge

Add a filter to the pivot table to show sales only for the last two months.

Show Hint

Add the 'Date' field to the Filters area and select the last two months.