Data hidden/lost in Excel 2003 spreadsheet?

[Barkley Bees]

I have an odd situation I was hoping some of you excel experts might be able
to help me with. I was sent a spreadsheet (task schedule list) that was
about 25kb (only 40 rows by 8 columns).
After I did some editing (adding 2 new columns and adding some information
to them) and saved the file, I noticed the file size has ballooned up to
2MB!

I went hunting through the file to see if had accidentally pasted a
something huge from my clipboard but could find nothing. I know it is the
column I added as when I delete the entire column and save the file it goes
back to the original 25kb. But when I delete the rows from this column where
I added data (knowingly!) the file size does not change....it remains 2MB.

I manually scrolled down from row 41 to 65,536 and could see nothing but
white cells. I figure there's some data hidden in there but I can't seem to
figure out how to find it. Can anyone recommend a good way to hunt this
down? Much appreciated.

 

[Dave O]

Sometimes this happens when data is entered in a cell and later that
cell is cleared. Excel then assumes the lower right corner of the
"used area" of the spreadsheet still includes that cell. You can test
for this in each tab by pressing CTRL-END, which moves the cell
pointer to that lower right corner. If the lower right corner includes
rows and columns that you do not need, you can delete (not clear
contents, but delete) those rows and columns and save the file. This
reduces file size.

Another thing to look for is unneeded named ranges. If you Move a tab
from one file to another file, any named ranges in the original tab
move to the new file. These become unnecessary overhead. Same deal for
heavily formatted cells: the more formatting, the more "overhead" is
created and greater file size results. If there are phantom named
ranges, delete them; if there is no need for heavy formatting then
remove the formatting requirments.

Dave O
Eschew obfuscation

 

[JP]

After inserting the column, you may have affected the "dirty area" of
the worksheet. Press Ctrl-End and see where you end up. If it is far
below your data, delete the empty rows/columns and save your file.

Check out http://support.microsoft.com/kb/244435 for more help.

HTH,
JP

 

[Otto Moehrbach]

Hugo
You know where your last cell is, the cell in the last column and in the
last row. Make a note of that cell's address. Then do Ctrl-End. Excel
will go to some cell. That is the cell that Excel thinks is the last used
cell. If Excel's last cell is way off from your last cell, that is the
source of your problem.
To correct this, do the following. Let's say your last cell is H40
Click on row number 41. The row number, not some cell in that row.
Now do Ctrl-Shift-Down arrow. This selects every row from 101 down. With
the mouse cursor in the selected area, right-click and click on Delete.
Now click on Column letter I The column letter itself. Now do
Ctrl-Shift-Right Arrow. This selects every column to the right of Column H.
With the mouse cursor in the selected area, do right-click and click on
Delete.
Save the file.
Close the file.
See how big the file is. Done. HTH Otto