how do I extend an existing pivot table's range

[Guest]

I have an existing pivot table that, after some work, is producing the
results my boss and I are after.

Now, in the spreadsheet that is the base for the pivot table, several dozen
new rows have been added.

Is it possible to extend the range that the existing pivot table is based
on, or even to change it? I really don't want to have to go through
re-creating it.

tia

 

[Guest]

If you right click on the pivot table, select pivot table wizard, select back
and then re input the range into the box you can do this.

 

[Roger Govier]

Hi Chris

which version of Excel do you have?
If it is XL2003, click on a cell in your source data then Data>List
Create List.
This will create a named list which is dynamic and will grow as your
data grows and will add formulae (if necessary) to any new line added.
ON the PT itself, right click a cell >PT wizard>back>Give the source as
=LIst1 or whatever your list is named.

If you have XL2002 or lower, then create a Dynamic Range
Insert>Name>Define>
Name myRange
Refers to =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
This assumes your data starts in column A and your headers are in row 1.
Adjust as appropriate.
It also assumes that column A has contiguous data with no gaps. If that
is not the case, choose a column for the first COUNTA where this will be
true.

Again in the PT wizard, give the source as being =myRange

For more help on Dynamic Ranges take a look at Debra Dalgleish's site
http://www.contextures.com/xlNames01.html#Dynamic

 

[Guest]

Thank You!

If you right click on the pivot table, select pivot table wizard, select back
and then re input the range into the box you can do this.

 

[Guest]

Hi,

There is no need to name the list or use the list name for the pivot table
source. As long as you indicate the range Excel will take care of the rest
automatically. (Excel 2003 or 2007).