Mastering Excel Pivot Tables (2010/2013/2016) 2017-09-15T13:57:44+00:00

Project Description

Loading...
Welkin Course Codes: MPT0, MPT3, MPT6

Course Features

Pivot Table is one of the more advanced and complicated features of Microsoft Excel, but many oversee the benefits of using it to generate analysis and data summaries. It can be used for viewing, summarizing and analyzing the information in multiples ways to give you an insight into the data. In this course you will learn how a Pivot Table works and the skills and techniques to use the tools effectively. Course topics include:

  • Creating Pivot Table
  • Customizing fields in a Pivot Table
  • Formatting Pivot Table report
  • Using Pivot Table for summary and presentation
  • Creating and using Pivot Charts
  • Using external data sources for Pivot Table
  • Leveraging the power of OLAP Cubes

On course completion, you will have a good understanding of the functionality of Excel Pivot Tables and be able to analyse, summarise and present data in an effective way.

  • Qualified and experienced instructors
  • Easy-to-follow, step-by-step hands-on approach
  • Best practices explained and demonstrated
  • Weekday and weekend classes available

Learn from the Experts

Get Professional Recognition

mos-certificate

Certify your Excel skills with Microsoft Office Specialist Excel certification. The certification is not a requirement for course completion but we highly recommend you to get your skills professionally recognized.

Remark: The course fee does not include the certification exam.

Further Study

After completing this course you may further develop your knowledge and skills Microsoft Excel with the course Data Analysis with Microsoft Excel.

Course Fee

$960

Course Duration

6 Hours

Course Prerequisites

Course participants should have a basic knowledge of Microsoft Excel.

Title of Award

Upon successful completion of the course, students will receive a Certificate of Achievement.

Schedule & Online Enrolment
Enquiry/Booking
Mastering Excel 2016 Pivot Tables
COURSE INFODATES(dd/mm)TIMEDURATIONDAY(S)AMOUNT
MPT6-45C7K
 Mongkok
 Cantonese / PC
7/12~8/12
1900-22006 hrs/
2 sessions
THU, FRI960.00
MPT6-45C4C
 Central
 Cantonese / PC
4/1~5/1
1900-22006 hrs/
2 sessions
THU, FRI960.00
MPT6-51C19K
 Mongkok
 Cantonese / PC
19/1~22/1
1900-22006 hrs/
2 sessions
MON, FRI960.00
MPT6-6D27C
 Central
 Cantonese / PC
27/1~27/1
1000-1300
1400-1700
6 hrs/
2 sessions
SAT960.00
MPT6-24C20C
 Central
 Cantonese / PC
20/2~22/2
1900-22006 hrs/
2 sessions
TUE, THU960.00
MPT6-6A3K
 Mongkok
 Cantonese / PC
3/3~10/3
1000-13006 hrs/
2 sessions
SAT960.00
MPT6-51C32C
 Central
 Cantonese / PC
6/4~9/4
1900-22006 hrs/
2 sessions
MON, FRI960.00
MPT6-24C17K
 Mongkok
 Cantonese / PC
17/4~19/4
1900-22006 hrs/
2 sessions
TUE, THU960.00
MPT6-6A19C
 Central
 Cantonese / PC
19/5~26/5
1000-13006 hrs/
2 sessions
SAT960.00
MPT6-6A2K
 Mongkok
 Cantonese / PC
2/6~9/6
1000-13006 hrs/
2 sessions
SAT960.00
MPT6-45C5C
 Central
 Cantonese / PC
5/7~6/7
1900-22006 hrs/
2 sessions
THU, FRI960.00
MPT6-13C16K
 Mongkok
 Cantonese / PC
16/7~18/7
1900-22006 hrs/
2 sessions
MON, WED960.00
MPT6-6A33C
 Central
 Cantonese / PC
18/8~25/8
1000-13006 hrs/
2 sessions
SAT960.00
MPT6-45C30K
 Mongkok
 Cantonese / PC
30/8~31/8
1900-22006 hrs/
2 sessions
THU, FRI960.00
Mastering Excel 2016 Pivot Tables
Complete your Enrol
How to enrol/pay online

Mastering Excel 2013 Pivot Tables
COURSE INFODATES(dd/mm)TIMEDURATIONDAY(S)AMOUNT
MPT3-17121K
 Mongkok
 Cantonese / PC
18/12~18/12
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT3-6A6K
 Mongkok
 Cantonese / PC
6/1~13/1
1000-13006 hrs/
2 sessions
SAT960.00
MPT3-6A32C
 Central
 Cantonese / PC
6/1~13/1
1000-13006 hrs/
2 sessions
SAT960.00
MPT3-18011C
 Central
 Cantonese / PC
25/1~25/1
0930-1230
1400-1700
6 hrs/
2 sessions
THU960.00
MPT3-12C12C
 Central
 Cantonese / PC
12/2~13/2
1900-22006 hrs/
2 sessions
MON, TUE960.00
MPT3-35C21K
 Mongkok
 Cantonese / PC
21/2~23/2
1900-22006 hrs/
2 sessions
WED, FRI960.00
MPT3-18021C
 Central
 Cantonese / PC
22/2~22/2
0930-1230
1400-1700
6 hrs/
2 sessions
THU960.00
MPT3-18031K
 Mongkok
 Cantonese / PC
16/3~16/3
0930-1230
1400-1700
6 hrs/
2 sessions
FRI960.00
MPT3-6D24C
 Central
 Cantonese / PC
24/3~24/3
1000-1300
1400-1700
6 hrs/
2 sessions
SAT960.00
MPT3-6A36K
 Mongkok
 Cantonese / PC
7/4~14/4
1000-13006 hrs/
2 sessions
SAT960.00
MPT3-18041C
 Central
 Cantonese / PC
23/4~23/4
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT3-45C10C
 Central
 Cantonese / PC
10/5~11/5
1900-22006 hrs/
2 sessions
THU, FRI960.00
MPT3-35C23K
 Mongkok
 Cantonese / PC
23/5~25/5
1900-22006 hrs/
2 sessions
WED, FRI960.00
MPT3-18051C
 Central
 Cantonese / PC
29/5~29/5
0930-1230
1400-1700
6 hrs/
2 sessions
TUE960.00
MPT3-6A33C
 Central
 Cantonese / PC
23/6~30/6
1000-13006 hrs/
2 sessions
SAT960.00
MPT3-18061K
 Mongkok
 Cantonese / PC
27/6~27/6
0930-1230
1400-1700
6 hrs/
2 sessions
WED960.00
MPT3-45C5C
 Central
 Cantonese / PC
5/7~6/7
1900-22006 hrs/
2 sessions
THU, FRI960.00
MPT3-6A37K
 Mongkok
 Cantonese / PC
7/7~14/7
1000-13006 hrs/
2 sessions
SAT960.00
MPT3-18071C
 Central
 Cantonese / PC
30/7~30/7
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT3-13C20C
 Central
 Cantonese / PC
20/8~22/8
1900-22006 hrs/
2 sessions
MON, WED960.00
MPT3-24C32K
 Mongkok
 Cantonese / PC
21/8~23/8
1900-22006 hrs/
2 sessions
TUE, THU960.00
MPT3-18081C
 Central
 Cantonese / PC
23/8~23/8
0930-1230
1400-1700
6 hrs/
2 sessions
THU960.00
MPT3-18091K
 Mongkok
 Cantonese / PC
28/9~28/9
0930-1230
1400-1700
6 hrs/
2 sessions
FRI960.00
MPT3-18101C
 Central
 Cantonese / PC
8/10~8/10
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT3-18111C
 Central
 Cantonese / PC
21/11~21/11
0930-1230
1400-1700
6 hrs/
2 sessions
WED960.00
MPT3-18121K
 Mongkok
 Cantonese / PC
21/12~21/12
0930-1230
1400-1700
6 hrs/
2 sessions
FRI960.00
Mastering Excel 2013 Pivot Tables
Complete your Enrol
How to enrol/pay online

Mastering Excel 2010/2007 Pivot Tables
COURSE INFODATES(dd/mm)TIMEDURATIONDAY(S)AMOUNT
MPT0-17111K
 Mongkok
 Cantonese / PC
27/11~27/11
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT0-6A33C
 Central
 Cantonese / PC
2/12~9/12
1000-13006 hrs/
2 sessions
SAT960.00
MPT0-17121C
 Central
 Cantonese / PC
8/12~8/12
0930-1230
1400-1700
6 hrs/
2 sessions
FRI960.00
MPT0-35C27K
 Mongkok
 Cantonese / PC
27/12~29/12
1900-22006 hrs/
2 sessions
WED, FRI960.00
MPT0-18011K
 Mongkok
 Cantonese / PC
8/1~8/1
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT0-18021K
 Mongkok
 Cantonese / PC
6/2~6/2
0930-1230
1400-1700
6 hrs/
2 sessions
TUE960.00
MPT0-18031C
 Central
 Cantonese / PC
2/3~2/3
0930-1230
1400-1700
6 hrs/
2 sessions
FRI960.00
MPT0-18041K
 Mongkok
 Cantonese / PC
9/4~9/4
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT0-18051K
 Mongkok
 Cantonese / PC
17/5~17/5
0930-1230
1400-1700
6 hrs/
2 sessions
THU960.00
MPT0-18061C
 Central
 Cantonese / PC
14/6~14/6
0930-1230
1400-1700
6 hrs/
2 sessions
THU960.00
MPT0-18071K
 Mongkok
 Cantonese / PC
9/7~9/7
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT0-18081K
 Mongkok
 Cantonese / PC
8/8~8/8
0930-1230
1400-1700
6 hrs/
2 sessions
WED960.00
MPT0-18091C
 Central
 Cantonese / PC
13/9~13/9
0930-1230
1400-1700
6 hrs/
2 sessions
THU960.00
MPT0-18101K
 Mongkok
 Cantonese / PC
26/10~26/10
0930-1230
1400-1700
6 hrs/
2 sessions
FRI960.00
MPT0-18111K
 Mongkok
 Cantonese / PC
5/11~5/11
0930-1230
1400-1700
6 hrs/
2 sessions
MON960.00
MPT0-18121C
 Central
 Cantonese / PC
5/12~5/12
0930-1230
1400-1700
6 hrs/
2 sessions
WED960.00
Mastering Excel 2010/2007 Pivot Tables
Complete your Enrol
How to enrol/pay online

In this course, you will learn:

  • Preparing data for creating PivotTables
  • Creating PivotTables with different layouts that suit your presentation
  • Understanding different ways to summarize data, e.g. Sum, Average or Count for each data category
  • Grouping dates into yearly, quarterly, monthly, and weekly subtotals
  • Making your report more readable by converting data into different types of percentage calculations
  • Creating PivotCharts for better data presentation
  • Formatting PivotTable reports for better look and feel
  • Keeping PivotTable data up-to-date
  • Setting up calculated fields and calculated items in a PivotTable
  • Advanced techniques for consolidating multiple data tables into a PivotTable
  • Setting up PivotTables to pull data directly from an Access database
  1. Pivot Table Fundamentals
    • What Is a Pivot Table?
    • Why Should You Use a Pivot Table?
    • When Should You Use a Pivot Table?
    • The Anatomy of a Pivot Table
    • Pivot Tables Behind the Scenes
    • Limitations of Pivot Table Reports
  2. Creating a Basic Pivot Table
    • Preparing Your Data for Pivot Table Reporting
    • Creating a Basic Pivot Table
    • Keeping Up with Changes in Your Data Source
  3. Customizing Fields in a Pivot Table
    • The Need to Customize
    • Displaying the PivotTable Field Dialog Box
    • Customizing Field Names
    • Applying Numeric Formats to Data Fields
    • Changing Summary Calculations
    • Adding and Removing Subtotals
    • Using Running Total Options
  4. Formatting Your Pivot Table Report
    • Using AutoFormat
    • Applying Your Own Style
    • Setting Table Options
  5. Controlling the Way You View Your Pivot Data
    • Showing and Hiding Options
    • Sorting in a Pivot Table
    • Producing Top 10 Reports
    • Grouping Pivot Fields
  1. Performing Calculations Within Your Pivot Tables
    • Introducing Calculated Fields and Calculated Items
    • Creating Your First Calculated Field
    • Creating Your First Calculated Item
    • Rules and Shortcomings of Pivot Table Calculations
    • Managing and Maintaining Your Pivot Table Calculations
  2. Creating and Using Pivot Charts
    • What Is a Pivot Chart Really?
    • Creating Your First Pivot Chart
    • Rules and Limitations of Pivot Charts
    • Alternatives to Using Pivot Charts
  3. Using Disparate Data Sources for Your Pivot Table
    • Working with Disparate Data Sources
    • Using Multiple Consolidation Ranges
    • The Anatomy of a Multiple Consolidation Range Pivot Table
    • Creating a Pivot Table from an Existing Pivot Table
  4. Using External Data Sources for Your Pivot Table
    • Building a Pivot Table Using External Data Sources
    • Importing and Using External Data Without the PivotTable Wizard
    • Creating Dynamic Pivot Table Reporting Systems
    • Pivot Table Data Options
  1. Pivot Table Fundamentals
    • What Is a Pivot Table?
    • Why Should You Use a Pivot Table?
    • When Should You Use a Pivot Table?
    • The Anatomy of a Pivot Table
    • Values Area
    • Rows Area
    • Columns Area
    • Filters Area
    • Pivot Tables Behind the Scenes
    • Limitations of Pivot Table Reports
  2. Creating a Basic Pivot Table
    • Ensure Your Data Is in a Tabular Layout
    • Avoid Storing Data in Section Headings
    • Avoid Repeating Groups as Columns
    • Eliminate Gaps and Blank Cells in Your Data Source
    • Apply Appropriate Type Formatting to Your Fields
    • Summary of Good Data Source Design
    • Creating a Basic Pivot Table
    • Adding Fields to the Report
    • Adding Layers to Your Pivot Table
    • Rearranging Your Pivot Table
    • Creating a Report Filter
    • Understanding the Recommended PivotTables Feature
    • Using Slicers
    • Creating a Standard Slicer
    • Creating a Timeline Slicer
    • Keeping Up with Changes in Your Data Source
    • Changes Have Been Made to Your Existing Data Source
    • Your Data Source’s Range Has Been Expanded with the Addition of Rows or Columns
    • Sharing the Pivot Cache
    • Saving Time with New Pivot Table Tools
    • Deferring Layout Updates
    • Starting Over with One Click
    • Relocating Your Pivot Table
  3. Customizing a Pivot Table
    • Making Common Cosmetic Changes
    • Applying a Table Style to Restore Gridlines
    • Changing the Number Format to Add Thousands Separators
    • Replacing Blanks with Zeros
    • Changing a Field Name
    • Making Report Layout Changes
    • Using the New Compact Layout
    • Using the Outline Form Layout
    • Using the Traditional Tabular Layout
    • Controlling Blank Lines, Grand Totals, and Other Settings
    • Customizing the Pivot Table Appearance with Styles and Themes
    • Customizing a Style
    • Modifying Styles with Document Themes
    • Changing Summary Calculations
    • Understanding Why One Blank Cell Causes a Count
    • Using Functions Other Than Count or Sum
    • Adding and Removing Subtotals
    • Suppress Subtotals When You Have Many Row Fields
    • Adding Multiple Subtotals for One Field
    • Changing the Calculation in a Value Field
    • Showing Percentage of Total
    • Using % Of to Compare One Line to Another Lin
    • Showing Rank
    • Tracking Running Total and Percent of Running Total
    • Display Change from a Previous Field
    • Tracking Percent of Parent Item
    • Track Relative Importance with the Index Option
  4. Grouping, Sorting, and Filtering Pivot Data
    • Grouping Pivot Fields
    • Grouping Date Fields
    • Including Years When Grouping by Months
    • Grouping Date Fields by Week
    • Ungrouping
    • Grouping Numeric Fields
    • Using the PivotTable Fields List
    • Docking and Undocking the PivotTable Fields List
    • Rearranging the PivotTable Fields List
    • Using the Areas Section Drop-Downs
    • Sorting in a Pivot Table
    • Sorting Customers into High-to-Low Sequence Based on Revenue
    • Using a Manual Sort Sequence
    • Using a Custom List for Sorting
    • Filtering the Pivot Table: An Overview
    • Using Filters for Row and Column Fields
    • Filtering Using the Check Boxes
    • Filtering Using the Search Box
    • Filtering Using the Label Filters
    • Filtering a Label Column Using Information in a Values Column
    • Creating a Top-Five Report Using the Top 10 Filter
    • Filtering Using the Date Filters in the Label Drop-Down
    • Filtering Using the Filters Area
    • Adding Fields to the Filters Area
    • Choosing One Item from a Filter
    • Choosing Multiple Items from a Report Filter
    • Replicating a Pivot Table Report for Each Item in a Filter
    • Filtering Using Slicers and Timelines
    • Using Timelines to Filter by Date
    • Driving Multiple Pivot Tables from One Set of Slicers
  1. Performing Calculations Within Your Pivot Tables
    • Introducing Calculated Fields and Calculated Items
    • Method 1: Manually Add the Calculated Field to Your Data Source
    • Method 2: Use a Formula Outside Your Pivot Table to Create the Calculated Field
    • Method 3: Insert a Calculated Field Directly into Your Pivot Table
    • Creating Your First Calculated Field
    • Creating Your First Calculated Item
    • Understanding the Rules and Shortcomings of Pivot Table Calculations
    • Remembering the Order of Operator Precedence
    • Using Cell References and Named Ranges
    • Using Worksheet Functions
    • Using Constants
    • Referencing Totals
    • Rules Specific to Calculated Fields
    • Rules Specific to Calculated Items
    • Managing and Maintaining Your Pivot Table Calculations
    • Editing and Deleting Your Pivot Table Calculations
    • Changing the Solve Order of Your Calculated Items
    • Documenting Your Formulas
  2. Using Pivot Charts and Other Visualizations
    • What Is a Pivot Chart…Really?
    • Creating Your First Pivot Chart
    • Keeping Pivot Chart Rules in Mind
    • Changes in the Underlying Pivot Table Affect Your Pivot Chart
    • The Placement of Data Fields in Your Pivot Table Might Not Be Best Suited for Your Pivot Char
    • A Few Formatting Limitations Still Exist in Excel 2013
    • Examining Alternatives to Using Pivot Charts
    • Method 1: Turn Your Pivot Table into Hard Values
    • Method 2: Delete the Underlying Pivot Table
    • Method 3: Distribute a Picture of the Pivot Chart
    • Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for Your Char
    • Using Conditional Formatting with Pivot Tables
    • Creating Custom Conditional Formatting Rules
  3. Analyzing Disparate Data Sources with Pivot Tables
    • Using Multiple Consolidation Ranges
    • Creating a Multiple Consolidation Pivot Table
    • Analyzing the Anatomy of a Multiple Consolidation Ranges Pivot Table
    • The Row Field
    • The Column Field
    • The Value Field
    • The Page Fields
  4. Pivot Table data analysis using a Data Model in Excel 2013
    • Using the Internal Data Model
    • Building Out Your First Data Model
    • Managing Relationships in the Data Model
    • Adding a New Table to the Data Model
    • Removing a Table from the Data Model
    • Create a New Pivot Table Using the Data Model
    • Limitations of the Internal Data Model
    • Building a Pivot Table Using External Data Sources
    • Building a Pivot Table with Microsoft Access Data
  1. Pivot Table Fundamentals
    • Why You Should Use a Pivot Table
    • When to Use a Pivot table
    • Anatomy of a Pivot Table
  2. Preparing Data for Pivot Table Reporting
    • Ensuring That data Is in a Tabular layout
    • Avoiding Storing Data in Section Headings
    • Avoiding Repeating Groups as Columns
    • Eliminating Gaps and Blank Cells in the Data Source
    • Applying Appropriate Type Formatting to Fields
    • Summary of Good Data Source Design
  3. How to Create a Basic Pivot Table
    • Adding Fields to a Report
    • Fundamentals of Laying Out a Pivot Table Report
    • Adding Layers to a Pivot Table
    • Rearranging a Pivot Table
    • Creating a Report Filter
    • Understanding the Recommended Pivot Table
  4. Using Slicers
    • Creating a Standard Slicer
    • Creating a Timeline Slicer
    • Keeping Up with Changes in the Data Source
    • Saving Time with New Pivot Table Tools
  5. Making Common Cosmetic Changes
    • Applying a Table Style to Restore Gridlines
    • Changing the Number Format to Add Thousands Separators
    • Replacing Blanks with Zeros
    • Changing a Field Name
  6. Making Report Layout Changes
    • Using the Compact Layout
    • Using the Outline Layout
    • Using the Traditional Tabular Layout
    • Controlling Blank Lines, Grand Totals, and Other Settings
    • Customizing a Pivot Table’s Appearance with Styles and Themes
    • Changing Summary Calculations
    • Adding and Removing Subtotals
    • Changing the Calculation in a Value Field
  1. Automatically Grouping Dates
    • Undoing Automatic Grouping
    • Understanding How Excel 2016 Decides What to Group
    • Grouping Date Fields Manually
    • Including Years When Grouping by Months
    • Grouping Date Fields by Week
    • Grouping Numeric Fields
    • Using the PivotTable Fields List
  2. Sorting in a Pivot Table
    • Sorting Customers into High-to-Low Sequence Based on Revenue
    • Using a Manual Sort Sequence
    • Using a Custom List for Sorting
    • Filtering a Pivot Table: An Overview
  3. Using Filters for Row and Column Fields
    • Filtering Using the Check Boxes
    • Filtering Using the Search Box
    • Filtering Using the Label Filters Option
    • Filtering a Label Column Using Information in a Values Column
    • Creating a Top-Five Report Using the Top 10 Filter
    • Filtering Using the Date Filters in the Label Drop-down
  4. Filtering Using the Filters Area
    • Adding Fields to the Filters Area
    • Choosing One Item from a Filter
    • Choosing Multiple Items from a Filter
    • Replicating a Pivot Table Report for Each Item in a Filter
    • Filtering Using Slicers and Timelines
    • Using Timelines to Filter by Date
    • Driving Multiple Pivot Tables from One Set of Slicers
  5. Performing Calculations in Pivot Tables
    • Introducing Calculated Fields and Calculated Items
    • Creating a Calculated Field
    • Creating a Calculated Item
    • Managing and Maintaining Pivot Table Calculations
  6. Using Pivot Charts and Other Visualizations
    • What Is a Pivot Chart?
    • Creating a Pivot Chart
    • Keeping Pivot Chart Rules in Mind
    • Examining Alternatives to Using Pivot Charts
  1. Proceed with on-line registration or send the completed enrolment form (download) to us together with a cheque (made payable to Welkin Compuer Training) for the program fee.
  2. CEF applicants have to complete the CEF Application Form (download) and send it to us for certification. They will then send the form to the Office of the Continuing Education Fund, Room 916, 9/F, Kwai Hing Government Offices, 166-174 Hing Fong Road, Kwai Chung, New Territories, together with a photocopy of their HKID Card before course commencement.
  3. Applicants will receive application results from the Office of the CEF within 14 working days.

You may also be interested in