Creating a PivotTable in Google Sheets - 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-15	South	Furniture	850
2024-02-10	East	Electronics	950
2024-02-20	West	Clothing	400
2024-03-05	North	Furniture	700
2024-03-15	South	Clothing	600
2024-03-25	East	Furniture	750
2024-03-30	West	Electronics	1100

1

Step 1: Select the entire sales data table including headers (A1:D9).

Expected Result

The data range A1:D9 is selected.

2

Step 2: Go to the menu and click Insert > Pivot table.

Expected Result

A dialog appears to create a PivotTable.

3

Step 3: In the dialog, confirm the data range is A1:D9 and choose to place the PivotTable in a new sheet. Click Create.

Expected Result

A new sheet opens with an empty PivotTable editor.

4

Step 4: In the PivotTable editor, add 'Region' to Rows.

Expected Result

Rows show the four regions: North, South, East, West.

5

Step 5: Add 'Product Category' to Columns.

Expected Result

Columns show the product categories: Electronics, Furniture, Clothing.

6

Step 6: Add 'Sales Amount' to Values and ensure it is summarized by SUM.

Expected Result

Values show the sum of sales for each region and product category.

7

Step 7: Verify the PivotTable sums match the data: For example, North Electronics = 1200, South Furniture = 850.

Expected Result

PivotTable correctly summarizes sales amounts by region and product category.

Final Result

PivotTable Report

Region   | Electronics | Furniture | Clothing
---------------------------------------------
North    | 1200        | 700       | 
South    |             | 850       | 600
East     | 950         | 750       | 
West     | 1100        |           | 400

Grand Total sums all sales amounts.

✓North region has highest Electronics sales with 1200.

✓South region leads in Furniture and Clothing sales combined.

✓West region has strong Electronics sales at 1100.

✓East region has balanced sales between Electronics and Furniture.

Bonus Challenge

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

Show Hint

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