+ Reply to Thread
Results 1 to 5 of 5

How to make Workbooks smaller / Excel performance better (#2)

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    COLOMBIA
    MS-Off Ver
    Excel 2011 for mac
    Posts
    5

    Question How to make Workbooks smaller / Excel performance better (#2)

    Here are my questions:

    1. Is there any way I can just cancel the cells not used? The default max ranges in an excel spreadsheet is like 65536 rows by 256 columns, so the idea would be to limit the default max range to my max used range. The way I have it set up is that the window doesn't even move (the pane is frozen), so I have no use for all the cells surrounding the first viewable cells on the screen.

    2. If this is not possible, even if I delete the rage of cells to be the actual used range of cells(as the fix by Otto mentions), is the file size affected by all the rest of the not used cell ranges?

    3. I also have shapes as buttons to navigate through the workbook, is that increasing the file size significantly? how can I compress the buttons?

    4. Essentially I just need the workbook to not stall when being used. How else can I help it.

    The following is a great FIX posted a few years ago, but... I need more...

    Link to quoted solution:
    http://www.excelforum.com/excel-gene...ce-better.html
    Quoted solution by: Otto Moehrbach
    Excel has a problem with determining the range, call it used range, in a
    sheet. For instance, do this:
    Open a new blank file.
    Type something in cell E10.
    Select A1.
    Now do Ctrl - End. This should take you to the last cell in the used range.
    Note that Excel selects E10. Good.
    Now clear E10.
    Select C5 and type something in that cell.
    Select A1.
    Do Ctrl - End again.
    Notice that Excel selects E10.
    You know the last cell in the used range is C5. Excel thinks it's E10.
    The size of the file is due, in part, to what Excel thinks is the used
    range.
    The above example is over a small range. If the above were to happen over
    thousands of rows and hundreds of columns, the difference in the size of the
    file would be significant. Even huge.
    Go to each of the sheets in this file you have and do Ctrl-End and see how
    Excel's idea of the used range and your idea of it differ.
    What you have to do, if this is a problem, is delete all the columns after
    the real last column and the same with the rows. Then save the file, close
    the file, reopen the file, and see what you have in size.
    There are ways to automate this process of resetting the used range if your
    file has many sheets.
    Thanks bunch~~

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to make Workbooks smaller / Excel performance better (#2)

    2. If this is not possible, even if I delete the rage of cells to be the actual used range of cells(as the fix by Otto mentions), is the file size affected by all the rest of the not used cell ranges?
    you cant delete cells permanently,they are always there,so the answer is no the file size is not affected
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: How to make Workbooks smaller / Excel performance better (#2)

    you could try to save as xlsb - excel binary--then delete excess cells not being used...
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  4. #4
    Registered User
    Join Date
    07-05-2012
    Location
    COLOMBIA
    MS-Off Ver
    Excel 2011 for mac
    Posts
    5

    Re: How to make Workbooks smaller / Excel performance better (#2)

    Alright, what about the Buttons(Shapes), is there a way to compress them?

    The help, much appreciated.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: How to make Workbooks smaller / Excel performance better (#2)

    only i now is to click the image -format -compress picture (for pictures ) lower resolutions also can compress them also cropping, on shapes
    on File -save as-window save there's a drop down menu "Tools" below that window just beside the "save" button, you'll find there "Compress Picture".
    hth.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1