Spreadsheet growth - due to Pivots

[Bucko]

Has anyone figured out the trick to keep a spreadsheet from "growing"
in size as pivot tables are added (and based on the SAME dataset)? If
my kids grew at the rate that my spreadsheets are growing, I'd have to
sell them for medical experiments!

For example, my dataset has 15,000 rows, 37 columns. It takes up about
7.44 megs on its own.

I create a pivot based on that dataset, to filter the data into
certain categories. From ONE of those categories, I create another
dataset within the same spreadsheet (the second dataset contains about
9,000 of the original 15,000 rows).

From the second dataset of 9,000 rows, I create 11 pivots (all
utilizing the same source).

My resulting spreadsheet is 50 megs big!

Could the answer to the growth problem be that behind each and every
pivot table is a dataset (not the source dataset, per-se, but the
"results" dataset) and that means that even though several pivots
share the same source dataset, each of the pivots will have different
result-datasets?

Anyone have any thoughts, confirmations, or recommendations? Thanks,
bundles, in advance!

 

[Ron Coderre]

You've probably noticed that Pivot Tables can be manipulated, using the data
from the last data refresh, even though the source data may have
subsequently been changed. That's because Pivot Tables take a snapshot of
the data and store it in a hidden pivot cache. Consequently, each time you
base a pivot table on the same data, the workbook size increases by the size
of that data.

One way around that issue is to base the 1st Pivot Table on the source data,
then base all subsequent Pivot Tables (that would normaly use the same
source) on the original Pivot Table.

Here's how:
Selecting <data><pivot table> from the Excel Main Manu
Step_1: Select the data source.
Select this option: Another PivotTable report of PivotChart report.

You might think that the new Pivot Table would only use the visible data of
the referenced Pivot Table, but it doesn't. It means the new Pivot Table
will share the same pivot cache as the referenced Pivot Table, avoiding the
creation of a new pivot cache. Note: the new Pivot Table can view the data
differently from the original Pivot Table and is not restricted in its
analysis capabilities. Also, I'm pretty sure if you refresh any Pivot Table
using a shared pivot cache...ALL Pivot Tables sharing that pivot cache are
also refreshed.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Bucko]

Hey thanks Ron!!

It does help - and I appreciate the really quick reply!

Actually, of those 11 pivots I created (from the second data-set), I
created the first from scratch, then copied the tab that pivot was in
to a new tab, renamed the new tab, and modified the pivot on the newly
created tab to do something different. Likewise for the other pivots.
That's the long way of saying that I'm thinking that those pivots of
mine DO share the same hidden cache of data. And you're so right - a
refresh of one causes all to refresh. In addition to that, certain
modifications to one can affect the rest (like groupings and such).
That little feature has caused a little headache from time to time
too, by the way.

Anyway, I guess that given that knowledge, it surprises me that the
addition of the 11 tables would cause the size of the spreadsheet to
grow the way mine has. I would expect the spreadsheet to grow to
maybe 25 megs (7.4 for the initial dataset, 7.4 for the hidden cache,
5 megs for the sub-universe dataset, 5 megs for that sub-universe
hidden cache), but 50 megs?? That's the part that's got me befuddled.

 

[Ron Coderre]

Debra Dalgleish has a UDF on her website that returns the amount of memory
used by the pivot cache:
http://www.contextures.com/xlPivot11.html#Memory

See if that sheds any light on the problem.

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Bucko]

Sweet! Thank you Ron Going to give it a whirl now.

That Debra sure is something, ain't she?