Spreadsheet Mastery: Exercises Part 2 - Order Summary Pivots

Scenario

The Order Summary Report that you submitted last week was well received by your department director and key stakeholders in external departments. The comprehensive analysis and clear presentation of the data helped stakeholders understand the current order patterns and shipping performance.

Building on this success, the stakeholders have requested interactive PivotTables to complement the static report. This will allow them to explore the data dynamically and answer ad-hoc questions without requiring additional reports. You schedule a meeting with your department director and interested parties to gather specific requirements for this enhancement.

Requirements

  1. One workbook with multiple worksheets, including:
    1. Summary Report for Total Reporting Period (as currently exists)
    2. PivotTables by previously defined categories
    3. All KPI calculations included
    4. Category slicers
    5. Date timeline selector
    6. Related Charts and Graphs

The specifications focus on functionality rather than design aesthetics. The stakeholders know what information they want to access and how they want to interact with the data, giving you flexibility in the visual presentation. This leaves the design and implementation approach largely to your professional judgment.

You decide to create multiple PivotTable mockups to review with your department director before finalizing the components for the deliverable.

Learning Objectives

By completing this exercise, you will:

  • Create and customize PivotTables for dynamic data analysis
  • Design interactive dashboard components with slicers and timelines
  • Build PivotCharts that update dynamically with user selections
  • Apply professional formatting and design principles to data visualizations
  • Organize complex analysis across multiple worksheets

Tasks

Starting Point: Use your completed Order Summary Report workbook from the previous exercise as the foundation for this analysis.

1. Set up the workspace:

  • Add a new worksheet named “Mockup” to your existing workbook
  • Ensure number formats and field header conventions align with your Summary Report
  • Build all initial components in this worksheet for organization

2. Create core PivotTables:

  • Create a comprehensive PivotTable showing Order Count, Sales $, % Total Orders, % Total Sales $, AOV, Total Units, % Total Units, and UPT by Status (Status in rows, values in columns):

pivot tables required pivot tables required

  • Test different layout options (values in rows vs. columns, status positioning) to determine optimal presentation
  • Create focused single-metric PivotTables: Order Count by Status, Sales $ by Status, Unit Count by Status
  • Create performance metrics table: UPT by Status
  • Generate a single PivotTable of percentage breakdowns: % Total Orders, % Total Sales $, and % Total Units by status

3. Develop advanced analysis components:

  • Create a set of identical PivotTables to those above, now using Fulfillment_Status instead of Status
  • Build time-based analysis: AOV by Date (Date in rows, values in columns)
  • Build hierarchical analysis combining ship-service-level and Fulfillment_Status
  • Design cross-category analysis: Order Count by Sales Channel and Status
  • Build ranked analysis: Sales $ with numerical ranking by Status (sorted largest to smallest)

4. Apply professional formatting:

  • Apply different PivotTable Styles to each component to explore design options for your final dashboard
  • Test various formatting approaches using PivotTable Styles and Options menus
  • Choose formatting that enhances readability and professional appearance

5. Organize components across worksheets:

  • Move each PivotTable to its own worksheet using the Move PivotTable function. You should have a total of 16 PivotTables.
  • Rename worksheets with descriptive names that align with each component purpose
  • This provides space for slicers, timelines, and charts while maintaining organization

6. Add interactive controls:

  • Add Category Slicers and Timeline Selectors to each PivotTable
  • Configure slicer connections to control only the PivotTable on the related worksheet
  • Apply custom formatting to slicers and timelines that complements each PivotTable’s design
  • Test functionality to ensure PivotTables update correctly with different selections

7. Create dynamic visualizations:

  • Create a 2-D Pie Chart for Order Count analysis (display top 5 categories, sorted by Orders largest to smallest)
  • Build a Column Chart for Sales $ analysis (sorted by Sales $ from largest to smallest)
  • Design a Line Chart for AOV over time analysis (include trend line)
  • Focus on professional formatting: axis titles, labels, data labels for optimal readability
  • Test chart appearance with different slicer and timeline selections to ensure consistent quality

Submission

Your department director has requested a status update on the component and style options you’ve developed for review before finalizing the dashboard approach.

Deliverable: Upload your Microsoft Excel Workbook to Canvas under Exercises: Spreadsheet Mastery Part 2.

Before submitting, confirm:

  • All PivotTables display data correctly and update with slicer/timeline changes
  • Formatting is professional, and components use a variety of different formatting options to showcase possibilities for final dashboard product
  • Charts are visually appealing and complement their respective PivotTables.
  • Charts have appropriate titles and labels
  • Worksheets are properly organized and labeled
  • Interactive controls (slicers/timelines) function as expected