Pivot Table Data Source

E

evoxfan

I have a pivot table already created; the data source is the next worksheet.

Since then I added some data outside the data source by addicg some rows of
data to the bottom of the data source.

Can the data source for the existing pivot table be change to include these
added rows (if so, how) or do I need to delete this pivot table and create a
new pivot table via the wizard to include all new data?

Thanks in advance for help.
 
K

KC Rippstein hotmail com>

You can just click anywhere in the pivot table, then on the Pivot Table
toolbar click Pivot Table > Wizard...then just hit the "<Back" button until
you get to the part of the wizard where you declare your data source and
change it to the new range.

Even better, use dynamic name ranges in your data source and point to that
instead. For example, on your data source sheet, go to Insert > Name >
Define and add a name called PTData and have it refer to this formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
assuming your data starts in A1. Now it will count how many rows are used
(by counting down column A) and will count how many headers you have in row 1
and adjust the data source accordingly. Go back to your Pivot Table data
source box in the wizard and type:
=Sheet1!PTData
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top