Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to speed up sluggish worksheet???

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a 2000 row spreadsheet with some hefty formulae, including several with 6 nested if stmts. Whne data is entered, it takes a long time for the sheet to run through it's calculations. Anything I can do to spped it up (short of buying a faster CPU)?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See http://support.microsoft.com/default...b;EN-US;q72622.

    [ This Message was edited by: Mark W. on 2002-04-08 08:34 ]

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You may want to rethink your entire setup to see if any efficiencies can be gained.

    http://spreadsheetstyle.com/

    is a great resource for this.

    Barring that, you may have a lot of volatile functions or complicated arrays that take a long time to process. It is just a long, slow haul.

    If you find yourself having to update data and, after *each* entry the spreadsheet goes through a recalc process, you have to set your Calculation setting to manual

    Tools>Options>Calculations (Manual)

    then set it back to automatic, or hit F9 (or Ctrl+Alt-F9) to recalculate.

    HTH,
    Jay

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for very good article Mark> I read through, but nothing I can change there ( I did change to 'Automatic Except Tables').
    I've automated a very big log, and the summaries require several array formulae. Maybe there's nothing that can be done... still better than manual calculation!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for advice Jay.
    My calulations are twofold - one, I strip dates out of serial numbers and post to ther columns to eliminate need for users to re-enter(limiting chance for error). I have extensive summaries subtotalling several aspects all by weeks (start and end dates taking statutory and company holidays into account).
    THOUGHT: The summary is a separate sheet. Could I somehow only calculate summary when someone looks at sheet? (ie instead of updating all the subtotals as a user is entering data on log sheet?) !!!!

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 08:44, Big Blue wrote:
    Thanks for very good article Mark> I read through, but nothing I can change there ( I did change to 'Automatic Except Tables').
    I've automated a very big log, and the summaries require several array formulae. Maybe there's nothing that can be done... still better than manual calculation!
    'Automatic Except Tables' won't make any difference unless you're using Data Tables. Have you considered summarizing your data using PivotTables?

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, to be honest I've never explored Pivot Tables. Only have a rudimentary understanding of them. If you think they might serve this kind of purpose, I start to research. My current summaries have about a dozen array formulae to provide weekly subtotals of volumes and values.

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can go into tools, options ... and set the calculation to manual during data entry. When you are finished, you can then either hit F9 to calculate or set it back to automatic calculation.
    Alternately, you can copy-paste-special the values for all rows except the first one, and simply copy the first row's formulas into any new rows when needed. Depends on hoiw much the information in the boady changes with time. This solution is good when the data remain essentially unchamged after they are captured.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also, when you have some spare time, have a look at Excel's Database functions... they are all prefixed with "D"

    DSUM
    DCOUNT

    etc etc

    it (sort of) switches excel into database mode - my non-technical way of thinking

    one of my recent 20 meg spreadhseets which took 3 mins to recalculate was smoothed down to 9 megs and just a SECOND to recalculate

    depends on what you want though, they're not always a definitive better way....... check them out though
    :: Pharma Z - Family drugstore ::

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for effort, but I started with D...
    and wound up with a horrendous spreadhseet
    imagine subtotalling 3 categories of items, by 8 departments, for every week of the year excluding holidays... I had to use arrays

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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