Chris Hatzis:
Excel Tips, a CPA Australia podcast.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode, I wanted to discuss how you can create interactive charts in Excel using dynamic arrays. Most charts in Excel are based on static ranges. So, the values might change, but the chart itself will still be plotting the same number of months as an example. Now, dynamic arrays make it possible for you to change the number of months that are plotted on the chart.
And the first example on the companion video shows you how to do that. So, I recommend checking out the companion video. It has all of the details of all of the functions, and it takes you through step by step. You can check out the article as well because there's lots of links to a lot of the techniques that I cover. So, a lot of the techniques have been covered in previous articles. You can check out the previous articles if you're not sure of some of the techniques.
This is part one of a two-part series on interactive charts. So, in this episode, I wanted to cover the basics, and in part two, we'll look at some more advanced techniques. So, as I mentioned in example one, the user can select how many months they want to report on and the chart will automatically update based on an input cell.
The example uses what I call helper cells. So, these are cells that make the final formula much easier to create. Helper cells also create transparent type formulas. So, instead of having huge formulas, you can break down the huge formulas into shorter formulas that are much easier to understand.
A lot of the helper cells use dynamic arrays, and they're all based on the SEQUENCE function. So, it's very common to use the SEQUENCE function to actually drive dynamic array calculations. SEQUENCE is used commonly in budgets and forecasts. And again, it provides a flexible way to control how many months are reported on. The example data had two years' worth of sales data, and I used the MAX function to find out the current date or the last date in the data.
So, the MAX function usually works with numbers, but it also works with dates because in Excel, dates are numbers. So, you can find the highest or the most recent date using the MAX function. I also use the EOMONTH function. It stands for end of month and as well as returning the end of the month, I shared a hack on how you can return the first of the month. So, EOMONTH is a fairly flexible formula. And then, I used the HSTACK function. So, once we brought together all of the figures we needed, then the HSTACK function allowed us to join two columns together, and that became the source data for the chart.
So, to make charts dynamic, it's important to create a single spill range. So, in this case, it was spilling down and across, and spilling is associated with dynamic arrays. So, the HSTACK function allowed us to join two columns together, and that became the source data for the chart. So, as the user changes the number of months, the spill range automatically updates to match that, and the chart automatically updates as well.
Now, in the companion video, I also shared a solution to a dates bug in dynamic arrays. So, unfortunately, this has been around since day one of dynamic arrays, but Microsoft haven't fixed it yet. So, it relates to date-based calculations that refer to a spill range. And the solution, which is really easy, is to put the plus sign in front of the spill range reference. Now, just a reminder, the spill range reference is the hash symbol.
So, you have the hash symbol after the cell reference. And if you're getting a value error, which is the error that displays when this error pops up, all you need to do is put the plus sign in front of the cell reference with the hash on the end. So, you have the plus sign on the left and the hash on the right, and that usually fixes the problem with this bug that relates to date-based calculations.
In the second example, the user could make three separate inputs to control the chart. So, they can choose the calendar year to report on. They can also choose a column within the table that they want to base the chart on. And then, they can choose one of the value columns to report on. This example also had some defaults in there, and the defaults were there just in case people deleted the input cells.
So, if the input cells were blank, the defaults would be used. Now, the helper cells in this case, were identifying the column numbers within the sales data. So, that used the MATCH function. You could have also used the XMATCH function, which is a newer function, but they returned the column numbers that we needed to identify that we wanted to report on.
And then, the INDEX function used those column numbers to extract those particular columns from the table, and the GROUPBY function used two INDEX functions to identify the column to report on, and then, the column value to report on. Now, the GROUPBY function is a fairly new function that works just like a pivot table, but it has the advantage that it automatically calculates, unlike a pivot table, which has to be refreshed.
Now, the second example had a problem with the left axis, and this is a common problem with column charts in Excel is that sometimes they don't start at zero. So, it's best practice for all column charts to have the vertical axis start at zero. And I show you in the companion video how you can make that happen.
As I mentioned, this is part one, and it goes through the basics of creating the interactive charts. Part two, which will be next month, I will show you how to handle financial years rather than calendar years. We'll also have a play with the FILTER function, which works really well with check boxes. And so, we'll see how we can use check boxes to control the chart.
And we'll also look at the PIVOTBY function, which is like GROUPBY, but it has the ability to create a two-dimensional report with rows and columns reporting on specific values. So, stay tuned for that. That will be out next month. Thanks for listening.
Jackie 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.
