Excel 365 New Functions Training Course
Table of Contents
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 recently. Most of us have relied on familiar functions like VLOOKUP and nested IFs for years and that has now all had a major overhaul.
Don’t worry, all the original functions are still supported but new functions like IFS, LET and XLOOKUP will completely change our formula game. Making them more powerful and efficient.
The new calculation engine introduces a new type of function, 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 Five Key Topics
- Manipulate text data with powerful text functions
- Develop simpler and more efficient decision making formulas
- Replace VLOOKUP with XLOOKUP
- Work with Dynamic Arrays
- Extract filtered and sorted datasets with dynamic formulas
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 Text functions CONCAT, TEXTJOIN, ARRAYTOTEXT and VALUETOTEXT
- New Filtering functions MAXIFS and MINIFS
- New Decision Making functions IFS, SWITCH and LET
- Working with Dynamic Array functions
- XLOOKUP and XMATCH functions
- SORT and SORTBY functions
- FILTER and UNIQUE functions
- SEQUENCE function
- RANDARRAY function
Course Outline
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.
- Use ARRAYTOTEXT and VALUETOTEXT to easily manipulate ranges and arrays.
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 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.
- LET 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.
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
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.
- XLOOKUP versus VLOOKUP
- XMATCH versus MATCH
- Making approximate, exact and partial matches
- Returning 12 months of data with one XLOOKUP
- A Two-Way lookup with XLOOKUP
- 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
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
