Course Overview

The Advanced Microsoft Excel Course offers in-depth skills and tools for data analysis, complex formulas, data visualisation, and automation. Participants will explore advanced functions, data modelling, pivot tables, macros, and VBA (Visual Basic for Applications) to streamline workflows and enhance data-driven decision-making. By the end of the course, learners will be able to analyse data efficiently, automate repetitive tasks, and create dynamic reports, positioning them as Excel power users in their professional settings.

 

Prerequisites

Basic to intermediate Excel skills are required.

Target Audience

This course is ideal for business analysts, data professionals, finance experts, and anyone who has foundational Excel knowledge and wants to advance their skills for more complex data tasks and analysis.

Course Highlights

  • Master complex Excel functions and data analysis techniques.
  • Develop skills in using pivot tables, advanced charting, and conditional functions.
  • Explore data validation, auditing tools, and error-checking.
  • Gain hands-on experience with automation using macros and VBA.
  • Learn advanced data visualisation for impactful reporting.

Course Objectives

By the end of this course, learners will be able to:

  • Apply complex formulas and nested functions for data calculations.
  • Use pivot tables and Power Pivot to analyse large datasets.
  • Create validation rules and auditing processes to ensure data accuracy.
  • Design advanced data visualisations and interactive reports.
  • Automate Excel tasks with macros and introductory VBA scripting.
  • Utilise Power Query for data cleaning, transformation, and integration.
  • Build dynamic, data-driven dashboards for reporting.
  • Integrate and manage data from multiple sources in Excel.
  • Implement advanced Excel security features to protect sensitive data.
  • Develop efficient, repeatable workflows that improve productivity and accuracy.

Course Outline

Instructional Methods: Hands-on exercises in complex formulas, group discussions on function applications, and case studies on advanced calculations.

Topics Covered:

  • Nested functions and array formulas.
  • Logical functions: IF, AND, OR, and nested IF statements.
  • Lookup functions: VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP.
  • Text functions: TEXTJOIN, LEFT, RIGHT, MID, and FIND.
  • Date and time functions: DATEDIF, NETWORKDAYS, EDATE, and EOMONTH.

Instructional Methods: Practical exercises in data summarisation, group discussions on data analysis, and case studies on pivot table insights.

Topics Covered:

  • Creating and customising pivot tables for data analysis.
  • Calculated fields and calculated items in pivot tables.
  • Grouping data by date, value, and custom criteria.
  • Introduction to Power Pivot for handling large datasets.
  • Using pivot charts for dynamic data visualisation.

Instructional Methods: Hands-on exercises in validation techniques, group discussions on error checking, and case studies on data quality management.

Topics Covered:

  • Advanced data validation rules for consistent data entry.
  • Creating dropdown lists and custom input messages.
  • Using auditing tools: trace precedents, dependents, and formula evaluation.
  • Error checking and troubleshooting formula errors.
  • Protecting and securing data with worksheet and cell-level permissions.

Instructional Methods: Practical exercises in charting, group discussions on visualisation techniques, and case studies on effective reporting.

Topics Covered:

  • Creating dynamic charts and interactive dashboards.
  • Using advanced chart types: waterfall, radar, histogram, and box plots.
  • Combining charts for layered visualisations.
  • Sparklines and conditional formatting for visual cues.
  • Building dynamic reports with slicers and timelines.

Instructional Methods: Hands-on exercises in macro recording, group discussions on automation, and case studies on workflow efficiency.

Topics Covered:

  • Recording and editing macros to automate repetitive tasks.
  • Writing basic VBA code and understanding the VBA editor.
  • Assigning macros to buttons and shapes for user-friendly automation.
  • Using loops, conditions, and variables in VBA.
  • Debugging VBA code and handling errors in automation scripts.

Instructional Methods: Practical exercises in data transformation, group discussions on data models, and case studies on data integration.

Topics Covered:

  • Introduction to data modelling and data relationships.
  • Using Power Query to clean and transform data from multiple sources.
  • Merging, appending, and reshaping data with Power Query.
  • Creating data models to support complex analysis.
  • Connecting data models with Power Pivot for advanced reporting.

Certification

A certificate of completion will be awarded upon successful completion of the course.

Course Fees

$788 $488

Advanced Microsoft Excel

Take Your Excel Skills to the Next Level with Advanced Analysis and Reporting

Apply Now

Register for the Advanced Microsoft Excel Course

Take Your Excel Skills to the Next Level with Advanced Analysis and Reporting

Register your interest for this course.
Declaration
This field is for validation purposes and should be left unchanged.