2

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...

Robert Crawford
  • 21

3 Answers3

2

(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.

pnuts
  • 6,242
0

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

Alan B
  • 1,305
-1

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.

Harrison
  • 157