pivot table file size

Rocket123

New Member
Joined
May 24, 2016
Messages
16
Hello All,

I have a question about Pivot Tables.

I have a pivot table template that has 3 pivot tables (all on separate sheets) and a data dump table.
This data dump table is on a TABLE that has 7 columns of formulas on the far right of the table.
When the file does not have any data in it, it is about 5.6 MB.
When the file is ready to accept a data dump, I use a macro to increase the size of the table from 9 row to 500,000 rows.
I recently dumped about 200K lines of data into the file which increased the size of the file to 76MB.
When I was done using the file, I used my macro to clear the data and also resize the table in the data dump sheet to 9 rows and then saved the file.
I checked the size of the file and it doubled from 5.6 MB to 10 MB. What happened? Where is this extra data coming from? How can I reduce the file size back to 5.6 MB?

Thank you,
Rocket
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
793
Office Version
  1. 365
Platform
  1. Windows
After you cleared the data did you also refresh the Pivot Tables to clear the the Pivot Cache ?
Also if you reopen the workbook does ctrl + End on each sheet stop where the data stops ?
 

Rocket123

New Member
Joined
May 24, 2016
Messages
16
After you cleared the data did you also refresh the Pivot Tables to clear the the Pivot Cache ?
Also if you reopen the workbook does ctrl + End on each sheet stop where the data stops ?
Hi Alex,

I use a macro to clear all data in my data dump sheet (500K rows only). The macro also does a re fresh of all active workbooks.
I only have one data dump worksheet.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
793
Office Version
  1. 365
Platform
  1. Windows
Hi Alex,

I use a macro to clear all data in my data dump sheet (500K rows only). The macro also does a re fresh of all active workbooks.
I only have one data dump worksheet.

I am a bit unclear on the reference to "re fresh of all active workbooks".

I was assuming that the 3 pivot tables and the data dump worksheet were all in the one workbook.
So I was just trying to confirm that you did a "Refresh All" in that workbook after deleting all the rows in the Data Dump.

I was also trying to get confirmation that all the sheets in that same workbook have been checking to see where Ctrl+End takes you and that none of them take you way past where the data finishes on each sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top