- 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:
- Introduction To Conditional Formatting In Excel
- How To Apply Conditional Formatting In Excel
- In-built Conditional Formatting Rules In Excel
- How To Create A Conditional Formatting Rule In Excel
- Useful Applications Of Conditional Formatting In Excel
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 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.