Advanced Microsoft Excel Workshop

Master Advanced Data Analysis, Dynamic Formulas, Pivot Tables, and Automated Reporting with Macros in Excel

Platform:

Online

In-class

Revised and Updated: 24 October 2026

Date	Venue	Duration
07 - 08 December 2026	East London, Eastern Cape	2 Days
10 - 11 March 2027	Sandton, Gauteng	2 Days
09 - 10 June 2027	Live/Online	2 Days
08 - 09 September 2027	Sandton, Gauteng	2 Days
08 - 09 December 2027	Sandton, Gauteng	2 Days

Course Introduction

It’s time to save time and gain control over your financial, accounting, auditing, presentation, and day-to-day data calculation, sorting, and storage work, using one of the most powerful software applications available: Microsoft Excel. This workshop covers advanced Excel functions, pivot tables, data analysis tools, automation using macros, complex formulas, and scenario analysis. It is designed to take users beyond the basics, helping them leverage Excel’s full potential for complex data manipulation, analysis, and visualisation.

Discover methods to dramatically reduce the time you spend on spreadsheets
Develop practical solutions to your business problems through superior spreadsheet design
Upgrade your spreadsheet skills with advanced data management techniques
Consolidate worksheets from different sources into one workbook
Develop efficient techniques for using charts

Course Objectives

By the end of this Advanced Microsoft Excel Workshop, participants will be able to:

Use spreadsheet skills for advanced financial applications
Create, format, and troubleshoot pivot tables
Analyse data accurately using pivot tables and slicers
Adopt logical methods to solve complex problems through accurate data analysis and pivot table controls over financial statements
Work efficiently across related worksheets and workbooks, leveraging modern reporting and presentation techniques

Who should attend?

Accountants
Auditors
Creditors Managers
Accounts Receivable Departments
Accounts Payable Departments
Finance Managers
Operations Managers
Project Managers
HR Managers
Business Executives
Consultants
Heads of Departments
And finance people and anyone who uses Excel to analyse and manipulate data extensively for business reporting.

Training Methodology

Our diverse instructional approaches ensure effective learning:

– Lectures & Presentations: Engage with expert-driven, stimulating content.
– Course Material: Access well-crafted supporting resources.
– Group Work: Collaborate on discussions and case studies for practical insights.
– Workshops & Role-Play: Participate in immersive, scenario-based activities.
– Practical Application: Focus on applying theoretical knowledge in real situations.
– Post-Training Support: Receive extensive support after training for skill implementation.

Training Outline

Day 1 — Formulas, References, and Data Foundations
Unit 1: Filling Data

Understanding filling
Filling a series and creating a custom fill list
Modifying a custom fill list
Deleting a custom fill list

Unit 2: Formula Referencing

Absolute and relative referencing
Relative formulas
Problems with relative formulas
Creating absolute references
Creating mixed references
Other basic formulas

Unit 3: Creating Named Ranges

Creating names for the range
Using names in new formulas
Creating names using the Name Box
Using names to select ranges
Pasting names into formulas
Creating names from a selection
Using the Name Manager

Unit 4: Logical Functions

Understanding logical functions
Using IF to display text
Using IF to calculate values
Nesting IF functions
Using IFERROR
Using AND
Using OR
VLOOKUP function
Multiple VLOOKUP

Current Issue Discussion:

XLOOKUP is Microsoft's modern successor to VLOOKUP, offering left-to-right lookups, exact-match by default, and no need to count columns. Participants are introduced to VLOOKUP as the widely-used industry standard, with a note on XLOOKUP as the recommended function in current Excel versions.

Unit 5: Conditional Formatting

Formatting cells containing values
Clearing conditional formatting
More cell formatting options
Top and bottom ten items
Working with data bars
Working with colour scales
Working with icon sets
Creating and editing sparklines

Unit 6: Advanced Data Validation

Creating drop-down lists in cells
Restricting values from a list only
Creating custom error messages
Creating dependent drop-down lists

Unit 7: Advanced Formulas and Text Functions

COUNT, COUNTA, COUNTIF, and COUNTBLANK
SUM, SUMIF, and SUMIFS
NETWORKDAYS
NETWORKDAYS.INTL
TODAY and NOW functions
TRIM (removing unwanted spaces)
CONCATENATE (combining columns)

Unit 8: Dynamic Tables

Converting data into a Table
Automated calculation in tables
Converting a table back to a normal range
Using tables in charts and VLOOKUP

Day 2 — Data Analysis, Visualisation, and Automation
Unit 9: Sorting Data

Understanding lists
Performing an alphabetical sort
Performing a numerical sort
Sorting on more than one column
Sorting by rows
Working with subtotals
Paste Special options
Inserting and managing hyperlinks

Unit 10: Filtering Data

Understanding filtering
Applying and using a filter
Clearing a filter
Creating compound filters
Multiple value filters
Creating custom filters
Using wildcards in filtering

Unit 11: Creating Charts

Choosing the chart type
Creating a new chart
Working with an embedded chart
Resizing a chart
Dragging a chart
Changing the chart type

Unit 12: Advanced Charting Techniques

Adding a chart title and axis titles
Positioning the legend
Showing data labels and data tables
Modifying the axes and gridlines
Adding a trendline
Adding a text box to a chart
Creating combination charts
Creating a dual 2-axis chart

Unit 13: Pivot Tables

Understanding pivot tables
Creating a PivotTable shell
Dropping fields into a PivotTable
Filtering a PivotTable report and clearing filters
Calculations inside pivot tables
Formatting a PivotTable report
Dynamic ranges for pivot tables
Understanding and creating slicers
Practical Exercise: Lab: Building a PivotTable with slicers from a multi-source dataset.

Unit 14: Macros

Recording and running macros
Creating buttons to run macros
Assigning shortcuts to macros
Deleting macros
Using relative references in macros

Unit 15: File and Data Security

Setting open and modify file passwords
Sheet protection techniques
Protecting worksheets from deletion
Protecting specific cells, rows, or columns
Protecting data from being copied

Course Categories

Office Administration Courses ❯ Audit, Risk And Governance ❯ Compliance Courses ❯ Finance Courses ❯ Human Resources Training & Development ❯ View All Course Categories ❯

Course Registration Guide

Everything you need to know about registering for a Prospen training course.

View Registration Guide

Success Stories

Discover how our courses enhance professionals’ effectiveness in their workplaces.

No reviews found. Add reviews from the dashboard or switch source to Manual.

FAQs – Advanced Microsoft Excel Workshop

Master advanced Microsoft Excel skills covering formulas, functions, data analysis, PivotTables, charts, automation, reporting, and advanced spreadsheet techniques.

What is covered in the Advanced Microsoft Excel Workshop?

The workshop covers advanced formulas, logical functions, named ranges, data validation, conditional formatting, dynamic tables, advanced charts, PivotTables, slicers, macros, and spreadsheet security.

Who should attend the Advanced Microsoft Excel Workshop?

It is designed for experienced Excel users, accountants, auditors, finance managers, project managers, HR managers, business executives, consultants, and professionals who use Excel for data analysis and reporting.

Does the course cover advanced Excel formulas and functions?

Yes. Participants work with absolute and relative references, IF, IFERROR, AND, OR, VLOOKUP, multiple VLOOKUPs, COUNTIF, SUMIF, SUMIFS, NETWORKDAYS, TODAY, NOW, TRIM, and CONCATENATE.

Does the Advanced Excel training cover PivotTables and data analysis?

Yes. The training covers creating and formatting PivotTables, filtering and analysing data, calculations, dynamic ranges, and using slicers to create interactive reports.

Does the workshop include Excel charts and macros?

Yes. Participants learn advanced charting techniques, combination and dual-axis charts, as well as recording and running macros, creating macro buttons, assigning shortcuts, and using relative references.

Does the Advanced Microsoft Excel Workshop include practical exercises?

Yes. The workshop uses practical application, group work, workshops, case studies, and real-world spreadsheet exercises to help participants apply advanced Excel skills to business data and reporting.

Related Courses

Share this page:

Facebook X (Twitter) LinkedIn WhatsApp Email

View our training calendar to explore available courses, dates, and locations, or request a copy to help you plan your training schedule with confidence.

Consulting

Training & Services

Reliable Shuttle Services

Qualifications

Short Courses

More Courses

More Courses

South Africa

Africa

More Africa

Other Countries

Training Formats

Course Durations

Training Calendar

Training Resources

About Prospen

Advanced Microsoft Excel Workshop

Master Advanced Data Analysis, Dynamic Formulas, Pivot Tables, and Automated Reporting with Macros in Excel

Course Objectives

Who should attend?

Course Outline

Request a Call Back

Course Introduction

Course Objectives

Who should attend?

Training Methodology

Training Outline

Course Categories

Course Registration Guide

Request a Call Back

Your submission has been successful

Success Stories

FAQs – Advanced Microsoft Excel Workshop

Related Courses

Your submission has been successful