1. COURSE OVERVIEW

Kursus ini memfokuskan kemahiran analisis data, fungsi kompleks, automasi, data modelling dan pembinaan dashboard profesional. Peserta belajar teknik sebenar yang digunakan dalam kewangan, HR, pentadbiran, audit, pemasaran dan pengurusan projek.

2. DURATION RECOMMENDATION

• 2 Hari: Intermediate + Advanced essential

• 3 Hari: Intermediate + Advanced full mastery + Mini Project

3. TRAINING METHODOLOGY

• 80% practical

• Step-by-step guided exercises

• Real office case studies

• Sample data disediakan

• Mini project (Day 2 or 3)

4. INTERMEDIATE MODULE

4.1 Managing & Cleaning Data

• Remove Duplicates

• Text to Columns (Split data)

• Flash Fill Advanced

• Find & Replace advanced

• Data Validation:

  • Dropdown list

  • Error alerts

  • Dependent dropdown (State → District)

4.2 Intermediate Functions

🔹 Logical Functions

• IF, Nested IF

• AND, OR

• IFS

🔹 Lookup Functions

• VLOOKUP (Exact & Approx)

• HLOOKUP

• XLOOKUP (Row/Column search)

• INDEX + MATCH (Pengenalan)

🔹 Math & Statistical

• SUMIF, SUMIFS

• COUNTIF, COUNTIFS

• AVERAGEIF, AVERAGEIFS

🔹 Text Functions

• LEFT, RIGHT, MID

• TRIM, CLEAN

• SUBSTITUTE

• PROPER, UPPER, LOWER

• CONCAT, TEXTJOIN

4.3 Tables & Sorting

• Convert to Table

• Structured References

• Slicers for Tables

• Sort & Filter Advanced

• Multi-level Sort

• Subtotal

4.4 Pivot Table & Pivot Chart

• Creating PivotTable

• Grouping (Date, Month, Quarter)

• Value Field Settings

• Show Values As (% of Total, Running Total)

• PivotChart

• Slicer & Timeline

• Building multi-sheet report

5. ADVANCED MODULE

5.1 Advanced Functions

🔸 Lookup & Reference (Advance)

• XLOOKUP multi-condition

• INDEX + MATCH + MATCH

• INDEX + MATCH vs VLOOKUP comparison

• XMATCH

🔸 Text & Cleaning (Advance)

• Working with messy data

• Combine TRIM + CLEAN + SUBSTITUTE

• Extracting patterns

• Custom text transformations

🔸 Date & Time Functions

• EDATE

• EOMONTH

• NETWORKDAYS

• WORKDAY

• YEARFRAC

• WEEKNUM

🔸 Array & Dynamic Functions

• UNIQUE

• FILTER

• SORT

• SEQUENCE

• RANDARRAY

• LET

• LAMBDA (Intro)

5.2 Power Query (Essential Advanced)

• Importing multiple files

• Transforming data (trim, clean, remove rows)

• Append & Merge Queries

• Unpivot data

• Creating data pipelines

• Refresh automation

5.3 Power Pivot & Data Modeling

• Creating Data Models

• Relationships

• Star Schema concepts

• Building KPI Calculations

• Creating custom measures using DAX

DAX Basics

• SUMX

• CALCULATE

• FILTER

• DISTINCTCOUNT

• RELATED

5.4 Advanced PivotTable Skills

• Calculated Fields

• Calculated Items

• Custom grouping

• Advanced PivotChart

• Building monthly/quarterly KPIs

5.5 Dashboard Design (Professional)

• Dashboard layout planning

• Using slicers/timelines

• Indicator vs KPI

• Interactive charts

• Linking multiple PivotTables

• Building drill-down reports

• Excel design best practices

5.6 Automation with Macros & VBA (Beginner)

• Record Macro

• Assign Macros to buttons

• Intro to VBA Editor

• Simple scripts:

  • Auto-cleaning data

  • Automate formatting

  • Auto-generate reports

• Creating user-defined functions (UDF)

6. MINI PROJECT (Day 2/3)

Peserta akan hasilkan projek lengkap menggunakan dataset sebenar:

Projek merangkumi:

✔ Data cleaning
✔ Consolidate data using Power Query
✔ Build Pivot Report
✔ KPI Dashboard
✔ Automate process
✔ Present final dashboard

7. TARGET AUDIENCE

• HR / Finance / Admin

• Analyst & Executive

• Pegawai kerajaan

• Trainer & Educator

• Marketing & Sales

• Sesiapa yang ingin kuasai Excel tahap tinggi

8. LEARNING OUTCOMES

Peserta akan dapat:

INTERMEDIATE

✔ Menggunakan formula IF & lookup secara profesional
✔ Mengurus, membersih & menganalisis data
✔ Menghasilkan laporan PivotTable lengkap

ADVANCED

✔ Menguasai formula kompleks dan Dynamic Arrays
✔ Menggunakan Power Query & Power Pivot
✔ Membina dashboard profesional
✔ Mengautomasikan laporan menggunakan macroDescribe important details like price, value, length of service, and why it’s unique. Or use these sections to showcase different key values of your products or services.