Pivot Table Webinar

Pivot Table Webinar (PIV000)

Course Length: 1 day
Delivery Methods: Available as private class only
Course Overview

Discover the power and efficiency of Pivot Tables in our Pivot Table Webinar, whether you're part of a team or seeking individual upskilling. This course provides you with fundamental knowledge and skills to effectively summarize and analyze data, allowing for dynamic grouping, filtering, and aggregation. Perfect for team training but equally beneficial if you're the sole participant representing your company.

The journey begins with a solid explanation of What Is a PivotTable?. You'll learn how PivotTables serve as an indispensable tool for summarizing, analyzing, exploring, and presenting data in a dynamic fashion, all without the need for complex formulas. This lesson highlights how PivotTables allow you to efficiently handle large data sets.

The next step involves Preparing Data for PivotTable use. You’ll understand the importance of structured data, ensuring no blank columns or merged cells, and the benefits of unique headers for each column. The lesson also emphasizes the use of Excel Tables to improve efficiency through auto-expanding ranges.

In the Creating a PivotTable segment, you’ll master the process of selecting your data range or table, utilizing the PivotTable options from the Insert tab, and deciding on whether to place your PivotTable in a new or existing worksheet. This includes a detailed review of the PivotTable Field List and how to strategically drag fields into the Rows, Columns, Values, and Filters areas for effective data analysis.

As you proceed, explore Value Summarization & Formatting. You'll discover how the default summarization is usually Sum or Count, but can be changed to Average, Max, Min, and more. Learn how to use Show Values As for percentage calculations and gain proficiency in formatting numbers for clearer insights.

Gain insights into Sorting & Filtering, learning methods to sort and filter data using PivotTable drop-down arrows or visual Slicers for enhanced data interaction. This will enhance your data presentation and analysis, ensuring the most relevant information is at your fingertips.

The course continues with Grouping techniques, allowing you to group numbers, dates, or text manually. By right-clicking in a row label, you can learn the efficient way to group items, improving your data’s clarity and usability.

Stay current with Refreshing Data strategies, ensuring your PivotTable reflects the most updated data. This lesson covers the process of refreshing individual PivotTables or refreshing all data simultaneously, keeping your analyses accurate and up-to-date.

Finally, tailor your learning with the Pivot Options lesson, where you'll review and apply different options to best suit your specific data needs, enhancing both your proficiency and productivity in data management.

By the end of the Pivot Table Webinar, you'll possess a strong understanding of creating and managing PivotTables, enabling you and potentially your team to effectively handle data analysis tasks with confidence and precision. Prepare to harness the power of PivotTables to support better decision-making and reporting in your organization.

Course Benefits
  • Understand what a PivotTable is and its role in data summarization and analysis.
  • Prepare structured data suitable for PivotTable creation, including using Excel Tables.
  • Create PivotTables from a data range or table, and configure placement in a worksheet.
  • Use the PivotTable Field List to organize data into Rows, Columns, Values, and Filters.
  • Summarize and format values using built-in aggregation functions and percentage calculations.
  • Apply sorting and filtering techniques, including the use of slicers for visual filters.
  • Group numeric, date, and text data within a PivotTable.
  • Refresh PivotTable data to reflect updates in the source data.
  • Access and configure PivotTable options to customize functionality and appearance.
Microsoft Certified Partner

Webucator is a Microsoft Certified Partner for Learning Solutions (CPLS). Our curriculum has been tested and approved by ProCert Labs, the official tester of Microsoft courseware, and has been found to meet the highest industry standards of instructional quality.

Microsoft Silver Certified Partner
Course Outline
  1. What is a PivotTable?
    1. A tool to summarize, analyze, explore, and present data.
    2. Allows dynamic grouping, filtering, and aggregation of large datasets without formulas.
  2. Preparing data
    1. Structured data: One row per record, no blank columns/rows or merged cells.
    2. Ensure each column has a unique header.
    3. Use Excel Tables (Ctrl + T) to auto-expand ranges for efficiency.
  3. Creating a PivotTable
    1. Select your data range/table → Insert tab → PivotTable.
    2. Choose to place it in a new worksheet or existing one.
    3. PivotTable Field List review.
    4. Drag fields to these areas:
      1. Rows: Grouping criteria (e.g., Region, Category).
      2. Columns: Categories across the top (e.g., Year).
      3. Values: What to calculate (e.g., Sales, Count).
      4. Filters: High-level filters across the whole table.
  4. Value summarization and formatting
    1. Default is Sum or Count.
    2. Right-click value → Summarize Values By (e.g., Average, Max, Min).
    3. Use Show Values As for percent calculations (e.g., % of Grand Total).
    4. Format numbers via Value Field Settings.
  5. Sorting and filtering
    1. Click drop-down arrows on Row or Column fields to:
      1. Sort (A-Z, Z-A, by values).
      2. Filter specific items.
    2. Use Slicers for visual filtering.
  6. Grouping
    1. Group numbers (e.g., by 10s), dates (e.g., months, quarters), or text manually.
    2. Right-click in a row label → Group.
  7. Refreshing data
    1. When source data changes:
      1. Right-click PivotTable → Refresh.
      2. Or Data tab → Refresh All.
  8. Pivot options
    1. Review the options available.
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Microsoft Excel class:

  • Basic proficiency with Microsoft Excel, including navigation and worksheet formatting.
  • Understanding of common Excel data types such as numbers, text, and dates.
  • Familiarity with basic Excel functions and tools such as sorting, filtering, and using the Insert tab.
Live Private Class
  • Private Class for your Team
  • Live training
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Proposal