Excel 2013l CPU usage

[AC_C]

Hello. Couldn' find any solution for cpu usage(more accurately non-use) problem.
All started with Conditional formating. When put a lot of formating functions it freezes for some time. Started to explore this problem and saw that cpu's aren't loaded for 100%
There is some pics with CPU graphs.
1. Normaly when using just every day programs it's standing normal.
c8kr5q0ar5wlpijsi3a.jpg



2. When trying to open Conditional formating it freezes for about 10 sec making changes very time-consuming
7fmz6ruks92cfl33oixu.jpg



3. Tryed excel benchmark test. For some activities its loaded still at 25%, but for some reached higher.
5ru5dtdsb270liauk3ep.jpg



When set formating to diet that problem become smaller, but it's not an solution.
Workbook: http://failiem.lv/down.php?i=mspikas&n=01.04+x3.xlsm
Workbook after little diet: http://failiem.lv/down.php?i=jihpner&n=01.04+x5.xlsm
Benchmark test workbook: http://failiem.lv/g/lwkhknn#aa/BenchMark_ET.xls

I know that every formula can be done easier, its just a begining for planing in wood production deadlines.
Mby there is some solution that could max out CPU usage for faster work?

OS Name Microsoft Windows 8.1 Pro
System Type x64-based PC
Processor Intel(R) Core(TM) i7-3630QM CPU @ 2.40GHz, 2401 Mhz, 4 Core(s), 8 Logical Processor(s)
Installed Physical Memory (RAM) 16.0 GB
MS Office Home and Business 2013 x64
Day back had MS Office Home and Business 2013 x32

[Special-K]

Can you give some examples of the conditional formatting formulas you're using?

[AC_C]

Just 1 to many
hrnmpa9z7fmsnykgjd8n.jpg

[szwel]

Hello AC_C

After diagnosing the file it appears it's the huge amount of conditional formattings in sheet "KOP" which is causing all the troubles.

It's not an Excel 2013 bug. It's for all Excel versions using XLSX files.

If you delete them, by going to the ribbon Conditional Formatting --> Clear rule --> Clear rules from entire sheet, you will see the file gets a major change in everything : opens instantly, no lag when making changes, etc.

Obviously deleting conditional formattings is not an option because they are here for a reason. But I would suggest to rebuild them by taking advantage of the fact that each conditional formatting group applies to one or more ranges.

In sheet "KOP", in most cases conditional formatting groups apply to a range made of a single row, for instance F18:NG18 (which means 370 columns wide and one row tall). If you could insert conditional formattings of a kind to more than one range at a time (for instance F18:NG78 F41:NG41 F49:NG49, ...), this would reduce as much the internal conditional formatting objects created by Excel. And as a result, the file would be usable without losing the conditional formattings.

Hope this helps,

[AC_C]

Hello AC_C

After diagnosing the file it appears it's the huge amount of conditional formattings in sheet "KOP" which is causing all the troubles.

It's not an Excel 2013 bug. It's for all Excel versions using XLSX files.

If you delete them, by going to the ribbon Conditional Formatting --> Clear rule --> Clear rules from entire sheet, you will see the file gets a major change in everything : opens instantly, no lag when making changes, etc.

Obviously deleting conditional formattings is not an option because they are here for a reason. But I would suggest to rebuild them by taking advantage of the fact that each conditional formatting group applies to one or more ranges.

In sheet "KOP", in most cases conditional formatting groups apply to a range made of a single row, for instance F18:NG18 (which means 370 columns wide and one row tall). If you could insert conditional formattings of a kind to more than one range at a time (for instance F18:NG78 F41:NG41 F49:NG49, ...), this would reduce as much the internal conditional formatting objects created by Excel. And as a result, the file would be usable without losing the conditional formattings.

Hope this helps,

Yes I understand that formating can be done with wider area and less formulas (that's done in diet workbook). That solves the problem for some time. But this sheet will become 5 times bigger. Problem is that excel doesen't use all of PC power and becoming lagy.

[:) Sixthsense :)]

Two suggestions from my end:-

1) Save the file in Binary format (.xlsb) and check
2) Use VBA Macro to highlight the Data with desired Interior Color instead of using CF.

[szwel]

Yes I understand that formating can be done with wider area and less formulas (that's done in diet workbook). That solves the problem for some time. But this sheet will become 5 times bigger. Problem is that excel doesen't use all of PC power and becoming lagy.

It's important to understand that the CPU usage is high only because of the conditional formattings, not the actual data. In fact there is currently no content in sheets, so when the file starts to grow as the content gets added, it will barely change anything CPU wise because it's the conditional formattings which is taking most of the CPU load.
More accurately it's the conditional formattings in sheet "KOP" which is the culprit.

By making sure those 3000+ conditional formattings get reduced by a factor of at least two, there is an immediate such benefit for working with that file, be it for opening, saving, making changes.

Granted, that's a man intensive task to rebuild those conditional formattings. That's why I am working towards adding this feature in one my tools so you can obtain the same result by clicking on a button.

[szwel]

Yes I understand that formating can be done with wider area and less formulas (that's done in diet workbook). That solves the problem for some time. But this sheet will become 5 times bigger. Problem is that excel doesen't use all of PC power and becoming lagy.

It's important to understand that the CPU usage is high only because of the conditional formattings, not the actual data. In fact there is currently no content in sheets, so when the file starts to grow as the content gets added, it will barely change anything CPU wise because it's the conditional formattings which is taking most of the CPU load.
More accurately it's the conditional formattings in sheet "KOP" which is the culprit.

By making sure those 3000+ conditional formattings get reduced by a factor of at least two, there is an immediate such benefit for working with that file, be it for opening, saving, making changes.

Granted, that's a man intensive task to rebuild those conditional formattings. That's why I am working towards adding this feature in one my tools so you can obtain the same result by clicking on a button.

[AC_C]

It's important to understand that the CPU usage is high only because of the conditional formattings, not the actual data. In fact there is currently no content in sheets, so when the file starts to grow as the content gets added, it will barely change anything CPU wise because it's the conditional formattings which is taking most of the CPU load.
More accurately it's the conditional formattings in sheet "KOP" which is the culprit.

By making sure those 3000+ conditional formattings get reduced by a factor of at least two, there is an immediate such benefit for working with that file, be it for opening, saving, making changes.

Granted, that's a man intensive task to rebuild those conditional formattings. That's why I am working towards adding this feature in one my tools so you can obtain the same result by clicking on a button.

Highlight that Excel does not use more than 25% of the CPU. With other Programs like Autocad it goes for 100%. That's the main problem.

Two suggestions from my end:-

1) Save the file in Binary format (.xlsb) and check
2) Use VBA Macro to highlight the Data with desired Interior Color instead of using CF.

Thanks. When workbook will become bigger will try VBA formating.

[szwel]

Highlight that Excel does not use more than 25% of the CPU. With other Programs like Autocad it goes for 100%. That's the main problem.

My computer which has one CPU core goes to 100% for three minutes when it recomputes conditional formattings in sheet "KOP". This is very consistent with the explanations I gave earlier. You can ignore them, obviously.

[szwel]

@AC_C,

So I have been adding the feature I mentioned earlier in one of my tools, one which refactors and streamlines conditional formattings.

And when used with your file, it gets 691 conditional formattings deleted, with as many updated with additional ranges where the conditional formattings apply.

The resulting file is in attachment.

It is very snappy. Just take a look at the difference in speed for everything.



Processed.01.04 x3.zip

[AC_C]

@AC_C,

So I have been adding the feature I mentioned earlier in one of my tools, one which refactors and streamlines conditional formattings.

And when used with your file, it gets 691 conditional formattings deleted, with as many updated with additional ranges where the conditional formattings apply.

The resulting file is in attachment.

It is very snappy. Just take a look at the difference in speed for everything.



Attachment 388242

I remade it a little bit different. Now production manager use it for planing. Its only a test and i'm not a VBA pro. If needed to be upgraded maby will do it by myself or order for programmer to make now prog.
This topic was made to find why 64bit excel doesn't use all of PC power, and solution for that.
Link for file.
Manager got dual core and running fine, and there is no need for a lot of data entries. So even if it's little bit slow, that doesn't maters.

[newdoverman]

Just to be sure (I didn't see it in the above posts so if I missed it I apologize) do you have in the Advanced Options, Formulas section, Enable multi-threaded calculations and Use all processor on this computer selected?

CF as noted will really kill performance if not done correctly.

[HerryMarkowitz]

1) Save the file in Binary format (.xlsb)

I have just awared of Binary format (.xlsb)...
My question is;
Is it better if I change my .xlsm file to .xlsb ???
Thanks in advance...

[AC_C]

Just to be sure (I didn't see it in the above posts so if I missed it I apologize) do you have in the Advanced Options, Formulas section, Enable multi-threaded calculations and Use all processor on this computer selected?
CF as noted will really kill performance if not done correctly.

It's on. Can see it on first screen shots with benchmark test.

I have just awared of Binary format (.xlsb)...
My question is;
Is it better if I change my .xlsm file to .xlsb ???
Thanks in advance...

For me that didn't change anything, but if you made formatting with VBA i think that would make significant change.
Here is info for xlsb
http://blog.datasafexl.com/excel-art...binary-format/