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.

conditional formatting in excelNow 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]

conditional formatting in excel (1)

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)

conditional formatting in excel (3)

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.

conditional formatting in excel (5)

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.

conditional formatting in excel (4)

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.

conditional formatting in excel (8)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.

conditional formatting in excel (9)

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.

conditional formatting in excel (6)

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.
conditional formatting in excel (7)

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. 私は本当に 愛あなたのブログ .. 素晴らしい色&テーマ。 このサイトについてあなたは開発なかった自分自身? 私の非常に自身の作成私は だとして戻って返信してくださいサイトと見つけるあなたはこのを持ってどこか正確に何テーマはという名前が付けられます。 ありがとうございます、それに感謝!
    正規店急上昇 即日出荷 http://www.zwik.olawa.pl/tide-25180-217.html

  2. I わからないブログ 遭遇経験 。 書かれたテキストテキストの一部ポストオフ実行しているあなたの内のあなたに 画面。 他の誰かことができますしてくださいコメント、これは彼らに起こっているなら、私に知らせてあまりに?私はこれが起こる持っていたので、 Webブラウザブラウザ 以前私と問題問題です。 おかげ
    即日出荷 新色大人気 http://apollogym.lt/milk-25176-171.html

  3. 回答|スパム 好奇心の不思議 こんにちは 時から時折と私は同様のものを所有し、私はちょうどして、私はあなたのブログを読んで?ので、どのようにあなたがしなければ それは、任意のプラグインや、あなたができるもの低減? 狂った狂った私は最近、それが私を駆動するのはそんなにを取得するので、任意のヘルプ非常に高く評価されています。
    激安期間限定 3日間限定 http://bodegasandrade.es/sport-24662-193.html

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">