Level 3 (Advanced) Excel Course

This course will show you how to create robust, well-designed spreadsheets using advanced formulas, pivot tables, macros and much more. Develop sought-after skills and become a legend in your office.

Advanced Excel

Course Description

This is a 1 day advanced Excel course that covers the top-end functions of Excel. It is aimed at experienced Excel users who work with large volumes of data and require methods to better handle and maintain this data.

Want To Customise Your Content?

Don't see exactly what you want in the topic list below?

You are welcome to swap topics in and out from the different courses available to create your own custom course.

We can accommodate all requests. Customisation is 100% free. 

Let us deliver a powerful course with useful content that you or your staff will actually use. Tailor the training around your own needs and requirements.

Click to tell us what you need.

Duration

One day

Versions Taught

Our computers are installed with Excel 365 (desktop) - the latest version.

If you have an earlier or Excel such as Excel 2016 or Excel 2013 we will explain any key differences during the training.

Still using Excel 2010 or earlier? It's time to say goodbye to an old friend and upgrade! Microsoft don't support this version any more.

Prerequisites

This course is designed for people who have used Excel regularly for a minimum of 12 months. Please view the contents of the Intermediate Excel course and make sure that you are comfortable with at least 80% of the contents before considering this course.

Related Courses

Level 1 (Beginners) Excel course

Level 2 (Intermediate) Excel course

Customise with additional Excel topics

what you get

Here's what you'll learn in this course

Pivot Tables, Pivot Charts, Power Pivot & Power Query

  • Creating a pivot table
  • Exploring pivot-table components
  • Filtering data using report, column and row fields
  • Using the Slicer tool
  • Using the Timeline filter
  • Moving, adding and removing data from the pivot table
  • Updating the original data and refreshing the pivot-table
  • Changing the data function
  • Renaming a field
  • Using the Show Pages tool
  • Showing how a data total was generated
  • Formatting a pivot-table
  • Exploring the pivot-table options
  • Collapsing and expanding data
  • Using calculated fields and calculated items
  • Grouping text items, dates and numerical ranges
  • Sorting data manually and automatically
  • Creating a custom PivotChart Report
  • Cleaning data with Power Query
  • Consolidating & aggregating data with Power Pivot

Macros

  • What is a macro and why would you use one?
  • Recording a macro
  • Running a macro from the Quick Access toolbar or a graphic
  • Exploring macro code using VBA
  • Going a little deeper with VBA fundamentals
  • Debugging VBA code using stop points and step-through

Advanced Formula Functions

  • Lookup functions - VLOOKUP, HLOOKUP, LOOKUP, MATCH and INDEX and why XLOOKUP trumps them all
  • TEXT functions - UPPER, LOWER, PROPER, LEFT, MID, RIGHT, SEARCH, LEN, CONCATENATE and TEXT and how to combine them to be useful
  • Financial functions
  • Database functions (if time or if requested)
  • Advanced date and time functions (if time or if requested)

Custom Formatting

  • Beyond the standard Excel formats
  • Creating custom formats for numbers, currency and dates
  • How to set formats using a formula

Consolidating worksheet data from different places

  • Consolidating data by position
  • Consolidating data by matching labels

"What If?" Analysis

  • Using Goal Seek to set a specific answer and ask Excel to calculate what the input value(s) need to be
  • Using Solver to find the best solution based on a set of given constraints
  • Adding constraints
  • Keeping a solution or restoring original data
  • Save a Solver solution as a scenario
  • Creating a Solver Report
  • Scenario Manager (if time)
  • Data tables (if time)