Microsoft Excel Intermediate to Advanced: Data Analysis, Reporting & Automation

COURSE CONTENT

2 Days (14 Hours) · Intermediate to Advanced · In-house at your office or online · 100% HRD Corp claimable programmes

Most Excel work in a Malaysian office is the same job done again every month. You pull an export from SQL Accounting, AutoCount or the ERP, tidy it by hand, rebuild the same PivotTable, and paste the charts into a report. It takes most of a day, and the numbers are already old by the time anyone reads them.

This programme fixes that in two days. You start with the formulas and lookups that do real analysis, move into PivotTable mastery and Pivot-driven dashboards, then use Power Query to clean and combine data so the whole report refreshes with one click. The last part covers Macros and VBA, with AI used to write, explain and correct the code.

The focus is on work you take back to your desk. You go home with a dashboard built from a real export, a query that refreshes next month, and a macro that does something you currently do by hand. You do not need any coding background.

What participants will be able to do

• Use intermediate and advanced formulas including IFS, SUMIFS, COUNTIFS, MAXIFS and TEXTJOIN.
• Choose between XLOOKUP and INDEX with MATCH, and handle results that are not found.
• Build PivotTables that answer a business question instead of only showing data.
• Add calculated fields, running totals and percentage analysis inside a PivotTable.
• Turn PivotTables into a one-page interactive dashboard with charts and slicers.
• Use Power Query to extract, clean, combine and load data that refreshes.
• Record and edit Macros, and write simple VBA for a real business task.
• Use AI tools such as ChatGPT to write, explain and debug Excel formulas and VBA.

Programme outline

Module 1: Excel Functions for Analysis

• IFS and condition-based logic, and when to stop nesting
• SUMIFS, COUNTIFS and AVERAGEIFS for multi-condition analysis
• MAXIFS and MINIFS to find the best and worst performers
• Advanced lookup: XLOOKUP against INDEX with MATCH
• Handling a result that is not found, instead of showing an error
• TEXTJOIN and CONCAT for building structured text from columns
• Absolute, relative and mixed references so a formula copies correctly
• Structuring formulas so a large file still calculates quickly

Hands-on activity

• Rebuild a multi-condition summary that currently needs manual filtering
• Replace a chain of VLOOKUPs with XLOOKUP and test the error handling

Module 2: PivotTable Mastery

• Preparing a range or table so a PivotTable behaves
• Building a PivotTable and choosing what goes into rows, columns and values
• Optimising field layout, subtotals and report layout for reading
• Sorting and grouping, including grouping dates into months and quarters
• Drilling into a figure to see the records behind it
• Filters, slicers and timelines, and when each is the right control
• Refreshing correctly when the source data grows
• Naming conventions and clarity standards so a colleague can follow your file

Hands-on activity

• Build three PivotTables from one export, each answering a different question
• Add a slicer and a timeline and connect them to more than one PivotTable

Module 3: Pivot Calculations and Insights

• Calculated fields: adding a measure that is not in the source data
• Calculated items, and the limits you need to know about
• Percentage of total, of row and of parent
• Running totals and rank inside a PivotTable
• Difference from the previous month and the previous year
• Building a KPI such as margin, yield or cost per unit inside the Pivot
• Deciding what belongs in the source data and what belongs in the Pivot
• Checking a Pivot figure against the source before you publish it

Hands-on activity

• Add margin and percentage-of-total calculations to a sales Pivot
• Build a running total and a year-on-year comparison, then verify both

Module 4: Pivot Charts and Dashboards

• Creating a chart directly from a PivotTable
• Choosing the chart that answers the question: column, line, bar or combination
• Formatting for a management audience: labels, axis, gridlines and colour
• Building a dashboard sheet that is separate from the working sheets
• Linking Pivots and visuals so one control drives the whole page
• Laying out KPI figures, trend and breakdown on a single screen
• Keeping the dashboard to one page that prints properly
• A simple framework you can reuse for the next dashboard

Hands-on activity

• Build a one-page dashboard with KPI cells, a trend chart and a breakdown
• Connect the slicers and confirm every visual moves together

Module 5: Power Query

• ETL in Excel: extract, transform, load, and why it replaces manual cleaning
• Connecting to and importing multiple data sources
• The Query Editor and the applied steps list
• Transforming and cleaning a messy data set step by step
• Removing duplicates and unwanted records safely
• Splitting, merging, replacing and reshaping columns
• Fixing errors and handling blanks
• Loading clean, ready-to-use data back into Excel or straight to a PivotTable

Hands-on activity

• Clean a messy export we provide and confirm that no records are lost
• Combine a folder of monthly files into one table, then refresh it

Module 6: Cleaning and Preparing Data

• How data should be arranged before it can be analysed
• Spotting merged cells, blank rows, subtotal rows and stacked headers
• Removing irrelevant and duplicate records
• Handling missing values, and choosing between blank, zero and excluded
• Fixing dates stored as text and numbers stored as text
• Standardising spellings, codes and branch names
• Removing unwanted characters and hidden formatting
• A repeatable checklist so next month’s file takes minutes

Hands-on activity

• Turn a report-style sheet into an analysis-ready table
• Write your own cleaning checklist for the export you brought

Module 7: Automation with Macros

• What a macro is, and what it cannot do
• Using the macro recorder so the result still works next month
• Relative against absolute recording
• Assigning a macro to a button and to the Quick Access Toolbar
• Saving as a macro-enabled workbook, and the security prompt
• Practical use cases: formatting a report, splitting a file, filling a template
• Testing a macro on a copy before you trust it
• When a macro is the wrong answer and Power Query is the right one

Hands-on activity

• Record a macro that formats a raw export into your report layout
• Assign it to a button and run it against a second file

Module 8: VBA Essentials

• The VBA editor, modules and the immediate window
• Variables and data types, in the smallest useful amount
• If and Select Case for decisions
• For and Do loops for working through rows
• Reading and writing cells, ranges and sheets
• Message boxes and simple input
• Basic error handling so the macro stops cleanly instead of crashing
• Structuring and commenting code you will have to read again in six months

Hands-on activity

• Write a loop that checks every row and flags the exceptions
• Convert a recorded macro into readable VBA you can change

Module 9: AI-Enhanced Automation

• Using AI to write a formula you can describe but not construct
• Asking for VBA, then reading it before you run it
• Debugging: what to paste in, and what to leave out
• Explaining someone else’s macro in plain English
• Turning a description of a manual process into a working script
• Checking AI output against the source data before you rely on it
• What you must never paste into a public AI tool, under PDPA and company rules
• Building the habit: describe, test, correct, keep

Hands-on activity

• Use AI to write and fix a macro for a task you currently do by hand
• Take one AI-written formula and prove the result against the source data

What you leave with

You leave with a one-page dashboard built from your own export or from the sample data we provide, a Power Query connection that refreshes it next month, and a macro that does one job you used to do by hand. More importantly, you can repeat all three on a different file without help. You will know how to check a figure against the source, and how to hand the file to a colleague without having to explain it line by line. For the company, the monthly reporting cycle gets shorter and the numbers stop changing depending on who built them.

Who should attend

• Executives and analysts who rebuild the same Excel reports every month
• Finance and accounts staff working with large exports from SQL Accounting, AutoCount or an ERP
• Operations, production and supply chain staff tracking volume, cost and yield
• HR and admin staff producing headcount, claims and attendance reports
• Sales and marketing staff analysing pipeline, target and performance
• Engineers and technical staff handling test, inspection or process data
• Anyone comfortable with basic Excel who now works with more data than manual formulas can handle