7. Conditional formatting


Conditional formatting allows you to define a formatting (essentially font or background colors of cells, etc.) that will be applied to cells meeting a condition. This has the advantage of highlighting cells that meet certain criteria or showing variations or trends.

A great advantage with this technique is that after defining conditional formatting, Excel automatically updates the formatting, every time you make updates at your sheet.

To define conditional formatting, we will use:

  • Either the Conditional Formatting command from the Home tab of the ribbon
  • Or the Quick Analysis button

We are going to give you an overview of this Quick Analysis button. Next, we will see a multitude of real-life examples of conditional formatting.

7.1. The Quick Analysis button

This button, introduced from the 2013 version of Excel, appears as soon as you select a range of cells ... at the lower right corner of the selection: Excel 2016 Quick Analysis Button

Click on the Quick Analysis button, to see conditional formatting options.

If the selected cells contain only text, the options that appear are:

  • Text That Contains
  • Duplicate Values
  • Unique Values
  • Equal to
  • Clear Rules
Excel 2016 Quick Analysis Button Text

If the selected cells contain numbers, the options that appear are:

  • Data Bars
  • Color Scales
  • Icon Sets
  • Greater Than
  • Top 10%
  • Clear Rules
 Excel 2016 Quick Analysis Button Number

Other conditional formatting commands are available from the Conditional Formatting command in the Styles group on the Home tab of the ribbon.

7.2. Values less than, greater than, between

Consider the following excerpt from a spreadsheet:

Excel 2016 Example Conditional Formatting 

Let's apply some formatting for the values in column B as follows:

  • Values greater than 8000 in green
  • Values between 4000 and 8000 in yellow
  • Values less than 4000 in red

Select the range B2:B14 and click on the Conditional Formatting command on the Home tab of the ribbon, choose Highlight Cells Rules, then choose Greater Than:

 Excel 2016 Conditional Formatting Greater Than

In the Greater Than dialog box that appears, enter the value 8000 and select Green Fill with Dark Green Text:

Excel 2016 Conditional Formatting Greater Than Dialog Box 

Keep the range B2:B14 selected and click the Conditional Formatting command on the Home tab of the ribbon, choose Highlight Cells Rules, then choose Between:

Excel 2016 Conditional Formatting Between

In the Between dialog box, enter the value 4000, the value 8000 and select Yellow Fill with Dark Yellow Text:

Excel 2016 Conditional Formatting Between Dialog Box 

Select the range B2: B14 again and click the Conditional Formatting command on the Home tab of the ribbon, choose Highlight Cells Rules, then choose Less Than:

 Excel 2016 Conditional Formatting Less Than

In the Less Than dialog box, enter the value 4000 and select Light Red Fill with Dark Red Text:

 Excel 2016 Conditional Formatting Less Than Dialog Box

Here is the result:

 Excel 2016 Example Conditional Formatting

Modify a value; for example, at line 4, put the value 2200 and validate with the Enter key on the keyboard. Notice that cell B4 automatically turns red:

Excel 2016 Example Conditional Formatting

7.3. Cells containing text

Consider the following spreadsheet extract:

 Excel 2016 Example Conditional Formatting

Suppose we want to show the inhabitant cases at Charlwood Street. Let's do the following:

Select the range C4:C16. The Quick Analysis button Excel 2016 Quick Analysis Button appears at the bottom of the selection. Click on the Quick Analysis button then on the button Text That Contains:

Excel 2016 Quick Analysis Button Text that Contains

The Text That Contains dialog box appears:

Excel 2016 Conditional Formatting Text That Contains Dialog Box 

At this dialog, you can enter the text sought, that is to say, "Charlwood Street." But, since the searched text is written in cell B1, click on that cell. Excel adds the value "=$B$1". The advantage is that, if you want to search for another text, you just have to type it in cell B1, without having to redo the formatting.

In the list of formats, you can choose a format from the list or choose Custom Format…

 Excel 2016 Conditional Formatting Text That Contains Dialog Box

By choosing Custom Format…, the Format Cells dialog box appears. You can compose here the format that suits you by using the 4 tabs of this dialog box:

Excel 2016 Conditional Formatting Format Cells Dialog Box

Choose a format and validate by clicking on the OK button.

Then click on the OK button in the Text That Contains dialog box. Notice the fields highlighted using the format you chose:

Excel 2016 Example Conditional Formatting 

Now, modify the text of cell B1 by "Cumberland Street" for example, and validate with the Enter key on the keyboard. Notice that the formatting is updated automatically by Excel:

Excel 2016 Example Conditional Formatting

7.4. Duplicate values

Let's take the extract of the following spreadsheet containing in column A Mail addresses:

Excel 2016 Example Conditional Formatting 

Normally, there should not be any duplicate email addresses, otherwise it is a typing error. So let's use this conditional formatting technique to find and correct any duplicates.

Select the range A2:A14 and click on the Quick Analysis button and then on the Duplicate Values button:

Excel 2016 Conditional Formatting Duplicate Values Button 

Excel highlights duplicate values in the selection:

Excel 2016 Example Conditional Formatting 

7.5. Data Bars

Consider the following extract from a spreadsheet:

Excel 2016 Example Conditional Formatting 

Select the range B2:B14 and click on the Quick Analysis button and then on the Data Bars button:

Excel 2016 Conditional Formatting Data Bars Button 

You can do this in a different way. Select the range B2:B14 and click on the Conditional Formatting command on the Home tab of the Ribbon, choose Data Bars, and then choose the colour you want:

Excel 2016 Conditional Formatting Data Bars Button

Excel shows the importance of the values in the selection in a similar way to a graph:

Excel 2016 Example Conditional Formatting 

7.6. Icon Sets

Consider the following extract from a spreadsheet:

Excel 2016 Example Conditional Formatting 

Select the range B2:B15 and click the Conditional Formatting command on the Home tab of the Ribbon, choose Icon Sets, then choose an icon set:

Excel 2016 Conditional Formatting Icon Sets Button 

The result is as follows:

Excel 2016 Example Conditional Formatting 

The disadvantage with this result is that the green colour indicates large numbers of offences (6 and 7), whereas it should indicate small numbers. On the other hand, I suppose we want a different distribution between the 3 colours, e.g:

  • Red colour for 4 and more
  • Orange colour for 2 and 3
  • Green colour for 1

To get this result, proceed as follows:

Select the range B2:B15 again and click on the Conditional Formatting command on the Home tab of the Ribbon, choose Icon Sets and then choose More Rules:

 Excel 2016 Conditional Formatting Icon Sets Button More Rules

The New Formatting Rule dialog box appears:

Excel 2016 Conditional Formatting New Formatting Rule dialog box 

Click on the Reverse Icon Order button, change the Type to "Number" and the values as follows:

Excel 2016 Conditional Formatting New Formatting Rule dialog box 

Confirm with the OK button. You should have the following result:

 Excel 2016 Example Conditional Formatting

7.7. New formatting rule

Consider the following spreadsheet extract:

Excel 2016 Example Conditional Formatting

We want to highlight the names of subscribers whose donations exceed £4,000 and who live in London, for example. To do this, we will create a new formatting rule.

  • Select the range of cells in column A where the names of the subscribers are written. For example the range A2:A80.
  • Click on the Conditional Formatting command on the Home tab of the Ribbon, choose New Rule...

Excel 2016 Conditional Formatting New Rule

In the New Formatting Rule dialog box that appears :

  • Click on Use a formula to determine which cells to format from the list Select a Rule Type...
  • In the Format values where this formula is true field enter the formula :

=AND(E2>4000;F2="London")

The result of this formula is true when the conditions 'E2>4000' and 'F2="London"' are both true. See our sections on Excel 2016 Functions for more information on the AND function.

  • Then click on the Format... button to open the Format Cells dialog box and choose the format that suits you. The use of this dialogue box is mentioned in section 7.3. above.
  • Confirm with the OK button.

Excel 2016 Conditional Formatting New Formatting Rule dialog box

Here is the result:

Excel 2016 Example Conditional Formatting

I would like to draw your attention to the fact that in the formula I have used the addresses of cells E2 and F2. These cells correspond to the first cell of the selection. That is to say, the one situated at the top left of the selection; that is to say, A2. The formula is applied to the other cells in the selection in a similar way to the Autofill.

And if we want to highlight the whole row for which the condition is true. Like this: 

Excel 2016 Example Conditional Formatting

Explanations in the next section.

7.8. Viewing and modifying formatting rules

Let's modify the rule defined in the previous section to highlight the whole row for which the condition is true:

  • Click on a cell in the range A2:A80; where the conditional formatting is defined
  • Click on the Conditional Formatting command on the Home tab of the Ribbon, choose Manage Rules...

Excel 2016 Conditional Formatting Manage Rules

  • The Conditional Formatting Rule Manager dialog box opens:

Excel 2016 Conditional Formatting Rules Manager dialog box

In this dialogue box,

  • You can display the formatting rules for :
    • The current selection
    • The current sheet
    • Or each sheet of the current workbook
  • The list of rules is displayed in the table below. In the extract above, two rows corresponding to two formatting rules
  • The buttons New Rule..., Edit Rule... and Delete Rule... allow you, as their names indicate, to
    • Create a new formatting rule
    • Modify the rule selected in the list below
    • Delete the rule selected in the list below

I remind you that we want to modify the rule defined in the previous section in order to highlight the whole line for which the condition is true, i.e.; donations greater than 4000 and city equals London. To do this, in the Conditional Formatting Rules Manager dialog box:

  • Edit the Applies to field for the rule to :

=$A$2:$F$80

NB. Note that this field is editable in the dialog box.

NB. You can now click on the OK button and see the result. The rest of the line does not take the defined formatting.
Why not? I remind you again that with the relative cell references E2 and F2, Excel increments the E and F for the cells in columns B, C, ... This means that for cell B2 for example, Excel will check the conditions in cells F2 and G2...
You must, therefore, change the formula of the rule to : =AND($E2>4000;$F2="London"). To do this:

  • Click on the line of the rule we want to modify
  • Click on the button Edit Rule...
  • In the Edit Formatting Rule dialog box that appears, enter the following in the Format values where this formula is true field:

=AND($E2>4000;$F2="London")

Excel 2016 Conditional Formatting Edit Formatting Rule dialog box

  • Click the OK button to return to the Conditional Formatting Rules Manager dialog box
  • Click on the OK button and see the result:

Excel 2016 Example Conditional Formatting

7.9. Clear conditional formatting

To clear the conditional formatting for a range of cells, select that range of cells and use the Quick Analysis button, then the Clear Rules button:

 Excel 2016 Conditional Formatting Clear Rules Button

NB. You can undo a conditional formatting by going through the Conditional Formatting Rules Manager dialog box as mentioned in the previous section.

7.10. Copy of conditional formatting

As this is a layout, copying it from one cell range to another must be done using the Format Painter button Excel 2016 Format Painter Button

But, if you make a copy of cell contents, for example, using the Autofill technique, then the formatting is copied by Excel at the same time.


<< 6. Use of calculations

8. Work with spreadsheets >>

Reader comments

Add a comment