Have you ever been confronted by the duplicated, fragmented and almighty mess your conditional formatting (CF) rules can get into? This is what I mean by the term duplicated Conditional Formatting Rules. I hope the following post helps you.
Basically this situation arises as you go about day to day coping and pasting into a range of cells or a Table with the same conditional formatting rules. It duplicates the rule rather than incorporating it in an existing rule covering that range. You use the workbook in day to day operations it may well be still working great but then one day months later you might need to change it and whoa your 2 rules have morphed into dozens or hundreds of lines of rules and is now a train wreck.
This can mean inaccurate results, makes it harder to amend them and the more rules the more effect on workbook performance and CF is something you have to watch even without this particular problem when applied to thousands of rows – it can really be toxic for performance. Charles Williams on his site FastExcel.Wordpress.com is THE expert on all things related to Excel performance and has a Three Part series on this aspect of using CF if you are interested going deeply into this Excel speed related topic…
- Exploring Conditional Format Performance Part 1: What’s slow, whats buggy and whats faster!
- Exploring Conditional Format Performance Part 2: What’s slow, whats buggy and whats faster!
- Exploring Conditional Format Performance Part 3: What’s slow, whats buggy and whats faster!
I want to tie together a couple of aspects I have found in managing this problem.
Basically I use CF where it really adds value but avoid it if I can when there are a lot of data or calculation rows (my gut feel is a lot means thousands) and instead use say a flag or message instead. An IF or CHOOSE statement returning either blank or a big ” !!! ” or “:(” or even “WTF” as one of my recent clients liked using formatted to bold red font is pretty effective in highlighting issues yet very light on the performance hit. You see prior to Excel 2007 we could only have three CF rules per cell and you always ran out of them in anything you did, so we had to think of workarounds to CF. Don’t get me wrong the fantastic Excel 2007+ versions expanded functionality allowing for many rules per cell and if used wisely on smaller ranges, dashboards, interactive tables and reports is really great. But be careful using it on growing lists of data. The duplicated rules problem of course multiplies an already significant hit to performance when CF is used in high numbers of rows.
So my view there are two approaches to this problem you can take.
Avoid / minimise the problem – You can avoid this problem if you adopt a new good Excel habit. When copying and pasting just get in the habit of (mostly) pasting only what you need. You might be hitting (full) paste by habit (which pastes everything values, formulas, formatting, validation rules and so on), instead hit paste values or paste formulas and leave formatting out of copying and pasting and always do that as a separate overall task. It’s no extra keystrokes in modern Excel if 
you add these functions to your QAT (see left) and it works because you are not effecting formatting at all. Also note when pasting in your macros – its the same principle, a careless copy paste instead of copy paste special values or formulas for a routine process might create hundreds of rules. I find this simple habit not only avoids this problem, it avoids other annoying problems such as unwanted validations, unwanted other number formatting and so on.
OK but sometimes you forget or your users just use copy / paste because that’s all they know. So the second approach to the problem is…
Periodically fixing the problem.
For this you need two solutions depending on the two situations you face:
- You have a range with potentially varying CF conditions by row or column or…
- A Table situation where each row has the same CF conditions
Re No.1 a range
If you have invested a lot of time getting complex CF rules right for a range of cells that may vary by row by row and/or column by column then it’s pretty easy to take a copy of that formatting only when it’s perfect and keep this copy aside in another sheet somewhere. It’s just copy / paste options: formatting (or the longer way is paste special choosing formats. I nearly always have a sheet called “Settings” in a worthwhile spreadsheet which is a good place to keep settings or complex formulas as text you may have developed that users could clobber.
Then it’s just a case, upon finding duplicate CF rules, of just copying the range in settings and paste special formats only over the working range in your workbook. You can do a simple macro so it can be done easily.
Re No.2 a Table
Debra Dalgleish on her Contextures site (one of the Excel world’s leading and most useful sites especially with its Pivot Table specialty) has a really life saving tip on how to get out of that mess with Tables.
Very simply, the fix is by erasing the formatting [clear, clear formats on the Home Tab] (clearing formats includes the CF) for all Table body rows except the first row. Then select the first row (that still has your CF rules) and select the format painter tool then apply to this first row (note this, include that first row) and all the other rows of your table. Here is a link to her site and the article is down the page called Fix Conditional Formatting Extra Rules with a short 3 min video and also Debra has provided a macro further down the page that does this as well. That’s a really practical fix, thanks Debra!
I hope this brings order to your Conditional Formatting!