Excel – Is there an easy way to manage a large number of conditional formatting rules

conditional formattingmicrosoft-excel-2010

Using Excel 2010, I've created a very large number of conditional formatting rules. You can see how small the thumb is – there are a lot of them.

enter image description here

Unfortunately, the conditional rules manager dialog box is not resizable, so I can't see more than a few characters of the formula, and in many cases, I can't see all the cells that a rule applies to. I have two very similar worksheets in my workbook which need a nearly identical set of rules (same rules, just different columns in the formulas).

Are there any tools available, as add-ons, plug-ins, or otherwise, to allow for the easier managing of a large number of conditional formatting rules?

I'm at the point of manually copying each rule (rule, format, applies to & stop) to a different spreadsheet, then manually recreating each rule, in order, in the other worksheet, and this just sounds like a nightmare.

Best Answer

Your "nightmare" scenario could actually be used as part of a solution. Don't copy the formulas from a different workbook, instead use a new sheet in the current work book and test the value against the formulas on that sheet.

Now maintenance is as simple adjusting the formula on that sheet.

Tip: hide the sheet to protect it from user edits.

Pro tip: create a named range for each condition and use the VBA editor to make the sheet "Very Hidden". Anyone fumbling through the name editor or format rules will be amazed at how you magically conjure colors from names, that is, unless your users are saavy enough to peek behind the curtain by changing the sheet properties in the VBA editor or unpacking the XLSX to edit the XML.

Related Question