Ally asks how to have a chart automatically extend as you type new data. The classic solution is to use a pair of dynamic named ranges using OFFSET. While this can be painful, Episode 993 shows how to set it up. Be sure to watch Episode 994 tomorrow for an easier way in Excel 2003-2007.
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 am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Ellie .
Ellie sent this in via YouTube.
She has a chart that currently is showing some data.
When she types new data to the right of the data, would like the chart to automatically extend.
Now, I'm going to talk about how to use a Dynamic named range to solve this.
Today and tomorrow, we'll take a look at an easier way to do it.
Particularly for an Excel 2003 or in Excel 2007.
Here is a notice today.
I'm back in Excel 2003.
I swear wouldn't do this but I've tried this trick now several times in Excel 2007.
It is not working.
It's working in Excel 2003.
So, let's show how it works, and then if you're in Excel 2007.
you can use the new method tomorrow.
A dynamic named range is a named range that generally uses the OFFSET function the OFFSET function.
Hard to understand.
Amazing function that can take up to five arguments.
Specify what the starting cell is.
It always has to have an anchor cell.
So in this case, we're going to create a dynamic named range that refers to this yellow range right here.
But will grow as we add new items to the right hand side.
So, of course the starting cell is B2, and I want to lock that down, So, starting range is going to SHEET1!$B$2 The number of Rows down, they give you an opportunity to say hey, from that starting cell, we want to go some number rows down.
In this case it's 0 and some number of rows to the right.
Well, in this case again, that's 0.
They let you specify how tall the range is going to be.
Well, It's only one row tall but the whip.
now this is the thing that's going to change every time.
As we add new data the right hand side, the width going to get wider and wider and wider.
So I'm going to use a formula here =COUNT =COUNT(sheet1!$2:$2) Let's just see how that works.
See right now, It says there's six numbers in this range.
If we type a new number out here that formula updates to show seven numbers in that range, So let me undo that and here I'm going to change this back to just text.
Put an apostrophe before it.
So, these are kind of my notes here.
I always have trouble with these dynamic named ranges.
I like to plan them out before I even start.
We're going to find a second range for the chart labels.
And that is pretty cool because that's going to be...
Chart data is the starting point.
Number of Rows down -1 in other words one row above our data.
Set number of Columns to the right 0.
and we're not going to specify anything for the height and width to make it be the same height and width as Chart labels.
Alright! So now, we're ready to do insert name to find.
So we kind of appear to Insert>Name>Define and Chart Data.
So, this going to be a sheet1 exclamation chart Data.
It refers to equal OFFSET And I'm going to press the F2 key.
Here right now, I'm in enter mode.
If I would try and use the End key or any of the arrow keys, it would start inserting cell addresses.
I press F2.
You'll see down here.
It says enter mode.
Press F2, it gets into edit mode.
That allows me to move right and left in here.
So, we started out from dollar sign B dollar sign 2, is our starting cell.
I have notes over here that show me that I want to put in a 0,0,1 and then finally count(sheet1!$2:$2 close the parenthesis for the count.
Close the parenthesis for the OFFSET and add that name All right now, the next thing that we're going to have is Sheet1 !Chart Labels.
And this is going to be an OFFSET function of sheet one chart data comma minus one.
That says go one row up from there comma 0 Click add.
So, we have our two dynamic named ranges.
Now click Ok.
We're going to go into the chart.
Save the workbook before you do this.
There's four elements here.
The first one is the label for the series.
We're not going to change that.
The next one is the labels.
So, here where it Says B1 to G1.
We're gonna say, type our named range there.
Sheet1 of chart labels and then here, where it has the data B2 to G2.
We're going to type our name range of chart data.
Make sure everything looks okay.
We press Enter and the line stays there.
That's great!
Now here's the cool thing.
Here's how this works.
When we come back next week, and we type week 7, nothing changes.
But as soon as we put a number in here 150.
You watch the chart.
It automatically updates.
Very cool isn't it.
Alright! So, there you have it a dynamic named range.
This is the way people have been solving this problem for a long time.
Now, in Excel 2003 and Excel 2007, there's easier ways to go.
We'll take a look at that maybe you just avoid the whole OFFSET function And get the same result.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Ellie .
Ellie sent this in via YouTube.
She has a chart that currently is showing some data.
When she types new data to the right of the data, would like the chart to automatically extend.
Now, I'm going to talk about how to use a Dynamic named range to solve this.
Today and tomorrow, we'll take a look at an easier way to do it.
Particularly for an Excel 2003 or in Excel 2007.
Here is a notice today.
I'm back in Excel 2003.
I swear wouldn't do this but I've tried this trick now several times in Excel 2007.
It is not working.
It's working in Excel 2003.
So, let's show how it works, and then if you're in Excel 2007.
you can use the new method tomorrow.
A dynamic named range is a named range that generally uses the OFFSET function the OFFSET function.
Hard to understand.
Amazing function that can take up to five arguments.
Specify what the starting cell is.
It always has to have an anchor cell.
So in this case, we're going to create a dynamic named range that refers to this yellow range right here.
But will grow as we add new items to the right hand side.
So, of course the starting cell is B2, and I want to lock that down, So, starting range is going to SHEET1!$B$2 The number of Rows down, they give you an opportunity to say hey, from that starting cell, we want to go some number rows down.
In this case it's 0 and some number of rows to the right.
Well, in this case again, that's 0.
They let you specify how tall the range is going to be.
Well, It's only one row tall but the whip.
now this is the thing that's going to change every time.
As we add new data the right hand side, the width going to get wider and wider and wider.
So I'm going to use a formula here =COUNT =COUNT(sheet1!$2:$2) Let's just see how that works.
See right now, It says there's six numbers in this range.
If we type a new number out here that formula updates to show seven numbers in that range, So let me undo that and here I'm going to change this back to just text.
Put an apostrophe before it.
So, these are kind of my notes here.
I always have trouble with these dynamic named ranges.
I like to plan them out before I even start.
We're going to find a second range for the chart labels.
And that is pretty cool because that's going to be...
Chart data is the starting point.
Number of Rows down -1 in other words one row above our data.
Set number of Columns to the right 0.
and we're not going to specify anything for the height and width to make it be the same height and width as Chart labels.
Alright! So now, we're ready to do insert name to find.
So we kind of appear to Insert>Name>Define and Chart Data.
So, this going to be a sheet1 exclamation chart Data.
It refers to equal OFFSET And I'm going to press the F2 key.
Here right now, I'm in enter mode.
If I would try and use the End key or any of the arrow keys, it would start inserting cell addresses.
I press F2.
You'll see down here.
It says enter mode.
Press F2, it gets into edit mode.
That allows me to move right and left in here.
So, we started out from dollar sign B dollar sign 2, is our starting cell.
I have notes over here that show me that I want to put in a 0,0,1 and then finally count(sheet1!$2:$2 close the parenthesis for the count.
Close the parenthesis for the OFFSET and add that name All right now, the next thing that we're going to have is Sheet1 !Chart Labels.
And this is going to be an OFFSET function of sheet one chart data comma minus one.
That says go one row up from there comma 0 Click add.
So, we have our two dynamic named ranges.
Now click Ok.
We're going to go into the chart.
Save the workbook before you do this.
There's four elements here.
The first one is the label for the series.
We're not going to change that.
The next one is the labels.
So, here where it Says B1 to G1.
We're gonna say, type our named range there.
Sheet1 of chart labels and then here, where it has the data B2 to G2.
We're going to type our name range of chart data.
Make sure everything looks okay.
We press Enter and the line stays there.
That's great!
Now here's the cool thing.
Here's how this works.
When we come back next week, and we type week 7, nothing changes.
But as soon as we put a number in here 150.
You watch the chart.
It automatically updates.
Very cool isn't it.
Alright! So, there you have it a dynamic named range.
This is the way people have been solving this problem for a long time.
Now, in Excel 2003 and Excel 2007, there's easier ways to go.
We'll take a look at that maybe you just avoid the whole OFFSET function And get the same result.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
