APRIL 8, 2022
Microsoft Excel makes it easy to create budgets, track inventory levels, and analyze sales. This software can handle huge amounts of information, making it an extremely useful tool for storing and analyzing data. No wonder Excel has become a staple in offices worldwide over the years. Its latest version, Excel 2021 for Windows, is packed with even more powerful features and functions that you can enjoy.
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.
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:
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.
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:
=LET(x,1,x+2) → x+2 → 1+2 → 3
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:
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 at Lanlogic today.