Lab Practice
Pivot Practice
Summarize raw invoice data using advanced Pivot Table tools. You will create salesperson performance summaries with calculated fields, category summaries with timeline slicers, and group unit prices with referencing functions.
Practice & Solution Files
Pivot Lab
Objective 4.2 Practice Tasks
Follow the steps below to modify the worksheets, then test your results in the verification forms.
1
ExtendedPrice by Salesperson & Bonus Calculated Field
Open
• Rename the sheet to
• In the Pivot Table, place Salesperson in Rows and summarize the values in the ExtendedPrice field in Values.
• Create a Calculated Field (PivotTable Analyze → Fields, Items, & Sets → Calculated Field) named
• Use a formula to calculate a 5-percent bonus for salespeople with total sales of at least $75,000, and 0 for those who sold less than that:
ExcelExpert_4-2.xlsx. In the Invoices worksheet, click inside the table and insert a new Pivot Table on a new worksheet:
• Rename the sheet to
ExtendedPrice by Salesperson.
• In the Pivot Table, place Salesperson in Rows and summarize the values in the ExtendedPrice field in Values.
• Create a Calculated Field (PivotTable Analyze → Fields, Items, & Sets → Calculated Field) named
Bonus.
• Use a formula to calculate a 5-percent bonus for salespeople with total sales of at least $75,000, and 0 for those who sold less than that:
= IF(ExtendedPrice >= 75000, ExtendedPrice * 0.05, 0)
(Note: After creating it, Excel will display the field name as Sum of Bonus).
2
Quantity by Country/Region & Category with Timeline
Return to the Invoices worksheet. Insert another Pivot Table on a new worksheet:
• Rename the sheet to
• Place Country/Region in Rows, Category in Columns, and summarize Quantity in Values.
• Swap the configurations: drag Category to Rows and Country/Region to Columns.
• Select the Pivot Table, click PivotTable Analyze → Insert Timeline Slicer, and select OrderDate.
• Rename the sheet to
Qty by Country_Region & Categ.
• Place Country/Region in Rows, Category in Columns, and summarize Quantity in Values.
• Swap the configurations: drag Category to Rows and Country/Region to Columns.
• Select the Pivot Table, click PivotTable Analyze → Insert Timeline Slicer, and select OrderDate.
3
Quantity by Unit Price Grouping & GETPIVOTDATA
Insert a third Pivot Table from the Invoices table on a new worksheet:
• Rename the sheet to
• Summarize the Quantity field by UnitPrice (Rows: UnitPrice, Values: Sum of Quantity).
• Right-click any UnitPrice row label → click Group... → group in $10 increments from $0 to $270. (Starting: 0, Ending: 270, By: 10).
• In cell A1, type
• In cell D1, enter a formula that returns the number of units sold in the
• Rename the sheet to
Quantity by Unit Price.
• Summarize the Quantity field by UnitPrice (Rows: UnitPrice, Values: Sum of Quantity).
• Right-click any UnitPrice row label → click Group... → group in $10 increments from $0 to $270. (Starting: 0, Ending: 270, By: 10).
• In cell A1, type
Units sold in the lowest price range: in bold.
• In cell D1, enter a formula that returns the number of units sold in the
0-10 group using GETPIVOTDATA:
= GETPIVOTDATA("Quantity", $A$3, "UnitPrice", 0)
Verify Task 1: ExtendedPrice & Bonus
Check your calculated values for the salesperson summary sheet below:
Salesperson Summary Verification
Input raw numbers (e.g. 108026.13 or 5401.31), tolerance: +/-0.02
| Item / Salesperson | ExtendedPrice | Bonus |
|---|---|---|
| Janet Leverling | ||
| Margaret Peacock | ||
| Nancy Davolio | ||
| Andrew Fuller | ||
| Grand Total |
Verify Task 3: Unit Price Grouping
Verify the quantity calculations for your grouped UnitPrice table:
UnitPrice Grouping Verification
Input raw integers
| Summary Location | Quantity Value |
|---|---|
| Cell D1 (0-10 lowest price range units) | |
| Grand Total (B17) |