I've just inherited control of an old .xls file that my office uses, and I've been digging through to find out why this (relatively small - three sheets, none larger than 20x300 cells, no graphics) sheet is 6MB. We create somewhere around a thousand copies of this file a month, since each client gets a customized version, so it's a fairly substantial disk space hog.

I assumed it was some embedded macros we had, but digging into the file structure(by using the xlsx>zip trick), that's not it at all. Instead, there's a mountain of the following tags:

<row r="65536" ht="12.75" hidden="1" customHeight="1" x14ac:dyDescent="0.2"/>

There's one of those tags for every row in the document, from the end of the content, all the way down to row #65536. And I have no idea why. I've tried deleting all the bottom rows, in hopes that it'd start just using the default format, but that hasn't worked - unhiding removes the hidden="1" part, but the rest remains despite my best efforts. What can I do to eliminate this cruft and cut down the file size?



Thank you.

Delete/change the sensitive data, save it under a new name, attach it here.

Ironically, I can't upload the .xls because of the upload file size limits. It's 1.6 MB, I'm capped at 500 KB. But I've saved it as a .xlsx, and uploaded it on this post - hopefully any fixes that work there will also work on the original .xls file.

Thanks.

Attached Files

C-B Stripped Down.xlsx (229.4 KB, 57 views)

May be the following will help:

Press F5, Special, Last Cell, OK

You will see that last cell is Y65536.

Now try the following:
(1) Press the grey are above row 1 and to the left of column A to select the whole sheet.
(2) Format, Column width, 8.43, Enter.
(3) Press A to select whole column A, Format, Hide & Unhide, Unhide rows.

Let's say your last used row is 1000 and your last used column is Y. Do the following:

(4) In the Name Field write 1001:1048576, press Enter, right click the mouse and press Delete.

(5) In the Name Field Z:XFD, press Enter, right click the mouse and press Delete.

(6) Save the file.

You have 2 sheets in that file. If I unhide all the hidden rows and columns of both sheets and save the file it comes down to 58k.
(After messing with the file a bit, I rehid the rows and columns, re-saved and got varying file sizes. as much as 4MB in one case.)

xor: The go to last cell thing is useful, but I already tried deleting everything outsize the active area before I posted this thread. It didn't do anything - whatever the default rows that Excel replaces deleted rows with are, they have the same problem.

p45cal: That was what I assumed at first, but I'm seeing basically the same file sizes with everything unhidden. I cannot replicate your result.

FYI, this is happening in Office 2010, v14.0.7192.5000 (32-bit). Perhaps that's changing things somehow?

Yes, but did you actually try the steps I proposed?

Yes, I tried your approach as well for completeness. It also did nothing. The file is still 1,628 MB.

I've also tried copying in rows from workbooks that do not have this problem, copying cells likewise, doing the same from another sheet in the same workbook, using format painter to copy the formatting from unaffected rows and cells, and deleting the rows without unhiding. None of these have had any measurable effect on the file size.

If it is not possible that you can anonymize the actual file and send it to the earlier given email address then I can't help you.

You wrote:
Yes, I tried your approach as well for completeness. It also did nothing. The file is still 1,628 MB.

-------------------------------

What was Last cell after you did those steps?

Why didn't you unhide?

Have you lost interest in your own question.
Wouldn't it be fair to respond to those trying to help you?

Here is the workbook you uploaded where I have made the steps mentioned i #4 which reduced the file size to 57 kb.

Attached Files

C-B Stripped Down_2.xlsx (56.9 KB, 11 views)

I only check this thread on work days, FYI. Sorry about the delay that imposes, and thank you for continuing to try to help.

I just tried your steps again, and again had no success. Could this be related to the version of Excel I'm using? I'm on Excel 2010 v14.0.7192.5000 32-bit, it seems.

> What was Last cell after you did those steps?

Y65536, same as it was originally.

> Why didn't you unhide?

I'm not sure what gave you that impression. I did.

I will try once more to describe exactly the steps i do on the file you uploaded in #3

1. Remove the object in A1:V2 and set column width for A:Y to 8.43
2. Press the square in the upper left corner above 1 and to the left of A to select the entire sheet.
3. Press Format, Hide & Unhide, Unhide Rows
4. Press Format Hide & Unhide, Unhide Columns
5. Press A to select column A.
6. Right click the mouse and press Delete
7. Press 1 to select whole row 1
8. Right click the mouse and press Delete
9. Save the file.

After I do these steps the file size is reduced to 57 kb and last cell is A1.

Ensure that now you are able to go to A1048576 by writing A1048576 in the Name field and press Enter. Do similarly to confirm that you can go to XFD1.

Can't you reproduce this?

Okay, I think I've figured out why we're getting such different results. I've been trying it with the .xls file. I gave it a try on the .xlsx file, and your steps worked. However, the version we use needs to stay as a .xls, for legacy compatibility reasons(and I can't convert>fix>convert back due to annoying issues related to embedded macros), so a .xlsx-only fix doesn't help me.

It is not easy to help you!

If I open your uploaded file as xlsx file, do the steps mentioned earlier and save the file as xls, then what's the problem?

I may completely misunderstand something.

Are you aware of this: https://support.microsoft.com/en-us/...crosoft-office

> It is not easy to help you!

My wife would agree with you :P

(Seriously though, thank you for continuing to try)

> If I open your uploaded file as xlsx file, do the steps mentioned earlier and save the file as xls, then what's the problem?

The actual file has an embedded macro, which for corporate security reasons I'm not allowed to have access to. Saving it as .xlsx eliminates that macro, which would make the sheet non-functional for some important purposes. That said, I can get it down to under 900kb just by saving as .xlsm, which is a huge improvement, and the macro will still work there. I may try that.