Excel 365 New Functions Training Course

Table of Contents

Excel 365 New Functions Training Course

Course Overview

Our Excel 365 New Functions training course gets you bang up to date with the quiet revolution that’s been going on with Excel formulas. Most of us have relied on familiar functions like VLOOKUP and nested IFs for years and that has now all had a major overhaul. New functions like IFS, LET, XLOOKUP and Dynamic Arrays will completely change our formula game. Making them more powerful and efficient.

The new calculation engine introduces the new type of formula, Dynamic Arrays. We can do calculations that we’d never even thought about doing before, because now you can easily work with multiple values at the same time in a formula. Dynamic Arrays solve some very tricky problems in Excel formulas and fundamentally change the way our worksheets are designed and constructed.

Excel 365 New Functions Training Course

Excel 365 New Functions Five Key Topics

  1. Manipulate text data with powerful text functions
  2. Develop simpler and more efficient decision making formulas
  3. Replace VLOOKUP with XLOOKUP
  4. Work with Dynamic Arrays
  5. Extract filtered and sorted datasets with dynamic formulas
Excel 365 New Functions Training Course

Course Details

Course Duration : 1 day
Course Availability : Monday – Saturday
Course Types : Group booking, 1-2-1 sessions, Bespoke & Public Courses
Training Times : Our courses run run from 09:30 to16:30, course registration 09:00 – 09:30, refreshments available throughout the day. A buffet lunch is available – please discuss with the team.
Facilities : A relaxed environment, air conditioned training rooms, note pads, pens and unlimited access to our online course materials.

Excel 365 New Functions Training Course Details

Course Location : Face to face group / public & onsite training available. Virtual training courses available.

Class Size : As a guide we recommend class sizes to be no more than eight.

Virtual / Online training Format : Teams or Zoom.

Tailored Course Content : Our Excel New Functions course can be tailored to your requirements – Get In Touch

Tailored Courses : We offer a free consultancy service to help tailor the course to meet your requirements.

Centre and Services

Comfortable & modern facilities
Unlimited access to our online training manuals
Complimentary lunch
Courses delivered by Certified Trainers
Microsoft & CPD courses available

Our Trainers

Steve, one of our lead Excel trainers, has over 25 years of delivering Excel from basics to development levels. All our trainers are encouraged to become Microsoft Accredited within their first year of working at Mouse Training.

Our instructors are very approachable, making the driest subject matter fun. 

At Course Completion of Excel 365 New Functions

  • New Filtering functions MAXIFS and MINIFS
  • New Decision Making functions IFS, SWITCH and LET
  • XLOOKUP and XMATCH functions
  • Working with Dynamic Array functions
  • New Shaping and Arranging functions TAKE, DROP and many others
  • SORT and SORTBY functions
  • FILTER and UNIQUE functions
  • SEQUENCE function
  • RANDARRAY function
  • Create your own Excel functions with LAMBDA

Course Outline

These two new functions complete the set of filtering functions that started with SUMIFS, COUNTIFS and AVERAGEIFS.

  • MAXIFS Returns the largest value in a list of numbers that satisfies multiple conditions
  • MINIFS Returns the smallest value in a list of numbers that satisfies multiple conditions

Tired of typing complicated, nested IF formulas? They are difficult to do but even harder to comprehend. Both IFS and SWITCH make the iterative process much easier and LET eliminates repetition. 

  • IFS tests values based on multiple conditions in a specific order. If passed, the result is returned. You can include an else “catch all” clause if none of the conditions are met
  • SWITCH is similar to IFS, but this function evaluates an expression against a list of values in order, and returns the first matching result. If no results match, the “else” is returned
  • When you enter the same expression multiple times in a formula, Excel calculates the result multiple times. LET allows you to store intermediate values as variables. The result is simpler and more efficient formulas

If you’ve ever suffered the delights of lookup formulas using Excel functions like VLOOKUP, HLOOKUP, MATCH and INDEX, then you’re going to love these new functions.

  • XMATCH versus MATCH
  • Making approximate, exact and partial matches
  • Returning 12 months of data with one XLOOKUP
  • A Two-Way lookup with XLOOKUP

Excel Dynamic Array functions fundamentally change the way we work with Excel formulas. We review how all formulas are affected by the new Excel calculation engine.

  • Spill ranges and handling SPILL errors
  • Using the hash sign (#), the new array reference notation
  • Using the @ sign, the implicit intersection operator
  • Combining functions
  • Referencing spilled data ranges in other Excel sheets

Complicated text formulas incorporating ampersands and the CONCATENATE function are the bane of our life. Not any more!

  • CONCAT This new function is like CONCATENATE, only better. It supports range references as well as cell references
  • TEXTJOIN And this one is even better because it also lets you control delimiters and empty cells. Works wonders when preparing data for CSV format
  • TEXTSPLIT to split text based on multiple delimiters. Grab the part of text before a delimiter with TEXTBEFORE and after a delimiter with TEXTAFTER
  • Use ARRAYTOTEXT and VALUETOTEXT to easily manipulate ranges and arrays

Tediously copying and pasting data is no fun. And now there’s no need, let these functions do it for you.

  • TOROW and TOCOL to combine data from multiple ranges into a single row or a single columns.
  • CHOOSECOLS and CHOOSEROWS to select which columns and rows from a range you want returned.
  • WRAPCOLS and WRAPROWS to take horizontal or vertical data and create multiple columns or multiple rows from it.
  • The TAKE and DROP functions let you pick which part of a table you want returned. You can keep the first top rows or the last rows. Combine this with the SORT function to get the top results of your dataset.
  • Use the EXPAND function to expand a range to a certain number of columns or rows.
  • Sorting with a single argument
  • A sort based on two or more columns
  • Horizontal sort
  • A random sort using SORT and RANDARRAY
  • Sort by something that is not in the results
  • Performing a multi-column sort
  • Using the FILTER function with one condition
  • Using FILTER with multiple conditions
  • FILTER for both criteria or either criteria
  • Excel FILTER to exclude blanks, zeros and text
  • Syntax of the UNIQUE Function
  • Understanding unique versus distinct
  • Nesting array functions: SORT, UNIQUE and FILTER
  • Generating a range of sequential numbers
  • Using SEQUENCE inside another function
  • Using TODAY and SEQUENCE for a calendar
  • Generating sequential letters with CHAR, SEQUENCE and TEXTJOIN
  • Returning the N Largest Items using LARGE and SEQUENCE (Top Ten etc.)
  • Generating an array of Random Numbers with RANDARRAY
  • Using RANDARRAY values for Modelling and Simulation
LAMBDA is a function that creates custom Excel functions without your having to do any VBA coding.  This is what you need if you find yourself doing repeated calculations but there isn’t an Excel function that fits your specific requirements.
  • Create a LAMBDA function and use it in your Excel formulas
  • Create a recursive LAMBDA which can loop through cells to do multiple replacements

Is this New Excel Functions Course for You?

Give us a call, our admin team will be delighted to assist in making sure you are on the correct course.

Related Courses

Excel 365 New Functions Training Course
Excel 365 New Functions Training Course
Excel 365 New Functions Training Course
Hints and Tips