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 quite 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!
But I want to tie together a couple of other aspects I have found in managing this CF problem.
Do you really need CF? 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) when instead I can 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 I guess we old school types had to think of workarounds to CF that needed complex rules. Don’t get me wrong, it’s fantastic the Excel 2007 and beyond versions expanded CF functionality to allow for many rules per cell especially if used wisely on smaller ranges, dashboards, interactive tables and reports. But you need to be careful of using using CF on massive data sets especially if the rules are complex.
Now the duplicated rules problem I mention at the outset then multiplies an already significant hit to performance when CF is used in a large number of rows.
So my view is there are basically two approaches to this duplicated rules problem.
1. Avoid or 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 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 be deliberate in what you want to paste and paste values or paste formulas and leave formatting out of copying and pasting unless you really need it. It can be very efficient in modern Excel if 
you add these functions to your QAT (see left) and get in the habit of using these. Also note when pasting in your VBA code – 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 and unwanted 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…
2. Periodically fix the problem.
For this fix, you need to consider whether you have a range situation with potentially varying CF conditions by row or column or a Table situation where row conditions don’t vary.
a) the range (non Table) situation
If you have invested a lot of time getting complex CF rules right for a range of cells then it makes sense when your formatting is perfect, to take a copy of your range and set it aside in a separate tab like a backup. Now there can be two range situations:
- Conditions vary row by row and depend on perhaps the first column, you need a copy of all rows of your range and this copy should be placed on another sheet with the same rows and columns
- Conditions don’t vary by row, in which case you only need copy the first row of the range and paste it to another sheet with the same columns (the row placement is not important). This is actually like a Table situation discussed below.
You can hide this sheet with the backup formatting if you like.
Then periodically or upon finding duplicate CF rules, copy either the one row or full range from this other sheet depending on the situation above and paste special formats only to the working range in your workbook.
b) the Table situation
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 if your situation is with Tables.
Tables should have the same CF for each row as the nature of a Table is to have consistent rows in terms of formulas, conditional formatting and data validation. First check the first row of your Table has the CF rules correctly as it’s going to be used to correct all other rows. So, the fix is then 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 correct 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!
Note aside from CF rules, if you have complex data validation rules users can just as easily wreck these with wanton copying and pasting and so the above approaches work just as well for data validation rules.