Conditional formatting rules in Excel

Conditional formatting is very useful if you have to deal with thousands of rows of data. With conditional formatting, you can visualize trends and patterns much quicker than without it.

You can find this feature by navigating to Home >> Styles >> Conditional
Formatting.

Five top icons are quick rules with suboptions, but if you
want to have bigger control over it, you have to create a new rule.

Conditional formatting rules

After you click on the New Rule button, you get access to
six types of rules.

In this tutorial, I’m going to explain each of these rule
types. Here’s is an example, we are going to use.

Format all cells based on their values

With this rule, you can create conditional formatting for data bars, color schemes, and icons
sets.

The difference is that when you create a New Rule, you have
much more control over the way how this formatting is displayed.

2 and 3 color scales

If it’s color scale,
you can choose exactly which color you want to use for minimum, midpoint, and
maximum value. You can also type, such as:

Lowest value

Highest value

Number

Percent

Formula

Percentile

This is the default result without any changes.

Data Bar

In this format style, you have options to choose between solid fill and gradient, decide whether you want to display bars with the border or without, choose the option to show bar with values or without values.

This is the result of the data bar.

Icon Sets

This rule uses icons. Excel offers a list of different icon
sets. Values are divided between three to five icons.

This is one of the sets.

Format only cells that contain

In this rule, you can search for blank cells, errors, dates,
and specific text.

If you choose one of these options, for example, specific
text, there will be additional options.