Contextures

Conditional Formatting Introduction

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

What is Conditional Formatting?

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:

  • Number format
  • Font, font style, and font colour (but not font size)
  • Fill colour and fill pattern
  • Border colour and border style (but not border thickness)

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.

Sample Conditional Formatting

Video: Color Cells Based on Cell Value

To view the steps for adding condtional formatting, watch this short video. The written instructions are below the video.

Apply Conditional Formatting to a Cell

In this example, you'll set conditional formats so that a cell:

  • turns green if it contains a value higher than 75 and
  • turns red if it contains a value lower than 50.

Follow these steps to apply conditional formatting to cells:

  1. In cell I1, type the high value -- 75
  2. In cell I2, type the low value -- 50
  3. enter high low values

  4. Select the cells to be formatted. In this example, cells E2:E7 are selected.
  5. On the Ribbon's Home tab, click Conditional Formatting
  6. conditional formatting command

  7. To format the high values, click Highlight Cell Rules, then click Greater Than...
  8. enter high low values

  9. In the Greater Than window, delete the value that appears, and click on cell I1, where the High value is entered.
  10. enter cell reference

  11. Click the drop down list for formats, and click Custom Format.
  12. select custom format

  13. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.
  14. select custom format

  15. Click OK to close the Format Cells window, and click OK to close the Greater Than window.

The cells with values greater than 75 are now coloured green.

high values formatted

Apply 2nd Conditional Format

To colour the low values in red fill, you can apply a second condtional formatting rule to the cells.

  1. Select the cells to be formatted. In this example, cells E2:E7 are selected.
  2. On the Ribbon's Home tab, click Conditional Formatting
  3. conditional formatting command

  4. To format the high values, click Highlight Cell Rules, then click Less Than...
  5. enter high low values

  6. In the Less Than window, delete the value that appears, and click on cell I2, where the Low value is entered.
  7. enter cell reference

  8. Click the drop down list for formats, and click Custom Format.
  9. select custom format

  10. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.
  11. select custom format

  12. Click OK to close the Format Cells window, and click OK to close the Less Than window.

The cells with values greater than 75 are now coloured green, and cells less than 50 are red.

high and low values formatted

Fix Conditional Formatting Extra Rules

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.

Two Conditional Formatting Rules

In this example, there are 2 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

two conditional formatting rules

New Rules Automatically Created

If you delete row 10 in this table, a new rule for the top border will be 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

duplicate conditional formatting rules

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.

Fix the Conditional Format Duplicate Rules

To remove the duplicate rules, follow these steps:

  • 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
  • clear conditional formatting 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
  • duplicate conditional formatting rules

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

duplicate conditional formatting rules

Macro to Fix Duplicate Rules

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

Download the Sample File

Click here to download a zipped sample file for this tutorial.

Get All the Excel News

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

Search Contextures Sites

 

Related Tutorials

Conditional Formatting -- Based on another cell

Conditional Formatting -- Examples

Conditional Formatting -- Documentation  

Conditional Formatting -- Data Bars  

Search Contextures Sites

 

30 Excel Functions in 30 Days

 

excel tools add-in

 

 

 

Excel Data Entry Popup List

 

Last updated: March 11, 2017 9:11 AM