Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Best config for heavy Excel usage

Status
Not open for further replies.
bilojax

bilojax

Programmer
Feb 17, 2003
41
US
I have NEVER had enough power to run the Excel worksheets I build at work. I always have to break them up into pieces and manually convert formulas to values to keep them from crashing my computer.

Well, PCs keep getting better, and I've finally landed in a job where my boss sees that buying me the best possible system will pay off in greater productivity. So, maybe between the two, I can finally get close to a system that can handle my Excel work. But I'm not sure what configuration would be best. Besides the top available processor, what other enhanced hardware components would let me do more of the following:

Typical Excel application - Start with 60,000 record download from SQL database with 40 fields per record, and import that into an Excel sheet. Add 10-20 columns of formulas which contain multiple IF and VLOOKUP statements. Then go to another sheet and construct several 20 column by 100-1000 row summary tables, each cell of which contains a SUMIF or COUNTIF formula keying off the main download worksheet. Once the workbook is completed, numerous subsequent downloads will be plugged into the same model.

So, in other words, there is a lot of pure data to start with, then there is a lot calculation and referential overhead contained in the programming, and the formulas need to stay live so new data can be plugged in.

Based on the above:

1. Would "hyper-threading" (Opti-plex) of the processor improve performance?
2. Among Dell processor options, does more "L2 cache" memory help? "Dual-core"? "HT"?
3. How much does RAM matter? Would I see a big difference from 1 GB of RAM versus 512 MB? 2 GB versus 1 GB? 4 vs 2?
4. How about the other RAM options? DDR2 versus non-DDR2 (or whatever the alternative is)? ECC versus NECC? 2 versus 4 DIMMS?

Is there anything else that matters?

Thanks for any help or advice you can give.

 
Sort by date Sort by votes
Sounds to me like the problem is Excel. Its not meant to do this much work! An Access or other application would be better I think.

However.

Excel benefits from RAM and CPU power. Perhaps you should monitor what your PC is doing when you run the sheet using taskmanager. Is it at 100% CPU, Is it swapping out to disk? How much RAM is the application taking? That may give you a better idea of what is required.

You may want to consider going 64 bit as well. That allows RAM greater than windows 2-3GB current limit with 32 bit systems. (64 bit can handle Terrabytes of RAM in theory)
 
Upvote 0 Downvote
Agree with stduc, RAM, RAM, and more RAM. All you can get.
 
Upvote 0 Downvote
Excel work is not an especially intense application for todays PC's....vs video editing etc. Excel is predominantly CPU/FPU intensive. Screaming video is not a benefit here.

Size of excel file will predicate speed/swapping environment.

Additional RAM will assist with minimizing swapping I would opt for 2GB RAM if budget allows....if XP, 1GB will increase performance alot over 512.

AS you are spec'ing this out and not attempting an upgrade I would also look to the most advanced "latest" technology to allow a future for this machine.

Meaning support for most current either Intel or AMD CPU's
higher front side bus
DDR2 memory
SATA support

When config'ing this machine..you might also consider relocating your swap file to a separate fast HD.

Are you stuck on DELL??

rvnguy
"I know everything..I just can't remember it all
 
Upvote 0 Downvote
Using Task Manager is a good idea, except I don't know what most of the stuff means other than CPU usage - and that stays at 100% from the time of importing the data to when it finishes.

I loaded a sample file, a small but typical version that I had already scaled down to run efficiently in the present environment. CPU usage showed a brief jump during the load. In the other categories, simply opening Excel decreased available physical memory (is that RAM?) from 188M to 162M, increased commit charge total to 301M from 277M and increased kernel memory total from 55M to 60M. Loading the workbook changed these three to 126M, 336M and 60M, respectively.

I then changed a data item in the download, triggering a recalculation. CPU usage went to 100% and stayed there for 50 seconds. None of the memory meters changed much during this process, just a few hundred K one way or the other, and these minor changes continued after the recalc was finsished (note, Word, Outlook and several IE windows are also running at the same time).

So, I guess that just means I need more GHz to calculate and more RAM to load. How about all the confusing marketing blather they attach to these two components (NECC, dual-core, 4 DIMM, etc)? Is any of that stuff meaningful for my purposes?
 
Upvote 0 Downvote
Unfortunately, yes, my company is stuck on Dell. As for a future, that is fixed, whatever I get will be mine for 2 years and then I can upgrade again to whatever is current.

Higher front side bus - is that the "L2 cache" thing? There's also a spec on the processor line on our quote sheet "2M, 800 FSB" - what does that mean (assuming FSB = front side bus).

What is SATA support?

Thanks for the replies.
 
Upvote 0 Downvote
P.S. Just noticed in the quote specs that SATA is specified on the hard drive already. "160 GB SATA, 7200 RPM
 
Upvote 0 Downvote
A faster hard drive will speed up the initial load.
Sounds like you need at least 1GB of RAM
Sounds like you need the fastest RAM available with DELL.

I'll leave it to others to discuss cache front side bus. LOL
 
Upvote 0 Downvote
As you now have a baseline for this specific operation @50sec's. I would repeat the same routine several times, recording times. Burn this file/info to a CD.

If you have a DeLL seller that you can preview systems at or a reseller that will bring one out????

You can repeat this routine on any PC you are condidering and see if the time is reduced (hopefully significantly). Do this before making the purchase so that you know that the selection will perform the task as you desire.

Hope Thias Helps

rvnguy
"I know everything..I just can't remember it all
 
Upvote 0 Downvote
If you're stuck with Dell, look into the Precision workstations. We've got a few and they have held up well. Stay away from the optiplex gx-280, we're running about 60% failure on a batch of 25.
 
Upvote 0 Downvote
Adding more 'grunt' to your PC will improve things somewhat but Excel is still inefficient when it comes to number-crunching, so if you already have a reasonable-spec machine you won't see a huge improvement from upgrading it.

One of the departments where I work uses a complex Excel spreadsheet for statistical modelling and it was getting to the point where it would take hours to run. I did some programming work for them putting some of their functions into a DLL written in C and this had a HUGE impact on performance. What took hours to run before now took minutes. I'm not suggesting you learn C and do this yourself but it may be financially worthwhile paying for someone to do a similar job for you. If the slowness of Excel is having a negative impact on your work, then all that wasted time is costing your company money anyway.

Regards

Nelviticus
 
Upvote 0 Downvote
bilojax, I do exactly the same type of work you do (boring isn't it)my system is a p4 3.4ghz 800mhz fsb with hyperthreading and 2gb ddr400 RAM and excel still takes its time but on the other hand it rarely crashes. STDUC is correct microsoft access is the way forward. Set up an ODBC connection to the SQL server within access and import all your data to access. from Access you can create queries that will pull only the data you require for each report and output it to far smaller more managable Excel files where you can do your VLOOKUP and IF etc.Or better still if you have an SQL server make SQL do all the work for you, and you shouldnt need to use excel until the very last stage!!???one last thing dont overlook a good monitor 19" TFT at least, with several sheets open its nice to be actually able to read the data, without squinting which ultimataly leads to a headache :)

If you dont ask you wont get :)
Steve Poole
Technical Analyst
Cinram UK ltd
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bryan - Gendev
  • Locked
  • Question
Mouse not active after return for sleep WIN11
Replies
4
Views
358
xwb
xwb
DrB0b
  • Locked
  • Question
Sandbox environment for URL research 1
Replies
6
Views
218
DrB0b
DrB0b
DrB0b
  • Locked
  • Question
Creating new intranet for dummies 2
Replies
4
Views
128
DrB0b
DrB0b
DrB0b
  • Locked
  • Question
Looking for a LAN Messenger-eque program that runs on Windows CE
Replies
0
Views
106
DrB0b
DrB0b
AncientTiger
  • Locked
  • Question
What's a good tool for logging failures
Replies
6
Views
121
goombawaho
goombawaho

Part and Inventory Search

Sponsor

Back
Top