Classroom/ Online: Yes/ No
Scheduling Date(s):
Note: Please click specific date for detailed venue and course fee etc.
MS Excel Pivot Table In-Depth
Excel’s most powerful analytical tool is the Pivot Table. In this course, you’ll be taught how to leverage Pivot Tables to summarize, sort, count, and chart your data in Microsoft Excel. This course shows you how to navigate the complexity of Pivot Tables while taking advantage of their power. You will learn how to build Pivot Tables from single or multiple data sources, add calculated fields, filter your results, and format your layout to make it more readable.
Objective
- Preparing data source to use in Pivot Table
- Creating a Pivot Table
- Get data from external source
- Summarizing multiple data fields
- Managing subtotals and grand totals
- Grouping Pivot Table fields
- Filtering with selections, rules, search filters, slicers, and timeline
- Applying Pivot Table styles
- Formatting cells with conditional formatting
- Show different calculations in Pivot Table value fields
- Create calculated fields using formulas
- Create calculated items with combine fields
- Creating Pivot Charts
Outline
- Introduction to Pivot Table
- Preparing data source to be use in Pivot Table
- Creating the Pivot Table
- Using external data source in Pivot Table
- About Pivot Table Fields Pane
- About Pivot Table structure
o The Values area
o The Columns area
o The Rows area
o The Filters area - Building up the Pivot Table
- Pivoting the Pivot Table
- Show report filter pages
- Removing field from the Pivot Table
- Managing Pivot Table
o changing Pivot Table name
o select the entire Pivot Table
o deleting a Pivot Table
o resetting Pivot Table using Clear All
o Show or hide field headers
o Show or hide the Expense and Collapse button - Grouping fields
o Multi-levels grouping
o Grouping a date field - Sorting and filtering Pivot Table data
o Sorting Pivot Table data
o Sorting Pivot Table label
o Filtering the Row Area
o Filtering the Column Area
o Clearing Individual Filter
o Clearing All Filters
o Using Slicer to Filter Data - Moving the Slicer
- Changing the Slicer Style
- Applying Filter Using Slicer
- Clearing Filter Using Slicer
- Deleting the Slicer
o Filter date field using Timeline - Elements of the Timeline
- Changing the filter types of the Timeline
- Applying filter with Timeline
- Add another criterion to an existing filter
- Clearing filter in the Timeline
- Moving & resizing the Timeline
- Changing the Timeline style
- Deleting the Timeline
- Refreshing Pivot Table
o Refresh Pivot Table manually
o Refresh Pivot Table automatically when opening the file - Formatting the Pivot Table
o Formatting number field
o Changing the values label
o Changing the report layout
o Changing Pivot Table styles
o Managing subtotals
o Managing grand totals
o Inserting blank rows after each item
o Collapse or Expand a Single Item Level
o Collapse or Expand the Entire Item Level - Summarizing Pivot Table Data
o Changing the data field summary operation
o Summarizing more than one data field - Working with Pivot Chart
o Creating a Pivot Chart
o Moving the Pivot Chart to a new worksheet
o Changing the chart style
o Controlling the chart using Pivot Table
o Hiding the legend from the Pivot Chart
o Display Data Table from the Pivot Chart - Printing Pivot Tables
o Printing a Pivot Table
o Printing headers at the top of each printed page
o Printing each item on its own page
o Printing a Pivot Chart
- Calculation in Pivot Table
o Calculated field - Create calculated field
- Edit calculated field
- Delete calculated field
o Calculated Item - Create calculated item
- Edit calculated item
- Delete calculated item
o Calculations in Show Value As
% of Grand Total
% of Column Total
% of Row Total
% of Parent Row Total
% of Parent Column Total
% of Parent Total
Running Total In
% Running Total In
Rank Smallest to Largest
Rank Largest to Smallest
% of Difference From
% Difference From - Solution for Multiple Pivot Tables using the same source data (optional)
- Consolidating data from multiple sources (optional)
- Converting a crosstab data into normal data list (optional)
Who should attend
- People who wants to learn how to create Pivot Table in a proper way
- People who wants to go beyond the basic Pivot Table like using formula to create calculated fields
- People who wants to summarize their data in different ways
- People who wants to generate more reports which is hard to achieve in normal spreadsheet
- People who wants to generate chart that can be change easily using Pivot Table
Prerequisite
- Must have attended Excel Basic or has been using Excel for the past three years continuously
- Must know how to use Excel basic functions
- Basic knowledge on Excel chart
- People who wants to go beyond the basic Pivot Table like using formula to create calculated fields
- People who wants to summarize their data in different ways
- People who wants to generate more reports which is hard to achieve in normal spreadsheet
- People who wants to generate chart that can be change easily using Pivot Table
Prerequisite
- Must have attended Excel Basic or has been using Excel for the past three years continuously
- Must know how to use Excel basic functions
- Basic knowledge on Excel chart
Methodology
This is a one-day hands-on course. The trainer will explain on the topic and then guide you through with step-by-step practice. You will be provided with real life scenario for individual topics so that you can apply what is being taught. After the hands-on practice on every topic, you’ll have a short Q & A session
Testimonials
Well connected and structured. The workshop is a very interactive and step-by-step approach. The trainer is very well versed with the software. Quick print to link out when preparing of table.
National Environment Agency
Clear and concise excellent trainer.
ISEAS-Yusof Ishak Institute
Very informative and impressed by the gravity of pivots function. The workshop is very interesting. Trainer welcome queries, are patient and guide us, spot on our weakness or when we are lost.
Motor-Way Credit Pte Ltd
The additional functions in Excel could allow us to work efficiently on data entry. The workshop is an eye-opener for people who wants to learn more functions in Excel. Alaster is very knowledgeable.
National Environment Agency
Excellent outcome. The workshop was very well organized. Trainer speaks very clearly and expresses very well.
ARBURG Pte Ltd
I have learned some formulas that I can apply at work. Alaster is very knowledgeable.
Republic Polytechnic
- Very informative and sure beneficial to my daily work
- Very patience and knowledgeable trainer, provide lots of techniques tips ( Singapore Polytechnic )
- Learnt many useful tips
- make sure everyone is up to pace, a comfortable pace ( Singapore Workforce Development Agency )
- Very Patient and friendly trainer, helpful and willing to share a lot of tips ( Lifelong Learning Institute Pte Ltd )
- Sufficient material and the files sent to us for practice
- very fun to attend, including tips for us as well ( Woodlands health Pte Ltd)
- Very practical and useful for daily work, fantastic
- Trainer is Superb, Very patient, and Knowledgeable (Singapore Pharmacy Council )
- I didn't know Pivot could be so fun and helpful.
- The materials can help me fall back on the things I have learned.
National Environment Agency
Clear and concise excellent trainer.
ISEAS-Yusof Ishak Institute
Very informative and impressed by the gravity of pivots function. The workshop is very interesting. Trainer welcome queries, are patient and guide us, spot on our weakness or when we are lost.
Motor-Way Credit Pte Ltd
The additional functions in Excel could allow us to work efficiently on data entry. The workshop is an eye-opener for people who wants to learn more functions in Excel. Alaster is very knowledgeable.
National Environment Agency
Excellent outcome. The workshop was very well organized. Trainer speaks very clearly and expresses very well.
ARBURG Pte Ltd
I have learned some formulas that I can apply at work. Alaster is very knowledgeable.
Republic Polytechnic
- Very informative and sure beneficial to my daily work
- Very patience and knowledgeable trainer, provide lots of techniques tips ( Singapore Polytechnic )
- Learnt many useful tips
- make sure everyone is up to pace, a comfortable pace ( Singapore Workforce Development Agency )
- Very Patient and friendly trainer, helpful and willing to share a lot of tips ( Lifelong Learning Institute Pte Ltd )
- Sufficient material and the files sent to us for practice
- very fun to attend, including tips for us as well ( Woodlands health Pte Ltd)
- Very practical and useful for daily work, fantastic
- Trainer is Superb, Very patient, and Knowledgeable (Singapore Pharmacy Council )
- I didn't know Pivot could be so fun and helpful.
- The materials can help me fall back on the things I have learned.
Profile of Alaster Leong
Mr. Alaster Leong is an IT Trainer who possesses a strong passion in IT Training. He has more than 25 years of IT experience. He has been providing IT training in government, private institutions and organizations since 1992. He specializes in creative courses such as Adobe Photoshop, Adobe Dreamweaver, Adobe Flash, Adobe Acrobat, Access and Excel VBA, Microsoft Office Applications, etc. He is a certified Microsoft Office Specialist Expert (MOS) in Excel.