Course Information

Date17 - 18 Aug 2020, Mon - Tue
Time9:00 AM - 5:00 PM
VenueRoyal Plaza on Scotts Singapore Hotel Including Tea breaks and International Gourmet Buffet Lunch at Award winning Carousel Restaurant (Halal Certified)
FeeS$ 850 (Nett)
9767 9686 / 6278 9785

Registration is on a 1st come 1st serve basis. Register early to avoid disappointment.


I've been making full length films and documentaries for many years. We use professional equipment and…
Nay Win Tun, Video Editor
Perfect Your Elevator Pitch through Digital Storytelling
Very through and comprehensive
1 day Workshop on The Motivational Conversation - Rodney Woulfe
I will recommend this to my colleagues who have negative attitude towards their life, so that they can…
– Nanyang Technological University
1 day Influencing Difficult People in the Workplace by Ms Nancy Yeo
Nancy is very clear, engaging and insightful. She is able to tap on her vast experience to share with…
– WWW Cargo Pte Ltd
1 day Influencing Difficult People in the Workplace by Ms Nancy Yeo
‹ back to previous page
Aug 2020

Mastering Excel 2016 PivotTable Basic to In-Depth Skills for Business Analysis and Reporting


Pivot Table is an excellent data analysis and reporting tool in Microsoft Excel 2016 that enable you to create summary of large data in a few clicks. You can use Pivot Table to sort, count, total or give the average of the data stored in a table, display the results in a second table and show the summarized data automatically. The new chart types and improvements added to Microsoft Excel 2016 makes PivotTables easier to work with a large amount of data rapidly and give it even more meaning!

This 2-days course will teach the participants useful skills such as prepare the source data for PivotTable, convert source data to table, change the PivotTable report layouts, apply PivotTable styles, sort, filter and group PivotTable data, create PivotChart, apply conditional formats, perform calculations, import external data sources, create table relationships, create slicer and timeline to filter data, as well as build dynamic reports to facilitate data analysis.

If you want to replace manual tracking of data with interactive data analysis and dynamic reports, this is the perfect course for you. This course covers PivotTable related contents from beginner level to advanced level.



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 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 users who has no experience in creating PivotTable report and intend to learn how to create effective and dynamic reports using PivotTable and PivotChart.



“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



Instructor led with hands-on exercises.

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

This course is NOT for MAC version Microsoft Excel users



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.

10 Years of successful track records

Our clients share our passion for people development. We take a long term view of relationships and the
deep and sustained impact has been greatly fulfilling. We have trained over 26,000 learners to date.

NUS - National University of Singapore
PSA - The World's Port of Call
PUB - Water for All Conserve, Value, Enjoy
Singapore Airlines
Singapore Tourism Board
Standard Chartered
WDA - Singapore Workforce Development Agency