You are in: how-to > Conditional Formatting

CONDITIONAL FORMATTING


Excel 2013/2016 | All levels | by Jorge Vilar

Conditional Formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells.
For example, you can set that all negative values in a range have a red background colour.
All the examples of this how-to are in the file ConditionalFormatting.xlsx.

CONDITIONAL FORMATS THAT USE GRAPHICS

There are three conditional formatting options that display graphics: data bars, colour scales and icons sets.

Using data bars

The data bars conditional format displays horizontal bars directly in the cell. The length of the bar is based on the value of the cell, relative to the other values in the range.

A simple data bar example

Below figure shows an example of data bars. It's a list of tracks on Bob Dylan albums, with the length of each track in column E.

Let's apply data bar conditional formatting to the values in column E. Then you can tell at a glance which tracks are longer (worksheet data bar):

1. Select the cells with the track duration (E3:E13).
2. Go to Home > Styles > Conditional Formatting > Data Bars.
3. Choose a predefined format or, for additional choices, click More Rules option.

You can see the final result in worksheet data bar example.

Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart.

Using colour scales

The color scale conditional formatting option varies the background colour of a cell based on the cell’s value, relative to other cells in the range.

scales example

Below figure show a range of cells that use color scale conditional formatting. It depicts number of employees on each day of the year. It's a 3-color scale (red for the lowest value, yellow for the mid-point and green for the highest value).

Values in between are displayed using colour within the gradient.

Let's apply this formatting (worksheet color scale):

1. Select relevant cells (B1:AF15).
2. Go to Home > Styles > Conditional Formatting > Color Scales.
3. Choose one of the existing formats or More Rules.

Final result on worksheet color scale example.

USING ICONS SETS

The last graphics conditional formatting is to display an icon in the cell. The icon displayed depends on the value of the cell. In the worksheet icon set we want symbols to show the status of each project, based on the value in column C.

To do this:

1. Select C3:C13
2. Choose Home > Styles > Conditional Formatting > Icon Sets

In this example I used Indicators 3-symbols. Final result in worksheet icon sets example.

By default, the symbols are assigned using percentiles. For a 3-symbol set, the items are grouped into three percentiles. For a 4-symbol set, they’re grouped into four percentiles. And for a 5-symbol set, the items are grouped into five percentiles.

You can modify the icon set rules to your expectations. For example, you can modify the icons so that only projects that are 100% completed get the check mark icons. Projects that are 0% completed get the X icon. All other projects get the exclamation icon.

You can either modify existing rule:

Select the cells
Go to Home > Styles > Conditional Formatting > Manage Rules.
Then select the rule to modify and click the Edit Rule button.

Or you can create rule from scratch, if it doesn’t exist:

Select the cells
Go to Home > Styles > Conditional Formatting > New Rule
Rule Type is the first – Format all cells based on their values.

In Edit the Rule Description select:

Format Style: Icon Sets
Icon Style: 3 Symbols (Uncircled)

In Icon:

First value >= 100
Second value > 0
The third value will be filled in automatically.

Final result on worksheet icon sets example.

RULES

If you want to format cells according to the content, select the cells, go to Home > Styles > Conditional Formatting > New Rule. Now choose Format only cell that contain.
You can now specify as many rules as you like by clicking the New Rule every time.

In the below example (worksheet rules),

you want the following rules regarding the contents:

If value is 0 or negative, display numbers in bold and red
If values are greater than zero and equal to 6, display numbers in bold and blue and
If numbers are bigger than 6, display numbers in bold and green.
To do this:

1. Select Value column (D4:D122)

2. Home > Styles > Conditional Formatting > New Rule.

3. Select Format only cells that contain and the following rule in Edit the Rule Description:

4. Press Format

5. In the Font tab, select Font style Bold and Font Color red:

6. Press OK.

To create the other two rules, follow again from step 2 on and using the desired colours in Format:

At any time, you can check all rules that apply to the selected range of cells:

Home > Styles > Conditional Formatting > Manage Rules.

Here you can add new rule, edit selected rule or delete selected rule.

ANOTHER RULE EXAMPLE

In the following list, you want the check if today a certificate is still valid.

If the date of the certificate (assuming today is 11/11/2015) is greater than today, fill the background in green, otherwise in red.

The steps are as follows:

Select the cells (H4:H7)

Home > Styles > Conditional Formatting > New Rule.

Select Format only cells that contain and the following rule in Edit the Rule Description:

Create another rule for values greater than or equal to today:

I used the function Today(), that returns the today's date.

CREATING FORMULA-BASED RULES

Excel's conditional formatting feature is versatile, but sometimes it's just not quite versatile enough.

Fortunately, you can extend its versatility by writing conditional formatting formulas. To specify conditional formatting based on a formula, select the cells and then choose

Home > Styles > Conditional Formatting > New Rule.

This command displays the New Formatting Rule dialog box. Click the rule type Use a Formula to Determine Which Cells to Format, and you can specify the formula.

You can type the formula directly into the box, or you can enter a reference to a cell that contains a logical formula. As with normal Excel formulas, the formula you enter here must begin with an equal sign (=).

Note

The formula must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, the condition is satisfied, and the conditional formatting is applied. If the formula evaluates to FALSE, the conditional formatting is not applied.

Example

Suppose that you want to set up a conditional formatting condition that applies shading to cells in range B2:C11 only if the cell contains text. None of Excel's conditional formatting options can do this task, so you need to create a formula that will return TRUE if the cell contains text and FALSE otherwise. Follow these steps (worksheet formula rules):

1. Select the range B2:C11 and ensure that cell B2 is the active cell.

2. Choose Home > Styles > Conditional Formatting > New Rule to display the New Formatting Rule dialog box.

3. Click the Use a Formula to Determine Which Cells to Format rule type.

4. Enter the following formula in the Formula box: =ISTEXT(B2)

5. Click the Format button to display the Format Cells dialog box.

6. From the Fill tab, specify the cell shading that will be applied if the formula returns TRUE. Same from the Font tab.

7. Click OK to return to the New Formatting Rule dialog box.

8. In the New Formatting Rule dialog box, click the Preview button. Make sure that the formula is working correctly and to see a preview of your selected formatting.

9. If the preview looks correct, click OK to close the New Formatting Rule dialog box.

Notice that the formula entered in Step 4 contains a relative reference to the upper-left cell in the selected range.

The results are in worksheet formula rules examples.

You can check more examples in worksheets formula rules and formula rules examples.

WORKING WITH CONDITIONAL FORMAT

Copying cells that contain conditional formatting

Conditional formatting information is stored with a cell much like standard formatting information is stored with a cell. As a result, when you copy a cell that contains conditional formatting, you also copy the conditional formatting.

If you insert rows or columns within a range that contains conditional formatting, the new cells have the same conditional formatting.

Deleting conditional formatting

When you press Delete to delete the contents of a cell, you do not delete the conditional formatting for the cell (if any). To remove all conditional formats (as well as all other cell formatting), select the cell. Then choose Home > Editing > Clear > Clear Formats. Or, choose Home > Editing > Clear > Clear All to delete the cell contents and the conditional formatting. To remove only conditional formatting (and leave the other formatting intact), use Home > Styles > Conditional Formatting > Clear Rules.

Locating cells that contain conditional formatting You can't tell, just by looking at a cell, whether it contains conditional formatting. You can, however use the Go To dialog box to select such cells.

1. Choose Home > Editing > Find & Select > Go To Special.

2. In the Go To Special dialog box, select the Conditional Formats option.

3. To select all cells on the worksheet containing conditional formatting, select the All option; to select only the cells that contain the same conditional formatting as the active cell, select the Same option.

4. Click OK. Excel selects the cells for you.

- end -

Comments, suggestions and questions are always welcome - jorge@jorgevilar.com.

 •   top...»