Technologie Google

Why does my conditional formatting keep changing Google Sheets

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.

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 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.

Two Simple 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 this Excel table, there are 2 conditional formatting rules:

Price: Turn price cell green if amount is higher than 500. This makes it easy to spot the highest prices
Date: Add a blue top border across the entire table row, if date in column A is different from date in row above. This marks the row where each day's data starts

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

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

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.

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

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

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

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):

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

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

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.

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. 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

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

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 code below into your workbook, or get the code in the Fix Macro workbook (below).

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.

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

How to Prevent Extra Rules

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

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.

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

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

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.

Get the Sample File

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.