Microsoft Excel is a powerhouse in data management and analysis. With each new release, it becomes even more powerful. The latest version, Excel 2021 for Windows, includes many new features and functions that will make your life easier. Here’s how to make the most of them.
Features exclusive to Excel 2021 for Microsoft 365
The following new features are available for Excel for Microsoft 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 function
Similar to the VLOOKUP, HLOOKUP, and INDEX+MATCH functions, XLOOKUP searches for an item in a range or table and returns the matching result. However, XLOOKUP 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 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.
LET function
The LET 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
For example:
=LET(x,1,x+2) → x+2 → 1+2 → 3
6 New dynamic array functions
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 wherein any 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.
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.
