Chris Hatzis:
Excel Tips, a CPA Australia Podcast.
Neale Blackwood:
Welcome to the Excel Tips Podcast. My name is Neale Blackwood, and this is part two of a two-part series on interactive charts in Excel. Now, in part one, we covered how we can create two separate charts and make them interactive and also dynamic so that they update automatically and you can change the entries and the user has complete control really, over what's displayed on the chart.
Now, we used a couple of different functions to achieve that. We used the HSTACK function, which allows you to combine columns, and we also used the Group By function, which is sort of like creating a simple pivot table. Now, the secret to creating these interactive charts is to create a single formula that creates a two-dimensional spill range, and that is the data source for the charts. And that's where we get the dynamic nature of making changes and they automatically appear in the chart.
Now, in part one, we were working with calendar years. In this podcast, I'll explain how to convert the calendar year calculation into a financial year calculation. Also, how we can add extra interaction to the charts using checkboxes, and also how to use checkboxes to control how the data is displayed and what is displayed. Now, we're using some sales data here that is in a formatted table, and there's multiple financial years sales data in that table.
It is a formatted table, so it does automatically expand. Now, I do recommend you check out the companion video, because that goes into detail on all of these techniques. And it also shows you, at the end, that when you add the next month data into your formatted table, all of the charts automatically update, and that's one of the advantages with using a formatted table as your data source.
Okay, so let's look at how we can convert the calendar year into the financial year. So the idea is that we use the Year function in Excel, which gives us the calendar year, but we can sort of hack that function by adding 184 to the date and then getting the year of that adjusted date. And that will give you the financial year instead of the calendar year. And the reason is, there is 184 days between the 31st of December and the 30th of June.
And also, this works with leap years. So that's the hack, you just add 184 to the date, get the year of the adjusted date, and then you are working with the financial year rather than the calendar year. Because in the chart selection, you had the ability to select a year, which, in part one, was only working with the calendar year, but in part two, you'll see that that will now work with the financial year rather than the calendar year.
In part one, the column chart that was created was sorted in descending order, and that was keyed into the formula. So what we want to do in part two, is we want to make that interactive so the user can choose whether to sort ascending or descending. So in the companion video, I take you through how you can convert the formula to work with a checkbox. So the checkbox allows the user to tick it to sort in descending order, and if you un-tick it, it sorts in ascending order, and that automatically flows through into the chart. Now, checkboxes are really easy to insert in Excel now. They're in the Insert ribbon. You select a cell, click on the checkbox, and it will insert the checkbox. You can also select a range and it'll insert multiple checkboxes.
Now, a checkbox, when it's above a cell, will put either true or false into that cell, and then you can use that in an IF function to actually control how something operates. A ticked checkbox will insert true in the cell beneath, and an un-ticked checkbox will insert false in the cell underneath. And you can't see those words. You can see them in the Formula bar, but they're not displayed on the sheet.
So I also shared a technique using the PIVOTBY function. So PIVOTBY is very similar to GROUPBY, but it adds the extra ability to create a two-dimensional report so you can control what appears on the rows of the report and what appears on the column of the report. So again, it's like a pivot table, and it's one single formula that creates the two-dimensional report, which then becomes the data source for the chart.
So in part two, we look at how we can take that simple line chart that we created in part one and add extra components to it that the user can control. So in part one, it was just a single line chart that you could control how many months were plotted. But in part two, I show you how you can use checkboxes to control the metrics that are actually plotted on the chart.
There are three options, dollar sales, quantity sales, and dollar margin. So basically I use checkboxes to control which of those three metrics is plotted on the chart. So you can plot all three or just one or any two. And the way you do that is using the FILTER function. So FILTER is typically used to filter by rows, but depending on the ranges that you select for the filter, you can actually filter out columns, and that's what the example uses. So it uses the FILTER function, the HSTACK function, as well as the VSTACK function. So HSTACK is a horizontal stack and VSTACK is a vertical stack. So a single formula combines a number of elements into the two-dimensional range that becomes the data source for the chart.
So when you are building the interface to work with checkboxes, be aware that it is really easy to delete the checkbox, so you do need to handle the situation where the cell that the checkbox is over could be blank. If you press delete on the keyboard when you've got an unchecked checkbox selected, it does delete the checkbox and the entry underneath. Obviously you can undo that, but it is possible that someone could accidentally delete the checkbox, so you do need to handle the situation where there might not be an entry in the checkbox cell.
And so the FILTER function does that really well, because it has a default option that if there's nothing to filter, then you can still display something if that is the case. So there were a lot of defaults used in the example. So as I mentioned, highly recommend you check out the two videos, because across the two videos you get lots of different techniques that allow you to create interactive charts.
Check out the companion article as well. At the bottom of that, there's a whole lot of links to all of the various components. All of the different functions have been handled in separate articles, so if you are unsure how to use some of the functions, then you can check out the other articles which go into a little bit more detail. And in the video, you can see when I add the July data, because the data ended in June '26, when I added the July data for July '26, it automatically updated the charts as well. So that's, again, the advantage with using formatted tables.
So interactive charts, a lot easier to build now using dynamic arrays, and now we can use checkboxes much easier than before, so worthwhile checking it out. So hopefully this podcast, the article, and the companion video can inspire you to create some interactive and dynamic charts that can bring your reports alive. 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.
