Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel spreadsheet which is pretty much just a template sheet with no actual data populated. The template isn't that large. Cells known and intended to be populated with either text (no formulas any more) or formatting range as follows:

  • Sheet 1 - A1:C19
  • Sheet 2 - A1:K8
  • Sheet 3 - A1:E6

The latter two ranges are formatted as tables.

I wouldn't think this should be a very large file, but the file's size is over 5 MB.

At one point, it was over 10 MB. Then, I found that I'd populated a formula down an entire column. Once I deleted the formula entirely (it was deemed superfluous) the size was dropped to its current 5 MB.

Are there any built-in tools or available plugins that can help me analyze the workbook for other space hogs like that? I really want to get this sheet trimmed down in size, since I'll be copying it for re-use a lot.


EDIT:

For the record, a friend has helped me figure out what the problem was with this particular case.

Even though I didn't have content in most of the sheet any more, Excel was still retaining the million or so extra rows because they used to have content (the formula I'd nuked). Presumably this is because Excel still thinks I need them for whatever reason.

The solution is to highlight all the rows I don't need, right-click and select delete. The sheet is now only a few hundred K (most of it probably because a company logo is included).

Still, I'd be very much interested in knowing if there's any tools or plugins that could help catch these things in the future.

share|improve this question
    
No built-in tools that I'm aware of. The best way to resolve this is to copy the content to a new sheet, delete the old one, and it should be smaller. –  user3463 Jul 16 '12 at 22:08
    
@RandolphWest That's great, but the important question still to be answered (as in, lesson learned for future reference) is why was the old one so bloated in the first place? A tool designed to identify issues like this would be very helpful and educational in this regard. –  Iszi Jul 16 '12 at 22:18
    
Simple: Excel for some reason doesn't clean up well when things are deleted. I see it this way: in the binary of the file, it says "this cell is empty now" instead of having nothing in the first place, a la a new file. This probably goes to how Excel saves. I don't know if it's better in 2007+, but at least the XLSX format is zipped XML, so it should be smaller due to the Zip algorithm. –  user3463 Jul 16 '12 at 22:24
    
I hate to leave you just a link, but I bumped into this page some time ago and bookmarked it when I was researching, generally, how to improve the response times for very large Excel workbooks. There is a ton of information there. Might be good for perusal :) –  JoshP Jul 17 '12 at 1:23
    
Depending on the complexity, you can export to tab or comma separated value type and look at it in a text editor. Otherwise you can try Gnumeric, open/libre/k-office etc... I prefer mtcelledit for simple tasks. –  technosaurus Jul 17 '12 at 3:10
show 1 more comment

5 Answers

To detect easily, Goto A1, press Ctrl+End. It will take you to the last cell. Now check the scroll bars (as answered above). Identify the blank cells that you don't need. Select Rows not required, right click delete & then SAVE. Repeat the same for columns. ENSURE TO SAVE after deleting, some versions of excel do not reduce the size / blank them completely.

for sending file(s) through email, just zip it.

share|improve this answer
add comment

As has been pointed out in the comments. Once Excel has had an entry in a cell, clearing the cell contents does not "delete" the row/column but rather leaves it with a null entry.

So if you ever enter data into a row/column beyond the logical end of your data, you should always delete the row/column not just empty the cell.

The nearest thing to a built-in tool to detect this is the scroll bars. They give a visual indication of how many rows/columns there are in the active worksheet. The smaller the "elevator" bar is, the more rows or columns you have in the worksheet. Also, if you are at the last row or column and the scroll bar still has plenty of space to go, you know there is "something out there". Once you've attuned your eyes to this, you soon spot the issue again.

share|improve this answer
add comment

Another two tips I found which work in certain circumstances (the first being the more obvious):

  1. Check for unused hidden sheets - delete if possible
  2. Check Name Manager: I've found occasionally there may be dozens or hundreds of unused 'names' eg. automatically generated by SAP BEX reporting. Delete all of them, but save a copy in case you delete something important! This can significantly reduce a large file.
share|improve this answer
add comment

just copy the contents to a new sheet. Voila ..reduced size..

Secondly, By any chance your have sharing enabled ?? IF yes then disable it and then disable track changes and see the magic

share|improve this answer
add comment

If you just need it smaller for sending the file elsewhere, I had a 14mb Excel file (11 x 34000 lines) created in 2010 - ran it through Winrar and it became 907kb

share|improve this answer
    
This completely side-steps the problem. It's not just that the file size is large - it's that it's unjustifiably large. There is no point in having a file, in its uncompressed format, larger than the usable data it represents. –  Iszi Feb 5 '13 at 13:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.