Large file with Pivot Tables

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
344
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
maybe try to save file as xlsb ?

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

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
344
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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 :)

 

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
344
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
344
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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

Active Member
Joined
Feb 6, 2013
Messages
344
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Feb 6, 2013
Messages
344
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,415
Messages
5,706,308
Members
421,441
Latest member
VapesRub

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