Pivot Table File Size Question


1

1genxer

I have a file with about 8,000 rows of data and about 30 columns. I have
about 26 pivot tables that are in one of the sheets. The file is over 13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every month.
Thanks!!!
 
Ad

Advertisements

R

Ron Coderre

Each new pivot table creates a hidden copy of the source data. That's how you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1 of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP
 
T

Thomas [PBD]

1genxer,

You are correct, a file containing PivotTables will become exponentially
larger the more data that you add to the file because it does infact copy a
"Temporary" image of all the records that it is referencing. So, with 26
PivotTables you have all in all 27 actual copies of the data (including the
original).
There are a couple alternatives, some more unweildy than others.
A) You could break all the links to the data in the PivotTables, however you
cannot update them anymore unless you reattach the link.
B)You could create a separate file for the DATA and another for the
PivotTables. They would link across to each other, even if the data book is
closed.
C)You could place this information, which sounds like a database (?), into
Access and run Queries to create your PivotTables. However, I must note that
PivotTables in Access do not work the same as they do in Excel (similar, but
not the same).
 
1

1genxer

Is there a way to do that outside the Wizard as my tables are already built?
I really don't want to have to build them from scratch again.

Ron Coderre said:
Each new pivot table creates a hidden copy of the source data. That's how you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1 of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


1genxer said:
I have a file with about 8,000 rows of data and about 30 columns. I have
about 26 pivot tables that are in one of the sheets. The file is over 13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every month.
Thanks!!!
 
R

Roger Govier

Hi

From Debra Dalgleish's "Excel Pivot Tables Recipe Book"

Sub ChangeAllPivotCaches()
Dim pt as PivotTable
Dim ws as Worksheet
On Error Goto err_handler
For each ws in ActiveWorkbook.Worksheets
For Each pt in ws.PivotTables
pt.CacheIndex = Activecell.PivotTable.CacheIndex
Next pt
Nex ws
Exit Sub
err_Handler
Msgbox "Cache Index could not be changed"
End Sub

Place cursor in any cell of the first PT you created, then run the macro
--
Regards
Roger Govier

1genxer said:
Is there a way to do that outside the Wizard as my tables are already
built?
I really don't want to have to build them from scratch again.

Ron Coderre said:
Each new pivot table creates a hidden copy of the source data. That's how
you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source
data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1
of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the
others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


1genxer said:
I have a file with about 8,000 rows of data and about 30 columns. I
have
about 26 pivot tables that are in one of the sheets. The file is over
13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is
it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every
month.
Thanks!!!
 
R

Ron Coderre

You can:
• Right-click on a pivot table
• Select the Pivot Table wizard
• Click the [Back] buttons until "Step 1 of 3"
• Change the pivot table source to "Another Pivot Table report"
• Select the pivot table that will be the source for al of the others
• Click [Finish}
Repeat for each of the other pivot tables.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


1genxer said:
Is there a way to do that outside the Wizard as my tables are already built?
I really don't want to have to build them from scratch again.

Ron Coderre said:
Each new pivot table creates a hidden copy of the source data. That's how you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1 of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


1genxer said:
I have a file with about 8,000 rows of data and about 30 columns. I have
about 26 pivot tables that are in one of the sheets. The file is over 13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every month.
Thanks!!!
 
Ad

Advertisements

1

1genxer

Thanks everyone for all your help! It is not a database that I am working
with, but rather the results of surveys given overtime. So, as mentioned
above the data gets larger every month. I will try these methods and if
something doesn't work I will get back on here. Thanks again!
 
1

1genxer

Can you tell me exactly what this macro does? I am not very educated in code
like that.

Roger Govier said:
Hi

From Debra Dalgleish's "Excel Pivot Tables Recipe Book"

Sub ChangeAllPivotCaches()
Dim pt as PivotTable
Dim ws as Worksheet
On Error Goto err_handler
For each ws in ActiveWorkbook.Worksheets
For Each pt in ws.PivotTables
pt.CacheIndex = Activecell.PivotTable.CacheIndex
Next pt
Nex ws
Exit Sub
err_Handler
Msgbox "Cache Index could not be changed"
End Sub

Place cursor in any cell of the first PT you created, then run the macro
--
Regards
Roger Govier

1genxer said:
Is there a way to do that outside the Wizard as my tables are already
built?
I really don't want to have to build them from scratch again.

Ron Coderre said:
Each new pivot table creates a hidden copy of the source data. That's how
you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source
data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1
of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the
others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


:

I have a file with about 8,000 rows of data and about 30 columns. I
have
about 26 pivot tables that are in one of the sheets. The file is over
13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is
it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every
month.
Thanks!!!
 
1

1genxer

I can't find the Pivot Table Wizard in 2007. How do I find where to make this
change in 2007?

Ron Coderre said:
Each new pivot table creates a hidden copy of the source data. That's how you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1 of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


1genxer said:
I have a file with about 8,000 rows of data and about 30 columns. I have
about 26 pivot tables that are in one of the sheets. The file is over 13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every month.
Thanks!!!
 
R

Roger Govier

Hi

Copy the Code from my previous posting
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select cell within your first PT
Alt+F8 to bring up Macros
Highlight the macro name
Run

It will cycle through each of the PT's in turn, setting the cache that each
uses to be that from the first PT, rather than you doing each one manually.
Save and Close your workbook and the file size should be reduced.
--
Regards
Roger Govier

1genxer said:
Can you tell me exactly what this macro does? I am not very educated in
code
like that.

Roger Govier said:
Hi

From Debra Dalgleish's "Excel Pivot Tables Recipe Book"

Sub ChangeAllPivotCaches()
Dim pt as PivotTable
Dim ws as Worksheet
On Error Goto err_handler
For each ws in ActiveWorkbook.Worksheets
For Each pt in ws.PivotTables
pt.CacheIndex = Activecell.PivotTable.CacheIndex
Next pt
Nex ws
Exit Sub
err_Handler
Msgbox "Cache Index could not be changed"
End Sub

Place cursor in any cell of the first PT you created, then run the macro
--
Regards
Roger Govier

1genxer said:
Is there a way to do that outside the Wizard as my tables are already
built?
I really don't want to have to build them from scratch again.

:

Each new pivot table creates a hidden copy of the source data. That's
how
you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source
data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step
1
of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the
others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


:

I have a file with about 8,000 rows of data and about 30 columns. I
have
about 26 pivot tables that are in one of the sheets. The file is
over
13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file,
is
it
the full size of the data it refrences? B) Is there a way to reduce
the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy
to
update and manipulate when opened, and the file just gets bigger
every
month.
Thanks!!!
 
B

bruce mcpherson

Here is some code to cycle through your workbook and tell you which pivot tables are using which cache

Sub PivotCacheReport()

Dim pc As PivotCache
Dim s As String, sn As String
Dim ws As Worksheet
Dim pt As PivotTable

With ActiveWorkbook
For Each pc In .PivotCaches
s = "Pivotcache " & CStr(pc.Index) & " uses " & CStr(pc.MemoryUsed) & " and has " _
& CStr(pc.RecordCount) & " records"
s = s & Chr(10) & "The following pivot tables use it"

For Each ws In .Worksheets
sn = ws.Name
For Each pt In ws.PivotTables
If pt.CacheIndex = pc.Index Then

If Len(sn) > 0 Then
s = s & Chr(10) & sn & Chr(10)
sn = ""
End If
s = s & Replace(pt.Name, "PivotTable", "PT") & ","
End If
Next pt
Next ws
MsgBox (s)
Next pc


sn = Chr(10) & "Couldnt find the pivotcache for these pivot tables"
s = ""
For Each ws In .Worksheets
For Each pt In ws.PivotTables
If pt.CacheIndex < 1 Or pt.CacheIndex > .PivotCaches.count Then
s = s & Chr(10) & ws.Name & ":" & Replace(pt.Name, "PivotTable", "PT")
End If
Next pt
Next ws
If (Len(s) > 0) Then
MsgBox (sn & s)
End If
End With


End Sub



Roger Govier wrote:

HiCopy the Code from my previous postingAlt+F11 to invoke the VB
30-Sep-08

H

Copy the Code from my previous postin
Alt+F11 to invoke the VB Edito
Insert>Modul
Paste code into white pane that appear
Alt+F11 to return to Exce

To us
Select cell within your first P
Alt+F8 to bring up Macro
Highlight the macro nam
Ru

It will cycle through each of the PT's in turn, setting the cache that each
uses to be that from the first PT, rather than you doing each one manually
Save and Close your workbook and the file size should be reduced
--
Regard
Roger Govie


Previous Posts In This Thread:

Pivot Table File Size Question
I have a file with about 8,000 rows of data and about 30 columns. I have
about 26 pivot tables that are in one of the sheets. The file is over 13MB.
If I delete the pivots my file size drops to only 6MB

My question is, A) how much space does a Pivot Table take in a file, is it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every month.
Thanks!!!

Each new pivot table creates a hidden copy of the source data.
Each new pivot table creates a hidden copy of the source data. That's how you
can keep working with the same pivot table data eve if the source data
changes

You have the option to make pivot tables share the same cached source data
when you create the pivot table

If you open the pivot table wizard and click the [back] button to Step 1 of
3..
Select the 4th option button (Another Pivot Table report...

Then click the [Next] buttons, to verify the remaining settings
The file size will decrease
Do that for all but the one pivot table that will be the basis for the others

Does that help
**********
Regards
Ro

XL2003, WinX

:

1genxer,You are correct, a file containing PivotTables will become
1genxer

You are correct, a file containing PivotTables will become exponentially
larger the more data that you add to the file because it does infact copy a
"Temporary" image of all the records that it is referencing. So, with 26
PivotTables you have all in all 27 actual copies of the data (including the
original).
There are a couple alternatives, some more unweildy than others.
A) You could break all the links to the data in the PivotTables, however you
cannot update them anymore unless you reattach the link.
B)You could create a separate file for the DATA and another for the
PivotTables. They would link across to each other, even if the data book is
closed.
C)You could place this information, which sounds like a database (?), into
Access and run Queries to create your PivotTables. However, I must note that
PivotTables in Access do not work the same as they do in Excel (similar, but
not the same).

--
--Thomas [PBD]
Working hard to make working easy.


:

Is there a way to do that outside the Wizard as my tables are already built?
Is there a way to do that outside the Wizard as my tables are already built?
I really do not want to have to build them from scratch again.

:

HiFrom Debra Dalgleish's "Excel Pivot Tables Recipe Book"Sub
Hi

From Debra Dalgleish's "Excel Pivot Tables Recipe Book"

Sub ChangeAllPivotCaches()
Dim pt as PivotTable
Dim ws as Worksheet
On Error Goto err_handler
For each ws in ActiveWorkbook.Worksheets
For Each pt in ws.PivotTables
pt.CacheIndex = Activecell.PivotTable.CacheIndex
Next pt
Nex ws
Exit Sub
err_Handler
Msgbox "Cache Index could not be changed"
End Sub

Place cursor in any cell of the first PT you created, then run the macro
--
Regards
Roger Govier


RE: Pivot Table File Size Question
You can:
??? Right-click on a pivot table
??? Select the Pivot Table wizard
??? Click the [Back] buttons until "Step 1 of 3"
??? Change the pivot table source to "Another Pivot Table report"
??? Select the pivot table that will be the source for al of the others
??? Click [Finish}
Repeat for each of the other pivot tables.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


:

Thanks everyone for all your help!
Thanks everyone for all your help! It is not a database that I am working
with, but rather the results of surveys given overtime. So, as mentioned
above the data gets larger every month. I will try these methods and if
something doesn't work I will get back on here. Thanks again!

:

Can you tell me exactly what this macro does?
Can you tell me exactly what this macro does? I am not very educated in code
like that.

:

I can't find the Pivot Table Wizard in 2007.
I cannot find the Pivot Table Wizard in 2007. How do I find where to make this
change in 2007?

:

HiCopy the Code from my previous postingAlt+F11 to invoke the VB
Hi

Copy the Code from my previous posting
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select cell within your first PT
Alt+F8 to bring up Macros
Highlight the macro name
Run

It will cycle through each of the PT's in turn, setting the cache that each
uses to be that from the first PT, rather than you doing each one manually.
Save and Close your workbook and the file size should be reduced.
--
Regards
Roger Govier



Submitted via EggHeadCafe - Software Developer Portal of Choice
How to Annotate Images from a database in a web page
http://www.eggheadcafe.com/tutorial...9-082c24acd999/how-to-annotate-images-fr.aspx
 
Ad

Advertisements

J

Josh K

Hello - I cannot figure out how to link new copies of the same pivot tables in Excel 2007 back to 1 pivot table. As a result, I have 30 pivot tables across 5 tabs causing the file size to be 100 Megs.

I remember how to change the link/source in Excel 2003 to have each pivot point back to 1 main pivot, but I cant figure it out in Excel 2007.

Can anyone help me?

Thank you so much!
Josh
I have a file with about 8,000 rows of data and about 30 columns. I have
about 26 pivot tables that are in one of the sheets. The file is over 13MB.
If I delete the pivots my file size drops to only 6MB.

My question is, A) how much space does a Pivot Table take in a file, is it
the full size of the data it refrences? B) Is there a way to reduce the
amount of file space of the Pivot Table?

Any help would be much appreciated as a 13 MB file is very unwieldy to
update and manipulate when opened, and the file just gets bigger every month.
Thanks!!!
On Tuesday, September 30, 2008 1:09 PM RonCoderr wrote:
Each new pivot table creates a hidden copy of the source data. That's how you
can keep working with the same pivot table data eve if the source data
changes.

You have the option to make pivot tables share the same cached source data
when you create the pivot table.

If you open the pivot table wizard and click the [back] button to Step 1 of
3...
Select the 4th option button (Another Pivot Table report...)

Then click the [Next] buttons, to verify the remaining settings.
The file size will decrease.
Do that for all but the one pivot table that will be the basis for the others.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"1genxer" wrote:
On Tuesday, September 30, 2008 1:12 PM ThomasPB wrote:
1genxer,

You are correct, a file containing PivotTables will become exponentially
larger the more data that you add to the file because it does infact copy a
"Temporary" image of all the records that it is referencing. So, with 26
PivotTables you have all in all 27 actual copies of the data (including the
original).
There are a couple alternatives, some more unweildy than others.
A) You could break all the links to the data in the PivotTables, however you
cannot update them anymore unless you reattach the link.
B)You could create a separate file for the DATA and another for the
PivotTables. They would link across to each other, even if the data book is
closed.
C)You could place this information, which sounds like a database (?), into
Access and run Queries to create your PivotTables. However, I must note that
PivotTables in Access do not work the same as they do in Excel (similar, but
not the same).

--
--Thomas [PBD]
Working hard to make working easy.


"1genxer" wrote:
??? Right-click on a pivot table
??? Select the Pivot Table wizard
??? Click the [Back] buttons until "Step 1 of 3"
??? Change the pivot table source to "Another Pivot Table report"
??? Select the pivot table that will be the source for al of the others
??? Click [Finish}
Repeat for each of the other pivot tables.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"1genxer" wrote:
 

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