Programme Objective

PivotTable is a powerful tool in Excel that summarizes and reorganizes selected columns and rows of data in a spreadsheet or database table to obtain a desired report. Pivot tables are especially useful with large amounts of data that would be time-consuming to calculate by hand. You can use PivotTable to count, sum, average data, then arrange your data in a simple, meaningful layout that draws attention to key values.

This workshop will equip the participants with the essential skills to create dynamic PivotTable and PivotChart reports, change the report layouts and formats, filter, sort, summarize raw data as well as build up interactive and informative reports.

Programme Outline

• Understanding PivotTable and PivotChart
  • Introducing the PivotTable
  • Explore PivotTable Features
  • PivotTable and PivotChart Report Limitations
  • Introducing the PivotChart
  • Explore PivotChart Features

 

• Building and Manipulating PivotTable
  • Prepare Worksheet Data
  • Create PivotTable from an Excel Data Range
  • Create PivotTable from Existing PivotTable
  • Use Recommended PivotTable Tools
  • Turn PivotTable Field List On or Off
  • Customize PivotTable Field List
  • Move Fields in Different Areas
  • Change Order of the Fields
  • Delete PivotTable

 

• Customizing and Formatting PivotTable
  • Display PivotTable Details on New Worksheet
  • Rename PivotTable Field
  • Change Data Source
  • Refresh PivotTable Data
  • Change the Summary Calculation
  • Applying PivotTable Styles
  • Changing PivotTable Style Options
  • Applying Number Format to Pivot Data
  • Applying Date Format to Pivot Data

 

• Changing PivotTable Layout and View
  • Change the Report Layout
  • Repeat Item Labels in Fields
  • Use Classic PivotTable Layout
  • Expand and Collapse Field Details
  • Show and Hide Subtotals
  • Show and Hide Grand Totals

 

• Grouping Date Values
  • Group Dates Values Automatically
  • Change the Grouped Fields Location
  • Modify the Group Items
  • Hide and Show Group Details
  • Ungroup Field

 

• Filtering and Sorting PivotTable Data
  • Filter PivotTable with Report Filter
  • Filter PivotTable with Label Filters
  • Filter PivotTable with Value Filters
  • Use the Top 10 Filter
  • Filter Multiple Items with Search Box
  • Sort PivotTable Data with AutoSort
  • Sort PivotTable Data Manually

 

• Creating and Formatting PivotChart
  • Create PivotChart from Excel Data
  • Create PivotChart from PivotTable
  • Filter PivotChart
  • Sort PivotChart
  • Expand and Collapse PivotChart Details
  • Change PivotChart Types
  • Move PivotChart to Another Worksheet
  • Apply PivotChart Styles and Color
  • Add and Remove PivotChart Elements

You may enrol for the 2-day course IT031C if you wish to attend both Basic and Intermediate level of Pivot Table.
 

Programme	Dates
IT031A: Creating Professional Interactive PivotTable and PivotChart Reports Using Excel (Basic Level)	•          8 Mar 2021 •          29 Jun 2021 •          29 Nov 2021
IT031C: Creating Professional PivotTable Driven Reports (Intermediate Level) Includes IT031A	•          8 & 9 Mar 2021 •          29 & 30 Jun 2021 •          29 & 30 Nov 2021

Training Methodology

Lecture style, with hands-on exercises

Closing Date for Registration
1 week before programme or until full enrolment.
 

Intended For

This workshop is for all existing Microsoft Excel users who intend to create effective and dynamic PivotTable and PivotChart to support the efficient analysis of extensive data records when working with Excel.

Programme Facilitator(s)