COURSE CONTENT
Microsoft Office 365
Excel Advanced with Data Analysis, Reporting & Automation
Training Overview
This practical Excel training program is designed for professionals who already have a working knowledge of Excel and want to level up their skills in data handling, analysis, and automation. The course bridges the gap between intermediate and advanced levels—helping participants build confidence in using powerful Excel features for real-world tasks.
Participants will learn how to:
- Use essential formulas like IFS, XLOOKUP, and TEXTJOIN for smarter analysis
- Build and customize PivotTables and PivotCharts to uncover trends and insights
- Clean and prepare messy data for accurate reporting
- Design simple dashboards for better data storytelling
- Record macros and write basic VBA scripts to automate repetitive tasks
- Use ChatGPT as a support tool to help write or modify VBA code
By the end of the session, participants will be able to handle complex datasets, create interactive reports, and automate routine processes—making them more efficient and valuable in their roles.
Program Outline
Module 1: Smarter Use of Excel Functions
- Understanding the role of functions in Excel
- Working with logical functions: IF, AND, OR, IFS
- Applying summary functions: SUMIF, SUMIFS, AVERAGEIF, COUNTIF
- Exploring lookup options: VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH
- Combining text values using TEXTJOIN and CONCAT
- Identifying top and bottom values with MAXIFS and MINIFS
Module 2: Making the Most of Pivot Tables
- Customizing PivotTable layout and design for clarity
- Adjusting field settings and renaming columns
- Using filters and slicers to focus on key data
- Sorting and grouping items to reveal insights
Module 3: Enhancing Analysis with Calculations
- Creating calculated fields inside PivotTables
- Using basic formulas for quick analysis
- Adding calculated items for custom metrics
Module 4: Visualizing with Pivot Charts & Dashboards
- Turning PivotTables into charts for better understanding
- Customizing chart formats for impact
- Creating simple interactive dashboards
- Sharing charts and dashboards effectively
Module 5: Advanced PivotTable Tips
- Exploring more analysis options like drill-down and conditional formatting
- Combining data from multiple sources in one PivotTable
- Highlighting trends using rules and formatting tools
- Linking PivotTables with PivotCharts for deeper insight
Module 6: Cleaning & Preparing Data
- Understanding data types (numbers, dates, text, logical)
- Removing and selecting specific columns efficiently
- Finding and removing duplicates
- Fixing missing or incorrect data (nulls, errors, spaces)
- Cleaning unwanted characters and hidden formatting
Excel Automation with Macros & VBA (Intro to Intermediate Level)
Module 7: Getting Started with Excel Automation
- Introduction to VBA and the Macro Recorder
- Recording simple macros for everyday tasks
- Assigning macros to buttons and quick-access tools
Module 8: Writing Simple VBA Scripts
- Basics of writing and editing VBA code
- Using formulas and basic logic in your scripts
- Understanding variables and how to store values
Module 9: Automating Tasks with AI Guidance (ChatGPT-Enhanced)
- Creating simple automated workflows (e.g., reports, cleanups)
- Looping through rows to perform actions
- Identifying dynamic data range
=======================
Dates options: 3 - 4 November, 13 - 14 November, 26 - 27 November, 1 - 2 December, 15 - 16 December 2025
Duration: 2 days
Venue: ACCEA Malaysia, Amoda Building, K.Lumpur
Training Fee: RM1,666/pax
