Advanced Analysis and Visualisation in Excel


As an experienced Excel user, you may often use tools such as Pivot Tables, VLookups and Conditional Formatting. However, there are many other features in Excel which provide efficient and effective methods to cleanse, analyze and visualize large datasets. On this course you will learn about Advanced Filter, Custom Sort, Power Query, Advanced Function writing and What-If Analysis, all invaluable tools to help you work more extensively in Excel.

Duration: 1 day

Who is it for: This is an Advanced training course, designed for current users who are comfortable with all the key tools such as pivot tables and VLookups, but want to explore what else Excel has to offer which will help manage worksheets and perform calculations in large datasets

Layout: 1 Day – Live classroom or virtual classroom


You will learn how to write more complex functions– using named ranges, structured referencing and nesting. You will use a range of Text Functions, Logical Functions and Lookup and Reference Functions. You will explore other tools such as Power Query to help cleanse data and work with advanced filtering and sorting, including some new array functions.


Advanced Filtering and Sorting

  • Using Advanced Filter and wildcards
  • Using Custom Sort options
  • Array functions (*Office 365 only) UNIQUE, SORT, FILTER

Grouping and Subtotals

  • Grouping and Outlining
  • Using the Subtotals feature
  • SUBTOTAL Function

Advanced Function Writing

  • Named Ranges
  • Advanced Lookup and Reference Functions
  • Using Nested Logical Functions: IF, AND, OR, IFERROR, ISFunctions

Using Power Query to Profile and Cleanse Data

  • Using Format as Table to load an Excel dataset into Power Query
  • Power Query tools: Split columns, Remove Blank Rows, Trim
  • Using Column Profiling to identify errors and blanks

Using What-If Analysis Tools

  • Scenario Manager
  • Goal Seek
  • Data Tables

Exploring Excel Chart Types

  • Creating charts using a range of chart types such as Waterfall charts, Combo Charts, Histograms and Treemaps
  • Using Sparklines to analyze trends over time
  • Using Conditional Formatting to visualize patterns and trends

Enquire about this course

"*" indicates required fields

By submitting this form, you agree to our Privacy Policy.
This field is for validation purposes and should be left unchanged.