After you set up conditional formatting rules, you might see a problem with new rules being created automatically -- you could end up with hundreds of extra rules! See how to remove the extra rules, and ways to prevent extra rules in the future. There is also a macro that removes the duplicate rules, if the data is in a named Excel table.
If you set up conditional formatting rules, you might see a problem later, with new rules being created automatically.
Watch this video to see how the conditional formatting nightmare happens, and how to fix the duplicate rules. The written instructions are below the video, and the full transcript is at the end of this section.
In this example (from the video above) there is a worksheet with a small formatted Excel table with sales orders, on a worksheet named Example..
In this Excel table, there are 2 conditional formatting rules:
NOTE: Click this link to see many more conditional formatting examples
To see the existing Conditional Formatting Rules, follow these steps:
In the Conditional Formatting Rules Manager:
Here's a quick overview of how the conditional rules were set up in the Excel table for sales orders.
NOTE: If you're not sure how to set up rules on your Excel worksheet, there are written steps and a video on the Apply Conditional Formatting to a Cell page.
To set up the rule for the Price column, I selected the cells in the Price column.
To set up the rule for the Date separator, I selected all the data cells in the sales order table.
You probably won't run into problems with most of the condtional formatting rules that you set up. However, the problem can be easily created in this example.
You can try the following steps in the sample workbook (below), to see how a new condtional formatting rule is automatically created by Excel.
In this example, extra conditional formatting rules are created because the one of the conditional formatting rules has a formula that refers to another row.
When a conditional formatting rule refers to a different row, Excel might create extra rules every time you insert or delete rows within the formatted range of cells.
In the sections below, you'll see how to
To remove the duplicate conditional formatting rules, follow the steps below
WARNING: ALL formatting in the first row will be copied to the other rows. Any special formatting in other rows will be replace by the row 1 formatting.
To confirm that the duplicate rules were removed, go back to the Manage Rules window.
Only the two original rules should be listed
To quickly remove the duplicate rules in an Excel named table, you can use the following macro - FixCondFormatDupRules.
You can copy the code below into your workbook, or get the code in the Fix Macro workbook (below).
First, copy the code shown below, and paste it into a regular code module in your workbook.
Then, to run the macro:
Here's how the macro fixes the extra rules -- just like the manual steps in the previous section:
NOTE: If there is other formatting in the first row, it will also be copied to the other rows
Sub FixCondFormatDupRules() Dim ws As Worksheet Dim MyList As ListObject Dim lRows As Long Dim rngData As Range Dim rngRow1 As Range Dim rngRow2 As Range Dim rngRowLast As Range Set ws = ActiveSheet Set MyList = ws.ListObjects(1) Set rngData = MyList.DataBodyRange lRows = rngData.Rows.Count Set rngRow1 = rngData.Rows(1) Set rngRow2 = rngData.Rows(2) Set rngRowLast = rngData.Rows(lRows) With ws.Range(rngRow2, rngRowLast) .FormatConditions.Delete End With rngRow1.Copy With ws.Range(rngRow1, rngRowLast) .PasteSpecial Paste:=xlPasteFormats End With rngRow1.Cells(1, 1).Select Application.CutCopyMode = False End Sub
Here are a few suggestions so you can prevent Excel from creating extra conditonal formatting rules.
In the conditional formatting rules, instead of a simple reference to another row, use the INDIRECT function to create the reference. For example,
In the revised formula, the reference to the cell above (A2) is created with INDIRECT.
Instead of inserting new rows within the existing data:
Instead of deleting a row within the table:
Here is the full transcript for the Fix Conditional Formatting Extra Rules video.
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.
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.
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.
Extra Rules: Click here to get the sample Conditional Formatting Problem workbook for this tutorial. Follow the steps shown above, to see how extra rules are created. The zipped Excel file is in xlsx format, and does not contain any macros.
Macro Fix: Click here to get the sample Macro to Fix Extra Rules workbook for this tutorial. Follow the steps shown above, to see how extra rules are created. The zipped Excel file is in xlsm format, and contains the macro from this page, to fix duplicated conditonal formatting rules.
Last updated: September 9, 2021 4:16 PM