Home > Format > Conditional Format > Duplicate Rules Conditional Formatting - Extra Rules After you set up Microsoft Excel conditional formatting rules, you might see a problem with new rules being created automatically -- you could end up with hundreds of duplicate rules! See how to remove them, and ways to prevent extra rules in the future.

1) Video: Fix Conditional Formatting Extra Rules

If you set up conditional formatting rules, you might see a problem later, with new rules being created automatically.

Watch this video tutorial 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.

2) Fix Conditional Format Extra Rules

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.

2.1) Clear Rules

• Except for the first row, select all the rows with the same conditional formatting rules
• On the Excel Ribbon's Home tab, click Conditional Formatting
• Click Clear Rules, then click Clear Rules from Selected Cells

2.2) Re-Apply Rules

• Select the first row, and on the Excel Ribbon's Home tab, click the Format Painter
• Drag the Format Painter over all the cells where the conditional formatting rules should be applied, including the first row

2.3) Check the Rule Manager

To confirm that the duplicate rules were removed, go back to the Manage Rules window.

Only the two original rules should be listed

3) Why Are Extra Rules Are Created?

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.

• The Date Border rule compares date in current row (A3), to date in row above (A2):
  • =$A2<>$A3

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.

• That's how you can end up with hundreds of duplicated rules!
  • You can read about one of my conditional formatting nightmares on the Contextures Blog, and see many comments from other Excel users who've had similar experiences
• And all those rules could slow down your workbook's calculation speed.

In the sections below, you'll see how to

• fix extra rules manually, or with a macro
• prevent rules from duplicating.

4) How to Prevent Extra Rules

Here are a few suggestions so you can prevent Excel from creating extra conditonal formatting rules.

4.1) INDIRECT Function

In the conditional formatting rules, instead of a simple reference to another row, use the INDIRECT function to create the reference. For example,

• Replace this formula: =$A2<>$A3
• With this formula: =INDIRECT("$A" & ROW()-1)<>$A3

In the revised formula, the reference to the cell above (A2) is created with INDIRECT.

4.2) Add New Data at End

Instead of inserting new rows within the existing data:

• Add new data at the end of the Excel table.
• Then, sort the table data, so it's in the correct order

4.3) Clear, Sort, Delete

Instead of deleting a row within the table:

• Select the row and press Delete, to clear the cells
• Then, sort the table data, so the cleared row goes to the bottom of the table
• Delete the blank row, or leave it there, for the next new entry

5) Example: Creating Extra Rules

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 columns A to E, the dates, text and numbers are entered manually.
• There are no Excel formulas on the worksheet

In this Excel table, there are 2 basic conditional formatting rules:

• Price: First rule will turn price cell green if amount is higher than 500. This makes it easy to spot the highest prices. Only a single cell in each row is coloured by this rule type
• Date: Second rule adds a blue top border across the entire row in the table, if date in column A is different from date in row above. This borker marks the row where each day's data starts

NOTE: Click this link to see many more conditional formatting examples

5.1) See Conditional Formatting Rules

To see the existing Conditional Formatting Rules, follow these steps:

• Go to the worksheet named Example
• Select a cell in the Excel table heading row
• On the Excel Ribbon, click the Home tab
• Click Conditional Formatting, then click Manage Rules

In the Conditional Formatting Rules Manager:

• In the Show formatting rules for: drop down, This Table is selected
• The two rules in the Excel table are listed
• Both rules are applied to cells in rows 3 to 19

5.2) Set Up Conditional Formatting Rules

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.

5.2.1) Price Column

To set up the rule for the Price column, I selected the cells in the Price column.

• In the Conditional Formatting drop down, I used Highlight Cell Rules - Greater Than setting, for 500.
• Formatting is set for light green fill

5.2.2) Date Border

To set up the rule for the Date separator, I selected all the data cells in the sales order table.

• In the Conditional Formatting drop down, I used the New Rule option, with this formula, to compare the dates in two rows: =$A2 <> $A3
  • NOTE: There is a $ before each A, because we need an absolute reference to column A
• Formatting is set for a blue top border

5.3) New Rule Automatically Created

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 the list of sales orders, delete row 10, with the Los Angeles order for Bars.
• Then, go to Conditional Formatting | Manage Rules, to see that a new rule for the top border was automatically created.
  • The original rule has changed, and excludes the deleted row.
  • The new rule applies to row 10 only, and it has a #REF! error

6) Macro to Fix Duplicate Rules

To quickly remove the duplicate rules in an Excel named table, you can use the following macro - FixCondFormatDupRules.

You can copy the Excel VBA code below into your workbook, or get the code in the Fix Macro workbook (below).

6.1) Copy the Macro Code

First, copy the code shown below, and paste it into a regular code module in your workbook.

Then, to run the macro:

• Go to the sheet that has a formatted Excel table with duplicate rules
• On the Excel Ribbon, click the View tab, then click Macros
• Select the FixCondFormatDupRulesmacro, and click Run.

6.2) How the Macro Works

Here's how the macro fixes the extra rules -- just like the manual steps in the previous section:

• First, the macro finds the first formatted Excel table (ListObject) on the active worksheet
• Next, it counts the number of data rows in the table
• Then it identifies the first, second and last data rows in the table
• Next, it clears the conditional formatting from the second row, to the last row
• Then it copies the first data row, and pastes its formatting onto all the data rows (including the first row)

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

5) Extra Rules - Video Transcript

Here is the full transcript for the Fix Conditional Formatting Extra Rules video.

Transcript

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.

8) Get the Excel Files ✅

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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Related Tutorials

Conditional Formatting - Based on another cell

Conditional Formatting - Examples

Conditional Formatting - Currency

Conditional Formatting Problems

Conditional Formatting - Data Bars