Conditional Formatting slows my Excel Workbook

Martin_H

Board Regular
Joined
Aug 26, 2020
Messages
170
Office Version
  1. 2016
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Martin_H

Board Regular
Joined
Aug 26, 2020
Messages
170
Office Version
  1. 2016
Platform
  1. Windows
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
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Martin_H

Board Regular
Joined
Aug 26, 2020
Messages
170
Office Version
  1. 2016
Platform
  1. Windows
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
 

hamiltpj

New Member
Joined
Oct 29, 2017
Messages
13
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?!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,394
Messages
5,837,011
Members
430,465
Latest member
Mackbay

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
Top