Join Dennis Taylor for an in-depth discussion in this video Creating dynamic charts that always show the last "N" periods of data, part of Excel 2013: Charts in Depth.
In this work sheet called Dynamic, reviewing sales for the last six months, that's data coming out of columns A and B. Recognize that there are actually nine months of data there. In cell N1 is the number six, if we change this to four, we're looking at the data for the last four months. So, we change it to three, last three months and so on. It's a dynamic kind of chart and it's based on using a function, perhaps a lot of you have never seen. It's called the Offset Function. Putting the pieces together for a chart like this takes a little bit of work.
And off to the right what I've got in a text box are the very steps involved here. So I'm going to pull this box over to the left, and actually create another chart just like the one we've got here. This dynamic in the same kind of way. One of the functions being used within this offset that I mentioned too is called Count A. And it counts the number of cells in Column A that have data. There's also a Count A that is being used for the same purpose in Column B, counting the number of cells there that have data. In order to set this up what we first need to do is to define these dynamic range names.
And ultimately what we will be doing with them is saying, in effect to the chart, use those as the source data and not the pure data in columns A and B. So, we begin the process by actually creating these range names. And you don't want to watch me type, so I will highlight this data right here, press Ctrl+C, and then go to the Formulas tab in the ribbon, Define Name. And this dialog box can be made wider, I'll do that. And in the lower panel Refers to, I'll click in there, press Ctrl+V.
Looks like there's some trailing spaces. We'll get rid of those. And give this a range name. If you have not worked with range names, recognize they cannot begin with numbers and they cannot contain spaces. So I'll call this Current Months. This is for the data in column A. Current months, plural, no space, click OK. We've done that, let's do the same thing for sales. I'll highlight this data here, press Ctrl+C, and again go back to Define Name, it's on the formula tab, and in the refers to panel below, Ctrl+V.
Once again getting rid of those trailing spaces. And we'll give this the name, similar to the other one, current sales, and click OK. So we've, in effect, defined our range names for the months and the sales. Let's now create the chart. The chart Date of the Source Data is surrounded by empty cells. It's in columns A and B. So we'll simply click there and press Alt+F1, and there's our chart. Move it off to the left a bit, and for the moment, it's showing all the data, as you would expect. In other words from January 2013 through February 2014. Now we're going to redirect the source of the data, by right-clicking in the chart, and choosing Select Data.
And on the right side here, where we see Horizontal Axis Labels, we're going to edit, and change that entry. Currently it says Dynamic, and refers to A2 to A10, and we're going to change that. Leave the word Dynamic in there, but put in current months. That's the range name we created, all done there. And over on the left side legend entries sales the actual sales. We'll edit this as well in a similar way the series values are not going to come out of column b strictly anymore. But out of the range name dynamic Current Sales, and we're done, click OK, click OK. This chart now has three months in it, because we see the number three up here. We'll change that to a five, Enter, and now we're seeing five months. And change it to a seven we're seeing the seven most recent months. And in all cases we're moving up from the bottom.
When you click on the chart at different times when this is happening take a look at columns A and B and you see what's going on as well too. Let me go back here and change this to a five. And then come back to the chart, we see what's happening as well. The only thing this chart doesn't have that the other one has is the title. Recognize that the title is Dynamic too. It picks up the number of months out of cell N1. And that's done with the text function. And you'll see it out here in cell W1. Now it's really a concatination of text and the value coming out of cell N1. And so we will use that some location for the title right here. And all we need to do in our new chart, I'll move over slightly, is click the Sales, type Equal, and click cell W1, and Enter.
And we've got our title in place, and see what a quick test here will change this to 8/g, and we have the last eight months, and our title adjust as well. We might want to do other things with the visuals here, but we're in good shape here. In creating this, I think you see, requires a number of unusual steps, but the payoff could be substantial. I can imagine how dynamic this appears during a presentation. We simply change the number and we get the last six months, eight months, nine months, whatever. And as our list grows over here, in fact what happens right now if I add another month? Right now, we're showing July through February, I'll drag this down.
We now have March in the list. We're showing August through March. Don't have the new total just yet. I'll put in the 1400, Enter, and we see that in place too. So it's a Dynamic Chart by way of some unusual functions.
- Selecting the right chart type
- Choosing data to display as a chart
- Creating charts fast with the Quick Analysis tool
- Choosing a chart layout
- Changing the location of the source data
- Dealing with empty and hidden cells
- Moving and resizing charts
- Adding data labels
- Analyzing existing and future data with trendlines
- Adding shapes and arrows
- Working with column, bar, line, pie, and area charts
- Working with specialized chart types: Gantt, Pareto, and Frequency charts
- Creating dynamic charts
- Printing and sharing your chart