Microsoft Excel is an excellent tool for data management, analysis, and organization. Each update released is better than the previous version, making it a more powerful tool each time. The latest version, Excel 2021 for Windows, has many new additions and features that will make your job easier. Keep reading to learn how to take advantage of these new features:
Features exclusive to Excel 2021 for Microsoft 365
Below is a list of the new features available with the new update. However, these features are not available for its long-term servicing channel (LTSC), which is formerly knows as the “perpetual license” version.
- Co-authoring: Lets multiple users make changes to a workbook at the same time and allows them to see the changes made in real time
- Threaded Comments: allows the users on a workbook to communicate more efficiently through the ability to reply to comments inline
- Visibility of active co-authors: This feature allows the users of a workbook to see who else is working on it and where they’re working on it at a give time
XLOOKUP function
The XLOOKUP function is pretty similar to VLOOKUP, HLOOKUP, and INDEX+MATCH functions. However, XLOOKUP searches for an item in a range or table and returns the matching result. What differentiates XLOOKUIP form the other functions above is the parameters required to perform the search. The other functions require more information to make an accurate search while this new function only requires 3 parameters:
- The value you’re looking for
- The list that should contain the value
- The list where the result should come from
A key difference between the VLOOKUP and HLOOKUP functions compared to XLOOKUP is that XLOOKUP searches for the result anywhere in the data to find the result while the other two functions are contained to specific columns or rows to search for the data. Also, XLOOKUP finds the exact match by default so you no longer have to worry about entering “True” or “False” into the formula.
For more information on how to properly utilize the XLOOKUP function, check out what Microsoft has to say about it here.
LET function
The LET function allows you to assign names to different formulas that are used in calculation results. For example, if you had a complicated formula used for calculating monthly gross income, you can rename the formula to “monthly gross income” making it easier to read. With the LET function, you have up to 126 name/value pairs. It is important to remember that the names only work within the scope of the LET function.
Here are the minimum parameters need to use the LET function:
- Name
- Associated Value
- Calculation that used the name and associated value/pairs
Here is an example:
=LET(x,1,x+2) → x+2 → 1+2 → 3
6 New dynamic array functions
Excel used to only let you enter one, single formula per cell. However, with the latest update, you can now insert multiple formulas through the use of dynamic arrays in which any formula that returns an array of values automatically spills into neighboring cells.
To use the dynamic arrays, Excel has created 6 new functions:
- FILTER: filters the database based off predefined criteria
- SORT: sort a cell range by a specified column
- SORTBY:
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, click here to chat with our technical support team.
