Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood CPA:
Welcome to the Excel Tips Podcast. My name is Neale Blackwood, and in this episode I wanted to cover using dynamic arrays to build automated reports in Excel. This is the first of a trilogy of episodes devoted to dynamic arrays and automating reports. The first two episodes, we'll look at reports, and the third one we'll talk about developing a dashboard using dynamic arrays.
The companion video and article go into a great deal of detail on the formulas, the functions, and the techniques. In this podcast, I thought I'd go more into the ideas behind it and also just talk about the techniques that are used. This does require the subscription version of Excel. The dynamic arrays were introduced way back in 2020, and since then they've really developed to now enable you to create automated reports in Excel that require very little maintenance. The example file does use a number of new functions, which as I mentioned are explained in the companion video and the article. You can download the example file from the INTHEBLACK website.
In previous podcasts, I've talked about individual dynamic array functions. The report actually brings a lot of them together and shows you how you can use them together to automate reporting in Excel. The report that was created is a 12-month report with the months up to January '24 populated with actuals, and then February through to June '24 are populated by the budget. And as data is added, so when we update the February actuals, the report automatically updates to include those actuals, and then the budget starts from March. So that way you can get, if you like, a reforecasted total year.
The companion file also has an example of the old way of creating the same report. That example has a lot of formulas in it, whereas the dynamic array example has very few formulas in it. That's one of the main advantages of using dynamic arrays. There are fewer formulas that you have to create and also maintain. Also, the old way of doing it wasn't fully automatic. It was semi-automatic. It didn't cover things like adding new accounts to the GL and things like that. Whereas dynamic arrays can actually handle new accounts being added to the general ledger and they automatically populate in the report.
Now, dynamic arrays have a new way of calculating in that they spill. So what that means is you create a single formula and that formula can spill or populate a range. Now that range may be horizontal, so the formula spills across the page. It may be a vertical range, which means that it spills down the page, and you also have the option to create a two-dimensional spill range, so it spills both across and down. So it populates a two-dimensional range. So that means that whole range is populated by a single formula which forms the basis of the report. And that two-dimensional range populated all the numbers that we required in this report that we're reviewing.
Now to refer to a spill range, you use the hash symbol. So you refer to the top left cell, and then you follow that reference, let's say B3 with the hash symbol. And the beauty of that is is in a formula that refers to a spill range, that formula will also spill to match that range either down across or in two dimensions. When you're building formulas now, it's sort of a different mindset when you're using dynamic arrays. Normally in Excel, you are thinking about single cells and working with cells, but dynamic arrays actually allow you to work with ranges now. And so that's the sort of change in mindset is you need to work with ranges rather than individual cells. So as I mentioned, this means you only have to create a single formula to populate a range.
Another advantage of dynamic arrays is that you need to use fewer dollar signs, which also cuts down the size of the formula. But on the other side, the formula may be a little bit more complex because it's doing so much. One way around that is to use helper cells. So these are other cells that you might create to simplify the final formula. And again, the helper cells are easier to create because they can spill as well. And so again, you're only creating a single formula that spills across, so you don't need to copy across and copy down. All you need to do is edit that single formula to change it, which means that maintenance is a lot easier.
The example file also uses formatted tables. So these are tables that are created using format as table, which is on the home ribbon. It's sort of like telling Excel to treat the table like a little database. I have done a podcast and some articles on the formatted table feature. It's really, really powerful. If you do use tables in Excel, I recommend you use the format as table option on those tables because it adds a lot of extra functionality. Dynamic arrays work seamlessly with formatted tables, so they automatically expand with the formatted table. Formatted tables automatically expand when you add new account codes to it, and the dynamic arrays that refer to those formatted tables can also automatically expand.
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:
Now, one of the things you need to do with the structure of the report is to leave extra blank rows so that if extra codes are added, there is space for the formulas to spill with those extra codes. But if more codes are added than there's rows there, what you'll get is what's called the spill error. Now that pretty much removes all of the values and you have a single error in the top left cell and you have to basically insert extra rows to allow the formula to spill down. The if error function won't handle the spill error, so you do have to resolve it, and it can be as simple as inserting rows or inserting columns depending on the structure you are using.
To insert a row, basically select the row, so click on the row number and you can use, hold the control key and press the plus sign. Now I use the plus sign on a full size keyboard, which is on the far right-hand side. If you've got a laptop keyboard or a small keyboard, it'll have to be control shift and the equals sign and that inserts a row and you can insert as many rows as you require. And then the spill error will just disappear. And once you've given it enough space to spill into, it will then populate the range again. So it's really easy to maintain.
As I mentioned, dynamic array formulas require fewer dollar signs. Because the formula is populating the whole range, you don't need to worry about using dollar signs to fix certain rows or columns, and so you find that there are fewer dollar signs required in dynamic array formulas.
Another advantage is they don't let the users edit the formulas. Because there is a single formula populating the whole range, you can't actually edit a single formula in the range. If you change the formula in the top left cell, it's going to populate every cell in that range. In the video, I show an example of the old school report where someone can go and edit a formula and they added $10,000 to it. So that's what you can do with the old school reports. You can edit each individual formula, but with the dynamic array, you can't. The formula populates the whole range so you can't go into an individual cell and change a formula.
There are some changes you need to make in your structures that you're going to use. In general, it's best to put the totals at the top, so above the range that you are adding up rather than below the range. And that's because typically the ranges spill down, and so your total could actually get in the way, if you like, of the spill range. So having the total above the range probably works a little bit better. You might need to have more blank rows in your report layout just so that things can spill. This may mean you might need to hide those blank rows once your report's complete, and then you might have to unhide them for the next month if new accounts are added. And then just hide the rows. You can use helper cells to shorten the formulas, and again, the helper cells can spill across to make that easier.
So in general, dynamic arrays are more automated. They use fewer dollar signs. There's fewer formulas to maintain. There's less chance of users editing the formulas. Dynamic arrays also allow you to sort and filter so a formula can actually do the sorting and the filtering. Dynamic arrays are an exciting addition to Excel. You do need the subscription version to be able to use them. Microsoft continues to add new dynamic array functions to Excel, but as they stand at the moment, there is enough flexibility in the dynamic array formulas to build automated reports 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.
