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 condtional 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 condtional 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 condtional formatting rules. The written instructions are below the video.
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
Click here to download a zipped sample file for this tutorial.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: March 11, 2017 9:11 AM