Creating an automatically expanding chart by basing it on a table

- [Instructor] On this worksheet are two sets of identical data. I'm gonna be creating a chart from each of them. For the second set of data, starting in row 12, I will first convert that into a table. If you base the chart on a table, whenever the table expands the chart expands automatically. In the first case up here all simply create a chart without having converted the data to a table. Because the information is surrounded by empty cells we don't even have to highlight the data, we can simply click within it and instantly create a chart with the key stroke combination Alt + F1.

That F1, of course, means the function key, F1. Alt + F1, we've got a chart. Now, I've just gotten data, imagine, for June 18th. I'll click on cell A8 and drag that lower right-hand corner downward. Automatically, we'll get the next day in there. Nothing has happened on the chart at all. I'm about to put in three numbers here, a 152, and over here a 96, and over here a 72. And nothing's happened to the chart. Furthermore, we're about to get data from Pennsylvania.

I'm going to add that into cell E1, state code Pennsylvania. Nothing has happened to the chart. However, I can click on the chart and we see what's being highlighted, the data that's currently being shown. I'll point to the lower right-hand corner of cell D8. Drag, rightward, downward. The chart has expanded to pick up the additional day and we've got room for the Pennsylvania columns even before you put the data in there. But every time we make a change, in other words, every time we add a new date on the bottom or possibly will be adding more states here, we have to, in effect, tell the chart that we're basing the data on an expanded view.

Doing that once a day maybe isn't that bad, but what is the kind of chart that we update four times a day, based on hourly data or what if it's updated every hour. It's going to get a bit annoying to have to continually redefine the source for the chart. I'll move that chart off to the right and click in the data down below here. I'm gonna convert this into a table. I can do this by pressing Ctrl + T or Ctrl + L or on the Home tab, it's a choice in the Styles group called Format as Table. It doesn't tell us too much about that feature.

On the Insert tab, there, as we see table, we see a better description. Create a table to organize and analyze related data, but it doesn't say anything in the description there about any advantage for using this as the source for chart data. Let's choose it anyway. Table, source data is automatically figured out we click OK and it looks like a table. I'm gonna create a chart simply by clicking on a single cell here, and like before, I'll press that keystroke combination Alt + F1. Got a chart looking like the other one had looked initially, but this time, imagine I've gotten the data for June 18th.

When I click on cell A19, in the lower right-hand corner just drag downward. Because that is a date, anytime you drag a data entry from the fill handle downward, you automatically will get the next day. As I let go of the mouse, keep an eye on that chart, the one on the left side, we will have room for that new date and there is. No data just yet, but I'll plug in the numbers right now. And as I do these one by one, we'll see the columns appear above 6/18/2018.

So, this one's going to be 152 and then off to the right is 96 and then it's 72. Furthermore, I'm going to add a state here just like I did before. So in cell E12 I'll put in PA. As I press Enter here, this will be a more subtle change, but each cluster of columns is gonna get slightly narrower, as a makes room for the potential Pennsylvania data. And there we see what's happening. By definition, if you have a table anytime you add data on the bottom of the table or on the right hand side, that information automatically becomes part of the table.

So, the table expands by rows and columns as you add data. We won't have to click on the chart anymore as we add new data, it will automatically expand. So a better model might be when the gets updated hourly where the need for this would be paramount than it is here. But give the idea some thought. When you're basing a chart on data, if you frequently have to update the chart because the data changes a lot, change that source data into a table, it'll make this process much simpler and faster.