Heavy User: Ultimate PC

[RXC]

Hi Mr. Excel,

I use Excel for analyzing large sets of data in Finance (like historical prices).

My spreadsheets are very large (25 Mo) with loads of VLookup and many Graphs.


My PC lags and often crashes when calculating, when scrolling ot lags as well because of the Graphs.

Can anyone recommend the ultimate PC given that I have a good budget but I don't want to waste money either on hardware which wouldn't be used by Excel 2010.

In particuar, can Excel use the following:
-64bit Windows 7?
-Quad Core?
-Dual Quad Core?
-A lot of RAM (max?)?
-Would a high-range graphic card help or not?

Thanks!

 

[MrKowz]

Excel 2007 and higher can utilize multi-core processing. However, the more lookup-style or array-entered formulas you have, the slower your spreadsheet will be altogether. I run on an Intel i7, 8gb RAM, 1gb gfx card, and I still come across spreadsheets that are painfully slow.

 

[JackDanIce]

I'm guessing there are other ways you could structure your data to avoid some of the problems

There are likely to be ways to use other formulae
-(e.g. combination of INDEX and MATCH which returns answers faster than VLOOKUP when using large data sets)

Have you checked that the last used cell in each worksheet is what you expect is the last cell or is it much further away?
-(e.g. you think your data is in the range A1:J10, but when you press CTRL+END, it selects the cell CD5000)
This can drastically increase the size of your file it believes the last used cell is much further away than it should be

Is it feasible to seperate the data into one (set of) workbook(s) and the graphs into another?

Have you thought about putting the data into a database and running queries to only extract that what you need into a workbook? (This would also drastically reduce your file sizes)

 

[RXC]

Actually I use these Spreadsheets for my job. So I would rather spend $5,000 on a new PC and have it to work the way it is rather than reorganizing the data.

I want the ultimate workstation for Excel 2010 without budget constraints but without picking useless extras (i.e. if the Graphic card doesn't speed-up things; then I don't want it).

 

[JackDanIce]

And I'm sure your financial controller would have questions if it felt you were requesting a new PC because you hadn't exhausted all options (i.e. being lazy!) to make it work

Joking aside, only one of my suggestions was about data re-organisation.

The rest are fairly easy to implement and quick win suggesions with potential of being much easier to speed up your calculations or reduce your file size than hoping a new PC will solve your problems - what happens when your file calculations become too slow or your data set increases in size?

As a practical matter, I'm guessing you/someone else could probably make those changes within 24 hours, whilst the time it takes waiting for a new PC to arrive may be longer - during which time will you not be working on it or getting annoyed at it crashing or stalling? And then the time taken to set up all your software on the new PC, get it linked to your work network, other related issues (suppose this could all happen at the weekend when I'm assuming you won't be working so it's ready for you come Monday).

Finally, if you get a better understanding or feel for bottlenecks (if any) in your spreadsheets, it could help you better use and design them in the future (i.e. improving your own work).

Just some helpful suggestions if you're in a hurry for a solution, otherwise, I'm sure any supplier of decent hardware PCs could recommend the ideal purchase, without the need for unrequired extra's. I know hedge-funds are certainly exploring the use of using graphical cards to make faster mathematical computations to speed up trading decisions but I'm fairly certain a powerful graphics card will have next to no effect on a spreadsheet where the calculations are more dependent on the processor, amount of memory (main and storage), motherboard type and connections, data transfer rate etc etc..

 

[MrKowz]

You can get by with a solid workhorse computer for around $1000 easily.



Look into a computer with the following specs:

• Intel i7 860 processor (it is among the better mid-range i7s, the higher end ones are for gaming purposes)
• 8-16GB RAM
• 500W Power Supply (bit excessive for this build, but a little extra power will help to maintain overall stability)
• Windows 7 64 bit
• Office 2010 (this is an a-la-carte item that you will purchase separate from the computer)

If you know anyone who can build computers, you can get these parts from www.newegg.com and likely build the computer for under $700, then get Windows 7 for $200 (regular edition), Office 2010 for $150-$500 (depending what version you need). I would highly recommend having the computer built rather than purchasing a pre-built due to the large amount of bloatware and uneccesary programs that are usually preinstalled.

Particular brands I would recommend steering clear from if you purchase a pre-built are:

• eMachines
• Gateway
• Dell
• Acer

My favorite pre-builts are ASUS and HP.

Also, your antivirus of choice does greatly affect overall computer performance. Do not use Norton Antivirus - that is time and time again proven to hinder a computer's performance drastically. I like to use Kaspersky Antivirus (lightweight and catches a lot more).

 

[Smitty]

Noting the fact that if you have 25+Mb workbooks on a regular basis, you should probably be rethinking your strategy, a new PC can't hurt. I recently helped my brother build a new box bassed very closely on one I built about 2 months earlier...The components all came from www.tigerdirect.com.

CoolerMaster HAF 932 Case
CoolIt Liquid Cooling
700W Power Supply
ASUS Mobo
Intel i7 930 Quad Core @ 3.2 Ghz
12Gb RAM
2 x 1Tb HD's
1Gb Video Card
DVD Drive
Windows 7 64 bit
Office 2010 Professional Plus (32 bit - After uninstalling the 64-bit version, because too much stuff won't work with it yet).

I also added hard drive cooling fans, extra fans and a fan controller, but they're not necessary if you're in an air-conditioned office (I'm not). I can't speak to the necessity of a video card, but if you're using graphic intensive workbooks it's not going to hurt.

I think we were both out less than $1,400 for each. His is mostly for home theater, but mine runs everything Excel & Access with no problem, including large db's and the occasional 25+Mb workbook.

I'll second what Keith said about Norton - I don't think I've ever heard of it not causing problems. Kaspersky is good, although I use Microsoft Security Essentials with no problem.

 

[RoryA]

I've not heard of problems with current versions of Norton, FWIW, but I'd rather go without AV than use McAfee. I use ESET (disclaimer: I get a free licence as an MVP) because it uses minimal system resource and I've not had a problem with it. It's also the first security software I've had installed that my wife has never complained about.

For the rest I'd say 25MB ain't that big but VLOOKUPs are generally implemented very inefficiently and charts can be real resource hogs. They are also very dependent on printer drivers.

 

[MrKowz]

I've not heard of problems with current versions of Norton, FWIW, but I'd rather go without AV than use McAfee. I use ESET (disclaimer: I get a free licence as an MVP) because it uses minimal system resource and I've not had a problem with it. It's also the first security software I've had installed that my wife has never complained about.

For the rest I'd say 25MB ain't that big but VLOOKUPs are generally implemented very inefficiently and charts can be real resource hogs. They are also very dependent on printer drivers.

The last Norton I dared to attempt to use was Norton 2010, and I did that under a clean build. After I installed it, my computer's processing power dropped by about 15-20% (estimated). eSET, Kaspersky, and (if you want to go the free route) Avast! are the top three antiviruses I've found as far as processor effeciency. I haven't delved into the Microsoft Security Essentials because, to be frank, I have not been impressed with any security feature Microsoft has put out in any program/OS. If their browser is hacked faster than any other browser out there (there was a contest a month or so ago), and their OS is the largest-targeted OS for viruses, it does not give me much hope for their other security.

But, alas, this is just the opinions and ramblings of one person. I do believe "to each their own"... except when it comes to McAfee. I am fully on Rorya's side when he states that he'd rather be without antivirus than use McAfee... because by installing McAfee, not only will it not catch viruses, but you get the added benefit of your computer slowing to a crawl.

 

[Domski]

I just bought a new laptop with a 2nd Gen Core i7 and it's well fast!!! From what I've read the 2nd Gen Sandy Bridge are loads better than the originals. Waiting for Crysis 2 to be delivered to give it a real test (although it'll rumble through the odd spreadsheet quite nicely)

I've been put off Norton after having problems with it before and been using Kaspersky for a few years without any complaint although I'm trying McAfee on the above as it came with a free license.

Dom