pivot table file size

[Rocket123]

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

 

[Alex Blakenburg]

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]

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]

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.