The five most useful new Excel functions for finance teams

Table showing new Excel function FILTER

This post sets out the five most useful new Excel functions for finance teams, based on my experience. By “new” I mean available in 2019 or later versions of Excel (including Office 365).

Many of us are self-taught in Excel, and it can be hard to keep up with the changes. In the past few years there have been loads of new Excel functions that replace and add to existing ones that you might be familiar with.

XLOOKUP

If you already use VLOOKUP (or HLOOKUP or plain LOOKUP) and have Office365 then please invest 5 minutes in learning how to use XLOOKUP instead. It solves many of the problems that VLOOKUP has;

  • XLOOKUP defaults to exact match (with VLOOKUP, you have to add “False” to get this);
  • it works horizontally as well as vertically;
  • you don’t have to have your look up array as the first column or row, nor do you have to sort the data first;
  • VLOOKUPS are not dynamic formulas (unless you add something else to them). A typical VLOOKUP formula looks like this: =VLOOKUP(A2,Sheet2!$A$2:$B$3,2,FALSE). The bold “2” tells the formula to pick up the second column in the data array “Sheet2!$A$2:$B$3”. However, if you insert another column into your looked up data, the formula doesn’t update. You don’t have this problem with XLOOKUP, which is a fully dynamic formula.

Here is a short video explaining how XLOOKUP replaces all the previous LOOKUPS and gives you more functionality and safety.

(You may have previously learned to use the INDEX/MATCH combo to get around the LOOKUP weaknesses – XLOOKUP replaces this too).

IFS (and relations)

This was a 2019 development, but I still don’t see that many people using it. It basically means you don’t need to do complicated nested IF formulas anymore. Here’s a blogpost explaining it.

TEXTJOIN

This is another new Excel function introduced in the 2019 edition and it is a much better version of the CONCATENATE function. TEXTJOIN lets you connect text strings and cells in the same way that CONCATENATE did, and as well as being easier to spell, it provides additional functionality. You can now join strings of text together with a delimiter:

Table showing use of the new Excel function “TEXTJOIN”

FILTER

This is an example of one of Excel’s new “dynamic array” formulas. A dynamic array is a set of results that is linked to just one formula, which then “spills” into neighbouring cells with as many results that are relevant. FILTER lets you extract data from one array into another based on a specific criteria.

In this example, we have a list of wards and their parliamentary constituency in columns A and B. Cell E2 is an input cell to let the user enter the parliamentary constituency they are interested in, in this case “Bristol West”.

In cell D6, the start of the results table, the following formula is entered:

=FILTER(A3:B9,B3:B9=E2)

This is telling Excel to pull all the data from the first table where column B = whatever is in cell E2. As there are four results, this has spilt into the next three rows. It’s a dynamic formula, because if we change the original data (for example, assigning Lawrence Hill to Bristol East), then the results table shrinks accordingly:

And if you changed the filter criteria in cell E2 to Bristol North-West, the list changes again:

In all of these examples, the formula is only entered in one place, cell D6. The results table expands and contracts according to the number of results. This is a powerful way to give users some control over data extraction, without messing up your original data, and it can be really useful for reporting and analysis.

If you like this, then also have a look at LARGE, SORT and UNIQUE, which work in similar ways. LARGE is great (particularly in conjunction with SEQUENCE) for generating dynamic top ten lists for dashboards or management reports.

SEQUENCE

SEQUENCE lets you generate sequences of numbers into a list or a table. It’s another dynamic array. I tend to use it most for generating dynamic time periods for financial modelling or for depreciation or amortisation calculations.

However, there are lots and lots of uses for SEQUENCE – it seems like every online webinar I go to has a cool new use for it. This blog post has some interesting examples.

Why didn’t you include…?

There are, of course shedloads more new Excel functions I could have picked. There are entire areas of development that I’ve ignored in this list, such as the ability to take formula writing to the next level with with LET and LAMBDA. However, I have targeted this list at the average finance user, and selected these functions in particular to demonstrate how Microsoft is evolving.

I also didn’t include Power Query, even though I think all regular users should get to know this. Power Query has been around since Excel 2010, so it’s not exactly new, and it’s the equivalent of getting an entire new tool box.

I provide training on Power Query and Excel in Bristol and beyond, particularly tailored to finance teams, so if you want to upgrade your knowledge then please get in touch.

If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.

Leave a Reply

Your email address will not be published. Required fields are marked *