Dynamic charts in Microsoft Excel

Intro:
Hello, and welcome to the CPA Australia podcast. Your weekly source for accounting, education, career and leadership discussion.

Neale Blackwood CPA:
In the February 2021 article, I look at another way to create a dynamic chart. So the December '20 article looked at using tables with a slicer. In this article we look at how we can use range names to create a dynamic chart. So we have two input cells, so a start date and an end date. Our data is daily sales. And so we've got sales from the first of the first 2020, all the way through to the 31st of the first 2021. And we want the ability to plot the sales and the margin from any start date to any end date. And what we're going to do is we are going to use three range names to allow that to happen. The easy way to do it is to create a standard chart just based on a range. So I used A1 down to C10, created the chart, modified it, and I changed the sales to a line chart.

Neale Blackwood CPA:
Now, if you want to see how to do all of this, this is on the companion video, which is on the in the black website. So to actually see this all happen, you can watch the video. So the podcast is more about explaining things and just going into a little bit more depth on some of the aspects of it. This example also uses helper cells. Now I'm a big proponent of helper cells, the cells you create to make your final formulas easier and shorter to create, basically. In this case, we need to identify the row number of the first cell that you want to plot as well as the last cell. Now, the best function to do that is the match function. So there are two helper cells in the example. So G2 and G3, and they each have a match function. So the match function returns a number that represents where whatever you're searching for is in a list.

Neale Blackwood CPA:
Now, in both cases, I've searched all of column A for the date. So there's an input date, a start date and the end date, and they're both input cells and I'm looking for the row number. And so the match function looks in column A and it finds where that date appears and it returns the row number. So when you refer to the whole column in the match function, you end up with the row number of the cell or of the value that you are looking for. And in the example, it's 368 and the ending cell or the ending date is on row 398. Now those two values are both used within an index function. So index and match work really well together and I highly recommend that you learn them.

Neale Blackwood CPA:
Now, XLOOKUP is also good for looking up. It's a brand new function. And in some cases it does replace the index match combination, but there are still a lot of useful examples of index match function. So there are functions that work really well together. They also work with other functions as well, so I highly recommend that you use them. So we've got two cells that have two match functions in them, and they provide the first row and the last row in the range that we need to extract. Now, we need to create three separate range names. So the first one's going to be called dates and that's going to work on column A, the second one's called sales and that's in column B, and the last one is margin and that's column C.

Neale Blackwood CPA:
So we need to create those three names. And we do that via the formulas tab and there is a define name option there. So we click on that. We use dates as the first name, and then we create the dynamic range. Now to do that, we use the index function. So the formula is quite straightforward. It's just equals index, open bracket, and then you refer to column A in the sheet, which is called example, comma and then you just refer to cell G2, which has that starting row number on it, and then close to bracket. And that index function will give you the cell reference for A368. And then you press column. And then you do another index function, which is pretty much identical, but instead of using G2, you use G3. And so what that does is it creates a dynamic reference to A368 as the first reference. Then you have the column character, and then you have the dynamic reference to the ending cell, which is A398 in this case.

Neale Blackwood CPA:
And that is where you get the dynamic nature of the range name. So when you change the cell entry, the match function will update, which will then update the index matches that are used there. So there's two index matches. Now isn't actually a match with this index function because we've captured the match functions in those cells. You could replace the reference to G2 with the match function, but that replicates what we need and so that's why I use helper cells. It also keeps the formula shorter if you just refer to a cell reference there.

Neale Blackwood CPA:
And so then in the video you'll see, I just copied that formula and then I've used that for the sales range name. And all I did was where we referred to column A, I've changed that to column B. And then for the margin, I did the same thing and just referred to column C. There's three separate range names, each of them have a start date and an end date. And so once you've got that, you are ready to then modify the chart so that, instead of using fixed references in the chart, it's going to use the range names, which are dynamic. So the easy way to do that is right click on the chart and click the select data option. And then when the dialogue that opens, on the right-hand side of the dialogue is the date listing. And so you click edit there and that'll have the fixed reference for whatever range you created. Now, in the example it was A2 down to A10.

Neale Blackwood CPA:
So you backspace over whatever the reference is. And then you leave the exclamation mark at the end of the reference. And then from the end of that, from the exclamation mark, you add the name, which in this case was dates. And so it'll have a sheet name, it'll have the exclamation mark, and then it will have dates and that's it. And that gives you the dynamic nature. Now just a heads up, on sheet names, if you've got a space in the sheet name, it will have extra apostrophes around the sheet name. But the last character before you enter the range name is always that exclamation mark. So just remember that.

Neale Blackwood CPA:
Then on the left-hand side of that dialogue that's opened up for select data, you click on the sales data series and there's an edit button, and you do exactly the same thing. So in the range, replace the range after the exclamation mark, enter the word sales, and then for the margin, you enter the margin one. So three separate edits, if you like. And once you've done that, that's it. Now you click "okay" and the chart is totally flexible in terms of the start date and the end date. And they are driven by two input sales that are on the sheet.

Neale Blackwood CPA:
Dynamic range names are really powerful. They allow you to have ranges that can adapt based on either inputs or they could also adapt based on formulas. So in the example video, I used a min function and a max function to calculate the first date in the range and the last date in the range. And that replaced the input cells and so then, whenever you add a new date to the end of your listing, that's automatically updated as the end date in the chart. So again, it's another example of how you can use dynamic names. Now you could use the offset function. So index match, that returns references to cells, which not many functions do. So Index Match, Offset, I think Choose can, and also XLOOKUP is a brand new function that can also return a reference to a cell.

Neale Blackwood CPA:
Now, the problem with off Offset is that it is what's called volatile. That means it calculates every single time that Excel calculates, whether it needs to or not, which can slow your model down. So I try and avoid offset. There are times when I will use it, but in general, I try and use the index match combination. That quicker than offset, because it only calculates when it has to. And now on the video I didn't add any error handling to the file. So for example, if you deleted the input cells, the entries in there, then that could cause an error message to pop up. So you might want to add the "if error" function and then maybe put a default for each of the matches.

Neale Blackwood CPA:
So in the video, I put a default of two for the start date and three for the end date. So that way, even if there was an error in the match, then it would always still return something for the chart to plot. There are a lot of other uses for dynamic range names in things like budgets and forecasts and reporting models. When you are normally using me, you don't need to worry about having that sheet name in front of them. For some reason, when you're referring to chart data, you do need that sheet name with the exclamation mark in front of the range name, to get it to work.

Neale Blackwood CPA:
If you just put the range name in, the chart will work once, but it won't be updateable. So you always, when you're creating it, have that sheet name, followed by the exclamation mark, and then put the actual range name in. One thing to note, if you are going back and editing the chart and you click that edit button again, that sometimes instead of having that sheet name there, it'll actually have the whole file name in front of the range name that you entered. So not sure why it does that, but just a heads up just in case it's it puts you off a bit.

Neale Blackwood CPA:
I did offer a little bonus tip as well when we were editing in the defined name dialogue. So when you're editing a formula in one of those dialogues, so a dialogue that opens and it accepts a formula. If you use the arrow keys to try and move around the formula, sometimes it actually puts references from the underlying sheet in there, which can be really frustrating. So to get around that issue, when you edit in these dialogues that may pop up for your formulas, always press the F2 function key. And what that does is that turns it into editing. And so then you can use your arrow keys to actually move around the formula rather than moving around a spreadsheet and putting different references in.

Neale Blackwood CPA:
So that's a little tip that applies to a lot of other dialogues, not just the range name dialogue. Okay, I hope you've found that useful. So again, if you want to watch the video, the in the black website has that as well as the full article with all the examples and the download file. So thanks for listening.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.cpaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify or Stitcher.