My second article in Accountants Weekly was published 20 years ago today and it was Top 10 Functions for Accountants.

Wow, 20 years ago I hadn’t heard of SUMPRODUCT or INDEX. These have been my two favourite functions for a long time now. In the older versions SUMPRODUCT is a great way to see how dynamic arrays work in the subscription version. My top 10 functions are in the image below.

If I did this now it would be different. It would also be more complex because of the fact that the subscription version of Excel has lots of new functions that won’t be coming to older versions.

So here is my top 10 list as at 24 August 2021 for Excel 2019 (the last “full” version of Excel).

My Top 10 list for Microsoft 365 (formerly Office 365) are new or other great functions.

Excel 2019 – Top 10 Function for Accountants

1. SUM – this the workhorse function of spreadsheets. It is by far the most used function in Excel for everyone.
2. SUMIFS – the updated version of SUMIF – this allows multi-criteria summing.
3. SUMPRODUCT this is the most flexible function in Excel – it allows multi-criteria summing and counting and it works on closed workbooks. It also allows the use of other functions in the creation of the criteria. This function is as close to dynamic arrays as it gets in the previous versions.
4. SUBTOTAL – if you are subtotalling this is the function to use. It has the super power of being able to sum only the visible cells using 109 instead of 9. When you use SUBTOTAL on a filtered list it only adds up the visible cells by default.
5. IF – this function handles the logic side of your formulas. Allowing you to build decision making into your spreadsheets and do different calculations based on the criteria you set up.
6. IFERROR – this function handles all of Excel’s errors and allows you to display values, text or calculate another formula if an error is encountered.
7. INDEX – This is my second favourite function. It also wasn’t on my original list. It can be used for a lot of different calculations. When used with the MATCH function INDEX-MATCH replaces VLOOKUP and HLOOKUP. It is great for month and YTD calculations in budget structures.
8. MATCH – this function returns a number representing where a value is within a range. It works seamlessly with INDEX and other functions like CHOOSE and VLOOKUP.
9. CHOOSE – This can simplify multiple IF functions that are based on values – also works well with the MATCH function. In both Excel versions it allows you to manipulate tables and ranges. this function has a new lease on life in the subscription version.
10. TEXT and TEXTJOIN– OK I cheated and combined these two different functions. TEXT allows you to convert dates into text and is great for use in headings. TEXTJOIN is a new function for Excel 2019 and it makes joining text ranges easier and more flexible.

Microsoft 365 – Top 10 New or Other Great Functions – starting with existing functions.

1. TRANSPOSE – this function has come of age with dynamic arrays. In the old version it was hard to use, now it is easy. It allows you to easily switch a rows-based range/table into a column-based table.
2. INDIRECT – this is the magic function in Excel. I moved it from the first list as it has limited applications. It allows you to create formulas that are impossible without it. It lets you create references for cell or ranges using text in other cells. This makes solution more flexible. As an example, a single formula copied down, could extract cell values from different sheets.
3. COUNTIFS – this function counts using multiple criteria. It can be used to identify duplicates and identify the first and last entries in a list that meets certain criteria. It can also validate in-cell drop down entries.
4. EDATE – this makes working with dates so much easier. Allows you add months to dates. Great for column headings in reports, budgets and forecasts.
5. XLOOKUP – new function – this function was released in 2020 with dynamic arrays. It replaces VLOOKUP and HLOOKUP, and to some extent the INDEX-MATCH combination. It is the lookup function of choice.
6. UNIQUE – new dynamic array function – this function returns a list of unique entries from a range. It basically removes duplicates to leave unique entries. It has an option to list only those items that occur once as well.
7. FILTER – new dynamic array function – a function that allows you to create a separate filtered list based on criteria in input cells. Change the input cells and the list automatically updates.
8. SORT + SORTBY – new dynamic array functions – SORT allows you to sort based on a column in a table. SORTBY enables sorting by a column in another table. You can also sort horizontally.
9. SEQUENCE – new dynamic array function – this function appears basic, but it can drive a budget or a forecast model because it creates a sequential list of numbers. That list can be used by dynamic arrays formulas to perform calculations. It is also great for flexible loan schedules.
10. LET – new function – the LET function allows to create and use variables in your formulas. This can both speed up and shorten formulas. The speed savings will only recognised in larger files that use a lot of LET functions. It also allows you to create solutions without using helper cells.

In the subscription all the existing functions have been given a new lease on life. The SUM function for example can do everything that the SUMPRODUCT function did in Excel 2019. These “old” functions can now perform criteria-based calculations in the subscription version.