When I create Excel files for my clients, one of my first questions is “Which Excel version do you use?”. The reason for this question is that I want to know which formulas and functions of Excel I can use and will work on my clients computer. For example: Can I use the more special IFNA formula or do I have to use IFERROR (which is much more broad) instead? Or can I use the XLOOKUP function?
Introduction
Let’s start with a recent example: I’ve created a simple import process for a client using the new Get & Transform tools. The client only had to press the refresh button and all data was updated although the input data was in a quite bad format. Unfortunately, he still used Excel 2013. So, he went to the IT department asking for the newest version of Excel. Guess what he was told? “Office 2013 is new enough. Other colleagues would also want version 2019”. (A side-note at this point: “Get & Transform” also works through the free Excel add-in “Power Query” in previous versions of Excel).
In this article, we first take a look at some statistics about the development of Excel formulas. Further down you can look up new functions per version and check which version of Excel you can use a specific function in.
Excel has 481 built-in functions (September 2021)
As of now, Excel has 481 formulas built-in in Microsoft 365. With 111 functions, statistical formulas account for more than 23% of them.
I would even go so far to say that the first four categories “Statistical”, “Math and trigonometry”, “Financial” and “Engineering” are (besides the exceptions like AVERAGEIFS, COUNTIFS and MAXIFS/MINIFS) not really relevant for the average Excel user. But, these four categories include in total 300 of the 481 functions; this is more than 60%. On the other hand, Excel only has 25 lookup functions (like VLOOKUP, INDEX, MATCH, INDIRECT) built-in.
My conclusion here: Excel is full of functions, but most of them are irrelevant for the average user.
Every new version of Excel has on average 26 new functions
As mentioned in the introduction: Microsoft keeps improving Excel. Between Excel 2007 and the current Microsoft 365 version (as of September 2021) the total number of functions has increased from 352 to 481. That means, on average there are 26 new functions coming with each version of Excel.
As a side-note, if someone from the Microsoft Excel team is reading this: There is still a function for a multi-conditional lookup for text values missing, e.g. VLOOKUPS… 🙂
In which area does Microsoft invest in terms of functions in Excel?
We have seen above that Excel is always getting new functions, which is great for us users. We have also seen that Excel has increased the total number by 55 in version 2010 and 51 in 2013. After that, the number has only increase by 5, 7 and 11 (so far) in versions 2016, 2019 and 365 (respectively). Does that mean, the versions 2010 and 2013 had much greater value increase?
To judge this, let’s take a look at the functions added by area and year:
In version 2010, Excel introduced 50 new statistical, in 2013 mainly Math and trigonometry as well as engineering functions.
On the other hand, in Office 365 there are six new lookup and reference functions, including XLOOKUP, XMATCH, FILTER, SORT, LET, etc.
My personal opinion here: As long as you are not a statistical, engineering or math professional, I’d say the new lookup functions have much greater impact on the average users life. I like where this development is going, solving many daily problems.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
What are the new functions per Excel version?
We have seen before that Microsoft is adding new functions to Excel with each version. But what are these function in each version? Here is the list: Just select your version and see the new names below.
Which Excel version can I use this function in? Is my version compatible?
That’s probably the main question, when you create and Excel model: Can I use this specific function? Is my Excel version compatible? Here is the lookup form, just select your desired function and see in which Excel versions you can use it in!
Please refer to this article to find out which Excel version you use.
Source: Microsoft, https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
Image by athree23 from Pixabay
This was an interesting page but unfortunately this error message appears:
“This item cannot be loaded at this time.” (translated from Swedish) after the texts “Just select your version and see the new names below.” and “Here is the lookup form, just select your desired function and see in which Excel versions you can use it in!”.