MS Excel Essential for Professionals - Advanced
This 2-day programme equips participants with advanced Excel skills for complex data manipulation, analysis, automation, and professional reporting using real-world datasets.
This is the second of the two Excel programmes and assumes the first, or equivalent working confidence with formulas and formatting. The focus moves from using Excel to automating it. Participants work through lookup and reference functions beyond VLOOKUP, nested logic, dynamic arrays, Power Query for repeatable data cleaning, PivotTables with calculated fields, and an introduction to macros for the tasks that recur every month. Throughout, the emphasis is on building workbooks that a colleague can inherit: named ranges, documented logic, and validation that stops bad input at the point of entry. Participants bring a real spreadsheet from their own work and rebuild part of it during the session. Participants finish with a rebuilt workbook they can use immediately.
HRD Corp SBL-Khas Claimable
Day 1: Advanced Data Management and Analysis
Calculating With Advanced Formulas
Quick Analysis Tools, mixed references, range names, text and financial functions, logical and lookup functions.
Auditing a Worksheet
Finding errors, Watch Window, evaluating and troubleshooting formulas.
Mastering Excel Tables
Creating, managing and analysing data with advanced table tools.
Organising Worksheet Data
Advanced sorting, filtering, subtotals and outlines.
Charting and Visualisation
Creating and formatting charts, and using Sparklines.
Working with Templates and Comments
Hyperlinks, comments and productivity templates.
Key Outcomes
- Master advanced formulas for complex calculations
- Audit worksheets and resolve errors efficiently
- Organise and analyse data with Excel Tables, PivotTables and advanced filters
- Create professional charts, Sparklines and conditional formatting
- Automate repetitive tasks using macros
- Collaborate securely with workbook protection and merging
- Use advanced lookup functions and data validation for accuracy
- Import/export data and prepare high-quality reports
Day 2: Advanced Excel Features and Automation
Analysing Selected Data
Advanced filters, database functions and outlines.
Applying Conditional Formatting
Custom conditional formatting and colour-based sorting and filtering.
PivotTables and PivotCharts
Creating, customising and analysing large datasets.
Introduction to Macros
Creating, running and editing macros (relative and absolute).
Collaborating with Others
Workbook protection, tracking changes and merging.
Advanced Lookup and Reference Functions
VLOOKUP, MATCH, INDEX, GETPIVOTDATA and array formulas.
Data Validation and Import/Export
Data validation, importing and exporting data, and external connections.
Training Mode Physical / Online / Hybrid / e-learning
HRD Corp SBL-Khas Claimable
Duration 2 Days
Certificate Certificate of Completion awarded upon full attendance