The Basic + OLAP Pivot Table

This is a 1-day advanced course in the basic and new functionalities of the Excel Pivot Table. This course structure allows a deeper dive into the new Pivot Table capabilities and brings about a different approach in data preparation where relational files and multiple data sources are concerned. 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 reduce users’ time when preparing data for further analysis work or reports from a single or multiple data sources. Objectives This is an advanced course which covers all important functionalities of the Table, conventional Pivot Table and OLAP Pivot Table. Upon completion of this course, 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. Pre-Requisites • Stable Internet Connection • Webcam and Earphones • If you have two computer monitors, please use them • Required software (either one): Excel 2016 Professional Plus, Excel 2019, Excel 2021, Microsoft 365. • Ensure Power Query and Power Pivot is working in your installed versions of your Excel. • Power Pivot info: https://support.microsoft.com/en-us/office/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b • Power Query info: https://support.microsoft.com/en-us/office/power-query-data-sources-in-excel-versions-e9332067-8e49-46fc-97ff-f2e1bfa0cb16 • Tutorial video: https://youtu.be/8tWi4nSUyWA *This is a hands-on follow-along workshop, packed with exercises. All participants must each have a computer/Laptop. Course Content 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 • Using GETPIVOTDATA with the Data Validation List 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 • The Data Model • Creating a Data Model with Power Pivot Module 9: The OLAP Pivot Table • CUBEVALUE & CUBEMEMBER functions • Pivot Table relationships • Pivot Table Sets When & Where 23 September 2026 (Wednesday; 9.00 am to 5.00 pm Remote Online Training - Zoom Who Should 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 Training Investment RM 800 per participant (inclusive of Service Tax, Online Course Materials, E-Certificate of Attendance) 🔥Group Promo: Save 15% Register min. 3 pax for RM 680 per pax (Normal Rate: RM 800) HRD Corp Claimable (SBL-Khas) Trainer’s Profile Vincent Chin Vincent has held financial, continuous improvement and I.T. positions in the banking, shared services, and hospitality industry. He is an FCCA, CA, Certified Six Sigma Black Belt, Microsoft Certified Trainer, Microsoft Office Specialist and a PSMB-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. Vincent’s experience in the continuous improvement sphere of the financial industry includes projects in process automation & robotics, productivity management, end-to- end reviews and streamlining. He also trained yellow belts and green belts in DMAIC (the acronym for Define, Measure, Analyze, Improve & Control; a problem-solving methodology in Six Sigma) for other various companies. Vincent codes in VBA and has developed complex Excel applications in the companies he worked for. He currently trains professionals in Excel, Power BI and Lean Six Sigma covering topics related to finance, business intelligence tools, pivot tables, dashboards, VBA and process improvement. Vincent also assists companies in automating Excel tasks, end-to- end process reviews, developing process target operating models and other areas of process continuous improvement. Qualifications • Microsoft Certified Trainer | MICROSOFT | 2020 • Microsoft Office Specialist Expert | MICROSOFT | 2020 • Microsoft BI Reporting in Excel | MICROSOFT | 2019 • Microsoft Office Specialist | MICROSOFT | 2017 • Certified Trainer | PSMB | 2013 • Certified Six Sigma Black Belt | ASQ | 2011 • FCCA | 2003 • MIA | 1999 • ACCA | 1998