Boost your skills with tips for Excel function updates

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood CPA:

0:29 Dynamic arrays
Welcome to the Excel Tips podcast. My name is Neale Blackwood and in this episode we're going to look at some updates to Excel functions that have happened in the last three or so years. We'll start with dynamic arrays. Now, this was a huge change to the way Excel calculates. It happened in 2020 and it's part of the Excel subscription version, and it changed the way we can perform calculations in Excel. The way that I like to think about dynamic arrays is that where we used to do a calculation or manipulate a single cell in a formula, we can now do the same to a range of cells. And depending on the range you choose, this can cause the formula to what's called spill. Now, it can spill down, it can spill across, and it can spill in two dimensions. And that spilling allows you to create one single formula that populates a range.

So that might not sound brilliant, but it is because it reduces the number of formulas you've got to think about. When you create a formula, and let's say you drag it across and then you drag it down, there's always that worry, I suppose, that someone has manipulated one of those formulas and added 10 to it or something like that. Whereas with a dynamic array, that's not possible. If you try and edit another formula in that range, it won't work because there is no formula in those other cells. The formula is only in the top left cell of the range. So it's a single formula that you maintain and you don't have to worry about the other formulas because there are no other formulas. So that's dynamic arrays. I covered them in detail in 2020 in a series of three articles in the middle of the year. I said it was a big change to the way Excel calculates. It affects the way we now can produce reports, budgets, forecasts. Pretty much all our calculations now can be changed by using dynamic arrays.

2:29 XLOOKUP
At the same time, back in 2020 when dynamic arrays were brought in, we also saw the XLOOKUP function introduced into Excel. Now, XLOOKUP, I've covered that separately in a podcast, I think, is the game changer in terms of looking up data from a table. So in the old days there was VLOOKUP and HLOOKUP. Now those functions are still there, but they're pretty much redundant now because XLOOKUP can do a vertical lookup and a horizontal lookup, which is what those other functions did. Plus it can do a whole lot of things that we used to do with the index match combination. So XLOOKUP is a lot easier to use, a lot more flexible. It has a lot more options. It even has a built-in error handler in it.

3:19 LET function
Another new function that's a little bit more recent is the LET function. Now, the LET function allows you to use variables within your functions. The impact of this is more on more complex formulas, but it's a big impact because you can cut down the size of those formulas. Also, the LET function works really well with the next new function, which is LAMBDA.

3:49 LAMBDA
So the LAMBDA function I've covered recently allows you to create your own custom functions in Excel without using VBA or macros. In the past, you could create a custom function in a file, but that file had to be saved as a macro-enabled file or a binary file type to keep the functionality working. But now we can create our own custom functions using the LAMBDA function. And what that does is it allows you to reduce the size of your formulas and basically capture a lot of business logic and put that inside a single formula. Again, you are reducing the amount of maintenance you need to worry about because you can get a calculation into a single function and then just maintain that function. And then you can use that function throughout the file. So custom functions use the LAMBDA function, so it's worth investigating if you are doing more complex calculations because you might be able to capture them using LAMBDA.

4:56 TEXTAFTER, TEXTBEFORE and TEXTSPLIT
TEXT functions. There were three new TEXT functions released fairly recently. There was TEXTAFTER, TEXTBEFORE, and TEXTSPLIT. So TEXTAFTER allows you to extract out of a text string after a certain character. So TEXTAFTER. The TEXTBEFORE works in the opposite direction so you can extract before a character. For example, you could split up first name and last name or extract first name and last name using the space character. In terms of splitting, the other function, TEXTSPLIT, you can actually define a delimiter, which is a character that's used to split up text and it will create a spill range that splits up a text string. So you might have a code, for example, that might have three parts to it, and it might use a dash between the three parts. And so you can split it into those three parts fairly easily using that TEXTSPLIT function.

Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.

Neale Blackwood CPA:

6:15 BYROW and BYCOL
BYROW and BYCOL functions. Now, in the early days of dynamic arrays, one of the problems with a two-dimensional range was that you couldn't add up the first row and then the second row and then the third row, and then the first column, second column, et cetera. It was very difficult to do that. So now you can basically split up a dynamic array spill range, a two-dimensional one, into rows and columns so that you can do a sum calculation on them. BYROW and BYCOLUMN function, or BYCOL it is, and they use the LAMBDA function to do their calculation.

7:00 the IMAGE function
A brand new function that I think is going to be updated fairly soon is the IMAGE function. So the IMAGE function at the moment allows you to bring an image from the internet into the Excel spreadsheet. It's going to need a HTTP address to bring it in. You can't bring it in from your hard drive or your server at the moment. I think that is going to change, but at the moment you need a HTTP address to bring the images in, but it's fairly easy to use. It's just image, and then you use the URL of the image to put that in to bring that in.

7:45 VSTACK and HSTACK
Two more brand new functions: VSTACK and HSTACK. VSTACK stands for vertical stack, so that allows you to take two lists and put them one on top of the other. In data terms, it's like appending the tables if you like. Again, really easy to use. All of these newest functions are fairly easy to use now. VSTACK, you just separate the ranges with a comma and it will put one range on top of the other, and you can have as many ranges as you like to create a vertical stack. And HSTACK is a horizontal stack. So what that can do is put columns together to create a table. Again, just separate the ranges by commas and it's really easy to implement.

8:31 TAKE and DROP functions
A couple more newbies are the TAKE and the DROP functions. TAKE allows you to specify a number of rows or columns to extract from a range. So if you use a five, for example, you can take the first five rows out of either a range or a spill range. And if you use a negative number, so minus five, that would actually bring in the last five rows. So the example I used in the companion video, I had a sorted list. So I could either bring in the top five sales or the bottom five sales just by using a plus five or a minus five. So that's extracting. The DROP function sort of does the opposite in terms of it drops things off rather than bringing them in. So if you give it a... I think there was a list of about 105, 106 entries. And if you put in minus 95, it deletes basically from the bottom up. If you put a 95, it deletes from the top down. So the TAKE and the DROP allow you to extract. Which one you'd use would depend on, I suppose, which way you're thinking about the range, whether you are dropping things or you want to include things. And again, it's a very simple syntax to both of those. You just refer to the range, comma, and then the number of rows or columns. So you can use rows or columns to extract.

10:03 CHOOSEROWS and CHOOSECOLUMNS
Another couple of new ones is CHOOSEROWS and CHOOSECOLUMNS. This is actually similar to the DROP and the TAKE, but you can sort of work on individual rows. So you can extract an individual row to work on it from a normal range or a dynamic array. And the same with the columns. And again, you just refer to the range and tell it the row number or the column number that you want to extract from.

10:34 DAX functions
And lastly, for the new functions, DAX functions. So DAX is spelled D-A-X. DAX functions are related to Power Pivot. They're also related to Power BI, which is Microsoft's dashboarding business intelligence package. The DAX functions have been developed and keep developing in Power BI, but there's been a bit of a moratorium on updating Power Pivot for some reason. And at the end of 2022, we got, I think it was 92 new DAX functions updated in Excel Power Pivot. So that's really good. It's updating Power Pivot. You can sort of learn a lot about DAX by using it in Excel before you make the leap over to Power BI. The same with Power Query. Actually Power Query is in Excel and it's also in Power BI. Power BI has a lot more resources I think being thrown at it. So it is getting updated every month basically. I think they might have Christmas off when they're doing their updates. So I think it's about 11 updates a year, and those updates sort of work their way back to Excel for Power Query every six months or so. But Power BI gets updated regularly and Power Pivot has just been updated. So it's good to see that Power Pivot has got some new DAX functions. So lots of new functions being released in Excel. To stay up to date you can check out INTHEBLACK website where we have lots of Excel resources and there's lots of companion videos there. So you can also subscribe to this podcast to keep up with what's changing in Excel. Thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.