Advanced Excel
Masterclass
funding-schemes sbl-khas-black-centered
Learn the key techniques to set up your reports
and be able to update your reports by simply
pressing the refresh button


Get in Touch With Us
Advanced Excel
Masterclass
Learn to create Excel
dashboards in minutes
funding-schemes-sbl-khas-white-centered
Get in Touch With Us

Take your skills to the next level with Microsoft Excel Advanced

Power Query is the recommended experience for discovering, connecting to, and importing data. Power Pivot is a powerful data analysis and data modeling tool. Use Power Query and Power Pivot to mold your data in Excel so you can explore and visualize it with PivotTables, PivotCharts and Power Map.

Duration

2 days / 16 hours

Level

Intermediate to Advanced

Delivery

100% Online - Instructor Led
Request For Information

    Key Features

    • 16 hours of instructor led training
    • Fully Online
    • Class recording available
    • Interactive Learning
    • Additional Coaching Session
    • 100% HRDF SBL-KHAS Claimable!

    Pre-Requisites

    • Intermediate Microsoft Excel Knowledge
    • Analytical Mindset
    • Willingness to self learn online
    • No prior experience is required
    • We will start from the very basics
    • Committed to complete all tasks

    Who Should Join

    • Analysts
    • Managers
    • Business owners
    • Executives
    • Anyone responsible for analyzing data
    Course Modules Covered in the Advanced Excel Masterclass program
    Module 1 - Introduction

    Introduction

    1. Extracting, Transforming and Loading (ETL) data
    2. Power Query vs “normal” Excel
    3. Importing data with Power Query
    4. Sources of data:
      Excel
      CSV/Text
      SQL Database
      Web
      Others
    5. Query Editor ribbon, Navigator pane & Preview grid
    6. Query settings panel - recording of transformation steps
    Module 2 - What is Power Query

    What is Power Query

    1. Extracting, Transforming and Loading (ETL) data
    2. Power Query vs “normal” Excel
    3. Importing data with Power Query
    4. Sources of data:
      Excel
      CSV/Text
      SQL Database
      Web
      Others
    5. Query Editor ribbon, Navigator pane & Preview grid
    6. Query settings panel - recording of transformation steps
    Module 3 - Data Cleaning and Formatting

    Data Cleaning and Formatting

    1. Data types: Number, Date, Text, True/False
    2. Remove Columns, Remove Other Columns & Choose Columns
    3. Removing duplicates from a data set
    4. Replacing values and errors
    5. Null values: Replace, fill or filter out
    6. Removing whitespaces and non-printable characters
    Module 4 - Data Transformation Techniques

    Data Transformation Techniques

    1. Transpose, Unpivot Columns and Pivot Columns: What are the differences? When to use them?
    2. Text transformations:
      Extract texts before delimiter
      Extract texts after delimiter
      Add Prefix/Suffix
      Convert texts to uppercase/lowercase
    Module 5 - Data Aggregation and Calculation

    Data Aggregation and Calculation

    1. Summarise data with Group By (Sum, Median, Min, Max, etc)
    2. Custom Columns: Create formulas for computations
    3. Index Columns
    4. Number columns statistical calculations
    Module 6 - Combining Data

    Combining Data

    1. Difference between merge and append
    2. Merge queries (the V-Lookup in Power Query)
    3. Append queries (combining tables with the same headers)
    4. Use cases of merge and append queries
    Module 7 - Organizing Power Query Automation

    Organizing Power Query Automation

    1. Best practices in organising your data sources
    2. How to automate your data refresh
    3. Importing multiple files located in a folder
    4. Update Power Query for change in location of source data
    Module 8 - Power Query Time Saving Tips

    Power Query Time Saving Tips

    1. Copy and paste queries to another Excel workbook
    2. Duplicate and reference queries
    3. Preview or navigate to table objects
    4. Navigate through complex query dependencies
    Module 9 - Power Pivot Concepts

    Power Pivot Concepts

    1. Extracting information from data with Power Pivot
    2. Uncovering data interpretation issues
    Module 10 - Power Pivot Data Model

    Power Pivot Data Model

    1. Data Terminology
    2. Defining a consolidated view of data
    3. Generating a data mashup from structured and unstructured data sources into a data model
    4. Deriving relationships from data sources with the Relationship tool and the Diagram View
    5. Acquiring data from related tables
    6. Create linked data
    7. Defining calculated columns
    Module 11 - Using PivotTable with Power Pivot

    Using PivotTable with Power Pivot

    1. Create PivotTable with data models
    2. Analyse and summarise data with PivotTable
    3. Creating hierarchies in data model
    4. Data visualisation with PivotCharts
    Module 12 - Implementing Dax Functions in Power Pivot

    Implementing Dax Functions in Power Pivot

    1. Expressing information with measures
    2. Understand the difference between calculated columns and measures
    3. Understand the difference between implicit and explicit measures
    4. Exposing hidden information from data
    5. Quantifying and mining information with DAX functions
    6. Evaluating expressions with the CALCULATE() function and filter functions
    Our Training Methodology

    Practical Assignments

    We provide hands-on assignments that requires practical implementation.

    Virtual Coaching Sessions

    Online coaching sessions that happen over the phone, via video, or on a web platform.

    1 Year Access to LMS

    Get access to learning resources upto 1 year of class completion.

    Live Project Experience

    Hands-on learning and training gives participants the opportunity to experience real world situations.

    Online Assessments

    Participants can assess reflect on their own learning and their level/skills.

    Free Industry Webinars

    Stay current on market research trends, learn best practices through our webinar sessions.
    Program Key Highlights
    online-learning-2
    48 hours of Remote Online Learning
    learning-hours
    Additional Coaching Hours
    hands-on
    Live Hands-on Projects
    certification
    Certified by International Body
    mentor
    Mentorship with Industry Experts
    industry
    Designed for Beginners & Professionals
    Request For Information

      Get in Touch With Us Today!

      This training program is suitable for anyone who intends to enter into the field of Microsoft Excel. This program is being conducted in Malaysia and can be joined by anyone, anywhere in the world remotely.
      Program Fee

      MYR 1300 per pax.
      funding-schemes-sbl-khas-white-centered
      Funding Schemes for Companies who are claiming from their HRDF levy or from the SBL Khas scheme.

      One-time fee. One year access to course materials and resources.
       
      hrdf-tp01
       
       
      Thulija Academy is a HRDF registered training provider. Our panel of expert trainers provide technology training for some of the biggest organizations in Asia.

       
       
       



      READY TO KICKSTART YOUR CAREER?
      Please fill in the form and a Program Advisor will reach out to you. You can also reach out to us at info@thulija.com or +60123661502
      Contact us on Whatsapp for more enquiries