Microsoft Excel Advanced Course

Overview

‍

Duration

1 Day

‍

Aims

This Microsoft Excel Advanced course is for attendees who want to expand their knowledge into some of the more advanced functions, analyse / summarise tables of data and automate common tasks.

Our Microsoft Excel course is delivered using the latest version available through Microsoft 365. This ensures that our demonstrations include the most up-to-date features and functions. While many of the concepts and techniques covered are applicable to earlier versions of Excel, there may be some differences in the user interface and available features. If you are using an older version of Excel, our trainers are happy to provide guidance on how to access similar functionalities within your version.

‍

Prerequisites

Attendees who wish to attend this course must have a good knowledge of Excel and be competent at working with formulas using absolute cell references, use basic functions like sum, average, max, min, count and be able to write formulas across multiple sheets and multiple workbooks.

‍

Objectives

‍On completion of this course, delegates will be able to:

• Use logical, lookup, statistical and error functions
• Use auditing tools
• Create range names and use range names in formulas
• Set restrictions on data entry using data validation
• Protect an Excel cell, worksheet, and workbook
• Automate common task by recording a basic macro
• Create, edit, format Pivot Tables and Pivot Charts

‍

Course Content

Introduction and Objectives

Using Logical, Lookup and Statistical Functions

• Using the VLOOKUP / HLOOKUP functions
• Using IF, AND & OR function
• Using the IFERROR function
• Using COUNTIF & SUMIF

Using Auditing Tools

• Displaying precedents and dependents
• Removing arrows
• Evaluating a formula
• Setting manual & automatic calculation

Using Range Names

• Assigning names to ranges
• Using range names in formulas
• Editing and deleting named ranges

Using Data Validation

• Validating data using a data validation list
• Creating a custom input and error message
• Editing a validation list
• Removing data validation

Protecting Excel Data

• Protecting entire worksheet
• Protecting and unprotecting certain cells within a worksheet
• Protecting workbook structure

Introduction to Basic Macros

• What is a macro?
• How to record macros
• How to run a macro using different methods
• Editing and deleting macros

Creating / Revising Pivot Tables and Pivot Charts

• Creating a Pivot Table
• Adding, moving, removing Pivot Table fields
• Formatting and structuring a Pivot Table
• Filtering Pivot Table items
• Changing the summary function
• Summarising data as percentages
• Refreshing a Pivot Table
• Changing the data source for a Pivot Table
• Adding a Pivot Table slicer
• Moving & deleting a Pivot Table
• Creating more than one pivot table on the same worksheet
• Creating a Pivot Chart
• Formatting a Pivot Chart

Hints and Tips

‍

Next Steps

• Excel Power User: Mastering Complex Functions and Data Models
• Excel VBA Introduction
• Power BI Desktop Introduction

‍

Download PDF Outline

‍

Client Testimonials

‍

"An excellent course well delivered."

"Enjoyed the practical aspects of the course, working through exercises to reinforce the learning"

"Great course and great trainer......very enjoyable. The day went quickly. Thank you"

"Great course, well presented and jam packed with content. Would recommend."

"This was one of the best organised training courses I've ever been on, everything was excellently set up and delivered."

"This was a very practical and well-structured course. The step-by-step approach to each topic, especially the hands-on practice with Pivot Tables and Macros, made the learning process clear and engaging. The hints and tips section was especially helpful for improving day-to-day efficiency. I feel much more confident using Excel now. Thank you!"

"Thank you so much for an insightful training, very well explained and practical example. Appreciate your patience and making the training engaging. Looking forward to putting these skills into action!"

"I do have to say that I feel the trainer is excellent, his delivery is fantastic"

"I will be recommending this course to my colleagues! I've learnt various things which can help me in my job going forward. Thank you."

‍

Frequently Asked Questions (FAQs)

Who should attend the Excel Advanced course?

Users who are confident with Excel at Intermediate level and want to master advanced functions, auditing tools, data validation, protection, macros, and Pivot Tables/Charts.

What are the prerequisites?

You should already be comfortable with absolute references, multi-sheet formulas and core functions such as SUM, AVERAGE, MAX, MIN and COUNT.

How is the course delivered?

Live online via Microsoft Teams with an expert instructor, demonstrations and guided practical exercises.

Which Excel version do I need?

Any current desktop version of Microsoft Excel (Microsoft 365 or Excel 2016+) is suitable. A Windows PC is recommended for the best experience.

Do I receive a certificate?

Yes — you will receive an ExperTrain certificate of completion after the course.

Does the price include VAT?

No. The price (£150) excludes VAT at 20%. VAT is added at checkout for UK customers where applicable.

How do I book a place?

Use the booking/enquiry options on the page, select your preferred date and we’ll confirm availability and send joining instructions.

‍

‍