New Excel Functions – business revolution or deckchair shuffle?

From the original release of Excel 2016, right back to the start of the millennium, there's probably only one new Excel function that really stands out and that is the introduction of SUMIFS() with its ability to replace the single criterion available to SUMIF() with up to 127 pairs of criteria and criteria ranges. In the past few years, things have changed dramatically with at least 4 really significant individual functions, or groups of functions, offering the potential to improve how existing solutions can be devised and also allowing Excel to conquer many additional types of problem.

In some ways the introduction of dynamic arrays and dynamic array functions is the most revolutionary of all the changes. Whatever any Excel function was able to do prior to the introduction of dynamic arrays, it happened in a single cell. Dynamic arrays allowed an Excel formula to fill a range of cells with results and to adjust that range as the source range changed. This is such an important development that some see it as a new Excel era, referring to Excel pre and post dynamic arrays. Our extensive knowledge base includes many posts explaining dynamic arrays, the new dynamic array functions and some practical ways in which they can be used:

• Modelling 101: Part 58 - Creating a Calendar with Dynamic Arrays
• Modelling 101: Part 53 - Dynamic Arrays One Year On
• Excel Tip of the Week #345 - Spilling errors with dynamic arrays
• Excel - dealing with duplicates 2, the dynamic array revolution
• Dynamic arrays - what do we think?
• Dynamic Arrays - limitations of #
• Tech News (open)
• Excel Tip of the Week #327 - Introduction to dynamic array functions
• Dynamic arrays webinar - Your questions answered

Excel's lookup functions are amongst the most controversial of all its functions. Many a business failure, or ruined professional reputation, can be traced back directly to a missing fourth argument in a VLOOKUP() formula. Accordingly, the arrival of a single function to make LOOKUP(), VLOOKUP(), HLOOKUP() and the INDEX() MATCH() combination all obsolete was an important development. Not only does XLOOKUP() cure its predecessors' exact/approximate match pitfall in two ways (exact is now the default and approximate matches don't need the lookup array to be sorted) but XLOOKUP() also copes with all the situations where INDEX() and MATCH() would previously have been required, makes it easier to deal with no match being present, allows for wildcard matches and also has significant performance advantages:

• Modern Excel - Beyond XLOOKUP. Fuzzy matching, merge & relationships
• XLOOKUP Provides Moving Arguments
• Excel Tip of the Week #337 - XLOOKUP

LET() is the first of a pair of functions that move Excel formulae closer to the world of programming. The function allows you to create a named variable that you can then refer to elsewhere in the same function. Possible uses for this would be to use these named variables to make it clearer how a formula works and to make a formula simpler and more efficient where it needs to perform the same calculation multiple times. This is an example of the use of text functions to reorder the characters in a date. It relies upon the use of the SEARCH() function to find the position of each / character in a text string:

=VALUE(MID(A2,SEARCH("/",A2)+1,SEARCH("/",A2,SEARCH("/",A2)+1)-SEARCH("/",A2))&LEFT(A2,SEARCH("/",A2))&RIGHT(A2,LEN(A2)-SEARCH("/",A2,SEARCH("/",A2)+1)))
Not only do you have to enter the same SEARCH() function 5 times, but it has to be calculated 5 times. The LET() function alternative avoids both these issues by calculating the result of a single SEARCH() function and allocating that result to the name StartM. A second variable EndM is also created to replace the calculations in the original formula that find the position of the second / character within our text:
=VALUE(LET(StartM,SEARCH("/",A16)+1,EndM,SEARCH("/",A16,StartM),MID(A16,StartM,EndM-StartM+1)&LEFT(A16,StartM-1)&RIGHT(A16,LEN(A16)-EndM)))
New Excel function: LET

Not only do you have to enter the same SEARCH() function 5 times, but it has to be calculated 5 times. The LET() function alternative avoids both these issues by calculating the result of a single SEARCH() function and allocating that result to the name StartM. A second variable EndM is also created to replace the calculations in the original formula that find the position of the second / character within our text:

=VALUE(LET(StartM,SEARCH("/",A16)+1,EndM,SEARCH("/",A16,StartM),MID(A16,StartM,EndM-StartM+1)&LEFT(A16,StartM-1)&RIGHT(A16,LEN(A16)-EndM)))

• New Excel function: LET

This takes the idea of encapsulating a calculation for reuse several steps further. LAMBDA() allows a custom function with parameters to be written as an in-cell function and entered as the 'Refers to:' of a named range. The name can then be used elsewhere in the Excel workbook whenever that calculation is required to be used. One of the breakthrough aspects of LAMBDA() is that it is recursive, meaning that the function can call itself as part of the calculation. The use of LAMBDA() also means that if you need to change the way a calculation works, you can do so by changing a single calculation rather than every individual cell that uses it.

• Creating Custom Functions Using LAMBDA

These new functions are undeniably significant and for some Excel users will greatly improve how they use Excel and enable them to extend the way in which they are able to use it. However, none of them are in any of the 'one-time purchase' editions of Excel – Excel 2016 and 2019. So, even when those functions that haven't yet done so filter through to your Office 365 edition of Excel, you will need to make sure that anyone with whom you might want to share your workbook is also using Office 365, and is on at least as frequent an update path as you. The likelihood is that this will delay the general implementation of all of these functions.

Secondly, I have my doubts as to how useful some of these functions will be to most Excel users. My hunch is that less than one in a thousand Excel users will use LAMBDA(). This is not to deny its significance or to suggest that it is not important to know about the function. If you are one of the 0.1% then it could be vital to know it exists and understand what it can do. However, there is so much to know about Excel that you might need to prioritise the areas that you concentrate on.

You might be beginning to suspect that I'm about to launch into the usual lecture about Power Query as the solution to all Excel issues.

And, of course, you'd be right.

Hopefully, you will have the time to gain an adequate knowledge of what these functions are able to do, as well as to develop a sufficient understanding of Power Query. However, if you have to choose your priorities then, for me, there is no question that Power Query is more important, almost irrespective of the particular way in which you might use Excel. The point is worth making because there is a tendency to think that anything that you enter into a cell is a legitimate part of Excel, whereas Power Query is in some way a separate thing that is only of relevance to people who use Excel for heavy-duty data analysis and is a whole level of complexity above 'normal' (legacy?) Excel. Such a view is completely wrong. Power Query can help with a wide range of 'day-to-day', practical Excel issues. It can also be far easier to use than writing a simple Excel formula.

So, as well as plunging into the details of the new and imminent Excel functions, spare some time to make sure you really can afford to ignore Power Query:

• Is it time to stop using legacy Excel?
• How Power Query increases Excel's Power exponentially
• Microsoft Power Query for Finance Professionals - An Introduction
• Tech News (open)
• An exciting time for Excel users - the added dimensions of deferred income
• Should all auditors understand Power Query?

This is very much a personal view and I'm sure that many people will disagree profoundly with my conclusions. If you would like to put forward an alternative view, or even to agree with me, please send us an email with your comments and we'll consider a follow-up article setting out the responses.