Manage conditional formatting

In the Conditional Formatting Rules Manager, you can choose to show formatting rules for the areas of a workbook that have rules, such as the current selection or a specific worksheet.

You can then create, edit, and delete rules as well as manage the precedence of rules for the cells or worksheet you selected.

When conflicting rules apply to cells, the rule with higher precedence wins.

By default, the most recently created rule is at the top of the list and has precedence over previously created rules.

For example, in cells B2 through B10, I have already created two rules.

The first rule I created is, if the cell value is greater than 70,000, make the fill color yellow.

The next rule I created, and therefore the one with precedence, is if the cell value is greater than 60,000, make the fill color blue.

In cell B2, for example, the value is greater than 70,000, but, the value is also greater than 60,000, so the cell is formatted with a blue fill.

The way the rules are currently ordered, cells that are greater than 70,000, which are always going to be greater than 60,000, too, are formatted with a blue fill.

The 60,000 rule has precedence over the 70,000 rule.

If we change the order of the rules, thereby changing the precedence, cells that are greater than 70,000 are formatted with a yellow fill, and cells that are greater than 60,000, but less than 70,000, are formatted with a blue fill.

To see which cells of a worksheet have conditional formatting rules, possibly to investigate why they aren't working as you expect, click the Find & Select button on the HOME tab, and then click Conditional Formatting.

The cells that have conditional formatting rules are selected.

In this example, there are two rules; one applies to cells that contain errors and the other to cells that contain blank values.

Neither case exists on the worksheet, which is why you don't see conditional formatting.

But the rules are there.

Another way to manage conditional formatting is to sort and filter, based on the colors you apply.

To sort a worksheet based on conditional formatting, select the cells you want to sort.

Click the Sort & Filter button on the HOME tab (the Sort & Filter group on the DATA tab provides similar functionality.)

Click Custom Sort. In this example, the Profit column has icons that are green, yellow, and red circles.

For Sort On, I select Cell Icon; I want the green icons at the top.

I copy the sort rule, change it to the red icon, and set it to On Bottom.

I click OK and the worksheet is sorted by conditional formatting.

To filter a worksheet based on conditional formatting, select the cells you want to filter, click Sort & Filter, click Filter, click the filter down arrow for a column, click Filter by Color.

I want the red icons to remain visible, so I click it.

And only the rows with red icons in the Profit column are displayed.

Now you have a pretty good idea about how to take full advantage of conditional formatting.

Of course, there is always more to learn.

So, check out the course summary at the end, and best of all, explore Excel 2013 on your own.