Features exclusive to Excel 2021 for Microsoft 365
The following Excel 2021 new features are available for Microsoft Office 365, but not its long-term servicing channel (LTSC) version, which was formerly known as the “perpetual license” version.
- Co-authoring – lets you work simultaneously with other users on the same Excel workbook and see each other’s inputs in a matter of seconds
- Threaded comments – allows you to have better discussions with other users in an Excel workbook, thanks to the ability to reply to comments inline
- Visibility of active co-authors – lets you see the other users who are also working with you and where they are in the workbook
XLOOKUP formula
Similar to the VLOOKUP, HLOOKUP, and INDEX+MATCH functions, in Excel 2021, the XLOOKUP function searches for an item in a range or table and returns the matching result. However, XLOOKUP, now available with Excel 2021, is more powerful than other lookup functions since it requires only three basic parameters to perform the search:
- The value you are looking for
- The list that should contain the value
- The list where the result should come from
XLOOKUP function in Microsoft Excel 2021 can search anywhere in the data and find the result, not just on a specified column or row like with the VLOOKUP and HLOOKUP functions. Moreover, XLOOKUP finds the exact match by default, so you don’t have to indicate “True” or “False” in the formula.
XLOOKUP formula Syntax
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Note: the XLOOKUP formula is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.
LET function
With Microsoft Office 2021, you now have access to the LET function. This new Excel 2021 function allows you to assign names to calculation results. For example, you can apply names, such as “monthlySales” and “totalSales” to an associated value, which makes formulas easier to read. The LET function supports up to 126 name/value pairs. Take note that the names work only within the scope of the LET function.
To use the LET function, you need to provide at least three parameters:
- Name
- Associated value
- Calculation that uses the name and associated value pair/s
LET function Syntax
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3…])
6 New Excel Array Formulas
For the longest time, Excel allowed users to use just one formula per cell. However, this has changed with the recent introduction of dynamic arrays in Microsoft Excel 2021, wherein any excel array formula that returns an array of values automatically spills into neighboring cells.
To leverage dynamic arrays, Excel 2021 for Windows introduced six new functions:
- FILTER – filters data based on the predefined criteria
- SORT – sorts a cell range by a specified column
- SORTBY – sorts a cell range by another range or array
- UNIQUE – takes out unique values from a range of cells
- SEQUENCE – creates a list of sequential numbers
- RANDARRAY – creates an array of random numbers
XMATCH function
The more powerful successor to the MATCH function, XMATCH looks for a specified item in an array or range of cells and then returns the item’s relative position in vertical or horizontal ranges. It can also return a value in an array.
The XMATCH function assumes that you want an exact match, so it requires only two parameters (i.e., lookup value and lookup array) to work. However, you can add an optional third parameter to change the default matching type to “next smaller,” “next larger,” or “wildcard match.”
XMATCH has another optional parameter that allows you to indicate whether to search from the top or bottom of the lookup list, or whether to conduct a binary search on ascending or descending values.
XMATCH function Syntax
The XMATCH function returns the relative position of an item in an array or range of cells.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
These are just some of the great new features you can leverage in Excel 2021 for Windows. For more Microsoft Office productivity tips, consult our IT experts today.