huge huge excel file... why?

[Josh]

i have an excel file that is 171 MB, I don't understand why... I removed the
autoformatting I thought might have been the problem, but it's still the same
size. It also takes an extremely long time to save my progress, to open or
close, and it sometimes pauses every ten or so rows when I'm scrolling to set
the print area. It shouldn't be that big- There are 5 sheets, each is between
400 and 500 rows, and there's only ten columns. What's going on? Thanks for
any help.

 

[Dave Peterson]

I think I would open that huge file and just copy all the cells in each
worksheet to a new worksheet in a new workbook.

I wouldn't waste too much time trying to fix the old--just create a new one.

This may sound simple, but it might not be. Watch out for formulas, named
ranges, named anything, VBA code, headers/footers, filters, and anything else
you can think of.

But it sure sounds easier than trying to fix that 171MB file.

 

[Josh]

I think it may be a formula I accidently put in there somewhere. Sometimes
when I cut and paste from one column into another, a message pops up and says
"your formula contains an invalid external reference to a worksheet. Verify
path, workbook and range name or cell reference are correct and try again". I
do have formulas on the other sheets of the workbook, but there shouldnt be
any on this sheet and I can't find any sign of a formula on this page. I did
a control f search within the sheet, and it didn't find anything. I tried
copying the data into a new workbook, but it was still a ridiculously huge
file. Thenks for your earlier atempt, do you have any other suggestions?
Thanks again, --
Josh

 

[Dave Peterson]

When I can't find links, I'll use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

and to make working with names easier...

I'd use Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager to search for any hidden names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

But I don't see how that could make a 171MB file.

I think it may be a formula I accidently put in there somewhere. Sometimes
when I cut and paste from one column into another, a message pops up and says
"your formula contains an invalid external reference to a worksheet. Verify
path, workbook and range name or cell reference are correct and try again". I
do have formulas on the other sheets of the workbook, but there shouldnt be
any on this sheet and I can't find any sign of a formula on this page. I did
a control f search within the sheet, and it didn't find anything. I tried
copying the data into a new workbook, but it was still a ridiculously huge
file. Thenks for your earlier atempt, do you have any other suggestions?
Thanks again, --
Josh

 

[Pete]

Do CTRL-END and see where your cursor lands up - this is where Excel
thinks the last cell is, but you might find it is way beyond your data
area. In this case, highlight all the blank columns in your sheet (i.e.
from column K to IV) and Edit | Delete (not just delete contents), then
do the same for the blank rows (500 to 65536) and again Edit | Delete.
Then save your file, close and re-open - Excel should have recovered
the "lost" memory.

Hope this helps.

Pete

 

[Josh]

Pete, thanks for the reply. I've tried deleting all the columns and rows not
occupied by my data, but after I do this, ctrl end still leaves me at IV
65536. Also, although my computer permitted me to do this for one sheet, when
I tried on the other sheets, a window popped up warning that there wasn't
enough memory to do the task, and asked if I wanted it to continue without
being able to undo (I didn't). I'm going to try what Pete suggested next,
but I don't really know what to use find link or find name programs for, or
exactly why and how I'd use these. Again, thanks for the help, and any more
advice would be greatly appreciated.

 

[Pete]

I think Dave's first suggestion, then, is the next course of action.
Highlight only the cells with data/formulae in them and copy them to
another sheet in a new file. If you have any formulae which refer to
other sheets in the first (big) file, they will refer back to that, but
if you rename all the sheets in the new workbook to be the same as in
the old one, you can then do Edit | Replace to change the
"[old_filename.xls]" with nothing to get them to point to the correct
sheet. Save the new file with a different name and it should be a
slimmer version.

Hope this helps.

Pete

 

[Dave Peterson]

Another way to fix those links would be to save the new workbook and use
Edit|links.

Or I like to change my formulas to text
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Do the copy|paste and then reverse the process.
Edit|replace
what: $$$$$=
with: =
replace all

I think Dave's first suggestion, then, is the next course of action.
Highlight only the cells with data/formulae in them and copy them to
another sheet in a new file. If you have any formulae which refer to
other sheets in the first (big) file, they will refer back to that, but
if you rename all the sheets in the new workbook to be the same as in
the old one, you can then do Edit | Replace to change the
"[old_filename.xls]" with nothing to get them to point to the correct
sheet. Save the new file with a different name and it should be a
slimmer version.

Hope this helps.

Pete

 

[Josh]

I tried the ctrl end suggestion again, and it worked perfectly. Everything
runs very smoothly now, and the entire file is 400 kb or so. Thanks a trill,
you really helped me out.

 

[shrutikhurana]

the only option i see under edit is 'delete sheet' ...and whole sheet
goes....how to just delete the extra columns or rows?

 

[Josh]

you have to click and drag your mouse over the column header or range of
cells you want to delete, then click edit, delete.

 

[shrutikhurana]

my file is still huge.....only around 100 kb difference total......i did
what was indicicated.....wherever my cursor landed up at IV column and
very down (65536th row)....i selected the columns from their
headers....went to edit and then delete.......and same for the
rows........how come not much difference like others experienced here.

help pls

 

[Josh]

remember to close excel after you've deleted the rows and columns. I tried
deleting everything and then doing ctrl end right afterward, I found it
wouldn't work untill I closed the program and then reopened it.