2 Users Updating Identical XLSX Produces 2 Different File Sizes?

[chipdouglas]

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

 

[bobsan42]

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.

 

[RoryA]

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?

 

[chipdouglas]

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?

 

[bobsan42]

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.

 

[chipdouglas]

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