Download Training Directory (2021) Download Now

Jun 2021

Pivot Table for Microsoft Excel 2016 & 365 Users: Basic to Intermediate Workshop

Course Information

Start Date17 Jun 2021, Thursday
End Date17 Jun 2021, Thursday
Time09:00 am to 05:00 pm
VenueConcorde Hotel OR Holiday Inn Orchard City Centre
Fee$600 (Excluding GST)
Contact6720 3333 (Ms Rina) training.aventis@gmail.com
Register Now
Get Group Quote
LIVE Stream

Course Schedule

16 Dec 2020 (Wed) FULL HOUSE

03 Mar 2021 (Wed) FULL HOUSE

17 Jun 2021 (Thu)

30 Sep 2021 (Thu)

17 Dec 2021 (Fri)

(Click on the dates above to register)


Make Your Data Work For You!

Unleash the Power of Pivot Tables in Excel: Make Your Data Work for you

A pivot table is a table which is used to store the summary of a certain data set in a condensed manner. Through having this table, it is very helpful in organizing a large amount of data in Microsoft Excel.  Pivot tables are one of the most powerful and useful features in Excel. It is the best reporting tool built into Excel for analyzing data without formulas. With very little effort, you can use a pivot table to build good-looking reports for large data sets. You can also create a basic pivot table in about one minute, and begin exploring your data interactively. Pivot tables are a great feature in Excel that will help you to organize and analyze data in the most time saving way, but not many Excel users know how to use pivot table effectively.

1 Day Step-By-Step Guide to Pivot Tables

Pivot Table – a great analysis tool! If you are exploring how to use pivot tables in Microsoft Excel or would like to build on your basic knowledge, this 1 Day Pivot Table for Excel is the right course for you. In this practical 1 Day Excel Pivot training course, you will learn ways to create and work with Pivot Tables to look, organize and summarizes, your data in a variety of ways. Discover how to perform a variety of calculations with Pivot Table Data. You will learn how to prepare source data for PivotTable, create and format PivotTable, change PivotTable report layout, filter and sort PivotTable data, create and format PivotChart, performs calculation inside and outside PivotTable, add Slicer and Timeline to PivotTable, group dates in PivotTable and etc…

Who Should Attend?

This course will certainly ease the pain of the individuals that are required to summarise and analyze a large volume of data effectively.This course is suitable for anyone with good proficiency in Microsoft Excel 2016 and intends to start using PivotTable to perform data analysis and reporting tasks in more efficient way.

  • Executives, officers, supervisors and managers who want to learn how to summarize business data and paves the way to put it into meaningful and useful reports.
  • professionals who wants to increase their work productivity with effective and time saving data analysis skills
  • Experienced Excel users who wish to upgrade their skills and competences in  Excel’s business-analysis capabilities
  • Business owners who run a small to medium sized businesses will be able to replace manual business data tracking processes with interactive Excel analysis and reports

Note: This workshop is only for Windows version Microsoft Excel users. Participants are required to bring a laptop equipped with Microsoft Excel 2016 or 365. Participants are required to have good working knowledge of MS Excel. If you are new to MS Excel, you are recommended to take our Basic to Intermediate Excel course instead.


“Very knowledgeable, precise and responsive to queries. Yes, I would definitely recommend this course as we need to use Excel on daily basis for reporting of KPI etc.” Aieni, Specialist at Yellow Ribbon Singapore

“The trainer is knowledgeable and conduct the training well. Makes the concept understandable and the pace is just right.” Valen, Customer Service Representative at Advantest (Singapore) Pte Ltd

“Training pace was smooth and comfortable. In addition, the trainer is able to answer our questions. This pivot table course is useful where the data sorting gave us an overview.” Jia Hua, Senior Executive Ministry of Health

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: Understanding PivotTable & PivotChart

1.1       Introducing PivotTable           

1.2       Exploring PivotTable Features          

1.3       Introducing PivotChart           

1.4       Exploring PivotChart Features


Chapter 2: Creating a PivotTable

2.1       Preparing Worksheet Data

2.2       Building a PivotTable from Excel Range

2.3       Customizing PivotTable Field List     

2.4       Turning On and Off the Field List      

2.5       Changing Field Areas

2.6       Adding Report Filter   

2.7       Changing Report Filter Layout

2.8       Renaming Fields


Chapter 3: Inserting Recommended PivotTable

3.1       Using Recommended PivotTable Tools        

3.2       Expand and Collapse Field Items

3.3       Using Classic PivotTable Layout

3.4       Show and Hide Subtotals

3.5       Show and Hide Grand Totals

3.6       Grouping Date Field Automatically

3.7       Refreshing PivotTable Data

3.8       Deleting PivotTable


Chapter 4: Formatting PivotTable

4.1       Applying PivotTable Styles    

4.2       Setting PivotTable Style Options       

4.3       Formatting Numbers in PivotTable

4.4       Formatting Dates in PivotTable

4.5       Show Zero for Empty Cells


Chapter 5: Filtering PivotTable Data

5.1       Using Report Filter

5.2       Using Labels Filter

5.3       Using Search Box Filter

5.4       Using Values Filter

5.5       Filtering Pivot Data with Slicer

5.6       Applying Slicer Styles

5.7       Filtering Pivot Data with Timeline

5.8       Applying Timeline Styles


Chapter 6: Sorting PivotTable Data

6.1       Sorting PivotTable Data Automatically          

6.2       Sorting PivotTable Data Manually

6.3       Sorting Values in Row Totals

6.4       Sorting Values in Column Totals


Chapter 7: Creating PivotChart

7.1       Creating PivotChart from Excel Range         

7.2       Creating a PivotChart from PivotTable          

7.3       Adding and Removing Chart Elements

7.4       Applying Chart Styles and Color

7.5       Hide and Show Field Buttons

7.6       Formatting PivotChart


Chapter 8: Calculating PivotTable Data

8.1       Summarize Values with Functions    

8.2       Show Values in Percentage  

8.3       Calculate Running Total

8.4       Add Ranking to Values

8.5       Create Difference Summary Calculation

8.6       Creating Form