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]

maybe try to save file as xlsb ?

I assume there is data only without any bells&whistles
and maybe re-create pivot tables

 

[sparky2205]

Hi Sandy,
saving as .xlsb does reduce the file size but I'm unfamiliar with using files of this type and if something goes wrong down the line I won't be sure that the file type isn't the problem. But it looks like this may be my only option. Nothing else I've tried has worked.
Also I'd like to fix this issue so I don't have the same problem in the future. I'll look at recreating the pivot tables and selecting the preferred options from the start. Maybe that's the issue.
It's almost like it's holding onto a large cache somewhere but I can't get rid of it.

 

[sandy666]

do you create standard Pivot or from Power Pivot?
and re-create Pivots in xlsb file
better if you delete all pivots, save as xlsb and create Pivots again
do everything on copy not on original file

What's an XLSB File and How Do You Open One?

An XLSB file is an Excel binary workbook file. Microsoft Excel is the primary program used to open these files, but other spreadsheet programs may work, too.

www.lifewire.com

 

[sparky2205]

Thanks Sandy,
I think I'll give the .xlsb a go. I have approx. 30 files which I presume are all going to have this problem.
So saving as .xlsb will be the easiest option.
In the meantime I'll recreate my template.

 

[sandy666]

You are welcome
Have a nice day

 

[sparky2205]

Some more information on this one.
I recreated all the pivot tables in the existing workbook but this didn't fix the issue.
So I decided I'd copy my data worksheets into a new workbook and then start from scratch with the pivot tables i.e. create new worksheets and create new pivot tables on those worksheets with the appropriate settings from the start.
I copied my first data sheet into the new workbook i.e. I copied the first data sheet from the existing template into a new workbook. This data sheet still contained the extra formatting that I was previously deleting. So I deleted that extra formatting, as before, and the problem immediately manifested itself again. i.e. as soon as I "Cleared All" below row 3000 and saved the workbook the size of the workbook immediately went from 9MB to 45MB. And just for clarity, there are no pivot tables in the new workbook. Just that one data sheet.
Does anyone have any idea where I might even start to troubleshoot this one?
Any and all ideas welcome. I'm really stumped now.
My next step is to recreate that data sheet from scratch but that's just to see if I can come up with a template for going forward.
I have approx. 30 existing spreadsheets that will have the same problem and I don't want to have to recreate each one of those.

 

[sandy666]

Could you share an example as is via OneDrive, GoogleDrive, tinyupload.com or any similar?

Of course if there is no any confidential information

 

[sparky2205]

I could share the blank template which does demonstrate the issue but I don't know how to do that.
Could you provide instruction?

 

[sparky2205]

Well that was simple.
Here's the link to the file.
http://s000.tinyupload.com/?file_id=23445719756420572021
As a recap.
I've identified that the issue is with the Data tab.
How to recreate the issue:
Highlight all rows below row 3000 and "Clear All". Save the file.
Initial file size is approx. 9MB
File size after "Clear All" and saving, approx. 45MB
I created a new Data tab independently and replaced the Data tab in one of my existing files which has the problem with the new Data tab.
Then I copied in the data from the old Data tab, saved it and the file size reduced back to normal. So the issue is definitely with the Data tab.