Is there a limit on how many conditional formatting rules I can have?

Graham Rimell · 02/16/21

I'm about to embark on created hundreds & hundreds of conditional formatting rules for a big full year shift planner sheet I'm creating. It's a massive faff, and It'd be great if I could just select a range of cells like you can in Excel, instead of being limited to just one chosen column. But that's not going to change any time soon.

Is there hard limit on the number of rules I can have?

Bassam Khalil · 02/16/21

Dear @GrahamR

Hope you are fine, you can add specific conditions to your conditional formatting to rectify it as you wish also you can add helper columns with filter formula then apply the conditional formatting on it, please read the following article before you start your conditional formatting setup it will help you.

Graham Rimell · 02/16/21

Hi,

Unfortunately I don't think that helps me too much. I've got a calendar type setup with 365 columns for days all with drop down boxes, with each row as a name. If I choose 'Days' from the dropdown I want the cell to turn a certain colour. If I choose 'Lates' I want it to turn a different colour etc.

The set up of conditional formatting means I can only format one column at a time. There doesn't seem to be anyway round this. I've got 5 different colours X 365 columns which means 1800 rules. I can make one rule and clone it, which is fine, but takes a lot of time. I've not seen anything to suggest I cannot make 1800 rules, it's just not ideal.

However with Excel I'd only need to make 5 rules....which is what makes this frustrating.

Ryan W · 02/19/21

I know this isn't a helpful response, but I have a sheet that is being created that is tracking inbound training and recertification training for our entire department, approx. 400 employees. The sheet will include plenty of conditional formattings as it progresses, but isn't anywhere near your demands.

If you embark on this task, could you reply with if you encountered any limitations? As you sated earlier, there isn't a definitive answer out there of a limit so your research would be helpful.

Good Luck!

Graham Rimell · 03/01/21

So far I'm probably at just shy of 1000 and it's going okay. That said there's a weird issue I encountered where cloning a rule would be very slow (take 10seconds+) if you had a lot rules below it. However if the rule is at the bottom of the list it's instant.

So if I have 900 rules. If I try and clone the 200th rule, it'll be painfully slow. Clone rule 900 and it's instant.

Other than that, no real roadblocks yet.Would be lovely If we could Set rules for more than one column at a time, or even by row, but until then we've got to do it the hard way.