Excel - Workbook pivot table design function

Asked By Jazz

20-Feb-10 06:46 PM

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?

21-Feb-10 02:38 AM

One thought is to simply set the pivot's source range (In step 2 of the PT
wizard) to cover the max expected extent of source data at the onset, then
configure the base PT format. Then just refresh the PT to recalc for any new
data added. The PT format should generally remain intact. Any help? hit YES
below.
--
Max
Singapore
---

21-Feb-10 11:55 AM

Hi Max, thank you for the feedback.

I understand what you meant when you said set the pivot's source range to
cover the max extent of source data.  For instance if my range is
$A$1:$AI$500 that would mean change it to $A$1:$AI$60000 in Step 2 of Pivot
table wizard.

I need help figuring out how to

-Configure the base PT format

-Refresh the PT to recalc for any new data added

Could you explain those steps to me as well?

21-Feb-10 05:54 PM

But that is simply too great an increase? Do a self estimate based on say,
the last 3 monthly variations in the extent (if its monthly data), then go
for the smallest increase just large enough

By base, its meant that you create your own pivot w/o applying any hidden
items

Right-click anywhere inside the PT, then click the exclamation button to
refresh. The pivot needs to be refreshed each time there is a change in
underlying source data
--
Max
Singapore
---

21-Feb-10 06:46 PM

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.

items"  I think I know but I want to make sure I got it right.

These are the steps I have 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

21-Feb-10 09:30 PM

Hi,

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.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

21-Feb-10 10:07 PM

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.
--
Max
Singapore
---