Data range for Pivot table

[Susanne]

Seems quite simple, but I simply can not seem to find out how to change the
data range of an exisiting pivot table.

Every month I add rows to this worksheet, and every month I create a new
pivot table. I simply can not find how to just expand the data range instead?

 

[CyberTaz]

Hi Susanne -

You'll probably get some additional suggestions that may be more elegant
but this is one option that may help in the meantime...

The new rows don't get picked up automatically if they are appended to the
bottom of the existing list even when you click Refresh - they do get
included if inserted within the existing defined range. So, to add the new
rows (that have been added at the bottom), click the Pivot Table Wizard
Button on the PT Toolbar then click the Back button & edit the range to
include the additional rows, then Finish.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Seems quite simple, but I simply can not seem to find out how to change the
data range of an exisiting pivot table.

Every month I add rows to this worksheet, and every month I create a new
pivot table. I simply can not find how to just expand the data range instead?

One way is to, with the PT selected, invoke the PT Wizard from the PT
toolbar, click Back, and reenter your range.

A better way is to use a dynamic range as your PT source. For instance,
if your data is in Sheet1, columns A:F, then define a name using
Insert/Name/Define:

Name in workbook: MyTable
Refers to:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),6)

Then, in creating the PT, use

=MyTable

as the source.

 

[the3rdParty]

One way is to, with the PT selected, invoke the PT Wizard from the PT
toolbar, click Back, and reenter your range.

A better way is to use a dynamic range as your PT source. For instance,
if your data is in Sheet1, columns A:F, then define a name using
Insert/Name/Define:

Name in workbook: MyTable
Refers to:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),6)

Then, in creating the PT, use

=MyTable

as the source.

I was faced with the same issue on a regular basis [until I got the XL-
FM ODBC link to work ] and did something very similar to that
suggested above but a bit simpler.

1. Get the data into the spreadsheet
2. Leave a blank row at the bottom of the data and then write a piece
of text in col A in the row beyond this to mark what will be the end
of your data range. Why do this? well I used to find that that
sometimes I got confused and inserted the new data outside of the data
range. With a marker in the last row I always knew whether or not the
data had gone into the correct place.
3. Select the data range incl column headings and that last row
containing the text marker in col A.
4.In the cell reference box at the far left of the formula bar type in
a name for the data range like MyTable and press Enter (this avoids
the insert/name/define step indicated above )
5 Define your pivot table and just put MyTable in the range box. If
the presence of the marker interferes with the pivot table, right
click the first cell in the row you dont want in the pivot table and
select 'hide'

When you want to insert more data in the data range just insert rows
above that data marker and then refresh the pivot table



All roads lead to Rome, but there are many of them.....

james

 

[bababa]

I've read everyone's responses and have to wonder why you wouldn't just select the columns as the data range...then it refreshes automatically if additional data is added.

I'm sure there's a reason - it just doesn't come to mind.