In Episode 994, another solution to Ally's problem of how to make a chart expand when you type new data in the worksheet. Today's method shows an easier way to set up a dynamic chart range in Excel 2003 and newer.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we are gonna analyze this. Well let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday Alex send in a question about how we can make a chart automatically expand as we type new data in and I used a dynamic named Range yesterday.
Now, just quickly.
You know one way to do this is not automatically, but fairly easy to do If you click on the chart, you can see the blue outline around the data just grab that lower right handle and you can add the new data to the right hand side.
So, that's one method but the other method that is available in excel 2003 or excel 2007 is where we use either the List function in 2003 or the Table function in 2007.
Now, this top left cell here. I'm gonna type a value there like a label or something like that.
So, we have headings above everything, in 2003 you would use Control+L, in 2007 we use control+T.
My table has headers, that's good click OK.
And now, we've defined a table for that range and the amazing thing about this table is as we add new data.
So, if I come here and type week 7 and type a value, you see that the chart automatically updates, so the chart was based on the table.
We add new Data to the right side of the table and the chart automatically updates.
So, a great way to go without having to worry about using the dynamic named ranges and so on.
Just set up a table control+T, back in Excel 2003 it was control +L, back in Excel 2002 you have to watch yesterday's podcast use the dynamic named ranges and you can solve that problem.
So, I want to thank ali for sending that question in.
I wanna thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we are gonna analyze this. Well let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday Alex send in a question about how we can make a chart automatically expand as we type new data in and I used a dynamic named Range yesterday.
Now, just quickly.
You know one way to do this is not automatically, but fairly easy to do If you click on the chart, you can see the blue outline around the data just grab that lower right handle and you can add the new data to the right hand side.
So, that's one method but the other method that is available in excel 2003 or excel 2007 is where we use either the List function in 2003 or the Table function in 2007.
Now, this top left cell here. I'm gonna type a value there like a label or something like that.
So, we have headings above everything, in 2003 you would use Control+L, in 2007 we use control+T.
My table has headers, that's good click OK.
And now, we've defined a table for that range and the amazing thing about this table is as we add new data.
So, if I come here and type week 7 and type a value, you see that the chart automatically updates, so the chart was based on the table.
We add new Data to the right side of the table and the chart automatically updates.
So, a great way to go without having to worry about using the dynamic named ranges and so on.
Just set up a table control+T, back in Excel 2003 it was control +L, back in Excel 2002 you have to watch yesterday's podcast use the dynamic named ranges and you can solve that problem.
So, I want to thank ali for sending that question in.
I wanna thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
