From: Ashish Mathur on

Just convert the pivot table source data to a List/Table (in Excel 2007) by
pressing Ctrl+L. Select the data including the header row and press Ctrl+L.
When one converts a range to a List/Table, it becomes auto expanding.

Hope this helps.


Ashish Mathur
Microsoft Excel MVP

"Jazz" <Jazz(a)> wrote in message
> I used Sheet1 as the data source to create a pivot table in a new
> worksheet
> which I named Sheet PT.
> Is it possible to design the workbook so that I can delete the data in
> Sheet1 and then paste new data with more or less rows in Sheet1 and have
> the
> pivot table in Sheet PT update to reflect the new data that has been
> pasted
> into Sheet1 but the formating of the pivot table stays the same?
From: Max on
The refrain given was based on my own experience working with PTs. Sometimes,
refreshing PTs with "filters" applied would unravel some aspects of it, and
you need to then re-apply it again post refresh. And sometimes its ok. Best
then that you experiment and build it up from your own experience.
"Jazz" wrote:
> Phenomenal! Thanks for clarifying Max.
> I have two questions remaining. I am sure I will feel silly when I realize
> what you meant but can you explain this statement.
> "By base, its meant that you create your own pivot w/o applying any hidden
> items" I think I know but I want to make sure I got it right.
> These are the steps I've been taking. Did I interpret all your instructions
> correctly?
> - Created the pivot table
> - Return to its source data and delete it
> - Entered new source data
> - Return to pivot table, right click, and choose Refresh Data
> - Click one of the downward arrows on the pivot table and de-select (blank)
> so the blank rows of data are hidden.
> Thanks for your time,
> Jazz