Excel - The New and Advanced Features Course

Are your Excel skills still up to date?

Excel has gone through MASSIVE changes in the past few years, with a completely new calculation engine opening up an incredible array of new tools!

This completely new, full-day course is designed for people who already use Excel for Microsoft 365 to an advanced level, and want to pick up the latest skills to work even smarter.

What you will learn on this course

This course is broken down into three sections - each a deep dive into an area with advanced or new functionality that will change the way you work in Excel.

PART 1: PowerQuery

First, we dive into PowerQuery – and if you’ve not used PowerQuery before, you’re in for a treat.

PowerQuery lets you take data from virtually any source - one or more spreadsheets, SQL Server, the web - and analyse it in Excel. But it doesn't just "dump the data" for you to sort out - PowerQuery lets you analyse, cleanse, prepare and adjust the data before presenting to your worksheet or a PivotTable.

For example, take a look at this video - where we show you how to get live data from the web into Excel in under 2 minutes!

We'll use PowerQuery to:

  • Transform and cleanse your data
  • Understand and edit query steps - and the M language behind them
  • Look column profiling to gain an insight into your data
  • Add complex calculations to your data without the need for functions 
  • Get data from the web - and see that the data updates on a regular basis without a single click of your  mouse!
  • Pivot – and unpivot – your data

Then we'll move on to perhaps the most exciting part of the course (at least for Excel geeks like me!) Starting in around 2020, Microsoft introduced a whole new calculation engine into Excel, which opens up a whole host of new possibilities. In this part of the course, we'll explore some of this new set of tools.

PART 2: New and Advanced Functions

  • A quick look at the VLOOKUP function, and then at the new replacement function – XLOOKUP
  • The LET function, which can simplify creating complex functions by allowing you to re-use components of a function more than once
  • New array-based and spill  functions, including UNIQUE, SORT, SORTBY, SEQUENCE, TAKE, CHOOSECOLS, VSTACK, HSTACK and FILTER
The LET function in Excel
Along the way, we'll look at how to combine these functions with existing, but lesser-used functions such as LARGE and SEARCH to get add power to your calculations.

And we'll look at some of the new functions coming out right now - including functions to allow you to add images such as logos into cells!

PART 3: Creating charts and dashboards in Excel

Charts are not often found in an advanced Excel course.... but then these aren't just any charts!

For example, how do you create a waffle chart in Excel? What even is a waffle chart?!

Well, it looks like this....

...and it's not even a chart! It's created purely using some of the functions that we've covered in Part 2 of the course, and it's a great way to show percentages and how they change over time.

We'll show you how to create this, plus
  • How to create dumbbell charts (just look at the top of this page for an example!)
  • How to add conditional formatting to your charts to highlight the important content - and have it update as your data changes
  • How to make your charts interactive, so that users can select the data that they're interested in, and have the chart change to reflect this
  • How to combine chart types in really useful and innovative ways - for example, adding a vertical "Average" line to a horizontal bar chart
Dynamic charts in Excel - responding to user interaction

FREE BONUS! An AI tool in Excel

In the last, discussion-based, part of this course, we'll have a chat about the role of AI in Excel's future. We'll look at how Microsoft's Copilot tool can integrate with your Excel and workbook, and what its benefits and limitations are. 

You'll also get a FREE copy of our in-house-developed AI workbook, which brings the power of Chat GPT and Open AI right into Excel (as demonstrated above). 

This fantastic tool can answer questions, analyse data and explain and spot errors in your formulas, and will save HOURS of time - and it's yours to take away with you.

Dynamic charts in Excel - responding to user interaction

Hands-on exercises throughout to reinforce the knowledge

All the way through this course, you'll see how the new tools and functions are used, then you'll get your own chance.

Covering the theory is great - but to really remember it, you need to get your sleeves rolled up and give it a go. So, there are challenges and exercises all the way through to give you the chance to experiment and find your way to solving the problem.

After all, once the course is over, that's what you'll be doing.

But you're not on your own...

We don't believe in just delivering the training, then saying a cheery "goodbye". So, after the course, you'll have a support email address that you can always send any questions to. It's all part of the support we offer to all our delegates.

Please note: This course won't be for everyone...

This is a fast-paced course, covering some advanced topics. 

If you're already happy with the likes of VLOOKUP and IF, and you're looking to stretch your skills to the next level, then you'll get a huge amount from this course.

But perhaps if you're not comfortable with why dollar signs are used in formulas, or you want something at a more gentle pace, then we'd be delighted to put something together for you which might suit your requirements better.

It's also a pre-requisite for the course to run that you're using Excel for Microsoft 365, as many of the new functions we look at only exist in this version of Excel and won't be made available in versions such as Excel 2016 and Excel  2019

Sounds good! What's next?

We run all our training "in-house" meaning that we come to you, and run the course in your own offices, to  your schedule.

The cost of this course is just £299 per person (+ VAT) with a minimum of three people on the course. This includes everything you'll need to get the most out of the day, including

  • the hands-on training
  • a printed manual
  • all the required files
  • email support after the event
  • a certificate of completion for each attendee.

Special offer - save up to 40%!

If there's a group of you who'd benefit from this course, then book a group session for up to 8 people for just £1400 + VAT - saving over 40% on the regular price!

One of the best training courses I've been on

Andrew delivered this course to a room full of Instructors who are all adept at delivering classroom lessons in their own subject matter. I would like to say that his delivery of the course material and demeanor suited the audience. He was open to questions and was clearly very knowledgeable in his subject matter. One of the best one day training courses I've been on for a long time


AM

Excellent!

I've learnt more in 3 hours than in the last 25 years!


Andy Dawe

Much better than expected!

The course was much better than expected! I really feel I learnt things that I will definitely use in the future. The trainer was great. So knowledgeable, patient and able to answer every question.


Hayley Cramer