Results 1 to 7 of 7
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Excel performance (Excel2003)

    Hi all

    This is a question about trying to squeeze the maximum performance from Excel.

    I am running an automated data processing routine in Excel that takes 240 secs to complete on a 2Mhz Core2 duo with 2GB RAM under Windows XP.
    (the routine involves data calculations with a pivot table where I need to change the values of two page fields each containing about 100 values i.e. around 10,000 pivot table updates)

    I then have to do this for each of 400 offices.

    I then have to repeat this for each of four years.
    So I am keen to try and reduce the time taken as much as possible.

    I believe I have optimised the process as much as possible - it'sjust a lot of data crunching.

    So I bought a QuadCore 2.4Mz with 3Gb RAM and 1Tb drive (Vista Home Premium)
    When I ran the same routine on the new machine it took 262 secs.
    I wasn't expecting that!
    It took longer!!

    Well, I thought maybe I could still save some time by running four separate Excel sessions (one for each of the processing years) at the same time.
    Each Excel session uses a different copy of my processing tool, running with data kept in separate source folders (there is no disk activity during the process).

    I split the four Excel windows (they look nice on the 22-inch wide screen flat panel) and clicked each start button in turn.
    Bummer! Three of the windows generated a VBA error when pasting data from the pivot table to the summary block.

    Should I try something else????
    Or is my only solution to have four separate PCs running this task????
    Or what is the quickest fastest best configuration for running such Excel stuff????

    zeddy

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

    Re: Excel performance (Excel2003)

    Splitting the job over several PCs is expensive but it will definitely reduce the total processing time.

    Some tips on optimizing Excel performance:
    Faster Excel Calculations and related pages on the DecisionModels website.
    Optimize VBA on Chip Pearson's website.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Excel performance (Excel2003)

    Hi Hans

    I am running out of space.
    I've got another quad core system sitting in a box outside.
    I try to keep a system with a similar Excel version and operating system that the end-User will be running.
    The combinations are now exceeding my ability to keep track.

    Why can't I have a system that
    1. Lets me pick the op. system first, then
    2. Lets me pick the Excel version

    It's doing my head in.
    And don't get me started on resolutions.
    I've just ordered new glasses to help with the 1920x1200 on this laptop.

    (I have FastExcel - very useful)
    zeddy

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

    Re: Excel performance (Excel2003)

    You could set up a dual (or multi) boot system...

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Excel performance (Excel2003)

    This will deal with the op system issue.
    But I've always had problems with multiple Excel versions.
    I keep a separate system for historical Excel97 stuff.
    But still have to deal with Excel2000, Excel2002, Excel2003 and Excel2007

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Excel performance (Excel2003)

    With flash drives being cheap and high capacity, do you thing it possible to 'install' different Excel versions onto separate flash drives, then run the required Excel from the flash drive??

    zeddy

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

    Re: Excel performance (Excel2003)

    Not directly (I think), but you could use a virtual machine. See for example the recent thread <!post=Virtual PC on (in?) a stick,686095>Virtual PC on (in?) a stick<!/post>.

Posting Permissions

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