Excel 365 – 2021, (R)evolution !

Microsoft Excel is - within the Office suite - a product used by a large number of people, with a wide variety of jobs and needs. It is constantly evolving to offer new features or to facilitate frequent operations.

Since the advent of the "Microsoft 365" suite (formerly "Office 365"), and its subscription model, updates are more frequent and it is sometimes difficult to keep up with these new features. If you do not have a subscription to Microsoft 365, you will find all these new features in the 2021 version of the Office suite, which will be released on 4 October 2021. So here we will review the major features added since Excel 2019. If some of them do not fundamentally change the way you use the software, others are real little revolutions!

Dynamic table formulas (« Spill »)

The most important new feature is the "Spill" ("Tableaux Dynamiques" in French - not to be confused with "Tableaux Croisés Dynamiques"). Behind this name lies a small revolution in Excel!

Until now there were only two types of formulas:

The "classic" formulas, returning only one value (this is the case of the vast majority: SUM, AVERAGE, XLOOKUP, etc...) that you validate simply by pressing the "Enter" key.

Matrix" formulas - more difficult to create, and rarer - validated by Ctrl+Shift+Enter: these formulas were necessary as soon as you wanted to return several values from a single formula, but this obliged you to select in advance the whole range that was to contain the result... We then had a dynamic result, but in a range of cells fixed in advance... 

The "Spill" (Dynamic Table) feature aims to eliminate all the shortcomings associated with the old "matrix" formulas: 

Entering the formula is done as usual, and is validated by "Enter".

The size of the result range is dynamically managed by Excel, if the source data changes and the result has to occupy more or less cells than before, Excel automatically expands the result range of our formula!

New functions have been created to take advantage of this new feature:

• UNIQUE : function allowing to produce the list of unique values of a range (thus, eliminating duplicates).
• FILTER : allows you to dynamically filter a list of values, according to criteria.
• SORT, SORTBY : allows you to dynamically sort a range of values.
• SEQUENCE : allows you to generate a list of values, according to a start and end value.
• RANDARRAY : allows you to generate an array of random values.

Improved search functions (XLOOKUP, XMATCH)

According to Microsoft, the VLOOKUP function is the 3rd most used function in Excel, after Sum and Average, but suffers from several shortcomings: it is slow on large tables, requires a "column number" from which the information must be extracted (rather than a range address, which is much more intuitive), can only return data located to the right of the search range, etc. XLOOKUP corrects all this: For example, here we see that it uses as 2nd argument the address of the range in which the search is performed (B2:B11) and as 3rd argument the address of the range in which the information we want to retrieve is located (D2:D11):
=XLOOKUP(F2;B2:B11;D2:D11)

To get the same thing with VLOOKUP - the old function - you would have to do : 
=VLOOKUP(F2;B2:D11;3;FALSE)
the 2nd argument should have encompassed the whole table, the 3rd argument indicated a relative column number, and the 4th argument indicated that you wanted an exact search (which XLOOKUP does now by default)

But that's not all: XLOOKUP also has additional arguments to specify a default value to return if no match was found, offers more flexibility in the key search method, such as a wildcard search ("*" "?"), and allows you to specify whether the search should be top-down or bottom-up!

The XMATCH function replaces the old MATCH function in the same way.

And much more!

The list of new features doesn't stop there, we could have mentioned the "LET" function, improved drawing possibilities, improved management of cell comments, a more modern look (and consistent with Windows 11!), the creation of customised views of a sheet, improved accessibility, increased performance for existing functions, and many other little things...

Join us in the training courses offered by Key Job and you will learn more...