Microsoft Excel, Office 2021 / Office LTSC /

New Features in Excel 2021

22 September 2021

In addition to the general Office 2021/LTSC features there are some useful additions to Excel 2021/LTSC including the dynamic array functions, improved XLookup()/XMatch() and Let() but sadly no Lambda().

All these features are in Excel 2021 and Excel LTSC for both Windows and Mac in addition to the overall updates that apply to all the Office 2021/LTSC apps, see New and better features in Office 2021 and Office LTSC

See all about Office 2021 for Windows & Mac plus The good and bad in Office LTSC

Dynamic Arrays

An array of values can be returned by using a single formula. Excel 2021/LTSC have the core dynamic array formulas: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, and RANDARRAY.

Excel now has Dynamic Arrays – Windows, Mac and more …

FILTER() filter a table, range or array to show only some items from the source.

SORT() to sort a table, range or array.

SORTBY() sorts a table, range or array based on a one part of the source. For example, sort a list of class test results by student scores.

UNIQUE() a list of values without any duplicates.

SEQUENCE() makes series of numbers (e.g. 1, 2, 3) across multiple columns or rows. Seemingly irrelevant, Sequence() can be combined with Index() to make ‘Top 5’ or ‘Bottom 10’ filtered lists.

RANDARRAY() makes random numbers across many columns or rows.

XLookup

By using the XLOOKUP function, you can find items in a table or range by row. You can, for instance, look up the price of a part by its part number, or find a person’s name by their employee ID. It allows you to look up a search term in one column and then return a result in another column derived from the same row.

The XLOOKUP function can return multiple items, for example, it can return the name of the employee and department from cell C5:D14 with a single formula. It’s much easier and better than the old VLOOKUP/HLOOKUP options.

Xlookup is coming and it’s truly a great thing

Xmatch

The XMATCH function locates an item in an array or range of cells, then returns its position relative to the item. Additionally, you can use XMATCH to return a value in an array. It’s similar to XLookup() which returns the cell value while XMatch returns a relative position in the array.

For example, if you want to find the position of an item on a list, in the below example the formula is: =XMATCH(E3,C3:C7)

Let() function

The LET function names the results of calculations. Using this method, you can store intermediate calculations, values, or name definitions within a formula.

Excel’s LET function requires you to specify pairs of names, their corresponding values, and a calculated formula using them all. LET supports up to 126 name/value pairs (variables), and at least one must be defined.

Let() assigns names to calculations in Excel

Alas, no Lambda()

Sadly, the really useful Lambda() function has NOT made it to Excel 2021/LTSC.

Improved Performance

Microsoft says that Excel 2021/LTSC has “faster calculations from common Excel functions like SUMIF, COUNTIF, and AVERAGEIF” which sounds like the improvements made in Excel 365 version 2005 to include an internal cached index of the target range.

That’s in addition to the general performance improvements promised for all Office 2021/LTSC apps.