huge huge excel file... why?

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

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 wrote:
>
> 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.
> --
> Josh

--

Dave Peterson

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" wrote:

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

Josh wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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.
> > > --
> > > Josh
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

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


"Pete" wrote:

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

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

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



Pete wrote:
>
> 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

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


"Pete" wrote:

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

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

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


"shrutikhurana" wrote:

>
> the only option i see under edit is 'delete sheet' ...and whole sheet
> goes....how to just delete the extra columns or rows?
>
>
> --
> shrutikhurana
> ------------------------------------------------------------------------
> shrutikhurana's Profile: http://www.excelforum.com/member.php...o&userid=29868
> View this thread: http://www.excelforum.com/showthread...hreadid=507396
>
>

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

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


"shrutikhurana" wrote:

>
> 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
>
>
> --
> shrutikhurana
> ------------------------------------------------------------------------
> shrutikhurana's Profile: http://www.excelforum.com/member.php...o&userid=29868
> View this thread: http://www.excelforum.com/showthread...hreadid=507396
>
>