Proper CPU for big Excel files (formulas & calculations)

[giusgius]

Hi,

 

I'm planning to buy new CPU, 9th or 10th Gen. i5-i7-i9 which is better for big Excel files, formulas and calculations. 

Does more cores give better results in Excel case? 

Can i7 9700 give me advantage over i5 9400, or can i9 9900 give better results over i7 9700?

 

Which CPU do you recommend? I think I'm gonna choose Intel CPU for some reasons.

 

I got problems with working big Excel files (on Intel 8265U), it hangs, taks much time, ever for Ctrl + or - options. Not responding often,

 

Thanks

 

 

[Haro]

1 minute ago, giusgius said:

think I'm gonna choose Intel CPU for some reasons.

 

If you can't actually say why your going Intel then that does not make sense

1 minute ago, giusgius said:

 

Budget ? 

 

[Mateyyy]

23 minutes ago, giusgius said:

Can i7 9700 give me advantage over i5 9400, or can i9 9900 give better results over i7 9700?

All three of those are last-gen products, you shouldn't even be considering them.

 

Are you getting 100% usage on all cores on your current 4-core or are you getting 100% usage only on a couple of cores? If you're getting 100% on all cores then you should look at Ryzen CPUs, since you get more threads/$ compared to Intel Comet Lake.

[Samfisher]

Only way to know which CPU is best is to know whether your Excel workloads are more single-threaded or multi-threaded.  Cos that not only changes the CPU recommendation, it's a complete platform change.

[RageTester]

Since when is Excel considered heavy on CPU? Might be more concerned with RAM if you really got tons of data.

Can you post a print screen of task manager during such hanging?

[White Brownie]

 If you can share an example of a sheet with the complexity your typically working with I/we could also try and find out whats bottlenecking => what system would work best for you

[Kilrah]

Unless it's changed recently Excel was pretty much all single threaded, so no need for many cores but you want fast ones.

 

But if you're at the point where calculations take many seconds you've got another issue and excel is just no longer the right tool for the job you're doing. 

[White Brownie]

Quote

Will depend on what version of Excel you are using, the structure of your worksheet and if you want to get into overclocking.

The following is a para-phrased summary from the Microsoft site,
http://msdn.microsoft.com/en-us/library/ff700514.aspx

Versions of Microsoft Excel earlier than Excel 2007 use a single thread for all worksheet calculations. In this case it doesn't make sense to get a 6 or 8 core CPU.

Starting in Excel 2007, Excel splits calculation across multiple processors or cores. When Excel loads a workbook, it determines from the operating system how many processors are available and then creates a separate calculation thread for each processor.

The degree of improvement depends on how many independent calculation trees the workbook contains. If you make a workbook that contains one continuous chain of formulas, it will not show any multithreaded calculation (MTC) performance gain, whereas a workbook that contains several independent chains of formulas will show gains close to the number of processors available.

https://www.passmark.com/forum/pc-hardware-and-benchmarks/3677-what-is-best-processor-for-complex-excel-workbook

From 2012 but still seems to be relevant (?)

[mariushm]

Most operations in excel are single threaded - this means Excel uses mostly ONE cpu core to do all computations.

Therefore, the IPC (instructions per clock) is what matters most ... this followed by cpu core frequency will determine the overall performance.

 

For example, a Ryzen 3 3xxx running at 4 Ghz will be much faster than a AMD FX-9xxx overclocked at 5 Ghz, because the IPC of FX series processors is horrible.

 

I would suggest going with a Ryzen 3300x , because:

1. four cores and 4 threads (so 8 cores showing up in Windows) will be enough for you

2. the cpu has high frequencies (3.8 ghz base, 4.3 ghz boost) and very good IPC, on par or better with Intel processors

3. this particular processor has all four cores on the same CCX  (ryzen processors have two groups of cores, usually) which means very low latency when it comes to data being passed between cores within the CCX (group of cpu cores)

4. You have 16 MB of L3 cache and 3 MB of L2 cache, which is a lot, and will help with math (all the data will be stored in this super fast memory and you crunch through numbers faster) - in comparison your current cpu has 6 MB L3 cache and 1 MB L2 cache.

You can get more cache with 6 or 8 core Ryzen 3xxx processors (32 or 64 MB cache) but i feel it's diminishing returns, you'll lose peak frequency and excel is probably not gonna be able to take advantage of so much cache.

 

If you want laptop, there's quite a few processors with Ryzen 4xxx processors, but it's important to focus on peak frequency and cache memory, those will help Excel ... on processors with integrated graphics, cache memory is often much lower, because this fast memory uses a lot of cpu die space which is reused by the integrated graphics. 

 

 

[mariushm]

Also to add, Anandtech does a spreadsheet benchmark (besides lots of Office related benchmarks) when they review processors. 

 

here's the benchmark results for 2019 (the Ryzen 3100 and 3300x are not in this list, but 2600 and 3900x are) : https://www.anandtech.com/bench/CPU-2019/2234

 

 

[crazychenski]

It really depend on your Excel's calculation and formula that determine how many CPU/Thread the excel utilise.

I do a lot of work on Excel and do create financial models for my work. On a daily basis, my model do calculation where 4 columns with formula independent of each, and with  20k+ rows of data, crosses to different tab with 100k+ rows of data to bring back information. When it is calculating, I can see the Excel Status bar indicates 4 threads in use, and takes approximately 3-5mins to calculate. 

 

I know this hasn't give the answer that the OP wants, but like other said, you will need to review your excel's formulas and see how that calculate/flow of data before determine the type of CPU needed.

 

 

[giusgius]

On 7/3/2020 at 12:20 PM, Mateyyy said:

All three of those are last-gen products, you shouldn't even be considering them.

 

Are you getting 100% usage on all cores on your current 4-core or are you getting 100% usage only on a couple of cores? If you're getting 100% on all cores then you should look at Ryzen CPUs, since you get more threads/$ compared to Intel Comet Lake.

Not always 100% usage, but it hangs often, takes much time, sometimes I leave it overnight to complete. New Ryzen CPUs might not be in my region yet, also in this case I better build INTEL. Now we are working on i58265U CPUs on HP laptop. So we have performance problems, I don't know, it can be Windows fault, or office. Sometimes it takes much time if I Ctrl + or - rows... New 10 Gen i7 CPUs and other compatible parts will be available this months.  

 

On 7/3/2020 at 12:20 PM, Mateyyy said:

you shouldn't even be considering them.

Why? 

 

On 7/3/2020 at 12:27 PM, Samfisher said:

Only way to know which CPU is best is to know whether your Excel workloads are more single-threaded or multi-threaded.

There are many formulas, I think we need more  multi threads, also Excel shows all 8 cores processing when working.

 

On 7/3/2020 at 1:01 PM, Kilrah said:

But if you're at the point where calculations take many seconds you've got another issue and excel is just no longer the right tool for the job you're doing.

what other tools are available ? People use excel most time right? , switching is not option now for me. 

 

On 7/3/2020 at 1:14 PM, mariushm said:

For example, a Ryzen 3 3xxx running at 4 Ghz will be much faster than a AMD FX-9xxx overclocked at 5 Ghz, because the IPC of FX series processors is horrible.

Ryzen 3000 is recent CPU while FX9000 is old gen, is that good comparison? 

 

On 7/3/2020 at 1:14 PM, mariushm said:

If you want laptop, there's quite a few processors with Ryzen 4xxx processors

That would be good, but too expensive to get 4700U, better buy much faster desctop.

 

On 7/3/2020 at 1:22 PM, mariushm said:

Also to add, Anandtech does a spreadsheet benchmark (besides lots of Office related benchmarks) when they review processors. 

 

thanks, I will check it out.

[Mateyyy]

6 minutes ago, giusgius said:

Why? 

Because they're last-gen and don't offer enough for the price to be worthwhile.

I mean they work but there's much better options out there.

[Kilrah]

26 minutes ago, giusgius said:

what other tools are available ? People use excel most time right? , switching is not option now for me. 

For simple things yes, but when it takes an entire night to process a sheet then it might be time to look into a dedicated business application that does what's needed, or start developing one if that doesn't exist. 

[giusgius]

here is one of many these type of formulas we use... also there are Pivot Tables. I think we need more cores/threads, this can't be single core only workload...

 

On 7/3/2020 at 12:37 PM, White Brownie said:

If you can share an example of a sheet with the complexity your typically working with I/we could also try and find out whats bottlenecking => what system would work best for you

=IF(AND(C15>=$B$4,IF(AND(AO15<$C$4,AO15>0),AO15,D15)>=$C$4,C15<$C$4),AI15*($C$4-C15)/(D15-C15),IF(AND($B$4>=C15,$C$4>=IF(AND(AO15<$C$4,AO15>0),AO15,D15),IF(AND(AO15<$C$4,AO15>0),AO15,D15)>$B$4),AI15*(IF(AND(AO15<$C$4,AO15>0),AO15,D15)-$B$4)/(D15-C15),IF(AND($B$4>C15,IF(AND(AO15<$C$4,AO15>0),AO15,D15)>$C$4),AI15*($C$4-$B$4)/(D15-C15),IF(AND(C15>=$B$4,$C$4>=IF(AND(AO15<$C$4,AO15>0),AO15,D15)),AJ15,))))

[Breaker]

I have a similar problem. Please give me a recommendation as well. Here is my taskmanager readout: 

CPU

    Intel(R) Core(TM) i7-1065G7 CPU @ 1.30GHz

    Base speed:    1.50 GHz
    Sockets:    1,      Cores:    4,      Logical processors:    8,     Virtualization:    Enabled
    L1 cache:    320 KB,     L2 cache:    2.0 MB,     L3 cache:    8.0 MB,     Utilization    100%
    Speed    3.37 GHz,     Up time    8:15:29:56,     Processes    267,     Threads    3877,     Handles    197973

 

I currently have a macro that searches for matches between 54k+ line items of text through about 1,000+ possible matches with some calcs like, if this work and but not that word... 

Anyway - what is the best. Mine is not as bad as his, but it will be. Mine is just 25 minutes now. 

I am looking for what is best not what is cheap. 

[tudormon]

On 7/3/2020 at 12:01 PM, Kilrah said:

Unless it's changed recently Excel was pretty much all single threaded, so no need for many cores but you want fast ones.

 

But if you're at the point where calculations take many seconds you've got another issue and excel is just no longer the right tool for the job you're doing. 

It changed more than 10 years before you posted that wrong piece of info. Excel does multi-threaded calculations since 2007 - hello!!!!