Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode we're going to look at quite a few recent updates to Excel. A couple of them relate to pivot tables and they're quite exciting. By the way, you do need the subscription version of Excel to see these updates.
In the old days, Excel was updated every three years or so, but now with the subscription version, you can actually get updated monthly, and TRIMRANGE is a new function that you might have access to. Now even though there's a TRIMRANGE function, I'm not going to talk about that. I'm going to talk about the new range referencing that is related to the TRIMRANGE function.
So, one of the problems in the past with Excel is the fact that you might create a range to do a calculation and someone will put some data below the range that you've used in your formula, which means you've got to update your formula to include the extra data.
Now, TRIMRANGE gets around that issue. TRIMRANGE uses the full stop in conjunction with the colon. So normally, a range function is something like A1:B10, something like that. So now you can use the full stop on the left and the right of the colon. So when you use the full stops, this is how it works. If you put a full stop on the left of the colon, the range will start from the first cell that has an entry from where you started.
So in the companion video, which I recommend you have a look at because you can sort of get a better idea of how this works, I used A1 to A20, so A1.:.A20. Now, what that did was it started the range, not in A1, but in A3 because A3 was the first cell that had an entry.
And then on the other side of the colon, when you have a full stop on the right-hand side of the colon, it means to extend the range as far as the last entry in that range. In our case, I think it was A17 in the example.
So basically, the full stop on the left starts with the first cell in the range with an entry. And that may be the very first cell, it just depends on the range. And when you use the full stop on the right of the colon, the range extends as far down as the last entry in that range.
So, the idea is you can refer to a very large range, so, 100,000 rows for example, but the range will end at the last cell that has an entry in it. Now just be aware, you can have a formula that returns a blank cell. So it might look empty, but it isn't. Okay?
So if there's anything in the cell, then that's the cell that will get picked up. So you can use the full stop on either side of the colon, but I think what will be used most is a fixed starting point. So, it'll be the colon followed by the full stop because that gives you a flexible ending point, which is that's typically what you need in Excel. You need to extend the range to include any extra data, but you want to start in the same position, if you like.
So, having a flexible end allows you to handle if someone does put some data below the range that you'd selected, because now you can select a very large range that will encompass everything that could be entered. Okay, so that's the TRIMRANGE functionality, is basically a new way of referring to ranges.
Now, there's a new function called TRANSLATE, and as you might guess, it translates between languages. Now, this function does require the internet because it uses the internet to do the conversion. It also has a quota. So if you use it a lot, you might find that it gets blocked. So, just a heads-up on that.
You can just use one argument in the TRANSLATE function, and what that will do is that will guess the language that you are converting from, and it uses the default language from your system to convert to. Okay? So that's the one argument. If you just refer to a cell that has some text in, it's going to guess the language that's been used and then it's going to use the default language to convert to, and you can specify the languages. So, the second argument is the from language and the third argument is the to language.
I have supplied a table of codes that I downloaded from Microsoft that lists the codes that you can use to specify the language. In the companion video, I used Indonesian, which is ID and French, which is FR, and you need to enclose those in quotation marks.
Okay, pivot tables. This is the exciting part, pivot table formats. Now when you used to create a pivot table, the first thing you did after you created the pivot table was pretty much format the values because they had no format. You'll be pleased to know that after decades of requests, we now have the situation where the source data format will flow into the pivot table.
So if you've used the format with the comma separator for thousands and no decimal places in the source data, that format will flow into the pivot table report. Oh, by the way, if you forget to do it, so let's say you do the format after you've created the pivot table, what you need to do is refresh the pivot table. Now that won't update the existing pivot tables, but any new pivot tables you create will use that recently applied format.
Okay, the other exciting thing to come, is there is an auto refresh option coming to pivot tables. It's not here yet. It's been released in the beta version, so they're testing it. But basically, one of the downsides of pivot tables is the fact that you need to refresh it to get the latest data, but there is going to be an option soon. So, coming to an Excel near you that will auto refresh the pivot table, so you don't need to think about it. So as soon as the data changes, the pivot table will refresh. Now, that's another thing that we've been waiting decades for.
Okay, two functions that are very popular in dynamic arrays are BYROW and BYCOL. So, their main use is to sum a two-dimensional dynamic array, and they basically split things up either into rows or columns and then do a calculation.
Now, in the past, you had to use the LAMBDA function to do the calculation. So, it wasn't an intuitive way to do the calculation. But now you can use a different syntax that simplifies BYROW and BYCOL. So, what you can do now is you specify the spill range, so something like C3 with the hash symbol, comma, and then when you type the comma, you'll be given a list of all of the functions that you can use.
Sum is at the very top because SUM is probably going to be the most used function in both, BYROW and BYCOL, and that's it. You just put a bracket on the end and you're done. Now, this is also a brand-new way to use a function because the SUM function does not have any parentheses, it's just listed.
So what happens is, let's take BYROW as an example. The spill range is going to be split up into rows, and those rows are going to be passed to the SUM function, and that's the results that will be returned, and that's it. So, it's a different way to use a function. Note, all of the old LAMBDA functions that you've done will still work. You can still use LAMBDA, which is the case if you need to do a more complex calculation. The functions that are included are like SUM, AVERAGE, COUNT, the standard, basic functions.
Okay, drop-downs. A couple of things have happened to drop-downs in the desktop version of Excel. Now when you refer to a range for a drop-down, so this is using data validation to create a in-cell drop-down, the duplicates are removed.
In the old days, if you had duplicates in your range, they would be shown in the drop-down, but now any duplicates in your listing will be removed for the drop-down list, which makes sense. Also now, this has been released in the desktop version. If you type a letter in a cell that has an in-cell drop-down, it will reduce the number of options. So, it'll just show you the options that have that letter in it. So that's something that again, we've been waiting for, for a long time.
Something else that's new is images inside cells. So, you've always been able to put images around the spreadsheet, but now you can put them inside a cell, and they're basically data in a cell. And the beauty with this is that as you change the size of the cell, the image will increase, but you can look up the images if you've put them at a table and you've got a label next to them.
So in the companion video and the companion file, I've included a listing of all the keyboard images. In the companion file, I have created a little model that allows you to capture keyboard shortcuts. So, it works for two keys. You select each key, and they are extracted from a table using XLOOKUP, and then the keyboard shortcut combination is shown on sort of the right-hand side. So you could take a screenshot of that if you wanted to capture and share a keyboard shortcut. So, it gives you a little bit more flexibility to use images, and you can look them up.
Another new feature is one that I don't use, but if you use large spreadsheets, then you might want to try it. It's called a Focus Cell, so it's in the view ribbon tab. And what it does is as you move your active cell around with your arrow keys, it will show you the current row and the current column with a different colour, and you can specify that colour, and it basically allows you to sort of see where you are on the spreadsheet.
Now again, if you're in a large spreadsheet, it can be a little bit daunting or overwhelming. So, these colours help identify where you are in terms of the current row and the current column, and you can change the colour as well.
And lastly, I wanted to talk about the Regex functions. So, Regex is short for regular expressions. It's typically a programming term, and there's three new functions that have been released. There's REGEXEXTRACT, REGEXREPLACE, and REGEXTEST. These use patterns that you can define to identify things.
So in the example, I was able to split first name and last name when there wasn't a delimiter. So what there was, was capitalization. So, the first letter of each word was capitalised. I was able to write a formula that extracted the first name and the last name, and there was no gap or text between them. They were all one word, if you like. And so you can actually use a pattern to identify that.
So, check out the video to see the formula and the pattern that I use to actually split up the first name and last name. Okay, so keep your eye on Excel because it keeps getting updated. If you're in the subscription version and what's called the monthly channel, then you can get updates fairly regularly.
They have been working on a lot of Copilot AI stuff recently, and I saw a recent posts on the Excel blog that they are releasing in the beta testing environment, a Copilot function. So, the idea behind the Copilot function is you give it a prompt and then you give it some context. So, you could link it to a cell which might have a prompt in it, and then comma and then link it to a data set. And so the prompt can ask a question about the data set.
So, that will be interesting. So as I said, keep an eye on Excel because there's lots of new things coming. Thanks for listening. Hope you enjoyed the podcast.
Garreth Hanley:
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.
