2 Users Updating Identical XLSX Produces 2 Different File Sizes?

chipdouglas

New Member
Joined
Aug 19, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, we have a ~30MB weekly office performance scorecard one individual is tasked with updating. When I update it, the file size remains around 30MB. When the other individual updates it, it adds ~6MB which breaks our Outlook attachment size cap (non-negotiable group IT policy) and it cannot be mass-emailed to stakeholders. What we've examined/confirmed so far:
  1. The base template is a shared Sharepoint file, so it's not like we're starting with 2 different versions
  2. All cells and all formatting in the entire workbook appear to be 100% identical before and after updating
  3. We're pasting in new data identically (just straight CTRL+V) and refreshing pivots. Formatting remains identical
  4. We're both starting with XLTX and saving the updated file as an XLSX
  5. Save options (left of "Save As", under "Tools") are identical - every conceivable sub-option on every tab and menu is identical
The sheets are totally indistinguishable before, during and after updating - but theirs is +6MB larger? Any ideas? TIA
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
First immediate thought is Data Model. Are you creating Pivots or just refreshing them? Is the data added to a data model when pivots are created? Or pivot cache.
30 MB is not a small file.
I cannot say for certain without looking at the files.
A good starting point is to decompress the xlsx files and compare their contents file by file.
 
Upvote 0
We're pasting in new data identically (just straight CTRL+V) and refreshing pivots. Formatting remains identical
My guess would be - assuming you paste the same amount of data - that this is not actually true and some additional formatting is coming through - eg cell styles.

Alternatively, have you checked that ctrl+end on the paste sheet(s) only goes to the end of the data after the other user pastes?
 
Upvote 0
First immediate thought is Data Model. Are you creating Pivots or just refreshing them? Is the data added to a data model when pivots are created? Or pivot cache.
30 MB is not a small file.
I cannot say for certain without looking at the files.
A good starting point is to decompress the xlsx files and compare their contents file by file.
You might be onto something here. A new piece of information I got last night is that the file size jumps NOT after it's pasted in but after the pivot tables are refreshed, which seems to rule out disparities in data and formatting, and suggests it's something to do with the Data Model/caching instead.

I'll do some more research here, but if I don't find the answer, how would one ensure that only the smaller cache/model is retained, and not the one that breaks the Outlook attachment size cap?
 
Upvote 0
Compare the processes step by step - try to figure out what is made differently, when exactly the file size spikes,then find the reason why.
Investigate thoroughly and try to narrow it down as much as possible. It may be that not all pivots contribute to the problem, but one or two only.
Since you are getting different files they may also present different results, check this possibility in details too.
If possible try to modify the template, split it in two or come up with another way of presenting the information in order to reduce the file size and keep it as far below the limit as possible.

I know that what I'm saying here may be a bit generic, but I'm in the dark on the details, so this is the best I can do.
Last resort - use an archiver to compress the file size. Depending on the file contents - if ZIP is not good enough try RAR.
 
Upvote 0
Compare the processes step by step - try to figure out what is made differently, when exactly the file size spikes,then find the reason why.
Investigate thoroughly and try to narrow it down as much as possible. It may be that not all pivots contribute to the problem, but one or two only.
Since you are getting different files they may also present different results, check this possibility in details too.
If possible try to modify the template, split it in two or come up with another way of presenting the information in order to reduce the file size and keep it as far below the limit as possible.

I know that what I'm saying here may be a bit generic, but I'm in the dark on the details, so this is the best I can do.
Last resort - use an archiver to compress the file size. Depending on the file contents - if ZIP is not good enough try RAR.
Thanks. We had already tried the above but it didn't yield fruit.

But for anyone else finding this, the explanation for why our files were differently-sized was that our Excel pivot table settings were different. We were able to not only achieve the same file size, but actually reduce the overall size by 20% by changing pivot table settings:
  1. Right click on each unique pivot table
  2. Pivot Table options > Data tab
  3. Uncheck "save source data with file" and check "refresh data when opening the file."
Some other things also helped, but all the fixes were equally useful and undertaking several of them concurrently didn't make an incremental difference. One was sufficient.

At the moment, we're looking at how to build within the data model rather than on separate sheets as that promises to shrink the file size considerably
 
Upvote 0
Solution

Forum statistics

Threads
1,224,465
Messages
6,178,821
Members
452,881
Latest member
motivationgyan

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
Back
Top