Joe sends in a tip about converting your pivot table source data to a dynamic range. While this used to mean using the OFFSET function, now it simply means using Ctrl L (in Excel 2003) or Ctrl T (in Excel 2007). Episode 825 shows you how.
This blog is the video 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 blog is the video 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. Alright. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's tip is sent in by Joe Martin.
Now, Joe is a frequent contributor to the podcast.
He was watching one of the podcasts, and, as I created the pivot table, you know, I just created the pivot table on a static range, and, you know, I think that's what a lot of us do because we were creating pivot tables back since, you know, what, 5 or 6 versions of Excel.
So, DATA, PIVOT TABLE, and then, in step 2, we just leave it there as A1 through H564.
Well, the problem with that is if, later, if we add more rows, then what do we have to do?
We have to come back to the wizard and respecify, and so, Joe said, you know, it’d be much easier just to specify a dynamic range, and we have a great new way to do this starting in Excel 2003 and newer.
Now, in Excel 2003, what you're going to do is select one cell in your data and press CONTROL+L. CONTROL+L is going to define that range as a list.
Now, in Excel 2007, they replace this with CONTROL+T. Either way, same basic concept.
So, we'll create DATA, PIVOT TABLE, FINISH, and let me just put REGION along the left-hand side and REVENUE in the heart of the pivot table.
So, $6.7million.
Now, let's say that we get some more records from somewhere.
I'm just going to copy a bunch of records and we paste those immediately below our table.
Notice down there, in row 565, that little *, kind of like an access the new record *. That's saying, hey, anything you paste here is going to become part of the table.
I do CONTROL+V and, sure enough, that causes all of those new records to become part of the table, and any charts or pivot tables that refer to that table are going to work perfectly.
So, all we have to do, we don't have to respecify the data range, simply click the ! to refresh the data and it gets us the new data.
Great way to do dynamic ranges in Excel 2003 and newer.
Now, before that, we would have had to define a name with the offset function.
Much, much harder to do.
Simply easier, in 2003, to hit CONTROL+L or, in 2007, CONTROL+T to convert your pivot table range to a dynamic list or table.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today's tip is sent in by Joe Martin.
Now, Joe is a frequent contributor to the podcast.
He was watching one of the podcasts, and, as I created the pivot table, you know, I just created the pivot table on a static range, and, you know, I think that's what a lot of us do because we were creating pivot tables back since, you know, what, 5 or 6 versions of Excel.
So, DATA, PIVOT TABLE, and then, in step 2, we just leave it there as A1 through H564.
Well, the problem with that is if, later, if we add more rows, then what do we have to do?
We have to come back to the wizard and respecify, and so, Joe said, you know, it’d be much easier just to specify a dynamic range, and we have a great new way to do this starting in Excel 2003 and newer.
Now, in Excel 2003, what you're going to do is select one cell in your data and press CONTROL+L. CONTROL+L is going to define that range as a list.
Now, in Excel 2007, they replace this with CONTROL+T. Either way, same basic concept.
So, we'll create DATA, PIVOT TABLE, FINISH, and let me just put REGION along the left-hand side and REVENUE in the heart of the pivot table.
So, $6.7million.
Now, let's say that we get some more records from somewhere.
I'm just going to copy a bunch of records and we paste those immediately below our table.
Notice down there, in row 565, that little *, kind of like an access the new record *. That's saying, hey, anything you paste here is going to become part of the table.
I do CONTROL+V and, sure enough, that causes all of those new records to become part of the table, and any charts or pivot tables that refer to that table are going to work perfectly.
So, all we have to do, we don't have to respecify the data range, simply click the ! to refresh the data and it gets us the new data.
Great way to do dynamic ranges in Excel 2003 and newer.
Now, before that, we would have had to define a name with the offset function.
Much, much harder to do.
Simply easier, in 2003, to hit CONTROL+L or, in 2007, CONTROL+T to convert your pivot table range to a dynamic list or table.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
