Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Excel 2003 performance (Excel 2003)

    My client is experiencing performance and stability problems when using workbooks of file sizes 30 to 80 Mb.
    Say 10 work sheets, 5000 rows on multiple worksheets and large numbers of vlookups and sumifs.
    Are there ways to sped this up?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003 performance (Excel 2003)

    Here are some web pages with suggestions:
    Excel Best Practices. Efficient Excel Spreadsheet Designs in Spreadsheets.
    Importance of Excel Calculation Speed - Decision Models (follow the links near the top of the page).

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel 2003 performance (Excel 2003)

    Thanks Hans, i have passed this on. It's up to them now.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel 2003 performance (Excel 2003)

    They have seen those links.

    They ask if having an Excel server should make a difference?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003 performance (Excel 2003)

    What do they mean by an Excel server?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel 2003 performance (Excel 2003)

    A server that is just for those few users (3-4) who need optimum performance.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003 performance (Excel 2003)

    A copy on a local hard disk is probably faster than even a dedicated network server, and definitely a lot cheaper!

    If they need to be able to edit the data with multiple users at the same time, they should look into a database - an Excel workbook is not the ideal solution for that.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003 performance (Excel 2003)

    Are these VLOOKUP formulas using False as their last argument (indicating an exact match is required)?

    If so, you can dramatically speed things up by:

    - Sorting the data on the lookup column
    - Using a formula like this:

    =IF(INDEX(Sheet1!$A$1:$A$100,MATCH(A1,Sheet1!$A$1: $A$100,1))=A1,INDEX(Sheet1!$B$1:$B$100,MATCH(A1,Sh eet1!$A$1:$A$100,1)),NA())

    Even though this may seem a formula that should be slow, it is in fact much, much faster than a VLOOKUP
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts

    Re: Excel 2003 performance (Excel 2003)

    Hi Jan

    I always prefer to use the match and index combo rather than VLookup for large datasets for the big speedup gains you mention.

    A long while ago while using a 30,000+ row datasource, I seem to recall using a more complex looking formula that essentially computed the start and end rows for a 'subset' of the sorted datarows.
    For example, if the datarows were say, 50,000 vehicle registrations and these were sorted, you could set the 'subset search range' commencing from the first row that matched the first character (number/letter etc) of the registration being 'looked up', and similary work out the last search row.
    It was a whopping looking formula but I recall a process performance speedup of magnificent proportions.
    A process that was taking several minutes could now be done in seconds.
    A report that took two hours to process could be done in less than 2 minutes.
    (It wasn't vehicle registrations by the way, it was a data involving establishments that sold alcohol)

    So to anyone thinking that Jan's formula is complex and Vlookup appears simpler, if you want performance you should definitely try Jan's method.

    zeddy


    So

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2003 performance (Excel 2003)

    Jan Karel,

    What is the reason for this formula being so much faster. Is it just because this version forces the user to have to pre-sort the indexing field?

    PS: You may want to alter the last portion of the formula to something like this: <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    =IF(INDEX(Sheet1!$A$1:$A$100,MATCH(A1,Sheet1!$A$1: $A$100,1))=A1,INDEX(Sheet1!$B$1:$B$100,MATCH(A1,Sh eet1!$A$1:$A$100,1)),"Ensure column A is sorted to see a result")
    Regards,
    Rudi

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003 performance (Excel 2003)

    Searching in list which is known to be sorted can be done much more efficiently than searching in a list for which you can't assume that.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003 performance (Excel 2003)

    HansV beat me to it...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel 2003 performance (Excel 2003)

    Can you use a function that uses a binary chop to find those list items?

    I will pass these comments on to the people who require it.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003 performance (Excel 2003)

    The functions which search in an ordered list are already optimised for that situation.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel 2003 performance (Excel 2003)

    I would have been surprised if they weren't. Thanks

Page 1 of 2 12 LastLast

Posting Permissions

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