Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a user that will regularly have to create, work on and manipulate very large excel sheets. He often will utilise Pivot tables querying 15,000 rows pulling numbers from 10 different sheets within the one spreadsheet. Not surprisingly, he is saying that the calculations can be incredible slow to the point of hanging and on average is adding a couple of hours to his working day.

    We currently use Dell laptops - Dual Core Intel processors(9300) at 2.26Ghz, with 2Gb of RAM so we are well beyond the minimum spec required to run Office 2003 applications, but can anyone advise on a spec that will cope with huge Pivots?

    Thanks

    Alba

  2. #2
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    Much has been said elsewhere in the Lounge regarding whether or not to install Office 2010 64-bit. One of the "pros" has been if large spreadsheets are to be used. Clearly this would require a system capable of running Windows 7 64-bit and office 2010 64-bit with more than 4GB RAM. Multi-core/hyperthreading capable processors will add further benefit.
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What you describe is not what I would call a huge spreadsheet. My suspicion would be that the formulas in use are inefficient.

    If you can upgrade to a later version of office you will benefit from multi-threaded calculation; if not, I would generally recommend using a workstation rather than a laptop, and use 3GB of RAM (if it's a 32bit OS) and as fast a processor as you can afford.

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alba,

    I'd second Rory's advice. One thing you can do is watch when the workbook is recalculating is the Disk Activity light going wild? If so, you need more memory. If not, you need a faster processor and/or tune up the efficiency of your formulas. Another option, although not easy to find in laptops, is to get a dual drive system and move the swap file to the second drive although I'm not sure you'll get much from this option.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you still want a Dell laptop, try one of these.*
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As an organisation we are not yet at the point of moving to Office 2010 (but have at least made the decision to bypass 2007) so this solution may be some time off - I guess that I will just have to find the most powerful machine that I can in the meantime!

    Thanks for your input

    Alba

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Memory and disk space can be conserved if you can answer Yes to the following question when creating a new pivot table in a workbook that already has a Pivot Table.

    [attachment=89497:Pivot Table save memory and space.jpg]


    You then get to choose what to use....

    [attachment=89498:Pivot Table choices.jpg]



    The references in the pics are to one of my workbooks. Your references will be different.
    Attached Images Attached Images

  8. #8
    New Lounger
    Join Date
    Feb 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi - I don't think 15000 rows is a large spreadsheet
    However pulling data from TEN worksheets for a pivot table !
    I can't conceive why anyone would want to try that.

    I would say 97% most likely cause of the problem is poor spreadsheet design.

    Some references - from Microsoft - where the developer thought a few seconds is how long a pivot table should take
    http://blogs.msdn.com/b/excel/archiv...es-faster.aspx
    ( It's old but gives you the flavor)

    Try a site like Walkenbach
    http://spreadsheetpage.com/index.php...g_your_data/#c
    or http://www.ozgrid.com/

    It could be worthwhile to pay someone to review the design of the spreadsheet before spending money on hardware.

Posting Permissions

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