Excel 365 New Functions

Half DayHalf DayVersion 365Official CPE Provider

Rewrite your formulas the modern way. Replace clunky lookups with XLOOKUP, spill results with dynamic-array functions like FILTER and SORT, and simplify complex formulas with LET - all in Excel for Microsoft 365.

Course Objective

Excel 365 has many exciting new functions which are not included in other versions of Office (2019 and earlier). These either have been a part of Excel 365 for years, or have been added through monthly updates to the software. In this class, students will learn how to use these new Logical, Lookup, and Dynamic Array functions in formulas.

Prerequisites

Students should be familiar with how to create formulas using intermediate functions in Excel like SUMIFS, COUNTIFS, AVERAGEIFS and have been exposed to VLOOKUP.

Course Outline

5 lessons · 13 topics

1Lesson 1: Improved Lookup Functions

The superior lookup function, XLOOKUP
Return a position or count values with XMATCH

2Lesson 2: Using Logical Functions

Summarize data using MINIFS, MAXIFS
Test multiple conditions with IFS

3Lesson 3: Data Modification using Formulas

Joining data strings using CONCAT and TEXTJOIN
Replace values with SWITCH

4Lesson 4: Dynamic Array Functions

FILTER returns a filtered array
Create a sorted array with either SORT or SORTBY
UNIQUE builds an array of all distinct values
Use SEQUENCE to create consecutive data
Build an array of random values with RANDARRAY

5Lesson 5: Use Variables in a Formula

LET function
Use FILTER and SEQUENCE in LET

Excel Solutions Team
Kirkwood Tower, 11757 Katy Fwy, Suite 1300, Houston, TX 77079
(713) 589-2655 · contactinfo@excelsolutionsteam.com · excelsolutionsteam.com

Official CPE Provider (TX State Board of Public Accountancy)
BBB Accredited since 2006 · 20+ years training Houston professionals