Large file with Pivot Tables

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
315
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?
 

sandy666

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,136,284
Messages
5,674,841
Members
419,530
Latest member
undisclosed

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