Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
With certain kinds of dynamic charts, you want them to be updated monthly, daily, maybe even hourly-- depends upon the nature of the data. In the chart that we're looking at here, this line chart, maybe we are going to add data every month. If you look at the information in column A, we see that it does cover the period up to December 2010, and maybe we're going to put in the January 2011 data, then February, and so on. Now when you do click on the line chart, as I'm doing here, click on the actual line, you see the data being referred to.
One approach to this updating could be, well let's just put in the new data here and then readjust those markers simply by dragging them down. Nothing wrong with that. Another approach could have been that when the chart was created maybe you highlighted cells like this, and it would have left gaps over here to the right. In fact, I could show the effect of this right now simply by pressing Alt+F1. This will create a new chart on top of this Column chart this example here. You can see what's happening there. The reason this looks a little bit strange, by the way, is because of what Excel does with dates.
So ignore that aspect for the moment, just to show what we have going on down here. Let me get rid of that. Okay, so let's show what would happen here if we were to add, simply by dragging downward, maybe with a right drag, Fill Months. Nothing has happened in the chart at all. I'll put in the new value of 185 and nothing has happened. So certainly one way to adjust this, as I had suggested, was to click the line, take these choices here. We can drag the markers at the bottom here downward to expand the data referred to in the chart.
So now we have picked up January 2011 with the values appropriately. So you could do that every month. Maybe that's not a whole of work. But there could be times when you're dealing with data that's updated more often, or maybe you just want this data to be more automatic. What if we were to turn this data into a table? Now, table is a new feature in Excel 2007. It's very much like the old list that had existed in prior versions. If we define this data as a table, then as we add information to the bottom of it, either the numbers and/or the dates, the chart will automatically expand.
So let's convert this into a table by doing one of three things. You can click anywhere within the data, and then from the Insert tab, you could insert a table there. From the Home tab, you can start here by formatting as a table, or you could press Ctrl+T or Ctrl+L, a lot of different ways here. Format as a Table, and right away it comes up with the visual, which isn't that important. I'll just pick this one. We see what's going on there. It says, in effect, you want to do it for these cells? Yes, I do. Click OK.
So what's different now? We've got a chart. I'm going to simply drag this downward. You see what's happening in the chart already and the value here. Now in dragging that I made a mistake. I repeated the dates, so what I meant to do, and I'll do it now, is either drag with the right mouse button downward to increment it by a month. Now we're going to see February, and then I'll put in the amount. I want to emphasize too, you will be able to do this manually as well. Next number of 195, let's say, and let's see what's happening there. So March data is now available. Maybe I'll put in the sales number first.
So right here I'm going to type in 230, and so we don't have to manually adjust it each time. And then over here I'll put in the March data, and maybe I'll just type it this time. Now the actual content of this cell, for example, you can see it in the formula bar, 2/1/2011, so down here 3/1/11, Enter, and you see what's happening there. So you can enter either one. And sometimes you just type them in. 4/1/11 and over here this value is 200.
So by converting the data into a table-- and there are some other advantages to that too, some of which are just formatting. But even if you haven't use that feature very heavily, this is an ideal way to set up certain charts so that they really do become dynamic, and it makes the chart grow and grow and grow and grow. Now in the process here, if we do this month after month into other years as well, we might want to consider other aspects of the design. But the main point here is we've made it dynamic simply by converting the source data into a table.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74625 Viewers
80 Video lessons · 129794 Viewers
52 Video lessons · 64006 Viewers
59 Video lessons · 49787 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.