Activity : Pivot Table
Step 1: Insert a Pivot Table
1. Select the Data Range: Highlight the entire dataset (ex: A1:J9).
2. Insert Pivot Table:
o Go to the Insert tab on the Ribbon.
o Click on PivotTable.
o In the Create PivotTable dialog box, ensure the Table/Range is correct.
o Choose to place the PivotTable in a new worksheet.
o Click OK.
Step 2: Build the Pivot Table
1. Drag Fields to Areas:
o Rows: Drag Product to the Rows area.
o Columns: Drag Region to the Columns area.
o Values: Drag Revenues to the Values area.
2. Analyze the Data:
o The Pivot Table will now display the total revenues for each product by region.
Step 3: Customize the Pivot Table
1. Change Summary Function:
o Click on the drop-down arrow next to Sum of Revenues in the Values area.
o Select Value Field Settings.
o Choose the desired summary function (e.g., Sum, Average, Count).
o Click OK.
2. Filter Data:
o Drag Sales Method to the Filters area.
o Use the filter drop-down to select specific sales methods (e.g., Online, In-Store).
Activity Questions
1. Total Revenues by Product: What are the total revenues for each product?
2. Revenues by Region: Which region has the highest revenues?
3. Average Revenues: What is the average revenue for each region?
4. Sales Method Analysis: Use the sales method filter to analyze revenues for online vs. in-store
sales.
Additional Challenge
• Create a Chart: Use the Pivot Table data to create a bar chart showing revenues by region for
each product.
