A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK



 
 
Thread Tools Display Modes
  #1  
Old January 20th 09, 09:59 AM posted to microsoft.public.excel.misc
keyson2004
external usenet poster
 
Posts: 4
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

I have a very large wotkbook 10MB and want to find out how I can reduce it
and also find out the size of each worksheet within the workbook to see if
there is anything I can do to reduce the size of each worksheet. I use many
vlookup , if formauls in the workbook
Ads
  #2  
Old January 20th 09, 11:50 AM posted to microsoft.public.excel.misc
joel
external usenet poster
 
Posts: 9,101
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

A 10MB file for Excel isn't very large, It is just Large. Excel often makes
workbooks larger than necessary. Sometimes making a small change will cause
excel to grow from a 1 MB file to a 10 MB file. It is difficult to return it
back to a smaller file when you have a lot of formulas. I been successful in
returning a workbook to a smaller size by copying each sheet one at a time to
a new workbook. This won't work if you have a lot of formulas that reference
multiple worksheets.

The key is what is really your problem. Is your problem that the workbook
is slow or take a long time to open and close? You can turn automatic
updating off to speed up the entering of a workbook. You can convert some of
your formulars to macros which will speed up operations. I don't think that
finding the SIZE of the worksheets will really solve any problems.

"keyson2004" wrote:

> I have a very large wotkbook 10MB and want to find out how I can reduce it
> and also find out the size of each worksheet within the workbook to see if
> there is anything I can do to reduce the size of each worksheet. I use many
> vlookup , if formauls in the workbook

  #3  
Old January 20th 09, 12:56 PM posted to microsoft.public.excel.misc
Charles Williams
external usenet poster
 
Posts: 968
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

You can find out the size of each worksheet by deleting a worksheet and
saving the workbook using a different name. Do this in turn until you only
have 1 worksheet left. The difference in the file size gives you the size of
the worksheet you just deleted.
Note that the disk size of a worksheet does not neccessarily correspond well
to the memory used by that worksheet.

But I agree with Joel that the size of a worksheet is not usually the
problem, but calculation time and memory is.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Joel" > wrote in message
...
>A 10MB file for Excel isn't very large, It is just Large. Excel often
>makes
> workbooks larger than necessary. Sometimes making a small change will
> cause
> excel to grow from a 1 MB file to a 10 MB file. It is difficult to return
> it
> back to a smaller file when you have a lot of formulas. I been successful
> in
> returning a workbook to a smaller size by copying each sheet one at a time
> to
> a new workbook. This won't work if you have a lot of formulas that
> reference
> multiple worksheets.
>
> The key is what is really your problem. Is your problem that the workbook
> is slow or take a long time to open and close? You can turn automatic
> updating off to speed up the entering of a workbook. You can convert some
> of
> your formulars to macros which will speed up operations. I don't think
> that
> finding the SIZE of the worksheets will really solve any problems.
>
> "keyson2004" wrote:
>
>> I have a very large wotkbook 10MB and want to find out how I can reduce
>> it
>> and also find out the size of each worksheet within the workbook to see
>> if
>> there is anything I can do to reduce the size of each worksheet. I use
>> many
>> vlookup , if formauls in the workbook



  #4  
Old January 20th 09, 01:09 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default HOW DO FIND OUT THE SIZE OF EACH WORKSHEET WITHIN A WORKBOOK

You could copy each worksheet to a new workbook (each one to a separate
workbook), save those workbooks, then use windows explorer to look at the size
of those new workbooks.



keyson2004 wrote:
>
> I have a very large wotkbook 10MB and want to find out how I can reduce it
> and also find out the size of each worksheet within the workbook to see if
> there is anything I can do to reduce the size of each worksheet. I use many
> vlookup , if formauls in the workbook


--

Dave Peterson
  #5  
Old January 15th 14, 02:20 PM
Jon777 Jon777 is offline
Junior Member
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 1
Default

Quote:
Originally Posted by keyson2004 View Post
I have a very large wotkbook 10MB and want to find out how I can reduce it
and also find out the size of each worksheet within the workbook to see if
there is anything I can do to reduce the size of each worksheet. I use many
vlookup , if formauls in the workbook

I have had this problem. A common cause of a bloated workbook is the creation of a lot of empty rows at the bottom of one sheet. For example a reformat that selects all 100,000+ rows in a column might do this.

My solution: Go to each worksheet. Go to the last desired row (you can even add a buffer. Select all cells or all rows beneath that and delete the rows. Often when you get to the offending sheet you get a pop-up warning that there is not enough memory, but you can continue as long as you agree that the "undo" won't be possible. In a recent case after I cleaned up the offending worksheet, my file shrank from 40MB to 0.276MB (an excel 2007 file).

This was much faster than trying to keep resaving the 40MB file after a sheet is deleted.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a worksheet by its name in a workbook? Alchemist Excel Discussion (Misc queries) 2 January 15th 09 01:17 AM
SIZE Window, Tile, Worksheet, Workbook, Whatever it's called. JamesInNeedFootballInDeed New Users to Excel 4 October 31st 07 11:16 PM
How to evaluate the size of each worksheet in a workbook? [email protected] Excel Discussion (Misc queries) 1 August 22nd 07 11:50 AM
Find & Replace a link to another worksheet in the same workbook Sidesfive Excel Worksheet Functions 1 July 6th 06 10:23 PM
Find a value in a workbook and return the worksheet name Craig Excel Discussion (Misc queries) 2 August 10th 05 09:47 PM


All times are GMT +1. The time now is 10:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.