Overview
You will be introduced to flat and relational files, best practices, the Data Model, the OLAP and other key OLAP functions of the Pivot Table.
These new capabilities improve performance and reduces users’ time when preparing data for further analysis work or reports from a single or multiple data sources.
Learning Outcomes
By the end of the Power Query & Power Pivot live session, you will be able to:
- create, manage and operate the Pivot Table on flat files and across files in relational databases.
You will also acquire a working understanding of the Excel Data Model and the difference between conventional Pivot Table functions from the OLAP Pivot Table.
Who Must Attend
HR managers, project managers, revenue managers, finance managers and administrative personnel who are in the basic to intermediate group who regularly handle a medium-to-high volume data from single or multiple sources.
Required software: Excel 2016 or above.
Internet connection for participants required.
Course Details
Module 1: The Excel Table
What is a flat file?
Overview of the Excel Table
Creating the Table
Filtering & sorting the Table
The Table Ribbon
Naming & resizing the Table
Table styles
Inserting slicers
Inserting formulas in the Table
Best practices on maintaining good data
Module 2: Introduction to the Pivot Table
Creating the Pivot Table
Populating Rows, Values, Columns & Filters
Formatting values in the Pivot Table
Pivot Table subtotals & grand totals
Pivot Table Report Layout & Styles
Components in Pivot Table Options
Renaming the Pivot Table
Drilling down into data
Module 3: Filtering & Sorting
Date filters, Label filters & Value filters
Sorting items in Pivot Table in an ascending or descending order
Slicers & Timeline
Filter Connections
Module 4: Calculated Fields & Items
How to insert a Calculated Field
Calculated Field behavior
Module 5: Pivot Charts
What is a Pivot Chart?
Creating a Pivot Chart
Pivot Chart rules and design
Managing the underlying data, adding layers and altering structure
Dynamic Charts
Module 6: Retrieving & summarizing data from the Pivot Table
Introduction to the GETPIVOTDATA Function
Drop-down lists & variance analysis
Module 7: Managing Changes in Your Source Data
Dynamic Named Ranges
Dealing with zeroes
Module 8: Business Intelligence overview
What is BI
The OLAP & relational databases
What is the OLAP Pivot Table
The Data Model
Module 9: The OLAP Pivot Table
Introduction to the CUBEVALUE & CUBEMEMBER Function
Pivot Table relationships
Pivot Table Sets
Module 10: Creating and Managing Sets
Creating & managing sets based on row or column items
Methodology
Course Leader
VINCENT CHIN
Chartered Accountant, FCCA
Microsoft Certified Trainer
Certified Six Sigma Black Belt
HRD Corp Certified trainer
Vincent has held financial, continuous improvement and I.T. positions in the banking, shared services and hospitality industry. He is a FCCA, Certified Six Sigma Black Belt, Microsoft Certified Trainer, Microsoft Office Specialist and HRD Corp accredited trainer. His previous roles include being an internal change consultant of a global team, a financial shared-services local head and a finance regional head. He currently trains professionals in Excel and Lean Six Sigma covering topics related to finance, business intelligence tools, pivot tables, dashboards, VBA and process improvement.
Vincent brings to you his 20 years’ of work and training experience in multiple industries where he was in charge of financial and business reengineering functions. He also develops complex VBA applications regularly in process improvement projects which requires his expertise.
One of his highest achievements in the role of a Financial Controller for several multinational conglomerates, he was being able to turn a loss making company into a profitable one within the year. Vincent has also managed a portfolio of business improvement projects with realized benefits of USD2 million in the prior year in an international bank.
Vincent has trained employees from Genting Group, Dell, Motorola, Technip, CIMB, Malakoff, Digi, IBM, Starwood Hotels & Resorts, Petronas ICT, HSBC.
Vincent adapts a highly practical approach in his training and relates topics he conducts to real life scenarios. Because he is a no-nonsense person, his solid background in financial operations and process re-engineering experience aid him to create a unique training methodology that mimic closely the solutions that are needed in his participants’ organizations.
OR
Other professional trainers in this field.
To contact Vincent , please call +6012 6869 628, +603 8074 9056, +603 -8082 3707 or info@itrainingexpert.com
Testimonies
Investment
In house arrangement only.
Click here to Request for In house Quotation
Please contact us at +6012 6869 628 | +603 8074 9056
Email: info@itrainingexpert.com
Payment mode:
Payment must be 100% upfront upon confirmation.
