Large file with Pivot Tables

[sparky2205]

Hi folks,
I have a spreadsheet with a few pivot tables in it. All worked fine last year.
I've just update the file for this year, i.e. using the same template enter this year's data.
But when saving the file it becomes very large 40MB+ and takes a while to save.
I assumed it was due to extra formatting on the sheet which went all the way to the last row.

I went back to the template, opened it, "Cleared All" below row 3000 and saved the file. The file size now jumps from 9MB to 45MB, even though I deleted formatting.
So, I went back to the original template and did the following:
Set "Number of items to retain per field:" to None, Cleared All" as previously, saved the file. The file size jumps to 45MB.
So, I went back to the original template and did the following:
Unticked "Save source data with file", "Cleared All" as previously, saved the file. The file size jumps to 45MB.
So, I went back to the original template and did the following:
Set "Number of items to retain per field:" to None, Unticked "Save source data with file", Cleared All" as previously, saved the file. The file size jumps to 45MB.

Does anyone have any ideas on how to prevent the big jump in file size?
Is there some other pivot table setting I'm missing?

 

[sandy666]

- create copy of workbook
- in original workbook:

- delete Data tab​

- save​

- New Tab​

- Rename​

- copy Headers and two rows to new tab from copy of workbook​

- add your vba buttons​

- save​

size: ca 102 kb
test if you vba works (I didn't test because I don't touch vba)
IMHO it doesn't make sense to fill all 1048576 rows with table/formatting, etc.