Conditional Formatting slows my Excel Workbook

Martin_H

Board Regular
Joined
Aug 26, 2020
Messages
190
Office Version
  1. 365
Platform
  1. Windows
Hi team,

as the title says, I have a workbook that contains 5 sheets with multiple Conditional Formatting.

Whenever I chose anything from drop-down list (data validation list) Excel kinda slows right after for about half a second.

I am using Conditional Formatting to color rows based on the specific cell value.

Each sheet contains around 100 rows (so 5 of them = 500 rows).

Is there anything I can do to avoid getting those half-second freezes after each drop-down selection other than deleting conditional formatting?

Thanks a lot.
Much appreciated.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I learned on this forum that conditional format is volatile. So it causes Excel to recalculated all the time. Would you have complex (array) formulae somewhere?
How is the data validation being build?
 
Upvote 0
I learned on this forum that conditional format is volatile. So it causes Excel to recalculated all the time. Would you have complex (array) formulae somewhere?
How is the data validation being build?
Hi @GraH,

0 array formulas have been used in this workbook.

The first one looks like this (see below).

1638641393117.png


The second looks like this (see below).

1638641565167.png
 
Upvote 0
Hmm, doubtfull these simple lists are the cause.
What about the formulae used in the conditional formatting? How many do you have?
But has half a second such an impact? Any delay under 3s is acceptable in most cases.
 
Upvote 0
Hmm, doubtfull these simple lists are the cause.
What about the formulae used in the conditional formatting? How many do you have?
But has half a second such an impact? Any delay under 3s is acceptable in most cases.
Formulas inside Conditional Formatting are very simple.

I have 3 of them, one for red color, one for green color and one for yellow color.

They are all the same as below, but have a different letter.

Half a sec delay is not a big deal, I have been just wondering what could be the cause.

1638648705148.png
 
Upvote 0
I am having the same issue. I have a workbook with conditional formatting that performs fine, and then all of a sudden it starts grinding on every row insert or delete, and it gets worse as time goes on. i try to strip out the conditional formatting, which helps, but even when there are just a few cells left with formatting rules still in place, there is still a lag on insert/delete. only when every rule is gone does it go back to normal.

This has happened to me on so many projects. It's like a good workbook all of a sudden goes bad and then can't be recovered.

I'm at a loss for what to do?!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top