In-house course
Advanced Excel Training — Power Query, dashboards and automation
For teams who already know PivotTables and now spend their week rebuilding the same reports by hand.
- Duration
- 1–2 days
- 7–14 hours
- Delivered
- At your premises
- Including Sarawak and Sabah
- HRD Corp ceiling
- RM21,000
- For 2 days, whole group
What your team will be able to do
- Import, clean, merge and append files with Power Query, and refresh the whole process in one click
- Combine every file in a folder — twelve monthly exports, one per branch — into a single clean table
- Build a data model with relationships in Power Pivot instead of chains of lookups
- Write core DAX measures such as totals, year-to-date and percentage of total
- Use FILTER, SORT, UNIQUE, LET and LAMBDA to replace long, fragile formulas
- Build an interactive dashboard driven by slicers across several PivotTables
- Record, edit and assign a simple macro to automate formatting and repetitive steps
- Document an automated workbook so someone else can maintain it
Who it is for
- · Finance, management-accounting and reporting teams with a heavy monthly close
- · Analysts who already use PivotTables daily
- · Teams that consolidate data from several branches, systems or files
- · Groups who want to automate first and decide later whether they need Power BI
When something else fits better
- · Mixed or beginner groups — start with Microsoft Excel, Basic to Advanced
- · Teams whose reports must be shared online with many viewers — Power BI is the better tool for that
- · Full VBA programming — this course records and edits macros; it does not teach VBA as a language
About this course
There is a point where knowing more formulas stops helping. The team is competent — they can write a SUMIFS, build a PivotTable, fix a broken lookup — and they still spend the first week of every month copying exports into a master workbook, cleaning the same columns, and rebuilding the same charts. The problem is no longer skill with Excel. It is that the work is being done by hand at all.
This course is about removing that manual layer. Power Query takes over the import and cleaning, so a new month is a refresh rather than a rebuild. Power Pivot and a small amount of DAX let one model combine several data sources that would otherwise be glued together with lookups. Dynamic arrays and LET/LAMBDA make formulas shorter and easier to audit. Recorded macros handle what is left.
It runs as one intensive day for a strong group, or two days where the team wants time to rebuild their own reporting in the room. Either way the exercises are the team’s real monthly process, captured during scoping, so the course ends with that process automated rather than with a demonstration of what could be done.
Cost and funding
Who pays for it
In-house training is priced per group, not per person, and HRD Corp reimburses it per group too — up to RM10,500 per full day where the provider and course are registered and your company applies before the training. Put in your numbers to see what your levy covers.
The levy ledger
What your company already contributes, what this costs, and what is at risk if nobody claims.
Your monthly levy
RM3,000
1% of monthly wages — compulsory
HRD Corp reimburses up to
RM21,000
About RM1,400 per person at 15 people
At risk of forfeiture
RM62,000
Two years of contributions above the RM10,000 floor, if no claim is made
At your contribution rate, the claimable ceiling for this course is roughly 7.0 months of levy — money already leaving the payroll every month whether it is used or not.
Send it to whoever signs
The numbers above, written as an email. Edit it, then forward it.
An estimate, not advice. Levy rates and the forfeiture rule are HRD Corp’s, read 10 September 2026; the reimbursement ceiling is from HRD Corp’s Allowable Cost Matrix (January 2026 version), read 25 September 2026. Your actual balance depends on your claim history. Confirm your position with HRD Corp.
Course outline
Indicative. The final agenda is built from your team’s own files and processes during scoping, and adjusted to the group’s level from a short skills check before the course.
01Power Query — the import you only build once
- · Connecting to Excel, CSV and folder sources, and what a query actually stores
- · Cleaning steps: types, splits, trims, replacing values, removing junk rows
- · Unpivoting a report laid out for printing into a table Excel can analyse
- · Merge (lookup) and Append (stack) queries, and when to use each
- · Folder queries that pick up next month’s file automatically
02Power Pivot and DAX — one model, several sources
- · Loading queries to the data model instead of the worksheet
- · Relationships between a fact table and lookup tables, and why that replaces VLOOKUP
- · Calculated columns versus measures, and the one-line rule for choosing
- · Core measures: SUM, DIVIDE, CALCULATE with a filter, and a year-to-date total
- · PivotTables built on the model, including from several tables at once
03Modern formulas
- · Dynamic arrays and spill ranges: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE
- · LET to name the parts of a long formula so it can be read
- · LAMBDA to turn a repeated calculation into a named function for the team
- · Error-proofing: handling blanks, text-as-numbers and missing matches
04Dashboards and automation
- · Laying out a management dashboard that answers three questions, not thirty
- · Slicers connected across several PivotTables and charts
- · Recording a macro, reading what it recorded, and editing it safely
- · Assigning macros to buttons, and saving as a macro-enabled workbook
- · Documentation: a notes sheet that tells the next person how the file works
Format and delivery
| Detail | |
|---|---|
| Duration | 1–2 days |
| Contact hours | 7–14 hours |
| Group size | From 2; 15–20 works best, larger teams run as batches |
| Equipment | One laptop per participant with the software installed |
| Location | Your premises, anywhere in Malaysia including Sarawak and Sabah |
| Format | In person. Online delivery possible where a team is split |
| Pricing | Quoted per group per day, after scoping |
| Funding | HRD Corp claimable where provider and course are registered |
| Certificate | Certificate of attendance |
Questions
›How is this different from the Basic to Advanced course?
Basic to Advanced takes a mixed group from data entry to PivotTables and a first dashboard. This course assumes all of that and is about automation: Power Query, the data model and DAX, and recorded macros.
If you are not sure which fits, the pre-course skills check answers it. We would rather move a group to the right course than run the wrong one.
›Should we learn this or go straight to Power BI?
Power Query and DAX are the same technology in both, so nothing learned here is wasted. The difference is where the result lives. If reports stay in Excel and are emailed to a small group, this course is enough. If many people need to view live dashboards online, Power BI is the better destination, and this course is a good first step towards it.
›Is it HRD Corp claimable?
Yes, where the provider and the specific course are registered with HRD Corp and the employer applies before the training takes place. A one-day in-house course is capped per group, so it suits a small specialist team well.
›Which Excel version is needed?
Microsoft 365 for Windows is the right version for this course. Power Query and Power Pivot are available on Excel 2016 and later for Windows, but LET, LAMBDA and the dynamic-array functions need Microsoft 365 or Excel 2021 or later, and the Mac version of Excel does not include Power Pivot.
We check versions during scoping so nobody discovers on the day that a feature is missing.
›Will the trainer automate our actual monthly report?
That is the intended outcome of the two-day version. Send the files and the steps you follow today, anonymised if necessary, and the second day is spent building that process with the team rather than for them — so they can maintain it afterwards.
Get a scoped quote
Tell us the course, your team size, where you are and whether you pay the HRD Corp levy. Within one working day you get a suggested course shape, next steps on a quote, and how to claim it through HRD Corp.