19
Feb 2020

Mastering Pivot Table for Microsoft Excel 2016 & 365 Users: Advanced Features & Skills

Course Information

Start Date19 Feb 2020, Wednesday
End Date19 Feb 2020, Wednesday
Time09:00 am to 05:00 pm
Venue100 Orchard Road, Concorde Hotel Singapore
Fee$680 (Excluding GST) Inclusive of hotel buffet lunch, light refreshments and course materials
Contact6720 3333 (Ms Rina) training@aventisglobal.com
Register Now
Get Group Quote
Download Directory

Course Schedule

19 Feb 2020 (Wed) NEW

06 May 2020 (Wed)

 

(Click on the dates above to register online)

INTRODUCTION

Master Excel Pivot Tables & Powerful Data Analysis Using Real-life Cases

Learn Advanced Features of Microsoft Excel & Master Pivot Tables

Pivot Tables is one of the most powerful and useful tools in Microsoft Excel application that you should learn to master, especially when dealing of large database. It allows you to analyse and visualise data in various ways that can provide deep insights. Using PivotTable will save you a lot of time by allowing you to quickly summarize large amounts of data into a meaningful report. Understanding exactly how a pivot table works will really help you unleash the full potential of this amazing tool.

Master Excel Pivot Table, Today!

Learn how to use Pivot Tables and Pivot Charts to streamline and absolutely revolutionize your workflow in Excel

Upon completion of the course, you would be able to understand the workings of Excel Pivot Tables, various techniques of summarizing data and create complex models for reporting. You will also learn how to create a Pivot Charts for presentation. Essentially, this 1 day course gives you a complete understanding of the use of Pivot Tables in Microsoft Excel.

 

Get the opportunities to practice with real world scenarios.  In this one day course, you will learn how to prepare dynamic source data for PivotTable, create and format PivotTable, filter and sort PivotTable data, create and format PivotChart, performs calculation in PivotTable using functions and calculated fields, connect Slicer and Timeline to multiple PivotTables, group dates, text and numbers in PivotTable, create table relationships, create PivotTable using Data Model, create interactive dashboard with slicer and etc….

Who Should Attend?

This is a fast pace advanced level course and is not suitable for beginners who use Excel occasionally.  Participants are required to have basic to intermediate experience in using PivotTable to perform data analysis and reporting tasks in more efficient way. (You may click here to view the basic to intermediate level of PivotTable)

  • This course is for frequent Excel users who wish to learn how to increase their productivity with effective and time saving data analysis skills
  • Participants must have a basic or intermediate level Excel knowledge and a few years working experience using Excel.
  • Experienced Excel users who wish to learn more about Excel’s business-analysis capabilities

 

Note: Participants are required to bring a laptop equipped with Microsoft Excel 2016 or 365

Testimonials

The workshop was well-planned and designed. Well-versed in the subject taught. One of the most useful course I have attended; my teammates and I will try to sign up for similar courses conducted by Ms Valene. She is an excellent trainer, She is patient, explains concepts well and paces the class well. Excellent facilitator. Ministry of Trade and Industry

Trainer’s teaching is excellent. The workshop is very good, practical examples and exercises. One of the best Excel courses I’ve ever attended thus far. The trainer is very knowledgeable and have very good rapport with the group. Health Promotion Board

Perfect pace. I have learnt a lot – Everything is useful. Enjoyed your lessons, Valene. Thank you. Learnt an in depth with regards to excel as my works uses a lot of excel. Overall is good. Trainer is very knowledgeable and experience. Ministry of Law

MS Excel Expert and Microsoft Certified Trainer: Valene Ang

Valene Ang is a Microsoft Certified Trainer and Master Instructor of Microsoft Office Specialist (MOS). She brings with her 20 years’ experience in corporate training and consultancy. Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outline and course material, assisting corporate clients in business data analysis and providing dynamic report solutions.

She had conducted many Microsoft Office workshops in Singapore and Malaysia. Her training focuses on providing practical solutions to real life Excel problems. As a Certified Trainer, Ms Ang has trained hundreds of executives, managers and directors from across medium sized organisations, government offices and multinational companies. Some of her high profile clients including PSA International, DFS Galleria Singapore, Neptune Orient Lines Limited (NOL), National Environment Agency (NEA), Inland Revenue Authority of Singapore (IRAS), CPF Board, Public Utility Board (PUB), Canadian High Commission, British High Commission, Temasek Polytechnic, Republic Polytechnic, Baxter Healthcare Pte. Ltd, and many more.

Program Outline

Chapter 1: Building Excel Table
1.1 Overview of Excel Table
1.2 Create Table from Data Range
1.3 Remove Duplicate Records
1.4 Add Total Row to Table
1.5 Insert Calculated Columns
1.6 Insert Slicer to Filter Table Data

Chapter 2: Analysing Data with PivotTable
2.1 Summarize Data with PivotTable
2.2 Change the Report Layout
2.3 Show and Hide Report Totals
2.4 Refresh PivotTable Data
2.5 Group Dates Automatically
2.6 Group Numeric Values
2.7 Group Text Values
2.8 Create PivotTable from

Chapter 3: Creating Custom Calculations
3.1 Introducing Custom Calculations
3.2 Insert Calculated Field
3.3 Insert Calculated Items
3.4 Edit and Delete Custom Calculations
3.5 Performing Distinct Count in PivotTable

Chapter 4: Adding Data Visualization to PivotTable
4.1 Overview of Conditional Formatting
4.2 Apply Conditional Formatting
4.3 Overview of Sparklines
4.4 Show Data Trends with Line Sparklines
4.5 Compare Values with Column Sparklines

Chapter 5: Creating PivotTable using Data Model
5.1 Introducing Data Normalization Concept
5.2 Creating Table Relationships
5.3 Creating PivotTable from Data Model
5.4 Creating PivotChart from Related Tables
5.5 Inserting Slicer and Timeline to PivotTable

Chapter 6: Creating Slicer Dashboard Report
6.1 Overview Of Dashboard
6.2 Prepare Source Data for PivotTable
6.3 Create PivotTable And PivotChart
6.4 Hide Field Buttons From PivotChart
6.5 Create Combo Chart
6.6 Format Chart Axis And Align Charts
6.7 Use Slicer To Filter PivotCharts
6.8 Connect PivotTables to Slicer