Introduction To Conditional Formatting In Excel – MS Excel Conditional Formatting Tutorial Part 1

Conditional Formatting in Excel is a very powerful tool. It is easy to use and provides a lot of options to users. Most of us would have come across a situation where our managers would have asked:

  • Who are the top 3 and the bottom 3 salespersons for this quarter?
  • Name our 10 top selling products this year?
  • Can you compare the performance of this year to the performance of the previous year and tell us which year has been better for us?
  • Can you highlight the no. of customers for this particular product?

This information is important for any organization. But have you ever wondered, how difficult it is to arrive at this information from simple excel data files? If our dataset is small going through it once will still help us gather this information or perhaps other important information. But what if our dataset is large and has more than hundreds of records? What if the organization’s requirement is to get this information quickly without wasting much time? Excel Charts alone don’t always suffice the purpose.

There has to be a mechanism to highlight the important information as per our criteria. Conditional Formatting can solve our problem. With conditional formatting we can highlight important cells in our excel sheet/table or make important information stand out based on our criteria. Conditional Formatting can be applied to cells, charts and tables.

In this series on MS Excel Conditional Formatting, we will cover below topics:

Figure below highlights the area in Excel which is used to apply conditional formatting.

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

conditional formatting in excelTypes Of Conditional Formatting

Conditional Formatting can broadly be divided into two types. Let us understand each of these.

Conditional Formatting Based On Values

Let us suppose that we have a list of students along with their marks and we need to highlight the students who have scored above 90. This form of conditional formatting is known as conditional formatting based on values because we don’t need any excel formula for this. We can directly apply the conditional formatting from the “conditional formatting” option. Other common types of conditional formatting based on values are highlighting the cells which contain more than/less than the average value, highlighting a particular value in a dataset, highlighting the top/bottom 10% values and highlighting duplicate values.

Conditional Formatting Based On Formula

Let us suppose that we have to highlight the names of students who have registered for a course during the last week. For this we require COUNTIF formula. This form of conditional formatting in which the cells have to be highlighted based upon the results of a formula is known as conditional formatting based on formula. Formulas can be used for many other applications.

So we learnt why do we need conditional formatting and what are the types of conditional formatting in this tutorial. In next tutorials, we will learn about how to apply conditional formatting, in-built and custom conditional formatting rules and application of conditional formatting in day to day life.

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

  1. Thanks for the auspicious writeup. It in truth used to be a amusement account it.

    Glance advanced to far delivered agreeable from you! However, how could we communicate?

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="">