Join Dennis Taylor for an in-depth discussion in this video Creating an automatically expanding chart by basing it on a table, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating an automatically expanding chart by basing it on a table