[SOLVED] Best CPU Solution for Large Complicated Excel Files

[EricThomas]

I work at an investing firm and we often receive significant amounts of data on companies that we need to crunch in excel. At the extreme, this can be as much as 20 million data points, with millions of rows of volatile formulas such as indirect functions, index match, etc running off of this data, interconnected across dozens of tabs. As a result, my company-issued laptop which we use as a desktop replacement, a Lenovo ThinkPad X1 Carbon, either can’t do these tasks or it can take several days to do a task that could have taken 20 minutes if it weren’t for the lag. I’m running a dual core i7 with 2.7ghz or so and 16 gb of ram - so lots of room for improvement to say the least.

Even when not working with these very large and complex files, my CPU reaches 100% utilization literally every single day and causes significant efficiency to my workflows.

There would be a major financial return on investing in high powered desktops or laptops for our organization and I have convinced the firm of the need. Because of the nature of our work, we are willing to pay for something that will be lag free for everything we do because it will directly pay for itself many times over.

Some are going to tell me I should use Tableau or something else for these files and I would ask those people to, respectfully, please press the back button on your browser now.

For Excel in particular, are there any limits to how much it can take advantage of clock speed, cores, or i7/I9/Xeon?

Does anyone have ideas on the best path to take to not have CPU be a bottleneck? What computer should we be buying? We’d probably pay up to $4k per machine if necessary.

Our IT firm is horrendous and has been really unhelpful here. In general everyone we speak to underestimates our level of need because they do not understand our work.

Thank you very much in advance for your honest advice.

 

[SkyNetRising]

To give any such recommendations, workload analysis should be performed.
Monitoring of CPU, RAM, Disk usage is necessary.

Component hitting 100% utilization obviously needs to be upgraded.
If cpu is at 100% utilization, then more cores are necessary. Then again - too much cores may not give any improvement over somewhat less cores.

For example i7-8700(k) has 6 cores/12 threads. Should be enough for your workload.
i9-9900(k) has 8 cores/16 threads. Would it provide better performance for your type of workload? Hard to tell. You'd have to test it.

 

[13thmonkey]

100% on both cores? including HT cores? how much memory are you using?
The key question is how parallel is the data, if each 'row' is independent of others then you can create a high degree of parallelism, if each core is not then you will struggle.
You need to test it.
Does anyone in the business have a better PC? Can you encourage any suppliers to 'demonstrate' a PC with a modern CPU?
Personally I'd be thinking next gen ryzen (coming very soon), get an R7, and you might get 12cores, 24 threads (if rumours are true) at a decent clock speed (rumours). Intel you are stuck on 8 cores unless you go i9, in which case 8 core 16 threads, and something that is a monster to cool, is high in the stack and I wouldn't want it in an office machine (v noisy without careful consideration). Or go workstation class machines, high end desktop, threadripper, xeon etc. but you are probably hitting diminishing returns at that point.
Will it be real time, not a chance, could it be 1-2 mins, quite possibly.
Re-working your workflow however is a consideration for the next 12-24 months, you are at the edge, and that's not a fun place to be.

 

[cdrkf]

To follow up with what SkyNetRising was saying- the tricky part is Excel does use multiple cores- but not for all functions. So some functions will get faster the more cores you provide, but then you can easily get bottlenecked if you rely on a function (example given on MS forum is macros) that can only use 1 thread. On that basis you really need a combination of high core count and high clock speed to get the best balance.

Here is a recent review of the new ThreadRipper cpu's which includes a comparison in an Excel workload (however keep in mind your workload may be quite diffrent):
https://www.techspot.com/review/1678-amd-ryzen-threadripper-2990wx-2950x/page2.html

Note that the 2990WX has twice the core count of the 2950X, yet actually performs slower than the latter due to lower clocks / reduced memory bandwidth.

I would strongly recommend you look at a desktop machine however, you cannot hit the frequencies or core counts with laptops.

Edit: If you would like some recommendations to look at, I think a good HEDT based machine would be a good idea. To keep in a sensible budget you probable need to look at a machine with 12 - 16 cores.

Current Intel Options: i9-9920X (12 core, up to 4.4ghz) or i9-9960X (16 cores, also up to 4.4 ghz).
AMD options: Threadripper 2920X (12 cores up to 4.3ghz) or 2950X (16 cores up to 4.4ghz)

The 2950X will likely sit between the two Intel options in performance (however it costs less than the 9920X) so probably represents the best value, however Intel might be the way to go if you are looking for best performance at any cost.

Memory wise, all these cpu's need quad channel memory so you'll need to be pairing them with at least 32gb of ram (4 x 8).

The other thing that is a good idea with this type of workload (and in general) is fast storage- ideally you want your data to be stored on a NVME SSD drive for best loading / saving performance.

 

[RobCrezz]

Do you plan to use a desktop/server?

Im familiar with Tableau and we tend to run it in a vm with pretty high cpu and ram resource (12cores and 64gb ram), with the SQL running in a seperate mssql vm.

 

[jsmithepa]

Im familiar with Tableau

The man says he's stuck with legacy Excel, and nothing to do buy keep asking Scotty for MORE POWER! and must be in a laptop.

 

[cdrkf]

The man says he's stuck with legacy Excel, and nothing to do buy keep asking Scotty for MORE POWER! and must be in a laptop.

He does say they could go for a desktop machine....

 

[hang-the-9]

What version of Office are you using, and is it 32 or 64 bit office? You would want 64 bit office, since 32 bit office will still limit memory use for the program.

Excel is pretty bad with speed and crashing/locking up when there is a lot of data and calculations it does, it is probably the #1 complaint I see for "computer is running slow".

We have also been buying programs like Tableau.

For a system, a desktop workstation or a laptop workstation (not a light system like the Carbon, those never get very fast due to heat issues) are good. I like the Lenovo workstation laptops if you need to be portable.

In my tests a few years ago, a 3 years older quad core i7 ran Excel much faster and more stable than a newer dual core laptop i5.

 

[RobCrezz]

What version of Office are you using, and is it 32 or 64 bit office? You would want 64 bit office, since 32 bit office will still limit memory use for the program.

Excel is pretty bad with speed and crashing/locking up when there is a lot of data and calculations it does, it is probably the #1 complaint I see for "computer is running slow".

We have also been buying programs like Tableau.

For a system, a desktop workstation or a laptop workstation (not a light system like the Carbon, those never get very fast due to heat issues) are good. I like the Lenovo workstation laptops if you need to be portable.

In my tests a few years ago, a 3 years older quad core i7 ran Excel much faster and more stable than a newer dual core laptop i5.

I agree. If it has to be a laptop, look a proper desktop replacements with the higher end 6 core cpus.

 

[geofelt]

If you need portability, buy a laptop.
Unfortunately, laptops have limited cooling capability and can throttle under heavy cpu load.
A laptop solution does not seem to be right for this application.

Next is the question of How multithreaded your EXCEL application may be.
How much does YOUR app depend on single thread performance?

I suggest you build a pilot test system.
Set up a representative test benchmark.

Today, the fastest single thread processors come from Intel 9th gen. I suggest a test system based on a i9-9900K with 16 threads that costs about $500. Add to that a motherboard, lots of ram and you can build for less than your $4000 budget.
If your workload is truly multithreaded, then there are processors with higher core/thread counts. On your pilot system, you can reduce the thread count to see if there is a big impact on run times. If reducing threads has a big impact, then consider a processor that gives up on cpu speed for more tasks.
Amdahl's law may start to impact your results with a very large number of threads
https://en.wikipedia.org/wiki/Amdahl's_law

Also, see if you can determine how much ram you need to hold all of your data in ram where access is fastest. 128gb is possible.
If it is impractical to hold everything in ram, you need to consider a very fast I/O configuration.

 

[EricThomas]

First of all, thank you all very much for taking the time to help out here. It is unbelievably helpful to be talking to people who actually know what they're talking about.

100% on both cores? including HT cores? how much memory are you using?

The key question is how parallel is the data, if each 'row' is independent of others then you can create a high degree of parallelism, if each core is not then you will struggle.

You need to test it.

Does anyone in the business have a better PC? Can you encourage any suppliers to 'demonstrate' a PC with a modern CPU?

Personally I'd be thinking next gen ryzen (coming very soon), get an R7, and you might get 12cores, 24 threads (if rumours are true) at a decent clock speed (rumours). Intel you are stuck on 8 cores unless you go i9, in which case 8 core 16 threads, and something that is a monster to cool, is high in the stack and I wouldn't want it in an office machine (v noisy without careful consideration). Or go workstation class machines, high end desktop, threadripper, xeon etc. but you are probably hitting diminishing returns at that point.

Will it be real time, not a chance, could it be 1-2 mins, quite possibly.

Re-working your workflow however is a consideration for the next 12-24 months, you are at the edge, and that's not a fun place to be.

How can I tell if it's 100% on both cores? When I go to task manager, the CPU column says 100% so I assume it's all cores? Memory typically doesn't go above 70% or so and I have 16 gb of RAM so would assume I don't need more than 32 gb in the new computer.

Nobody has a better PC at our firm. It's a good idea to have someone demo a PC - I can ask about this.

We were discussing having one very fast desktop for the office that people share for when they get the super complex files, and our IT vendor came up with the below - anyone have any views on this / if it would be optimal? This was the fastest processor they had in their inventory. Sounds like Ryzen might be even better? Would i9 be better than Xeon all else equal for these purposes or the reverse? They have a similar version of the below but with i9.

Dell Precision 5820 Tower,
Intel Xeon W-2145 3.7GHz, 34.5GHz Turbo, 8C
64GB of RAM
(2) 512GB SSD, RAID 1,
Dual NVIDIA® Quadro® P1000, 4GB, 8 mDP,

Then we were thinking of buying ThinkPad X1 Extreme laptops for each of us to use day to day , which has the following processor available:

8th Generation Intel® Core™ i7-8850H vPro 6 Core Processor (2.60GHz, up to 4.30GHz with Turbo Boost, 9MB Cache)
32 gb of RAM
512 GB SSD

I don't love that it's i7 (vs i9 or Xeon which I presume is better) and only has 2.60GHz clock speed (so for single threaded actions in Excel it sounds like you guys are saying this would create a bottleneck) but I like that it’s 6 cores. But would a desktop CPU be faster than a laptop CPU, assuming the same cores / clock speed / i version?

To follow up with what SkyNetRising was saying- the tricky part is Excel does use multiple cores- but not for all functions. So some functions will get faster the more cores you provide, but then you can easily get bottlenecked if you rely on a function (example given on MS forum is macros) that can only use 1 thread. On that basis you really need a combination of high core count and high clock speed to get the best balance.

Here is a recent review of the new ThreadRipper cpu's which includes a comparison in an Excel workload (however keep in mind your workload may be quite diffrent):
https://www.techspot.com/review/1678-amd-ryzen-threadripper-2990wx-2950x/page2.html

Note that the 2990WX has twice the core count of the 2950X, yet actually performs slower than the latter due to lower clocks / reduced memory bandwidth.

I would strongly recommend you look at a desktop machine however, you cannot hit the frequencies or core counts with laptops.

Edit: If you would like some recommendations to look at, I think a good HEDT based machine would be a good idea. To keep in a sensible budget you probable need to look at a machine with 12 - 16 cores.

Current Intel Options: i9-9920X (12 core, up to 4.4ghz) or i9-9960X (16 cores, also up to 4.4 ghz).
AMD options: Threadripper 2920X (12 cores up to 4.3ghz) or 2950X (16 cores up to 4.4ghz)

The 2950X will likely sit between the two Intel options in performance (however it costs less than the 9920X) so probably represents the best value, however Intel might be the way to go if you are looking for best performance at any cost.

Memory wise, all these cpu's need quad channel memory so you'll need to be pairing them with at least 32gb of ram (4 x 8).

The other thing that is a good idea with this type of workload (and in general) is fast storage- ideally you want your data to be stored on a NVME SSD drive for best loading / saving performance.

Super helpful. I think building our own machine would be out of the question because nobody here has the time or expertise. Can the processors you are contemplating be gotten in off the rack computers that have standard warranties, etc or would you recommend going to a company that builds custom machines (like Puget Systems (or maybe you know of one better than them))?

I didn't realize that certain CPUs required certain amounts of memory - I always thought they were independent - thanks for calling to my attention. Is it just the CPUs you mentioned that require this or is there some rule of thumb around core count or something? Would more than 32 gb be advisable even if I'm only hitting 70% RAM usage or so on a heavy day even with 16 gb currently?

Yes we'd be using SSD for local storage but we also run files off of a network which I do think adds some sluggishness to the setup.

You mentioned single threaded excel operations are going to be hampered by low clock speed. I'm assuming the below would not be a good setup then due to the low basic clock speed? It has 4.30GHz Turbo Boost but I'm not sure what that means in practice. I asked my IT department to overclock my laptop and they said it would be a heating concern.

8th Generation Intel® Core™ i7-8850H vPro 6 Core Processor (2.60GHz, up to 4.30GHz with Turbo Boost, 9MB Cache)
32 gb of RAM
512 GB SSD

He does say they could go for a desktop machine....

Correct - we are open to getting desktops for the team.

The reason we'd prefer laptops in a perfect world is that we are frequently on the road traveling to various cities meeting with companies and will often be forced to do work on airplanes, in hotels, etc.

That said, we do have a remote desktop solution that could theoretically allow us to use our current underpowered laptops to remote into our higher powered desktops at the office while we're on the road which could mitigate the issue but hotels often have bad wifi / aggressive firewalls and planes especially are nightmares for internet access. It's also more convenient to have one system that we can use everywhere that has all of our settings, plugins, programs, files, etc on it rather than having both a desktop and laptop.

However, a desktop is absolutely a direction we are willing to go down if the performance difference is large between a laptop like the one I outlined above and what these desktops can do for Excel in particular. Performance is our number one concern and we spend most of our time in the office.

That said, if you were to tell me that Excel caps out at a certain level and with the level of speed in the laptop I mentioned, we will be on an asymptote with respect to performance for what the program can handle that can't really be improved upon much, then we would prefer the convenience of a laptop.

What version of Office are you using, and is it 32 or 64 bit office? You would want 64 bit office, since 32 bit office will still limit memory use for the program.

Excel is pretty bad with speed and crashing/locking up when there is a lot of data and calculations it does, it is probably the #1 complaint I see for "computer is running slow".

We have also been buying programs like Tableau.

For a system, a desktop workstation or a laptop workstation (not a light system like the Carbon, those never get very fast due to heat issues) are good. I like the Lenovo workstation laptops if you need to be portable.

In my tests a few years ago, a 3 years older quad core i7 ran Excel much faster and more stable than a newer dual core laptop i5.

I switched over to 64 bit office and don't know if it made any difference unfortunately.

Funny you should mention Lenovo workstation laptops: I had been discussing the P72 with my team which is, according to their site, the fastest mobile workstation Lenovo makes. However, here's the fastest processor available with the P72:
Intel® Xeon® E-2186M 6 core processor with vPro™ (2.90GHz, up to 4.80GHz with Turbo Boost Technology, 12MB Cache)

And this is the highest option for the X1 Extreme
8th Generation Intel® Core™ i7-8850H vPro 6 Core Processor (2.60GHz, up to 4.30GHz with Turbo Boost, 9MB Cache)

Am I wrong to assume that the processors aren't that different because they're both 6 cores and only 0.30 GHz different at the low end and 0.50 GHz different with Turbo Boost? Or does the higher "Cache" make a difference - or is Xeon much faster than i7?

They really make this stuff difficult to compare for the average person like myself!

Is there a laptop that is better than the ones I've outlined, with higher clock speeds and 6+ cores, etc?

If you need portability, buy a laptop.
Unfortunately, laptops have limited cooling capability and can throttle under heavy cpu load.
A laptop solution does not seem to be right for this application.

Next is the question of How multithreaded your EXCEL application may be.
How much does YOUR app depend on single thread performance?

I suggest you build a pilot test system.
Set up a representative test benchmark.

Today, the fastest single thread processors come from Intel 9th gen. I suggest a test system based on a i9-9900K with 16 threads that costs about $500. Add to that a motherboard, lots of ram and you can build for less than your $4000 budget.
If your workload is truly multithreaded, then there are processors with higher core/thread counts. On your pilot system, you can reduce the thread count to see if there is a big impact on run times. If reducing threads has a big impact, then consider a processor that gives up on cpu speed for more tasks.
Amdahl's law may start to impact your results with a very large number of threads
https://en.wikipedia.org/wiki/Amdahl's_law

Also, see if you can determine how much ram you need to hold all of your data in ram where access is fastest. 128gb is possible.
If it is impractical to hold everything in ram, you need to consider a very fast I/O configuration.

Thanks - so I'm assuming this means a laptop with the same processor specs on paper as a desktop will be slower due to this cooling problem?

On your RAM point, are you saying that having more RAM is going to help me from a CPU perspective when that is the part that is maxing out in Task Manager? Does RAM step in to compensate somehow?

I agree. If it has to be a laptop, look a proper desktop replacements with the higher end 6 core cpus.

Sounds like you're saying the laptops with 6 cores are true replacements for desktops from a performance perspective? Or you're just saying they are the best among bad options?

 

[13thmonkey]

I'd say best amongst bad options, ok for short bursts of speed, but not sustained speed.

The desktop/workstation that was offered is ok, the quadro is overkill as is the ram, unless your switch to 64 bit excel has an impact on ram usage.

If you are at 100% then both cores will be, right clicking on the cpu graph in taskmanager will give the option to show individual cores. If all four are high then note will probably be useful.

 

[RobCrezz]

Yeah I agree, pretty good, but not as good as the desktop chips.

Ideally a workstation with a 9900k would be great as you get 8c/16t and also very high single core performance.

 

[geofelt]

A bunch of questions, let me see if I can address some:

Task manager interpretation can be tricky. In your case when you see 100% you can be certain that all cores are fully utilized and that cpu performance is your limiting factor.
Windows will spread out the activity across all available threads.
So, if you happen to see less that 100% busy, your limitation could very well be because you are limited by the performance of a single thread and what you see is that thread activity spread over many threads.
Individual single thread performance is almost always of great importance.

Memory utilization is also tricky. Windows manages memory and keeps some amount available for instant use. Windows will also keep unused code in ram so that it is available for instant reuse. Other than cost, you can hardly have too much ram.
If you look at the hard fault page rate, that would identify a serious shortage of ram if the number is anything like one per second.

Since you are looking at multiple units, it makes sense to have a test system built.
I have no personal experience with PUGET systems, but I like their performance articles and think they would be a good option for a professionally built machine.

I3-I7-I9 does not tell the whole story.
It used to be that I3 was 2 cores, I5 was 4 and so on.
Laptop processors have different I-X meanings.
To get a reasonable idea of the performance capability of a processor, google the processor name and passmark.
For example i9-9900K would result in this:
https://www.cpubenchmark.net/cpu.php?cpu=Intel+Core+i9-9900K+@+3.60GHz&id=3334
It would identify a i9-9900K as having 8 cores plus 8 hyperthreads.
A hyperthread is a separately dispatchable thread that uses residual capabilities of the main core.
The performance rating of 20163 is the overall capability as measured by many user sample benchmarks.
The single thread rating of 2899 is the performance capability of a single thread.
The I7-8850H has 6 cores(12 threads) and a total rating of 13119 and single thread of 2474

The clock rate does not tell all. The performance per clock is what is important.
Today, the performance per clock for current 9th gen processors is about the same. Today, ryzen is very close to Intel on performance per clock, but not quite there.
Supposedly that will reach parity on the next ryzen gen.

There is a stock clock rate that all cores can run at.
Then, there is a turbo rate which allows a single core to speed up if conditions are right.
This happens when temperatures are under control and when other threads are not highly utilized.

K suffix processors can have adjustable core speeds(sometimes called overclocking)
This allows all cores to operate at a consistently high clock rate.
This is done by increasing core voltage.
How high is determined by the quality of the chip, something that Intel can not guarantee.
Running at higher voltage causes an increase in heat buildup so overclocking needs a better than average cooler.
As of 2/6/2019 from silicon lottery:
What percent can get an overclock at a somewhat sane vcore in the 1.275v to 1.312
And AVX offset = 2.

I9-9900K
4.8 100%
4.9 86%
5.0 39%
5.1 8%
Current ryzen processors top out around 4.3

The intel processors will normally have integrated graphics which is sufficient for display purposes.
Some apps can use the CUDA cores of Nvidia graphics, but I have no knowledge if your app had that capability; I think not.

On ram, there are two things to look at, quantity and speed.
On quantity, you want to hold as much of the data you are working on in ram if possible. I have no idea as to what you would need. If you know the size of the data sets you will be working with, that might be a start.
I might guess that a kit of 2 x 16gb is right.
Buy what you need up front, ram must be matched and is sold as kits so adding ram later can be an issue.

Faster ram can benefit some applications but there is no need to go crazy on ram speeds. Base speed is perhaps 2400, 3000 to 3600 is likely right.

As to handling both a laptop and a desktop setup, I think you can handle that.
In the event that you need Nvidia CUDA cores for processing, the Lenovo thinkpad X1 extreme 15" laptop comes with GTX1050ti graphics.
A fully loaded laptop run for a long time may throttle or reduce performance because of the limited cooling capability that needs to be crammed into a small space.

Since you are going to need a laptop solution, at least in part, why not buy a X1 extreme and test it out?
That way, you will get a better idea of how your apps perform before committing to a full blown roll out.

 

[cdrkf]

First of all, thank you all very much for taking the time to help out here. It is unbelievably helpful to be talking to people who actually know what they're talking about.

How can I tell if it's 100% on both cores? When I go to task manager, the CPU column says 100% so I assume it's all cores? Memory typically doesn't go above 70% or so and I have 16 gb of RAM so would assume I don't need more than 32 gb in the new computer.

Nobody has a better PC at our firm. It's a good idea to have someone demo a PC - I can ask about this.

We were discussing having one very fast desktop for the office that people share for when they get the super complex files, and our IT vendor came up with the below - anyone have any views on this / if it would be optimal? This was the fastest processor they had in their inventory. Sounds like Ryzen might be even better? Would i9 be better than Xeon all else equal for these purposes or the reverse? They have a similar version of the below but with i9.

Dell Precision 5820 Tower,
Intel Xeon W-2145 3.7GHz, 34.5GHz Turbo, 8C
64GB of RAM
(2) 512GB SSD, RAID 1,
Dual NVIDIA® Quadro® P1000, 4GB, 8 mDP,

Then we were thinking of buying ThinkPad X1 Extreme laptops for each of us to use day to day , which has the following processor available:

8th Generation Intel® Core™ i7-8850H vPro 6 Core Processor (2.60GHz, up to 4.30GHz with Turbo Boost, 9MB Cache)
32 gb of RAM
512 GB SSD

I don't love that it's i7 (vs i9 or Xeon which I presume is better) and only has 2.60GHz clock speed (so for single threaded actions in Excel it sounds like you guys are saying this would create a bottleneck) but I like that it’s 6 cores. But would a desktop CPU be faster than a laptop CPU, assuming the same cores / clock speed / i version?

Super helpful. I think building our own machine would be out of the question because nobody here has the time or expertise. Can the processors you are contemplating be gotten in off the rack computers that have standard warranties, etc or would you recommend going to a company that builds custom machines (like Puget Systems (or maybe you know of one better than them))?

I didn't realize that certain CPUs required certain amounts of memory - I always thought they were independent - thanks for calling to my attention. Is it just the CPUs you mentioned that require this or is there some rule of thumb around core count or something? Would more than 32 gb be advisable even if I'm only hitting 70% RAM usage or so on a heavy day even with 16 gb currently?

Yes we'd be using SSD for local storage but we also run files off of a network which I do think adds some sluggishness to the setup.

You mentioned single threaded excel operations are going to be hampered by low clock speed. I'm assuming the below would not be a good setup then due to the low basic clock speed? It has 4.30GHz Turbo Boost but I'm not sure what that means in practice. I asked my IT department to overclock my laptop and they said it would be a heating concern.

8th Generation Intel® Core™ i7-8850H vPro 6 Core Processor (2.60GHz, up to 4.30GHz with Turbo Boost, 9MB Cache)
32 gb of RAM
512 GB SSD

Correct - we are open to getting desktops for the team.

The reason we'd prefer laptops in a perfect world is that we are frequently on the road traveling to various cities meeting with companies and will often be forced to do work on airplanes, in hotels, etc.

That said, we do have a remote desktop solution that could theoretically allow us to use our current underpowered laptops to remote into our higher powered desktops at the office while we're on the road which could mitigate the issue but hotels often have bad wifi / aggressive firewalls and planes especially are nightmares for internet access. It's also more convenient to have one system that we can use everywhere that has all of our settings, plugins, programs, files, etc on it rather than having both a desktop and laptop.

However, a desktop is absolutely a direction we are willing to go down if the performance difference is large between a laptop like the one I outlined above and what these desktops can do for Excel in particular. Performance is our number one concern and we spend most of our time in the office.

That said, if you were to tell me that Excel caps out at a certain level and with the level of speed in the laptop I mentioned, we will be on an asymptote with respect to performance for what the program can handle that can't really be improved upon much, then we would prefer the convenience of a laptop.

I switched over to 64 bit office and don't know if it made any difference unfortunately.

Funny you should mention Lenovo workstation laptops: I had been discussing the P72 with my team which is, according to their site, the fastest mobile workstation Lenovo makes. However, here's the fastest processor available with the P72:
Intel® Xeon® E-2186M 6 core processor with vPro™ (2.90GHz, up to 4.80GHz with Turbo Boost Technology, 12MB Cache)

And this is the highest option for the X1 Extreme
8th Generation Intel® Core™ i7-8850H vPro 6 Core Processor (2.60GHz, up to 4.30GHz with Turbo Boost, 9MB Cache)

Am I wrong to assume that the processors aren't that different because they're both 6 cores and only 0.30 GHz different at the low end and 0.50 GHz different with Turbo Boost? Or does the higher "Cache" make a difference - or is Xeon much faster than i7?

They really make this stuff difficult to compare for the average person like myself!

Is there a laptop that is better than the ones I've outlined, with higher clock speeds and 6+ cores, etc?

Thanks - so I'm assuming this means a laptop with the same processor specs on paper as a desktop will be slower due to this cooling problem?

On your RAM point, are you saying that having more RAM is going to help me from a CPU perspective when that is the part that is maxing out in Task Manager? Does RAM step in to compensate somehow?

Sounds like you're saying the laptops with 6 cores are true replacements for desktops from a performance perspective? Or you're just saying they are the best among bad options?

I don't know where you are based, in the UK I recommend talking to PC Specialist (www.pcspecialist.co.uk) who build machines to order. They would undoubtedly be able to work out a specification to suit your needs.

In terms of laptops vs Desktops- that 6 core laptop will be a big step forward from your current machine, that is for sure. The turbo frequency is the speed the chip will boost to on a single thread for short term operation, the all core max speed will be a bit lower (but still higher than the base speed a lot of the time). The tricky part is that as the machine balances the heat produced the actual speed of the processor is hard to determine. In my experience a full powered desktop of the same generation is quite a bit faster in heavy use cases than the laptop equivalent, even if on paper the 2 cpu's offer similar performance. The cooling solutions on laptops just can't sustain the clock speed.

It might be a case of having a good desktop machine at the office to work on, and a separate laptop to take with you? That is how I operate (I do 3D design work mainly).

With regards to memory, if you aren't maxing out 16gb now (and the workload isn't likely to increase significantly in the future) then honestly you probably don't need more. 32gb would be ample (and give you lots of headroom), more than that wouldn't net you any more performance. The key with ram is that you need enough to hold the data set you are working on- if you run out of ram the system is forced to work from the local storage, and even a top of the line NVME ssd is still an order of magnitude slower than system ram. Once you get to the stage you have enough ram to deal with the job you are doing, adding more isn't going to provide a benefit.

The other issue with ram is the speed and number of channels- higher speed ram will help (although honestly high clocked ram isn't going to make much difference in this type of workload imo). High bandwidth is probably more important here, that is why I mentioned the number of modules. The type of processor used will determine how many is optimal, for standard desktop / laptop cpu's ram is best used in pairs for 'dual channel' operation. This is because most cpu's offer 2 ram controllers, and by providing a module to each allows the memory load to be split between the modules effectively doubling throughput- this means configuring 8gb of ram as 2 x 4gb modules provides double the maximum bandwidth of a single 8gb module. When it comes to the high end processors I mentioned (the Intel Core 'X' series and AMD Threadripper cpu's), these offer 4 memory channels on the processor and so you need to be installing memory in matched sets of 4 modules for best performance. These cpu's will function with less modules installed but it would be a sub-optimal setup.

 

[hang-the-9]

Don't get the X1, or any slim model for heavy CPU usage. Just not enough space for cooling. Workstation or larger business systems like the Lenovo T models are better. Or a desktop.

 

[digitalgriffin]

QUOTE="EricThomas, post: 20997523, member: 2740889"]
I work at an investing firm and we often receive significant amounts of data on companies that we need to crunch in excel. At the extreme, this can be as much as 20 million data points, with millions of rows of volatile formulas such as indirect functions, index match, etc running off of this data, interconnected across dozens of tabs. As a result, my company-issued laptop which we use as a desktop replacement, a Lenovo ThinkPad X1 Carbon, either can’t do these tasks or it can take several days to do a task that could have taken 20 minutes if it weren’t for the lag. I’m running a dual core i7 with 2.7ghz or so and 16 gb of ram - so lots of room for improvement to say the least.

Even when not working with these very large and complex files, my CPU reaches 100% utilization literally every single day and causes significant efficiency to my workflows.

There would be a major financial return on investing in high powered desktops or laptops for our organization and I have convinced the firm of the need. Because of the nature of our work, we are willing to pay for something that will be lag free for everything we do because it will directly pay for itself many times over.

Some are going to tell me I should use Tableau or something else for these files and I would ask those people to, respectfully, please press the back button on your browser now.

For Excel in particular, are there any limits to how much it can take advantage of clock speed, cores, or i7/I9/Xeon?

Does anyone have ideas on the best path to take to not have CPU be a bottleneck? What computer should we be buying? We’d probably pay up to $4k per machine if necessary.

Our IT firm is horrendous and has been really unhelpful here. In general everyone we speak to underestimates our level of need because they do not understand our work.

Thank you very much in advance for your honest advice.
[/QUOTE]

We have very complex excel sheets that are on the order of hundreds of megabytes with no graphics. I deal with similar issues on a regular basis where billions of potential solutions must be evaluated from that data.

Unfortunately Excel is slow as @#$@#T@#$% even when threaded. (That's the hazard of using an live interpreter.) It also tends to be unstable with really really huge files. Trying to collating the data using several spreadsheets becomes problematic if you use VBA and try to go beyond 32Bit memory boundary. And the VBA is flaky to begin with.

You could try something like spreadsheet gear. It turns excel files into compiled code and it will run faster. But to be honest this is where you should consider getting a good programmer who knows what they are doing to write it for you in VBA and to dump the data into a B-Tree database. Then you would use sparce array techniques to preserve memory on C++ (NOT .NET unless you want to deal with unsafe directives). It would be a metric ton faster no matter what hardware you run it on.

 

[exmachines]

I work at an investing firm and we often receive significant amounts of data on companies that we need to crunch in excel. At the extreme, this can be as much as 20 million data points, with millions of rows of volatile formulas such as indirect functions, index match, etc running off of this data, interconnected across dozens of tabs. As a result, my company-issued laptop which we use as a desktop replacement, a Lenovo ThinkPad X1 Carbon, either can’t do these tasks or it can take several days to do a task that could have taken 20 minutes if it weren’t for the lag. I’m running a dual core i7 with 2.7ghz or so and 16 gb of ram - so lots of room for improvement to say the least.

Even when not working with these very large and complex files, my CPU reaches 100% utilization literally every single day and causes significant efficiency to my workflows.

There would be a major financial return on investing in high powered desktops or laptops for our organization and I have convinced the firm of the need. Because of the nature of our work, we are willing to pay for something that will be lag free for everything we do because it will directly pay for itself many times over.

Some are going to tell me I should use Tableau or something else for these files and I would ask those people to, respectfully, please press the back button on your browser now.

For Excel in particular, are there any limits to how much it can take advantage of clock speed, cores, or i7/I9/Xeon?

Does anyone have ideas on the best path to take to not have CPU be a bottleneck? What computer should we be buying? We’d probably pay up to $4k per machine if necessary.

Our IT firm is horrendous and has been really unhelpful here. In general everyone we speak to underestimates our level of need because they do not understand our work.

Thank you very much in advance for your honest advice.

I build PC's for a Hedge Fund with similar use case. Almost all of our users are Excel based. I would echo a lot of what has been said here. But in summary:

1. Run Office in 64 bit as 32 bit limits RAM usage - this is very critical and often overlooked

2. PROCESSOR - Excel seems to need a) high single core performance 2) High # of physical cores

I would use i7 8700K or better and if you are seriously willing to spend $4000 (which I understand working for a hedge fund) consider using i9 9980XE.

3. RAM - Use as much quality DDR4 RAM as you are willing to buy - RAM is historically cheap. A lot of prebuilt PC's (Dell / Lenovo) use single stick of RAM. Use 2 or 4 binned sticks. Speed (MHz) and timing (CL) are also important. Try to use 3000 MHz or better and 16CL or less. A good rule of thumb when comparing RAM is to divide the total MHz by the CL where a higher number should equal better performing RAM. Not scientific but...

Enable a XMP profile in the BIOS. RAM speed is limited if you don't overclock it. There are lots of good resource videos on this. A lot of PC builders do not enable XMP by default. You are leaving performance on the table.

4. MOTHERBOARD - Use a mobo with quality VRM. These high end processors require high end power delivery. You should be spending at least $150-$200 on a good motherboard. Just having the processor is not enough. Even if you are not overclocking buy a quality 300 series board from ASUS / GIGABYTE / MSI / EVGA. Can not stress this enough.

5. STORAGE - Put your Operating System (and as much else as budget allows) on m.2 PCIE storage. These are electrically connected to the board and don't SATA bottleneck so they transfer on par or better than SSD's in RAID. They have become extremely reliable and relatively cheap.

Excel hardware requirements for Excel are highly dependent on THE WAY YOU MODEL. We have heavy Excel users who write complex models that are fine on 4 core i7-7700's with 16GB RAM. More often though - we use i7 8700K or better and 32 GB RAM. We have some 9900k's and 9980XE's.

My general feeling is that 9980XE's w/ 4 memory channels perform best, but you will see seriously diminishing returns over 8700k/9900k's with 2 memory channels, financially speaking. What most people don't understand though - is that 15-30 minutes saved per week over 4 year PC life would be worth way more than an extra $2,000 up front for a lot of Finance applications. If it does anything at all in your use case, well worth the investment. Just be prepared to deal with thermals. Heat is the enemy of performance!

Hope this helps!

 

[_Jack_]

I have similar issues.

Huge Excel files, VBA Macros that take days to run. In my quest I melted a few mother boards along with the CPU.
What I learned is.
64 Bit.
Remove the auto load start up stuff. All of it.
Turn off screen refresh in the VBA.
Turn off "Auto Calculate" in VBA. Calculate only when you need to

I found out the hard way that the "Ultra Durable" motherboards are worth the money!
The Power Supply needs to be really clean, another painful lesson.
M.2 I like the "Pro" version and the software that comes with it. The "memory magic" really did work. But with all the RAM, I don't see the drives being used that much. The drives did not make that much of a difference.

The RAM is maxed out on the 4 core / 8 processor Z-Book, with 32 GB.

My fully loaded 17" Z-Book performs well beyond what the specs imply. Well beyond. And thanks to the latest "remote desk top" with Google, I can have my desk top crunching all night and do the lighter secondary crunching from a hotel room in .... I have the VBA regularly save data to a second directory, then close the file so I can monitor the internal spread sheet values periodically. But this requires a second PC that can access the "cruncher". The Z Book will run a smaller VBA Macro at 3 times to 5 times the time as the "Desk Top Cruncher".

I found that for my files, the number of cores really do matter A LOT. My desk top "Cruncher" is running 20 processors on a 10 core i7-6950X CPU and they are all pegged at 100% most of the time. I tried over clocking... not a positive experience and we will leave it at that.

The GPU makes no difference. Went from a Very small 1050 to a 20something Ti for $1,300 and no difference at all.

Cooling. When the Z-Book is crunching, yes, heat is an issue. I got a USB driven secondary fan to put under the Z-Book which helped processor speed a little, if any at all. The desk would get hot enough to wonder about the table top finish.

Initially, the Desk Top had CPU temp issues.. The desk top "Cruncher" is now sporting a pricey "Quiet" multi fan cooling brick that made CPU temp issues a thing of the past. The self contained water cooling unit was not up to the task and the downward facing fans of the new CPU cooler probably help keep the overall board temps.

After going though a few Motherboards, I used a laser temp probe when I installed the last one. There was one "hot spot", no where near the potential cooling of the downward air flow from the CPU cooler. I tie strapped in a 40 mm fan right above the hot spot which might not have made any difference since the "very durable" Mother Board is obviously up to the task.

I installed 128 of high speed, low latency memory on the desk top cruncher.. and the meter says I seldom gets over 10%. Memory probably matters, but there is a limit. It matters a lot on the lap top.

The Cache size of the CPU probably matters more than the expected, but no first hand knowledge on how much.

My biggest challenge of late is files getting corrupted. Microsoft needs to step up their game in my opinion in regard to Huge file management and very extensive VBA Macros. To protect the work, I have added layers of "Back Up" versions of files, and redundant saves. Easy in VBA after you realize it needs to be done.

Good luck on your journey.

Jack

 

[13thmonkey]

Are you getting to the point of needing to be off excel? Use alteryx or similar?

 

[Tomazs]

I have a very similar problem, and in fact came here looking for answers. However, while I’m seeing the kind of grinding performance degradation you report, there is a difference between my setup and yours. That difference — or rather, the thing we have in common — may hint at the solution.

The difference is that my data sets are not large. My data typically lives in tables of a few hundred rows and some tens of columns, so the order of up to 10,000 cells and no more. However, I still regularly put Excel on its back, having to wait several minutes or more every time I make even the smallest change. And that points to what I suspect is the fatal similarity between our situations:

…volatile formulas such as indirect functions, index match

Volatility. To quote Adam Savage from Mythbusters, “Well there’s yer prablem!”

I use INDIRECT() extensively, along with other volatiles. I also make widespread use of conditional formatting, which I believe is also volatile. And as I say, even with a good high end laptop, I can reduce 64-bit Excel to a gibbering heap despite my relatively small data sets.

Like you, I have been trying to understand what aspects of the hardware are most relevant, and I was seriously considering getting a heavy duty desktop just for my Excel load. But I am changing tack now, and for the following reason.

The problem as I’ve been experiencing it is not just that my current hardware can’t handle my current workload. It’s that the performance gets unbearable so quickly as I increase sheet complexity (i.e. more sophisticated computations rather than more data) that I suspect I’m seeing a nice practical example of exponential, or at least very high order polynomial, complexity. The computational demand I am imposing is growing so much faster than linearly that I suspect no matter how much hardware I throw at it, it won’t take long to kill that too.

I suspect that the underlying reason is simple and comes from two things. First, I use Excel like a programmer (which I am), such that I expect to be able to do the kinds of things that can only be done using INDIRECT() (unless you drop into VB, and I have some self respect!) But second, Excel just isn’t built to handle extensive use of volatiles. The tool tolerates occasional use of INDIRECT() (say), but it really rather you didn’t. And while that’s fine for the vast, vast majority of users (which explains why your IT guys don’t get you), it’s no use for we few, we happy few, we band of bloody-minded hacker types.

Solution? Well I think I have one, but you’re not gonna like it. I’m currently seriously considering moving to something like a Python/R/Gnuplot stack, and maybe even throwing in LaTeX as the documentation component. The undoubtedly pretty eye-candy of Excel is nice, as is the spreadsheet-ness as well. I live in that tool and, apart from this volatility issue, even like it. But I just can’t get stuff done anymore, and now I’ve pondered reverting to a solution where my source is just plaintext, where I can easily version using git or the like, and where I can apply good programming practices more widely across my creations…well, I’ve heard the siren song and it’s hard to unhear it.

There. I told you you weren’t going to like it.

P.S. This is worth a look (and is some evidence that if I am a lunatic for thinking in this way, then at least I’m not alone):

View: https://youtu.be/S8wO4ppE9L8

 

[flykrs1]

Like Tomazs reminds everyone, INDIRECT function is in use, evil, possibly the worst thing you can do in Excel. On even moderate size spreadsheets this function can cause disastrous performance just by itself. especially when referencing data on other worksheets. Optimization is hugely important, no VLOOKUPS (Office 2016+ is way better if you must).

The proper tool is important, recognize Excel's limits, there are limits to how much hardware you can throw at it. I deal with this weekly. In our case the associates can do what their manager wants, but most do it poorly leading to horrible performance on even small spreadsheets. There are definitely better and much worse ways of crunching data which can make significantly more difference than CPU and RAM. If you need more than a current gen i7, SSD, 16Gb RAM then that is a sign in most cases. Obviously I am only speaking to my experience and don't know everything about your situation (or Excel). DB based analytics solutions were created for a reason.

 

[IsItMeOr]

I have a very similar problem, and in fact came here looking for answers. However, while I’m seeing the kind of grinding performance degradation you report, there is a difference between my setup and yours. That difference — or rather, the thing we have in common — may hint at the solution.

The difference is that my data sets are not large. My data typically lives in tables of a few hundred rows and some tens of columns, so the order of up to 10,000 cells and no more. However, I still regularly put Excel on its back, having to wait several minutes or more every time I make even the smallest change. And that points to what I suspect is the fatal similarity between our situations:



Volatility. To quote Adam Savage from Mythbusters, “Well there’s yer prablem!”

I use INDIRECT() extensively, along with other volatiles. I also make widespread use of conditional formatting, which I believe is also volatile. And as I say, even with a good high end laptop, I can reduce 64-bit Excel to a gibbering heap despite my relatively small data sets.

Like you, I have been trying to understand what aspects of the hardware are most relevant, and I was seriously considering getting a heavy duty desktop just for my Excel load. But I am changing tack now, and for the following reason.

The problem as I’ve been experiencing it is not just that my current hardware can’t handle my current workload. It’s that the performance gets unbearable so quickly as I increase sheet complexity (i.e. more sophisticated computations rather than more data) that I suspect I’m seeing a nice practical example of exponential, or at least very high order polynomial, complexity. The computational demand I am imposing is growing so much faster than linearly that I suspect no matter how much hardware I throw at it, it won’t take long to kill that too.

I suspect that the underlying reason is simple and comes from two things. First, I use Excel like a programmer (which I am), such that I expect to be able to do the kinds of things that can only be done using INDIRECT() (unless you drop into VB, and I have some self respect!) But second, Excel just isn’t built to handle extensive use of volatiles. The tool tolerates occasional use of INDIRECT() (say), but it really rather you didn’t. And while that’s fine for the vast, vast majority of users (which explains why your IT guys don’t get you), it’s no use for we few, we happy few, we band of bloody-minded hacker types.

Solution? Well I think I have one, but you’re not gonna like it. I’m currently seriously considering moving to something like a Python/R/Gnuplot stack, and maybe even throwing in LaTeX as the documentation component. The undoubtedly pretty eye-candy of Excel is nice, as is the spreadsheet-ness as well. I live in that tool and, apart from this volatility issue, even like it. But I just can’t get stuff done anymore, and now I’ve pondered reverting to a solution where my source is just plaintext, where I can easily version using git or the like, and where I can apply good programming practices more widely across my creations…well, I’ve heard the siren song and it’s hard to unhear it.

There. I told you you weren’t going to like it.

P.S. This is worth a look (and is some evidence that if I am a lunatic for thinking in this way, then at least I’m not alone):

View: https://youtu.be/S8wO4ppE9L8

Try converting data to Tables and time consuming lookups & calculations to PowerQuery within Excel.