Learning Outcomes

By the end of this session, participants will be able to:

• Use advanced Excel formulas confidently and accurately. 
• Retrieve, match, and consolidate data effectively. 
• Analyse large datasets quickly and efficiently. 
• Build PivotTables and Pivot Charts for meaningful reporting. 
• Create dynamic and executive-friendly management reports. 
• Clean, transform, and structure data efficiently. 
• Reduce manual reporting effort through smarter Excel techniques. 

Session Agenda

Module 1: Advanced Formula Techniques

Topics Covered:

• Conditional functions, including IF() and nested IF() formulas 
• Error handling using IFERROR() 
• Multi-criteria calculations using SUMIFS() and COUNTIFS() 
• Key text functions for data preparation and reporting

Module 2: Lookup and Data Matching Mastery

Topics Covered:

• Traditional lookup functions 
• Modern best-practice lookup approaches 
• Powerful formula combinations for data matching 
• Common lookup errors and how to avoid them 

Module 3: Data Cleaning and Smart Controls

Topics Covered:

• Removing duplicates 
• Using Text to Columns 
• Creating data validation drop-down lists 
• Protecting formula cells 
• Using named ranges 
• Applying Find and Replace professionally 

Module 4: PivotTables and PivotCharts

Topics Covered:

• Building and structuring PivotTables 
• Creating PivotChart reports 
• Using interactive tools such as slicers and timelines 
• Summarising data for faster business insight 

Module 5: Dashboards and Visual Reporting

Topics Covered:

• Creating KPI summary sheets 
• Selecting the most suitable chart types for business reporting 
• Building dynamic charts 
• Designing executive-friendly report layouts 
• Applying traffic light indicators 
• Using conditional formatting for dashboard reporting 

Module 6: Power Query Essentials

Why Power Query matters:

Manual copy-paste reporting is time-consuming and outdated. Power Query enables users to clean, combine, and refresh data efficiently with minimal manual effort.

Core skills covered:

• Importing CSV and Excel data 
• Cleaning and transforming columns 
• Splitting text into structured fields 
• Merging files 
• Appending monthly data 
• Refreshing reports with one click 

Module 7: Practical Exercises and Case Challenge

Participants will apply the techniques learned through practical, business-focused exercises.

• Practical Lab 1: Create a procurement spend analysis
• Practical Lab 2: Build an HR headcount dashboard
• Mini Challenge: Analyse 5,000 rows of data and produce a management summary within 15 minutes.

Target Audience

This programme is designed for:

• Professionals who already use Excel and wish to enhance their analytical capability, 
• Improving reporting efficiency and strengthen their advanced productivity skills.