[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.
________________
I too have the same problem in an semi-automated template that has over 200 columns and between 30-500 rows of data depending on the user’s needs. Unfortunately, the users’ requirement to be able to insert blank rows anywhere within the data area makes using the last 3 row solution impractical for me.
the template has conditional formatting in about 50% of the columns, and a user may have anywhere from 3 to 30 tabs. This really creates a lot of unnecessary additional conditional formatting.
Wish there was a better solution to clean up the mess or prevent it from happening.
Slightly off-topic, but I find I use paste by value so often I long ago memorized the keyboard shortcut: alt-e, s, v. I also find myself using the keyboard for other special paste commands (alt-e,s,t for formats, alt-e,s,f for formulas, etc) but paste by value is far and away the most common for me.
I’m running into this with a table hooked up to a query. As the query expands and contracts the table, it is adding the conditional formatting problem talked about here. This is on 2007. I haven’t got the right data driver on my system with 2010, so can’t test if it is resolved for me.
I have the same issue as Greg, the user needs to be able to add rows anywhere in the table and that messes up the CF and the 3 rows or the paste as values do not help there. Any ideas on how to prevent that?
Mischa
Ok Greg,
I just found a solution on OZGRID thanks to Batman
He suggested using the INDIRECT function in the conditional formatting formula, so i tried
=INDIRECT(“A”&ROW())=INDIRECT(“A”&ROW()-1)
tried to enter a new row in the middle of the table and surprise, surprise it worked perfectly 😉
Mischa
IMHO, your solution is the best. Using INDIRECT() in CF rules makes them harder to write and read, BUT once the rules are set, editing the table data just works, plain and simple. No more auto-duplicated rules.
Why does it work? I think it is because INDIRECT() allows to avoid any reference to the active cell, which makes the rule invariant across the whole range of cells (i.e. Excel does not need to transpose the rule for each cell). And invariant rules are NOT affected by the copy/paste problem.
For the same reason, CF rules that are based on cell content (rather than based on a formula) are not affected by the copy/paste problem, because they are invariant across the range (they do not contain any reference to the active cell).
Example.
Imagine you need a CF rule to change the font of the cells that are calculated by formulas. If your selected range is the whole sheet you could make a CF rule based on this formula:
=ISFORMULA(A1)
A1 is the active cell, so Excel auto-translates the A1 reference for every cell in the range. It works, but it is affected by the copy/paste problem.
Alternatively, you could do the same with a CF rule based on this formula:
=ISFORMULA(INDIRECT(“L”&LINE()&”C”&COLUMN();FALSE))
Arguably less readable, but totally invariant across the whole range of cell. There is no explicit reference to the active cell that Excel would need to translate. Consequently, that rule is not affected by the copy/paste problem.
The INDIRECT formula I used above was improperly translated from French to English. A proper (and shorter) CF rule for my example would be:
=ISFORMULA(INDIRECT(“RC”;FALSE))
That reminds me that R1C1-styled adresses are different across localized versions of Excel, so using them as text (with INDIRECT) can break them if we share a file worldwide. For my example, a safer CF rule (automatically localized by Excel) would be:
=ISFORMULA(INDIRECT(ADDRESS(ROW();COLUMN())))
To make that easier to use, we can go to the Name Manager and define the name “currentCell” as a proxy for “=INDIRECT(ADDRESS(ROW();COLUMN()))”.
My example of CF rule then becomes:
=ISFORMULA(currentCell)
OK, I guess I’m just a little surprised that you haven’t combined your earlier idea of a one-click formatting of your spreadsheet to include conditional formatting. Without patting myself on the pack too hard (got to watch out for that elbow… don’t want to injure it) I’ve been doing it for quite a while. A little more difficult to code but worth the time to ensure that you don’t open up your spreadsheet and get frustrated.