New Features in Excel 2021

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

Source: Microsoft

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)

Source: Microsoft

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.

Source: Microsoft

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.

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


New Features in PowerPoint 2021
New Features in Outlook 2021

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address