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.

This question already has an answer here:

I have a very simple Excel 2010 file--the names, addresses, and phone numbers of about forty personal contacts. No formulae, special formatting, or complications other than alternating background colors for the rows so I can follow the line from a name to the appropriate phone number across the page. My computer has a quad processor and 6G of RAM, so this file just POPPED open for several months. Then it began to open more slowly, and now opens in percentage increments: 6%...8%...13%...

Asking it to insert a new line gets me the "This action will affect a large amount of data" warning; when I say OK, it then tells me it can't perform this action with the available resources. At that point, attempting to select and delete all cells to the right of or below the active table ALSO gets me the "large amount of data" warning and the no-can-do follow-up. Then saving and shutting down becomes a nightmare--have had to kill the program with Task Manager more than once.

Other Excel files of a similar complexity, and with exactly the same background color scheme--my UserName and Password list, for example--still POP open. I notice also that those files are about 25K in size; the troublesome address file is now at 8.35M.

At no point have I added any special formatting or otherwise tinkered with this file. I wonder, having read a great many similar accounts on the net, if someone has generated a bug that puts a single byte of data somewhere out in the Infinity Corner of the Excel program, compelling it to deal with everything from square one to out back of beyond...

share|improve this question
    
My personal view is that this and the other question should be combined into a wiki dealing with both issues but that there are two issues (albeit interrelated) - one asks about bloating and one about slowness. SIZE != SPEED. –  pnuts May 25 '13 at 0:48
add comment

marked as duplicate by allquixotic, Karan, Tanner, CharlieRB, Brad Patton May 24 '13 at 17:40

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers

(Copied from SO) Excessive size can have many causes:

Extraordinarily large (bytes) workbooks with very little apparent content are all too often created by data dumps into Excel.

One reason can be that the extraction code has been included with the data (check Developer, Visual Basic).

More likely to have a significant size impact is formatting, that can be detected by Editing, Find & Select, Go To Special, Last cell and observing the highlighted cell is a long way from the last ‘occupied’ cell. This can be fixed as below:

  1. Select row immediately below the last ‘occupied’ row.
  2. Ctrl+Shift+Down.
  3. Del.
  4. Save.

Size may also be increased by embedded objects (check Editing, Find & Select, Selection Pane) and also the content of Comments even where these are not displayed.

Pivot tables with broken links may also be a cause.

Other causes can include the likes of the apparently blank ‘last cell’ indeed being occupied (say with a font colour to match the background) or other hidden objects. Note that in all cases the file size may not reduce in response to ‘deletions’ until it is saved.

share|improve this answer
add comment

You could grab the OpenXML SDK and use the Productivity Tool to check the file structure as per this YouTube video.

share|improve this answer
add comment

When I have had this issue before its been because of lots of blank rows in excel.

So scroll to the end of your data and then select the rest of the rows and right click and then delete.

share|improve this answer
1  
You've identified a potential problem, but not a solution. I would upvote this if you had a solution. –  KronoS May 23 '13 at 18:39
add comment

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