What happens if multiple conditional formatting rules are applied to the same cell?

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.

Asked 8 years, 9 months ago

Viewed 75k times

I need to do the following. Highlight a row if cell E contains a number greater than 30 AND cell L contains a number greater than 100. This rule needs to be applied to all rows. Can you help please?

asked Feb 11, 2014 at 9:28

You should use CONDITIONAL FORMATTING:

1] Select all cells in the sheet [by pressing on the top left corner]:

2] With selected range go to "Conditional Formatting -> New Rule.."

3] Select "Use formula..." rule type, enter formula =AND[$E1>30,$L1>100]. Choose desired format and press "OK"

RESULT:

answered Feb 11, 2014 at 9:41

Dmitry PavlivDmitry Pavliv

35k12 gold badges78 silver badges80 bronze badges

You can include up to three different conditions on any cell or range of cells.
For example your three conditions might be:
0] If the number < 0 then shade the cell red.
1] If the number = 0 then shade the cell orange.
2] If the number > 0 then shade the cell green.

First Condition

We are going to apply conditional formatting to the range of cells "B2:E5" and we are going to shade all the cells identifying whether they are negative, zero or positive.
Select the range of cells you want to apply the conditional formatting to, in this case cells "B2:E5".

Select [Format > Conditional Formatting] to display the Conditional Formatting dialog box.
Select "Cell Value Is" in the first drop-down box and "less than" in the second dialog box.
Enter the constant value 0 in the third box.
Click the Format button to apply your specific formatting, in this case we are just applying a red background.

Second Condition

You can add another condition by pressing the "Add" button.
Select "Cell Value Is" in the first drop-down box and "equal to" in the second dialog box.
Enter the constant value 0 in the third box.
Click the Format button to apply your specific formatting, in this case we are just applying a orange background.

Third Condition

You can add another condition by pressing the "Add" button.
Select "Cell Value Is" in the first drop-down box and "greater than" in the second dialog box.
Enter the constant value 0 in the third box.
Click the Format button to apply your specific formatting, in this case we are just applying a green background.

Press OK to apply your conditional formatting.

Important

If you specify more than one condition it is the formatting associated with the first condition which is true, which will be applied to the cell [or range of cells].

Can you have multiple conditions in conditional formatting?

Conditional Formatting With Multiple Conditions To highlight cells according to multiple conditions being met, you can use the IF and AND Functions within a conditional formatting rule. Select the range you want to apply formatting to. In the Ribbon, select Home > Conditional Formatting > New Rule.

What will happen if some cells have overlapping conditional formatting ranges in MS Excel?

Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all of the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting.

How many rules can you have in conditional formatting?

Apply multiple conditional formatting rules to same cells When using conditional formats in Excel, you are not limited to only one rule per cell. You can apply as many rules as your business logic requires.

Can I apply the same conditional formatting to multiple rows?

Select the cell [or range of cells] from which you want to copy the conditional formatting. Click the Home tab. In the Clipboard group, click on the Format Painter icon. Select all the cells where you want the copied conditional formatting to be applied.