The Basic + OLAP Pivot Table

The Basic + OLAP Pivot Table

HRDCORP CLAIMABLE COURSES (SBL-KHAS)

INTRODUCTION

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.

OBJECTIVE

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

20 July 2026 (Mon); 9.00am to 5.00pm

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)

REGISTER HERE: https://carriera.com.my/index.php/training-registration-hrdf-sbl-claimable/

DOWNLOAD BROCHURE HERE: https://carriera.com.my/wp-content/uploads/2026/05/The-Basic-OLAP-Pivot-Table.pdf