[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.
________________
Here’s the trick: Insert, THEN Copy/Paste-Special
Let me explain…
This behavior, though annoying, is not a bug. Excel is simply trying to figure out what you want (aaaanndd not succeeding).
The trick to inserting rows without fragmenting the conditional formatting is to use this sequence:
1) BEFORE copying the existing row, insert the new row (inside area where conditional formatting exists). If you do this Excel will understand what you want and it will update the conditional formatting correctly for you.
2) THEN, copy (previously existing) row with existing data/formulas.
3) use Paste Special -> Formulas-only to paste those values/formulas into your new row/column. (Formulas-only is better than Values-only because Formulas-only will actually get both manually entered values as well as formulas.)
So, to simplify:
1) Insert Row
2) Copy Row
3) Paste-Special -> Formulas-only
So, to simplify further, remember to “Insert first, THEN Copy/Paste-Special”
DO NOT use this sequence:
1) Copy
2) Insert copied cells
Why? In this case you are inserting cells into a range covered by conditional formatting, but since this new row is coming from who-knows-where, Excel doesn’t know if you want this new row to be like the other rows. It seems like it could be smarter, and recognize that the paste is coming from within the region, and GUESS that you therefore would want them the same, but it doesn’t. To be fair, I don’t know what other use case the MS engineers had in mind, so it is hard to say it is a “bug”. I wouldn’t consider it a bug, but it does seem less intelligent than it could have been.
btw,
Debra’s 1-step-simpler solution (in her OP) works when adding lines AT THE END, because the Table feature of Excel kicks in a does one of the steps for you (it in effect inserts lines at the end, THEN pastes the data using Paste-Special Values. In that case you’re doing the same thing, but it is a single/special use-case. The more general use case is inserting a row into a table – anywhere in that table, even if it is not defined as an Excel “Table”.
With Excel 2013 at least, more conditional-formatting intelligence seems to be embedded if you already have your table set up as an Excel Table. The end result is that it is harder to fragment the conditional formatting, but it can still be done – without you knowing. Again, the solution is insert, THEN copy/enter values (but still use Paste-Special if copying).
I’m also finding problem in conditional formatting. The same conditions on the same worksheet, some time its work and some time doesn’t work. If I go in conditional formatting and just open edit and apply formatting without doing something, it starts working. One more thing it (conditional formatting) shows a lot of rules while I have applied only few. Can’t understand.
Top answer and explanation, thanks Chuck.
There are other ways to tackle this problem:
(1) Don’t apply Conditional Formatting until after you’ve finished inserting rows (impractical).
(2) Use VBA to format your cells with the touch of a button (not all users will enable VBA).
(3) Lobby Microsoft to enhance Conditional Formatting by allowing [Applies To] named or dynamic ranges (preferred, but a major object design change, I suspect).
(4) Stick to Insert (new row) then copy/paste-special (Chucks answer and the only reasonable workaround).
It boils down to poor Excel design. In other words “defects by design”.
I’ve completed an audit of Excel 2010 actions and what their effect on Conditional Formatting Rules actually is:
(1) Copy > Insert Copied Cells – CORRUPTS
(2) Insert > Copy > Paste (all) – CORRUPTS
(3) Insert > Copy > Paste-Special (Formats) – CORRUPTS
(4) Insert > Copy > Paste-Special (Values) – OK!
(5) Insert > Copy > Paste-Special (Formulas) – OK! (copies formulas + values)
(6) Cut > Insert Cut Cells – OK!
(7) Delete – OK!
I also suggest that you create a Named-Range (either dynamic or once only) of your Conditional Format affected range and also a text copy of your Conditional Format formulas (with a ‘ at the beginning of the cell data) because the fastest way to get rid of the thousand or so rules is to remove them all and recreate them from scratch (5 rules max). Excel will convert the Named-Range to absolute cell references as soon as you [Apply] or hit [OK].
I hope Debra reads this as I replicated her problem in another spreadsheet.
Put simply, the technique of selecting a row, then Insert (a new blank row formatted like the others), then copy and paste only formulas works well only if your Conditional Format Formulas refer to cells in ONE row. If the formula refers to cells above and below, then CF hell breaks loose.
Now I had a section of my SS that behaved properly CF-wise but the left side of my SS that I wanted to format the line for was “Corrupted” because I looked to the row below and compared it to a value in my current row. When you insert a row, suddenly, Excel thinks that the existing CF formula is split by a new row (correct), so it adjusts the CF formula of affected existing rows and creates a unique CF formula just for the new row. This is what I call “corruption” because my neat Applies To region is now carved-up into 3 or more regions.
Now my solution was to create a hidden evaluation column and use the formula:
=IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1))=INDIRECT(ADDRESS(ROW()+1,COLUMN()+1)),”Same”,”Line”)
Notice there’s no references to screw-up if it’s copied to another row.
Then my CF formula simply looks at this value in the current row to decide whether to rule a line or not.
Another workaround that does not require users to have knowledge of excel-specific insanity is to use this VBA code to automatically undo all pasting and redo it as Paste Values:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.CutCopyMode = xlCopy Then
Application.EnableEvents = False
Application.Undo
Target.PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
End If
End Sub
Downsides are that undoing pasting is now impossible for users. And pasting formulas will not work anymore unless the vba is (temporarily) disabled.
Just wanted to say thank you. This basically solves the issue, as it seems to prevent any new rules from being created, and all the existing rules adapt to incorporate the change in rows/columns. Much Appreciated.
Very nice!
If you change “Target.PasteSpecial Paste:=xlPasteValues” to “Target.PasteSpecial Paste:=xlPasteFormulas” then you can have the same result as Paste Values AND have the capacity to type formulas!
Hope this helps.