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.

Course Objectives

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
Course Outline

(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
Who Should Attend

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.

Training Methodology

Instructor led with hands-on exercises.

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

Testimonials

“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

Trainer

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.

NUHS

Rodney has provided alternatives to handle employees in different work context. Through effective communication tactics and K.S.A approach, employees faces can be understood and furthermore improve on their performance.

NUHS

NUHS

Rodney was clear with his presentation and was able to provide comfortable alternative solutions to manage difficult situations.

NUHS

Leitz Tooling Asia Pte Ltd

Thanks for all the interesting stories and for sharing them with the class. Nothing beats personal experience. NYP Elisabetta conducted a truly inspirational and eye opening training!

Leitz Tooling Asia Pte Ltd

CapitaLand Limited

The course has excellent content and trainer.

CapitaLand Limited

SPF

Mr. Leonardo not only cover powerful conversation at workshop, he had also share about his stories. It does come in handy. I will definitely do some soul searching and thereafter apply what I have learn from his workshop.

SPF

Ang Mo Kio Police Division

Mr. Leonardo is very knowledge and he deliver the topic very clearly and participant are easier to understand. The video shown were very good and meaningful.

Ang Mo Kio Police Division

Rolls Royce

The pace and supporting examples were just right, reinforcing key concepts.

Rolls Royce

SCEI (A*STAR)

The course was helpful and enlivening.

SCEI (A*STAR)

Ministry of Health

Very Insightful, Leonardo is able to engage the audience and help them analyse the barriers on communication. It goes deep enough to allow the participants to search their own thinking and make good changes to unhealthy habits. The practical presentations and demonstration are very helpful in learning the new concepts.

Ministry of Health

Republic Polytechnic

Very Insightful, let me know more about myself and how I can improve my communication. I would definitely benefit from this course, applying what I had learnt

Republic Polytechnic

Nanyang Polytechnic

Good! Thank you

Nanyang Polytechnic

BBDO Singapore Pte Ltd

I enjoyed the self-discovery journey, it allowed me to pause and reflect upon my current state, challenges I face and that I will have to make a choice and decide how I want the future to be. I am really glad to have attended the course

BBDO Singapore Pte Ltd

Institute of Education

Thank you for an interesting workshop!

Institute of Education

DSM Elastromers Asia

This course would be useful for my staff

DSM Elastromers Asia

Institute of Education

The course is well conducted

Institute of Education

National Institute of Education

Rodney is very knowledgeable and effective in delivering the training

National Institute of Education

Ministry of Port of Authority

The practical sessions are very useful in helping me understand and apply the new skills and ideas

Ministry of Port of Authority

Nanyang University of Technology

Good participation between the trainer and participants

Nanyang University of Technology

NAFA

Very through and comprehensive

NAFA

Anonymous

Dr Gabriel, thank you so much. Will live by your parting advice: It is not knowledge but consciousness of body language! Thanks again. Great course! Gabriel is really an experienced person.

Anonymous

SEAP

I was greatly interested in this course. Thank you.

SEAP

AIA

This is an excellent program to improve the aesthetics and impact of PowerPoint without making it looks cheap or amateurish. I’m amazed at how my skills improved by almost 300%.

AIA

MOM

This course is very engaging delivery.

MOM

ITE

Will recommend to colleagues to pick up this course.

ITE

SPF

Very enlightening and enjoyable session. Thank you for the knowledge.

SPF

NYP

Previously attended michael’s course. Finds that he constantly updates materials and customizes to suit class needs. Addresses case studies brought up in class to make it appreciable throughout.

NYP

PT Bukit Makmur Mandiri Utama

This course is good with the role play to practice.

PT Bukit Makmur Mandiri Utama

Training Strategies

A good course.

Training Strategies

MCYS

This course has added value to my current work.

MCYS

Ngee Ann Polytechnic

The course is useful and has a number of useful conflict resolution skills that I will most certainly use.

Ngee Ann Polytechnic

Ministry of Education

Comprehensive with good and realistic practical tips, stimulating and thoughts provoking.

Ministry of Education

Ministry of Manpower

Trainer is engaging, enthusiastic and knowledgeable.

Ministry of Manpower

Republic Polytechnic

This course is good and fun.

Republic Polytechnic

Jurong Town Council

This course has been excellent and an eye-opener. It is most definitely very informative and appropriate. It is also a reality check for all of us.

Jurong Town Council

Flotech Controls Pte Ltd

Very good course – practical!

Flotech Controls Pte Ltd

Singapore Prisons

Enjoyed the entire course and the rainer is very good in applying examples relevant to the workplace.

Singapore Prisons

Ideal Foods Pte Ltd

Very practical and interesting.

Ideal Foods Pte Ltd

People’s Association

Nancy is a wonderful instructor. She is very knowledgeable.

People’s Association

Institute of Education

Nancy is already a motivator. Allows me to see things at different angles. Totally agree with her that a lot of situation is about managing oneself. Enjoy her course totally.

Institute of Education

KK Hospital

Great course. Exceptional Facilitator.

KK Hospital

Singapore Prisons

Overall is good. I have learnt a lot. Thank you so much Nancy.

Singapore Prisons

Thye Hua Kuan Hospital

Good sharing of experiences by the trainer. I have enjoyed this 1 day seminar.

Thye Hua Kuan Hospital

Nanyang Technological University

I will recommend this to my colleagues who have negative attitude towards their life, so that they can maybe change their mindset after that.

Nanyang Technological University

WWW Cargo Pte Ltd

Nancy is very clear, engaging and insightful. She is able to tap on her vast experience to share with us ideas and ways to deal with different kinds of people. Great!

WWW Cargo Pte Ltd
Live Virtual Learning
17 - 18 Aug 2020
Royal Plaza on Scotts, Singapore
17 - 18 Aug 2020

Time

  9:00 am - 5:00 pm

Duration

2 Days

Cost

  S$850 (Nett)

Royal Plaza on Scotts, Singapore

Including Tea breaks and International Gourmet Buffet Lunch at Carousel Restaurant (Halal Certified)

Live Virtual Learning

Enjoy $100 off the course fees for Live Virtual Learning on Zoom
(Fees quoted is before the S$100 rebates)

Submit a Comment

RSS
Follow by Email