PivotTable formatting in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a clear summary of total sales by product category and region, with easy-to-read formatting.

📊 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 shows total sales amount by Product Category (rows) and Region (columns). Format the pivot table with bold headers, currency format for sales, and alternating row colors for readability.

Sample Data

Date	Region	Product Category	Product Name	Units Sold	Sales Amount
2024-05-01	North	Electronics	Smartphone	10	5000
2024-05-02	South	Electronics	Laptop	5	4500
2024-05-03	East	Home Appliances	Microwave	7	1400
2024-05-04	West	Home Appliances	Refrigerator	3	2700
2024-05-05	North	Furniture	Desk	4	1200
2024-05-06	South	Furniture	Chair	8	800
2024-05-07	East	Electronics	Tablet	6	1800
2024-05-08	West	Furniture	Sofa	2	1500
2024-05-09	North	Home Appliances	Vacuum Cleaner	5	1000
2024-05-10	South	Electronics	Camera	3	1200

1

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

Select range A1:F11.

Expected Result

The data range is selected for pivot table creation.

2

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

Go to Insert > Pivot table > New sheet.

Expected Result

A new sheet with an empty pivot table is created.

3

Step 3: Add 'Product Category' to Rows in the pivot table editor.

Drag 'Product Category' field to Rows area.

Expected Result

Pivot table rows show each product category: Electronics, Home Appliances, Furniture.

4

Step 4: Add 'Region' to Columns in the pivot table editor.

Drag 'Region' field to Columns area.

Expected Result

Pivot table columns show each region: North, South, East, West.

5

Step 5: Add 'Sales Amount' to Values and set aggregation to SUM.

Drag 'Sales Amount' to Values area and 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 sales amount values as currency.

Select sales amount cells in pivot table > Format > Number > Currency.

Expected Result

Sales amounts display with currency symbol and two decimals, e.g., $5,000.00.

7

Step 7: Make the header row and column labels bold.

Select header row and first column > Format > Text > Bold.

Expected Result

Headers stand out with bold text for easy reading.

8

Step 8: Apply alternating row colors for better readability.

Select pivot table range > Format > Alternating colors > Choose a light color style.

Expected Result

Rows alternate in color, making the table easier to scan.

Final Result

Pivot Table: Total Sales Amount by Product Category and Region

          | North    | South    | East     | West     |
---------------------------------------------------------
Electronics | $5,000.00 | $5,700.00 | $1,800.00 |          |
Home Appliances | $1,000.00 |          | $1,400.00 | $2,700.00 |
Furniture   | $1,200.00 | $800.00  |          | $1,500.00 |
---------------------------------------------------------

✓Electronics have the highest total sales, especially in North and South regions.

✓Home Appliances sales are strong in West and East regions.

✓Furniture sales are more evenly spread but lower overall.

Bonus Challenge

Add a Grand Total row and column to the pivot table and format them with a distinct background color.

Show Hint

In the pivot table editor, enable 'Show totals' for rows and columns, then select the total cells and apply a fill color.