Workbooks that the sizes don't make sense and won't support formulas

[mattxu2006]

hello Gentlepeople

I have been given workbooks that are use more memory than makes sense.

All are data; no formulas.

the smallest is a xlsx file of 173MB.

91308 rows, 35 Columns

If I make a file with the same number of rows and columns, the file size is 8.76MB
copying and pasting values in a new workbook does not help.

These files also have problems calculations:
I need to match two of these workbooks to see if they have the same value for the same ID
I tried doing a sumif down one worksheet and it will not calculate down to the bottom row. About a 3^rd of the way through, it just repeats the value down to the bottom.

I checked for non-printing characters. Only found 33.

Is there a way to check what may be happening in the background to make these workbooks so unwieldy?
Thanks

 

[sandy666]

maybe try :
Inquire - Clean Excess Cell Formatting

edit:
if there is no any sensitive data, post a link to this file

 

[mattxu2006]

HI Michael
What I mean is that I get the data workbooks, they arrive as described; when I try to do formulas with that data, the calculations are very slow. The Calculation results tend to be as describe above, taking several minutes, using as much processing power as my computer allows.

CTRL + End does take me to the end of the actual data.
I frequently copy data by starting at the top left (usually Cell A1) and using CTRL + SHIFT + END to select the entire range at once.

I realize that I didn't say this before: Copying and pasting values into a new workbook does not reduce the size of the workbook.

 

[mattxu2006]

HI Sandy
That did offer an alternative, but the results aren't any different.
this link may be helpful in the future
https://support.office.com/en-us/article/Clean-excess-cell-formatting-on-a-worksheet-E744C248-6925-4E77-9D49-4874F7474738

 

[sandy666]

what about link to your file?

btw. I did file with 10000 rows and 35 columns with data only = 5 MB

 

[Michael M]

Another thing to check is make a copy of the workbook, then delete a sheet save, and see if the size has changed, repeat as required.
At least it can then be narrowed down to 1 sheet hopefully !!

 

[sandy666]

I forgot, check if there are a lot of extra styles

I saw in the past workbook with 500-600 additional, not used styles

btw. this is binary or open XML version of Excel?

 

[mattxu2006]

Unfortunately, I cannot share the file.

I did a similar test and got similar results.

 

[sandy666]

try save this file as xlsb then try to save xlsb as xlsx (wierd solution, I know but maybe it will work)

also you can check structure of this file
change extension to .zip, unzip then check all inside the folders, especially bin files (always work on copy not original file!)

 

[mattxu2006]

HI Sandy
Toggling between xlsb and xlsx didn't affect the file size.
I am not sure how to interpret the results of the zip file. what should I be looking for?

 

[sandy666]

clean workbook should have structure like this (1) & (2)
(3) is a size of the "unpacked sheet" ~15 MB (standard xlsm has 5.01 MB - my test file)
if you've any additional folers or xmls bigger then you think they should be you need to check them

did you check workbook for hidden sheets ?