# How To Create A Conditional Formatting Rule In Excel – MS Excel Conditional Formatting Tutorial Part 4

In previous post, we learnt about in-build conditional formatting rules and in this post we will learn how to create custom formatting rules in Excel.

We will also learn how to manage and clear conditional formatting rules.

This is Part 4 in a series on MS Excel Conditional Formatting Tutorial. The full series is Part 1, Part 2, Part 3, Part 4 and Part 5.

#### Create New Conditional Formatting Rule

The new rule option in conditional formatting is used for custom formatting cells based on their values, formatting cells with respect to a specific value, formatting only top or bottom values, formatting values above or below the average value, formatting unique or duplicate values and most importantly for formatting cells as per an excel formula.

Let us consider our product sales database to understand this.

Now let us suppose that we want to highlight the products which have sales more than the average sales of all the products combined. In this example the average sales is 41891.95.

Step 1: We need to select the “New Rule” option. As soon as this option is selected a window appears. In that window select the option “Use a formula to determine which cells to format”.

[Enlarge the image by clicking on it for a better view]

Step 2: Enter the desired formula in the “Format values where this formula is true” text field. Always remember that a formula should start with an equal to(=) sign. In this case we want to highlight all the sales values which are more than the combined average. So we first need to calculate the average of all the values ranging from E5 to E24 and then we need to highlight the values(From E5 to E24) which are more than the calculated average value. In this example the average sales is 41891.95. All the values in column E more than this value should be highlighted. The formula we have entered is:

=\$E5>AVERAGE(\$E\$5:\$E\$24)

After writing this formula we have to click on the “Format” button. As soon as we click the “Format” button we will get a wide range of colors to choose from for highlighting our information. We have chosen “Yellow” here.

Step 3: As soon as we select the color and click the “OK button we will get our desired results. All the products which have sales more than the total average sales are highlighted in yellow.

#### Manage Conditional Formatting Rule

We can also edit our conditional formatting rules after we have applied them. For editing our rule we need to select “Manage Rules” option.

As soon as we click on “Manage Rules” a window will appear where will get various options to edit, delete or create a new rule.

#### Clear Conditional Formatting Rules

This option allows us to clear the conditional formatting created by us. We can choose to clear the conditional formatting from a range of selected cells, from the entire sheet or from a table.

If we choose the option “Clear Rules from Selected Cells” after selecting a range of cells, we will get our original table with no formatting.

So we know pretty much about what is conditional formatting and how to use it. In our next post, we will learn about some useful applications of conditional formatting.

1. The success of “The Devil Wears Prada” shows there’s an appetite for the subject, however, and anyone who allows the “Betty” controversy to put them off this winning comedy drama is missing out.