With conditional formatting, select one or more cells, and create rules (conditions) for when and how those cells are formatted. Conditions can be based on selected cell's contents, or based on contents of another cell
With conditional formatting, you can select one or more cells, and create rules (conditions) for when and how those cells are formatted. The conditions can be, based on the selected cell's contents, or based on the contents of another cell.
You can control the following formats:
If the rules (conditions) that you specified are met, then the formatting is applied.
For example, you can set conditional formatting so that a cell turns red if its value is low, and turns green if its value is high.
To view the steps for adding conditional formatting, watch this short video. The written instructions are below the video.
In this example, you'll set conditional formats so that a cell:
Follow these steps to apply conditional formatting to cells:
The cells with values greater than 75 are now coloured green.
To colour the low values in red fill, you can apply a second conditional formatting rule to the cells.
The cells with values greater than 75 are now coloured green, and cells less than 50 are red.
After you set up conditional formatting rules, you might see a problem with new rules being created automatically. This can happen if you insert or delete rows in the data.
Watch this video to see how the problem happens, and how to fix the duplicated conditional formatting rules. The written instructions are below the video, and the full transcript is at the end of this section.
There is also a macro that removes the duplicate rules, if the data is in a named Excel table.
In this example, there are 2 conditional formatting rules:
If you delete row 10 in this table, a new rule for the top border will be automatically created.
That happens because the conditional formatting 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
If you frequently delete and insert rows, you could end up with dozens, or hundreds, of duplicated rules. That could slow down your workbook's calculation speed.
To remove the duplicate rules, follow these steps:
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, use the following macro.
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 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.
Click here to download a zipped sample file for this tutorial.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: December 7, 2020 10:50 AM