Excel Formulas, touching the tip of the iceberg

Microsoft Excel is a multifaceted software: it actually can be used in various situations and more often than you can think you can get to the same result using different paths. Before IFS was introduced, many spreadsheets used to be filled with too many embedded IF statements: IF (cell A1) is less than 20, then multiply by 1, but IF it is greater than or equal to 20 but less than 50, then multiply by 2, but IF... well you should get the point, more or less like some philosophical questions you can find on the internet your spreadsheet looked unnecessarily complicated:

If I am what I have and if I lose what I have, who then am I? - Erich Fromm -

I use Excel on a daily basis and more than once it happened that, while googling the syntax of a formula, I discovered that a task that I have performed a million times, can actually be carried out much quicker, just by using Excel's functions differently or using a newly introduced formula. Since the whole point of using Excel is being efficient and saving time while getting a very accurate and reliable outcome, I started wondering how much of it I'm actually exploiting (by the way, I got completely blown away by all the things you can do with SUMPRODUCT, I wrongly thought it was just made to multiply two sets of numbers, but there is definitely more).

Basically, Excel users can come from different backgrounds: I know many finance and accounting users, in my line of business there are some engineers using it, but also marketing, HR, IT professionals, scientists, anyone, even someone just controlling her personal home budget or preparing her tax return. That is why in the Formulas menu there are many categories: Financial, Logical, Text, Date & Time, etc..

As a matter of fact, according to Office support official website, there are about 470 Formulas in Excel, that can even be combined all together, like INDEX MATCH for instance, to create even more value. Moreover, because of the Data Analysis Expressions (DAX) language in PowerBI, there are even more to add to the list. Naturally, about a hundred of the DAX functions are used by Excel itself (SUM for instance), but about 150 are brand new and unique to elaborate a nice calculated measure (CALCULATE would be a good example of DAX formula).

In a nutshell, 470 Formulas from Excel plus another 150 in DAX makes a total of 720 formulas curious Excel users could use. Honestly, I think that even at Microsoft there's no one who really knows all of them, but I'd be happy to be proven wrong.

Having said that, I decided to test how many functions I actually use on a regular basis and to boost my self-confidence I decided to audit my January professional worksheets, a month during which I create and modify a lot of files, as already mentioned.

The ground rules were pretty simple: counting how many Formulas I have used, meaning one formula counts one even if it is used a thousand times (I'm talking about SUM of course, would you think of some more used formula in your files?), but in situations like INDEX MATCH I took the liberty to count them as two (they can be used separately after all).

You're wondering about the outcome. Well, I was hoping to be as triumphant as the Minions were in this old advert while getting the (wrong) answer from their super mega computer. Instead, I was more like those whose connection failed and the banana download got lost.

I'm literally scratching the tip of the iceberg

Simply put, I'm literally scratching the tip of the iceberg. I was expecting a thunderous 100 Formulas, almost 15% of the total. Actually, my final score was 34 Excel formulas, meaning 5.5% of the total number of potential functions available on this software. Hubris? Dangerous over-confidence? Maybe just timing after a quick analysis of this output. At the beginning I have wondered whether I was too honest: for instance, I know XIRR formula and I have used it more than once in the past, but I haven't used it in January. On the other hand, I found out I used ROUNDUP this month: it's actually a formula I wouldn't consider on a regular basis, so this was not the real driver of my score. I reckon during year-end, my main goal is preparing all the reporting, therefore there is a lot of filtering and data manipulation: this results in Tables with a lot of Text formulas (CONCATENATE, LEFT, TEXT, etc) and Logical formulas (AND, OR, etc..) after the classic Mathematical ones (SUM, SUMIFS, SUBTOTAL, etc..).

To summarize, I believe everyone who uses Excel for more than 10 hours a week is considered an advanced user. Nonetheless, this doesn't mean an advanced user knows it all. This was a real eye opener to me, I ate my humble pie and the next time I'll play this game I'm sure I'll get to that 100 formulas on my workings.

How about you? How many functions do you use on a regular basis? More or less than me? Any comments? Feel free to challenge me and to add your thoughts here below!

Disclaimer: I'm not affiliated with Microsoft in any way. Opinions are my own and not the views of anyone else. No spreadsheet has been harmed in the writing of this article.