Key Takeaways: Gain Mastery, Confidence and Improve Productivity!

This course will equip participants with knowledge and essential skills they need for creating interactive PivotTable and PivotChart reports. Participants will learn how to use Excel 2016/2019/365 PivotTable and PivotChart features more effectively and wisely to improve their productivity.

• Able to collate and analyse data/information quickly and more effectively
• Able to better display financial information and other data relevant to the running of the business
• Collate data for important decision-making and planning

(1) Preparing Source Data

• Introducing Excel Table
• Create Table from Worksheet Data
• Working with Excel Table
• Understanding the PivotTable
• Understanding the PivotChart
• Exploring PivotTable and PivotChart Tools
• Understanding PivotTable Benefits
• Exploring PivotTable Fields List

(2) Building Your First PivotTable

• Use Recommended PivotTable Tool
• Customize the PivotTable Field Pane
• Change Area of the Fields
• Build PivotTable from Excel Table
• Add and Remove PivotTable Fields
• Create PivotTable from another PivotTable
• Link PivotTable Data to a Cell Range

(3) Formatting Your PivotTable

• Apply PivotTable Quick Styles
• Create Custom PivotTable Style
• Apply Number Format
• Create Custom Number Format
• Specify Character for Empty Cells
• Disable AutoFit Column Width
• Preserve PivotTable Cell Formatting

(4) Customizing PivotTable Fields

• Rename the PivotTable
• Rename PivotTable Field
• Enable Classic PivotTable Layout
• Change PivotTable Data Source
• Refresh PivotTable Data
• Set Option to Refresh Automatically
• Set Option to Remove Deleted Items

(5) Manipulating the PivotTable

• Change the Report Layout
• Display the Details of PivotTable Data
• Repeat Item Labels in Field
• Hide and Show Subtotals
• Turn Off or Turn On the Grand Total
• Delete a PivotTable

(6) Creating Your First PivotChart

• Create PivotChart from Excel Table
• Create PivotChart from PivotTable
• Sort and Filter the PivotChart
• Change the PivotChart Types
• Add and Remove PivotChart Elements
• Move the PivotChart to a Chart Sheet

(7) Applying Filters in PivotTable

• Add Report Filter to PivotTable
• Change Report Filter Layout
• Filter Row or Column Label Items
• Filter PivotTable Values
• Use Search Box to Display Multiple Items
• Use Slicer to Filter PivotTable Data
• Use Timeline to Filter PivotTable Data

(8) Sorting Values in PivotTable

• Sort Data with AutoSort
• Sort Data Manually
• Apply Conditional Formats to Values
• Sort Data by Color
• Filter Data by Color

(9) Grouping Fields in PivotTable

• Group Numeric Values
• Group Text Values
• Show or Hide Group Details
• Hide or Unhide Subtotals for Group
• Ungroup Date Values
• Create Slicer using Grouped Field
• Build PivotTable using Grouped Field

(10) Calculating Values in PivotTable

• Create Summary Functions
• Create Percentage Calculations
• Create Running Total Calculations
• Create Difference Summary Calculations
• Create Rank for Values
• Insert Calculated Fields
• Edit and Delete Calculated Fields
• Calculate Outside the PivotTable

(11) Creating Table Relationships

• Introducing Table Relationships
• Creating Excel Table
• Create Relationships Between Tables
• Create PivotTable Using Data Model
• Create PivotChart Using Data Model

(12) Creating Data Analysis Report

• Insert PivotTable and PivotChart
• Create Combo PivotChart
• Insert Slicer and Timeline
• Format Slicer and Timeline
• Customize Slicer to Multiple PivotTables
• Connect Timeline to Multiple PivotTables

This course is intended for senior officers, executives and decision makers in government, corporate and private organisations. The course is for Microsoft Excel 2016/2019/365 Users who has no experience in creating PivotTable report and intend to learn how to create effective and dynamic reports using PivotTable and PivotChart.

Instructor led with hands-on exercises.

Participants are required to bring a laptop equipped with Windows version Microsoft Excel 2016/2019/365

“Course is in good pace and I learnt many useful features which I can apply to my work quickly. NEA

“Valene is an effective trainer who highlights the key points and illustrate using good examples.” FairPrice

Microsoft Excel Expert and Microsoft Certified Trainer: Valene Ang

Valene Ang is an experienced certified IT trainer with over 19 years of experience in training professionals from various organisations. She received her degree in Business Computing from the University of Southern Queensland, Australia. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS).

Valene has extensive training and course development experience. She customized many Microsoft Office training courses for corporate clients, assist them in business data analysis and provide dynamic report solutions. Her training focuses on 100% hands-on exercises as well as providing practical solutions to real life Excel related problems.

Valene conducted many Microsoft Excel, PowerPoint and Word training in Singapore, Malaysia and China. Her corporate clients include CPF, PUB, MOM, PSA, IRAS, DFS, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING, FairPrice and many more.