Fix Excel Conditional Formatting Duplicate Rules

Conditional formatting is a great way to highlight specific data, but did you know that it can automatically create new rules on its own? I’ll show you how that happens, and an easy way to fix those conditional formatting duplicate rules.

Fix Conditional Formatting Duplicate Rules http://blog.contextures.com/

Video: Conditional Formatting Duplicated Rules

Watch this video to see how the duplicated rules are created, and an easy way to fix the problem. There are written instructions below the video.

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.

Simple Conditional Formatting Rules

In this example, there is a small table, with 2 simple conditional formatting rules:

  • In column E, highlight prices that are greater than 500
  • In columns A:E, add a blue top border if the date in column A is different from the date above

2 simple conditional formatting rules

New Rule Automatically Created

To see how a new rule is automatically created, delete row 10 in this table. Then, go to Conditional Formatting > Manage Rules, to see the results.

  • The original rule has changed, and excludes the deleted row.
  • There is a new rule that applies to row 10 only, and it has a #REF! error

new rule is automatically created

Why It Happened

The new rule was created because the Top Border rule has a formula that refers to another row. It compares the date in the current row, to the date in the row above:

=$A2<>$A3

When you delete a row, the row below that loses its reference to that row. If we had the same formula on the worksheet, you’d see a #REF! error.

The same #REF! error occurs in the conditional formatting formula, so Excel creates a new rule for that formula.

worksheet #REF! error

Fix Conditional Formatting Duplicate Rules

If you frequently delete and insert rows, you could end up with many duplicated rules. In a big workbook, that could potentially slow down your workbook’s calculation speed.

And, you might not even know about those extra rules, unless you go into the Manage Rules dialog box for some reason.

I’ve written about this problem before, and there are a few ways to prevent the problem from occurring. Now that I’ve found an easy way to fix the problem, I don’t worry about prevention – I just clean things up when necessary.

Easy Steps to Fix the Problem

Here are the easy steps to remove the conditional formatting duplicate 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
  • 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

To confirm that the duplicate rules were removed, go back to the Manage Rules window. Only the two original rules should be listed.

Get the Sample File and Macro

To get the sample file that I used in this example, go to the Conditional Formatting page on my Contextures website. The details are there, as well as the macro code. Scroll down a bit to see the link to download the file – it is in xlsx format, so the macro isn’t in that file.

________________

Fix Conditional Formatting Duplicate Rules http://blog.contextures.com/

Save

You may also like...

6 Responses

  1. Firas says:

    Thank you DEBRA.
    This problem keep annoying me. I have a large table and I am frequently adding rows in the middle.
    I tried to solve it using VBA to clear all the Conditional Formatting and reapply them on the table.

    but it still manual.

  2. David N says:

    Another solution would be to create a Defined Name that always refers to the “cell above” the cell in question. Mike Alexander over at DataPig Technologies wrote a post on this technique. And of course a similar approach could be used to refer to the cell below, or two columns to the right, or any other relative position.

    http://datapigtechnologies.com/blog/index.php/create-a-named-range-that-always-references-the-cell-above/#more-3711

  3. Mike says:

    While this demo is better than what I was previously doing to adjust the ranges and clear the duplicates, I was hoping there would be a visual basic fix where every time a new row is inserted the formatting automatically adjusts the range. I want some of my staff to use my spreadsheet for data entry but don’t want them messing up the formatting by trying to manually change the ranges as shown in this demo.

Leave a Reply to Mike Cancel reply

Your email address will not be published. Required fields are marked *