[Updated June 2018] I’m a big fan of Excel’s conditional formatting, and often use it to highlight cells on a worksheet. But this week I ran into a conditional formatting nightmare, and had to start from scratch. Fortunately, the fix didn’t take too long, but with complex formatting, things could have been much worse.
Separate the Dates in a List
There are lots of ways to use Excel conditional formatting. It’s useful for alerting people to problems, hiding duplicate values in a column, or even hiding cells’ contents, until related cells are filled correctly.
Earlier this year, I posted a tip for separating dates in a list, by using a red border at the start of a date change.
Easier to Read
It’s a technique that I use in one of my own workbooks, which I update every morning. The red border really makes the list easier to read, especially if I’m looking at it before my morning coffee!
The conditional formatting was set for cells A2:F9, and uses a simple formula to see if the date is equal to the date in the row above.
Quick Copy and Paste
In the workbook, I track some daily statistics for my website, and use the same headings every day. So, to save typing time, I copy and paste the headings from the day before.
For example, in the screen shot above, I could copy the regions and products headings from B2:C4, and paste them into cell B10:C12, for the latest day’s data.
The Excel table automatically expands to include the new rows, and fills in the formulas in column F. Awesome!
And that copy and paste trick is what I’ve been doing every day, for months on end.
Trouble With the Lines
Suddenly, this week, I started having trouble with the red border between dates – it wasn’t going across the full table.
So, I went into the Conditional Formatting Manager, to fix the problem. But, instead of one simple rule, there were hundreds of rules!
You can see the tiny scroll box at the bottom right in the screen shot below. That should give you some idea of how long that list was.
Every time that I copied and pasted within the conditional formatting range, it created another rule for the worksheet. Yikes!
Let’s take a look at the smaller example, where I just copied and pasted the Region and Product headings. Now, it has two rules, instead of one, after one copy and paste within the table.
Cleaning Up the Mess – UPDATED
UPDATE – March 2017: I’ve found a quicker way to fix the problem, as you can see in this video. Get the detailed steps on my Conditional Formatting page.
There is a full transcript of the tutorial following the video.
TRANSCRIPT FOR THE VIDEO
Conditional Formatting
In this workbook, I’ve got a couple conditional formatting rules, and I’m going to show you how those can get duplicated, so you end up with lots more rules than you started out with, and then how you can fix the problem.
So in this table I have two rules.
–One puts a line at the top of a date, if it’s different from the date above.
–The other rule changes the price to green, if it’s greater than $500 dollars.
We’ll take a look at those rules. In the Home tab, go to Conditional Formatting, Manage Rules.
And there you can see the two rules. They’re each applied from row 3 to 19 in this table.
–Now this one is just a cell value greater than 500.
–And this one is referring to a couple of cells. So if A2 is different from A3, we’re going to put a blue line, at the top of the cell.
How Problems Happen
And now, I’m going to insert a row, because I forgot to put in one of the records here. So I’ll click, Insert. And then just put some data in this row.
So that was a simple change to the table. And now I’m going to go back and look at my conditional formatting rules again. And suddenly I have a new rule, just for the row that I inserted.
So row 11 has its own rule, and the previous rule is still there, going from A3 to E10, and then A12 to E20. So it’s skipping this row, because that row has a separate rule.
And if you insert lots of rows, you can end up with hundreds of these rules set up, without even knowing it.
The Solution
And what I do to fix it is select all the rows, except for the first one. Then go to Conditional Formatting, Clear Rules, From Selected Cells.
Now if I go back in, and Manage Rules…I’ll go back to This Worksheet, just so we can see everything. So we’re back to those two original rules.
Then, I select the first row. I’ll select the first row in the table, this time, instead of the whole worksheet.
And go to the Format Painter, and make sure you’re selecting this first row as well, and apply that formatting to all the rows again.
And when we go back to Manage Rules, we still just have those two original rules.
If you need to do this frequently, go to my website to get a macro that removes the duplicate rules. It’s designed for data in in an Excel named table.
End Of Transcript
The previous solution is listed below, in case you’d rather try that.
Cleaning Up the Mess – ORIGINAL SOLUTION
I scrolled through the list, and deleted a few of the rules, but quickly realized that it would take far too long to delete all of them. So, to clean up the mess, I cleared the conditional formatting from the worksheet, and set it up again.
With all the extra rules gone, the conditional formatting borders were working correctly again.
Avoid Conditional Formatting Nightmare
Now, when I want to save some typing time in the daily list, I copy the previous headings, and use the Paste Values button to create the new records. I’ve got a copy of that button on Excel’s Quick Access Toolbar, so it’s easy to click.
That adds the text, and the table expands, including the formulas – but the conditional formatting doesn’t create a new rule.
Be careful out there — don’t let this conditional formatting nightmare happen to you!
Another Solution
Update: Thanks to Khushnood, who suggested leaving 3 blank rows at the bottom of the table, and inserting new rows above that, when adding new data.
Copy and pasting within the table doesn’t seem to produce the duplicate conditional formatting.
It’s still an extra step though, instead of a simple copy and paste.
________________
One way I go about keeping track of my conditional formatting rules is to edit the rules like so:
Before: A1=B1
After: AND(1/1=1, A1=B1)
Before: less than “=2”
After: less than “=1/1*2”
The “1/1” part can be “2/2”, “10/10”, or any other fraction resulting in 1. I use this to number each of my rules in order, so I can easily see which ones get duplicated/split.
I also avoid using relative references and instead use INDEX(ROW(),COLUMN()) to refer to relative cells. This way I just need to update the ranges and not worry if the rules will use the correct relative starting cell(s).
We ran into the CF accumulation and file bloat; slowdown. My solution was simply to .clear any destination before I copy to it. If you are coying in a for-next loop, it’s faster and you may have to take the .clear out of the loop, clearing the whole range at once. Likewise if you can CF the whole range at once, also reduces file size, and runs faster.
I have set up Conditional Formatting in Excel 2007, but when I have opened the file in 2013, the rules are not being applied. Does anyone know a solution please ? I am trying to colour cells depending on values using the formula method. Thanks
Yes the conditional formatting is not much fun when you insert/delete cells. WIth keywords ‘disable delete excel’ i came across a macro that disabled the right click options to delete or insert, instead leaving clear contents.
My other problem with it is when using an offset function for a dynamic named range, conditional formatting will not update with the defined range. It seems there is no solution there.
I’ve done a lot of digging on this Conditional Formatting rule “split” phenomenon lately (I’m using Excel 2010 btw). I’ve learned a few things that might help folks avoid this problem. Basically it boils down to 2 things:
– Understanding “why” this is happening, and that what’s happening actually makes sense (even though it’s not what I or most people want). I’ve observed the splits happen only when certain modes of addressing are used in the CF formula, and not with others.
– Once one understands the “why”, one can usually construct CF formulas that are immune to this phenomenon. I’ve managed to do so.
I’ll give you the workaround now, then explain it below. In fact, it’s already been posted here (by Misha in reference to something posted by Batman), but just having a solution without knowing “why” makes it hard to generalize it so you can apply it to all cases. I’ll try to give you the “why” also.
So the solution to avoid splits from row insertions/moves:
Whenver your CF formula refers to a cell that is not on the “same row” (in the context of how the CF evaluation steps through the “Applies to” range), you must either use a row-absolute address (e.g. A$1), which generally won’t meet your needs anyway, or (this will almost always be the case) you must reference that location indirectly, either with INDIRECT() or with OFFSET(), and use ROW()-x math to establish where you want it to land.
Note the same logic would apply to avoid splits due to *column* insertions or moves; just replace row-absolute above with column-absolute (e.g. $A1) and replace ROW() with COLUMN() math.
To be immune to both row and column inserts, all “out of cell” references must use full absolute addresses ($A$1), or use INDIRECT (or OFFSET) with both ROW()-x and COLUMN()-y type math in them.
Ok, so that’s the solution that’s working for me. Here’s the explanation why…
My test case is to have a range of data, define some CF rules across that range, then either a) insert a row in the middle of the range (e.g. expand), or b) cut a row and insert it somewhere else in the range (e.g. move/reorder). You don’t even need to go so far as to copy/paste anything into the blank row you created to get the problem to occur.. if it’s going to occur it will do so immediately following a row insert or a move.
Observations:
1. CF formulas with references to cells within the same row (as evaluation traverses the “Applies to” range) will not split due to a row insert or move. E.g. a CF formula like =$K6=”abc” is immune, given the “Applies to” range has it’s anchor on the same row as the address in the formula, such as $A$6:$B:29 .
Why? Because this formula can stay typed exactly as it is in the CF rule and still “work” (dependent cells still refer to same original data) despite a new row being wedged in the middle. This is just like cells out on the sheet (not in CF formulas) – you insert a row, and sure, Excel changes addresses in formulas in the rows below, but the formulas still all follow the same pattern.
Continuing with this analogy to a formula on a sheet, if your sheet formula references a cell one row above it, and you insert a row between the two, Excel has to change the address in the formula so it keeps pointing to the original data which is now two rows above it. This is fundamentally different to the first case in that now this new formula is not following the same “pattern” (e.g. refer to one row up).
So, back in CF land, when this happens (the second case), the CF rule can’t represent both patterns (formulas that refer to one row up and others that refer to two rows up) so the only thing it can do is split the rule into two rules, one with a formula that refers to one row up and another that refers to two rows up, and then sets the “Applies to” ranges accordingly.
2. CF formulas with references to cells by row-absolute addresses (e.g. K$6) won’t split due to a row insert or move.
If you think about it, this makes sense too. Just like out in a cell on a sheet, if there are multiple rows with formulas that all refer to the same row-absolute location, and you insert a row between the existing rows, the “pattern” in the formulas above and below the inserted row need not differ. So back in CF land, there is no need to split a rule that uses row-absolute addressing.
(and again, if inserting columns not rows, think column-absolute addressing).
One caveat:
– I’ve observed that nesting an INDIRECT or an OFFSET call within any other function (such as AND or IFERROR, etc) somehow messes up the result. I don’t know why this happens. I’d consider it a legitimate bug. For instance, =INDIRECT(“$K$”&ROW()-1)=”March” will result in TRUE when the cell in column K one row above the one being evaluted contains “March”, but =AND(INDIRECT(“$K$”&ROW()-1)=”March”,TRUE) will result in FALSE even though semantically this is an identical formula. To accomplish ANDing, I’ve had to resort to this form instead: (A=B)*(C=D). That means literally enclose the terms in parens, and use the asterisk to “multiply” each term’s TRUE/FALSE value to get an equivalent to AND. It’s a pain, but it works.
Again, thanks to Batman (and Misha) for the clues about using INDIRECT and OFFSET. I just hope I’ve provided a little more context so that you can generalize this solution. For instance, you won’t have to rewrite every CF function you have.. many forms of addressing are immune and won’t need indirection. But knowing which forms will need it, you can fix the minimum set of rules and be split free.
Paul,
Good explanation, however, it doesn’t work in my case – I always use absolutes for both row and column in my conditional formatting, yet, whenever I delete, cut, or insert rows, I always get conditional formatting bloat and fragmentation.