Computer specification for large Excel sheets

Topic

New Reply

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

Reply | Quote

Replies

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 Rig: AMD Ryzen 9 5900X 12-Core CPU; ASUS Cross Hair VIII Formula Mobo; Win 11 Pro (64 bit)-(UEFI-booted); 32GB RAM; 2TB Corsair Force Series MP600 Pro 2TB PCIe Gen 4.0 M.2 NVMe SSD. 1TB SAMSUNG 960 EVO M.2 NVME SSD; MSI GeForce RTX 3090 VENTUS 3X 24G OC; Microsoft 365 Home; Condusiv SSDKeeper Professional; Acronis Cyberprotect, VMWare Workstation Pro V17.5. HP 1TB USB SSD External Backup Drive). Dell G-Sync G3223Q 144Hz Monitor.

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

If you still want a Dell laptop, try one of these. 

Reply | Quote

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

Reply | Quote

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.

You then get to choose what to use….

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

Reply | Quote

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/archive/2007/04/23/help-us-make-excel-pivottables-faster.aspx
( It’s old but gives you the flavor)

Try a site like Walkenbach
http://spreadsheetpage.com/index.php/comments/arranging_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.

Reply | Quote