Excel: Functions You Need to Use

Next Generation Excel: New Functions You Need to Use

Written by Neil Malek, President of Knack Training

Excel Has New Functions?

Starting in 2018, Microsoft added a new function engine to Excel, and began inventing a new family of functions. These functions include some incredibly dynamic and useful tools that can change the way you work. At the heart of this is a new feature called dynamic array functions.

What's a Dynamic Array Function?

For decades, every function we used filled a single cell of the worksheet. To have that function return multiple values, you'd have to copy the function down manually. A dynamic array function works on an entire table of data at once, and can spill the results into more than one cell when it returns the results. Let's look at a simple example:

UNIQUE

=UNIQUE ( < array >, < by_col >, < exactly_once > )

Let's say you have a table of invoices, including a column for Vendor. You want to create a unique list of the vendors who still have outstanding invoices. The new function UNIQUE does exactly that - outputs a unique list of values from the table. This could be 1, 5, 20, or 2,000 vendors, but it doesn't matter - Excel spills the results down the page.

A quick note about these spilled ranges - if you already have something in a cell that's being spilled across, it will generate a #SPILL error. Just make sure the cells are clear before using one of these functions.

XLOOKUP

=XLOOKUP ( < lookup_value >, < lookup_array >, < return_array >, < if_not_found >, < match_mode >, < search_mode > )

Without a doubt, the most famous new function is XLOOKUP, a direct replacement for VLOOKUP. Why should you retire VLOOKUP and start using XLOOKUP? There are so many reasons - here are just a few:

1.      In the old days, we couldn't use VLOOKUP to look for an ID number on the right side of the table, and return a value from the left side of the table. XLOOKUP handles that easily - no more need for INDEX and MATCH!

Notice that in an XLOOKUP, the Lookup Array and Return Array are two separate elements.

2.      XLOOKUP has options for 'if_not_found' - the ability to return a useful error message if there's no match, and the ability to use wildcard characters and even regular expressions to find a match.

3.      XLOOKUP can spill multiple columns of results from one search - you can write one XLOOKUP to replace multiple VLOOKUPS.

The XLOOKUP is only in the Client Name field here – Account Manager, Contact, and Phone are all being spilled as additional information.

TOCOL, TOROW, VSTACK, and HSTACK

I've put together a few 'reshaping' functions here - they can be very useful in specific situations:

=TOCOL ( < array >, < ignore >, < scan_by_column > )

Put in a bunch of cells, and TOCOL will put them all in a single column.

=TOROW ( < array >, < ignore >, < scan_by_column > )

Put in a bunch of cells, and TOROW will put them all in a single row.

=VSTACK ( < array 1 >, < array 2 >, … )

Put in a bunch of data tables from different parts of your file, and VSTACK will vertically stack them into a single table.

=HSTACK ( < array 1 >, < array 2 >, … )

Put in a bunch of data, and HSTACK will horizontally stack them side-by-side.

LAMBDA, the Most Intriguing of Them All

Have you ever wished there was a premade function to solve a big, complex problem? In any spreadsheet, you can invent a function that can be used on anything in that file with LAMBDA. Let's look at a simple example:

Let's say you need a function for calculating the Beginning of the Month. There's already an EOMONTH function, but no BOMONTH. Start by creating the logic for BOMONTH:

=EOMONTH( start_date, months-1 ) + 1

In other words, use the EOMONTH function with any date you want, but go to one month earlier than the one specified, then add one day to go to the beginning of the appropriate month.

To create a custom function, you write a LAMBDA function like this:

=LAMBDA( < input argument 1 >, … < function > )

So, we would write this as:

=LAMBDA(StartDate, Months, EOMONTH(StartDate, Months-1)+1)

Now that we've figured out how to write the logic, we click on Formulas > Name Manager, and create a new name:

Name it BOMONTH, and input the function as we wrote it above into the Refers to field:

Now, after you save it, you can type =BOMONTH( anywhere in your workbook, and complete it to calculate the beginning of the specified month:

Conclusion

There are over 50 new functions introduced since 2018, including functions for sorting and filtering, breaking text apart, and even using regular expressions to test your data. There hasn't been a time in the last 30 years when Microsoft has made this much progress - you owe it to yourself to learn about them!

Other Sources from Knack Training

https://www.youtube.com/watch?v=unq7WM5hac0

https://www.knacktraining.com/videos/free-webinar-modern-excel-functions/