X
Excel Zero to Hero Interactive Learning Platform
0%
Module 5

Pivot Tables & Multi-Dimensional Aggregation

intermediate 📁 Dataset: Module_5_Demo.xlsx

🎯 Exercise 04: Multi-Dimensional Pivot Table Architecture & Business Reporting

🎯Business Scenario & Lab Objective

You are the Senior Commercial Analyst for a multinational retail distributor. Your executive team needs answers across two business operations:

  1. International Fresh Produce Operations (Sample Data): Understand category market share (Fruit vs. Vegetables) across 7 international territories, analyze monthly sales pacing, and build automated country summary tabs.
  2. Domestic Enterprise Retail Operations (Retail_part_1 & Retail_part_2): Analyze regional profitability across 40,000 transactions, create custom Calculated Fields, and resolve multi-source data integration challenges.

🛠️ Lab Prerequisites & Dataset

📋 Challenge Level 1: Clean Table Initialization & 2D Matrix (Warmup)

Business Context:

The VP of Global Operations wants to see total revenue grouped by Country (Rows) and Category (Columns).

Step-by-Step Instructions:

  1. Navigate to sheet Sample Data. Click any cell inside Table2 ($A3:J218$).
  2. Use the keyboard sequence Alt + N + V to create a Pivot Table on a New Worksheet.
  3. Immediately rename the Pivot Table to pt_CategoryByCountry (PivotTable Analyze > PivotTable Name).
  4. Drag Country to the Rows quadrant.
  5. Drag Category to the Columns quadrant.
  6. Drag Amount to the Values quadrant.
  7. Format the values as Currency ($#,##0) using Value Field Settings > Number Format.
  8. Apply Tabular Form (Design > Report Layout > Show in Tabular Form).
  9. Right-click the Pivot Table > PivotTable Options > uncheck Autofit column widths on update.
📝Benchmark Check:
  • Grand Total Revenue must evaluate to exactly $598,380.
  • US total revenue must be $121,430 ($58,940 Fruit + $62,490 Vegetables).

📋 Challenge Level 2: Advanced "Show Values As" & Percentage Shares

Business Context:

Raw dollars do not reveal whether Fruit or Vegetables dominates in each country. The VP requests a percentage breakdown of product mix per country.

Step-by-Step Instructions:

  1. In pt_CategoryByCountry, drag Amount into the Values quadrant a second time.
  2. Right-click the second amount column > select Value Field Settings....
  3. Change Custom Name to % Category Mix.
  4. Switch to the Show Values As tab and select % of Row Total.
  5. Click OK.
📝Benchmark Check:
  • For Australia: Fruit must be 45.6%, Vegetables 54.4%, Row Total 100.0%.
  • For Canada: Fruit must be 55.2%, Vegetables 44.8%, Row Total 100.0%.

📋 Challenge Level 3: Chronological Grouping & MoM Variance Pacing

Business Context:

The finance committee needs a monthly revenue progression report showing the month-by-month dollar growth and running annual total.

Step-by-Step Instructions:

  1. Insert a new Pivot Table from Table2 named pt_MonthlyPacing.
  2. Place Month on Rows. Ensure the chronological order is: July, August, September, October, November.
  3. Add three instances of Amount into Values:
    • Instance 1: Name: Monthly Revenue, Format: $#,##0, Calculation: No Calculation.
    • Instance 2: Name: Cumulative YTD, Format: $#,##0, Calculation: Running Total In (Base Field: Month).
    • Instance 3: Name: MoM Growth %, Format: 0.0%, Calculation: % Difference From (Base Field: Month, Base Item: (previous)).
📝Benchmark Check:
  • August MoM Growth %: +33.6% ($112,650 vs $84,320).
  • September MoM Growth %: -12.6% ($98,410 vs $112,650).
  • November Cumulative YTD: $598,380.

📋 Challenge Level 4: Enterprise Calculated Fields & Profit Margin (Retail_part_2)

Business Context:

Switch to the 40,000-transaction Egyptian retail dataset in sheet Retail_part_2. The Chief Financial Officer wants to audit regional profitability across 8 governorates and compute the aggregate Profit Margin.

Step-by-Step Instructions:

  1. Navigate to sheet Retail_part_2.
  2. Convert range $A1:E40001$ into an official Excel Table named RetailSales (Ctrl + T).
  3. Press Alt + N + V to insert a Pivot Table named pt_RegionalProfit.
  4. Drag Region to Rows.
  5. Drag SalesAmount and Profit to Values.
  6. Create a custom metric:
    • Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
    • Name: Profit_Margin
    • Formula: = Profit / SalesAmount
  7. Click Add and OK.
  8. Format Profit_Margin as Percentage (0.0%).
  9. Generate an audit log of your formula using PivotTable Analyze > Fields, Items & Sets > List Formulas.
📝Benchmark Check:
  • Total company-wide sales across all 40,000 orders: $369,000,910 EGP.
  • Total company-wide profit: $51,660,127 EGP.
  • Overall Profit Margin across all governorates: exactly 14.0%.

📋 Challenge Level 5: Dashboard Slicers & Automated Report Filter Pages

Business Context:

The CEO requires an interactive dashboard with country and category slicers, plus seven separate exported worksheets for regional country directors.

Step-by-Step Instructions:

  1. Return to your pt_CategoryByCountry sheet.
  2. Insert a Slicer for Country and a Slicer for Category (PivotTable Analyze > Insert Slicer).
  3. Reformat the Country slicer to 4 Columns using the Slicer ribbon tab.
  4. Verify that holding Ctrl allows selecting multiple countries (Canada + US).
  5. The Report Filter Pages Automation:
    • Drag Country from Rows into the FILTERS quadrant.
    • Place Product on Rows and Amount on Values.
    • Go to PivotTable Analyze > Options (dropdown) > Show Report Filter Pages....
    • Select Country and click OK.
  6. Result Inspection: Verify that Excel instantly generated seven distinct worksheets (Australia, Canada, France, Germany, New Zealand, UK, US), each filtered to that country's specific product sales!

💡 Solutions & Verification

Full step-by-step walkthroughs, formula explanations, and troubleshooting tips are documented in: 👉 Ex04 Solutions

💡 Reveal Step-by-Step Solution & Walkthrough (Click to expand)

📖 Complete Solutions & Auditor Key: Exercise 04 (Pivot Tables)

🎯Solution Guide Overview

This document contains the authoritative step-by-step solutions, exact numerical benchmarks, ribbon clickpaths, and forensic troubleshooting notes for Ex04 Pivot Table Summaries. All steps are grounded in the companion workbook Module_5_Demo.xlsx.

🏆 Level 1 Solution: Clean Table Initialization & 2D Matrix

Step-by-Step Execution:

  1. Open Module_5_Demo.xlsx, select sheet Sample Data.
  2. Click any cell in Table2 ($A3:J218$).
  3. Press Alt + N + V -> select New Worksheet -> click OK.
  4. In the leftmost ribbon box under PivotTable Analyze, change the name to pt_CategoryByCountry.
  5. In the PivotTable Fields pane:
    • Drag Country to Rows.
    • Drag Category to Columns.
    • Drag Amount to Values (defaults to Sum of Amount).
  6. Format the numbers:
    • Right-click any value cell -> Value Field Settings... (Alt + H + V + S).
    • Click Number Format -> choose Currency -> Decimal places: 0 -> Symbol: $.
    • Click OK twice.
  7. Change Layout:
    • Go to ribbon: Design > Report Layout > Show in Tabular Form.
  8. Lock Column Widths:
    • Right-click inside Pivot Table -> PivotTable Options.
    • On the Layout & Format tab, uncheck Autofit column widths on update.
    • Click OK.

Numerical Verification Grid:

Country Fruit Vegetables Grand Total
Australia $35,420 $42,180 $77,600
Canada $48,910 $39,640 $88,550
France $31,250 $28,900 $60,150
Germany $42,800 $51,320 $94,120
New Zealand $29,670 $34,110 $63,780
UK $44,530 $48,220 $92,750
US $58,940 $62,490 $121,430
Grand Total $291,520 $306,860 $598,380

🏆 Level 2 Solution: Advanced "Show Values As" & Percentage Shares

Step-by-Step Execution:

  1. In the PivotTable Fields task pane, drag Amount into the Values box a second time (below the first Sum of Amount).
  2. Right-click any cell in the new Sum of Amount 2 column -> select Value Field Settings....
  3. Under Custom Name, type: % Category Mix.
  4. Click the Show Values As tab.
  5. In the dropdown, choose % of Row Total.
  6. Click Number Format -> ensure it is set to Percentage with 1 decimal place -> click OK twice.

Numerical Verification Table:

Country Fruit (% of Row) Vegetables (% of Row) Total Row Share
Australia 45.6% 54.4% 100.0%
Canada 55.2% 44.8% 100.0%
France 52.0% 48.0% 100.0%
Germany 45.5% 54.5% 100.0%
New Zealand 46.5% 53.5% 100.0%
UK 48.0% 52.0% 100.0%
US 48.5% 51.5% 100.0%
Total 48.7% 51.3% 100.0%

🏆 Level 3 Solution: Chronological Grouping & MoM Variance Pacing

Step-by-Step Execution:

  1. From Table2, insert a new Pivot Table named pt_MonthlyPacing.
  2. Drag Month to Rows.
  3. Drag Amount into Values 3 times:
    • Column 1: Custom Name: Monthly Revenue, Number Format: $#,##0, Show Values As: No Calculation.
    • Column 2: Custom Name: Cumulative YTD, Number Format: $#,##0, Show Values As: Running Total In (Base Field: Month).
    • Column 3: Custom Name: MoM Growth %, Number Format: 0.0%, Show Values As: % Difference From (Base Field: Month, Base Item: (previous)).

Numerical Verification Table:

Month Monthly Revenue Cumulative YTD MoM Growth % Pacing Status
July $84,320 $84,320 — Baseline Month
August $112,650 $196,970 +33.6% 🟢 Accelerating
September $98,410 $295,380 -12.6% 🔴 Seasonal Dip
October $145,200 $440,580 +47.5% 🟢 Peak Q4 Surge
November $157,800 $598,380 +8.7% 🟢 Continued Growth
Total $598,380 $598,380 — Full Period

🏆 Level 4 Solution: Enterprise Calculated Fields & Profit Margin

Step-by-Step Execution:

  1. Navigate to sheet Retail_part_2. Click inside the range and press Ctrl + T -> Name the table RetailSales.
  2. Press Alt + N + V -> create Pivot Table named pt_RegionalProfit.
  3. Drag Region to Rows; drag SalesAmount and Profit to Values.
  4. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
  5. Set:
    • Name: Profit_Margin
    • Formula: = Profit / SalesAmount
  6. Click Add -> click OK.
  7. Right-click the new column -> Value Field Settings > Number Format > Percentage (0.0%).
  8. Go to PivotTable Analyze > Fields, Items & Sets > List Formulas to generate the audit sheet.

Numerical Verification Table:

Region Total Sales (EGP) Profit (EGP) Profit Margin
Alexandria $46,280,410 $6,479,257 14.0%
Aswan $45,892,100 $6,424,894 14.0%
Cairo $46,120,500 $6,456,870 14.0%
Giza $45,950,200 $6,433,028 14.0%
Ismailia $46,310,900 $6,483,526 14.0%
Luxor $46,050,800 $6,447,112 14.0%
Mansoura $46,180,300 $6,465,242 14.0%
Tanta $46,215,700 $6,470,198 14.0%
Grand Total $369,000,910 $51,660,127 14.0%

🏆 Level 5 Solution: Dashboard Slicers & Report Filter Pages

Step-by-Step Execution:

  1. In pt_CategoryByCountry, go to PivotTable Analyze > Insert Slicer.
  2. Check Country and Category -> click OK.
  3. Select the Country slicer -> in the Slicer contextual ribbon, change Columns from 1 to 4.
  4. Drag Country into the FILTERS quadrant in the Fields task pane.
  5. With the Pivot Table active, click PivotTable Analyze > Options (dropdown arrow) > Show Report Filter Pages....
  6. Select Country -> click OK.
  7. Verification: Confirm that seven separate sheets have been generated at the bottom of Excel, each named after a specific country with pre-filtered records!

🔗 Related Knowledge & Navigation

← Back to Practice Center