Useful Applications Of Conditional Formatting In Excel – MS Excel Conditional Formatting Tutorial Part 5

Well, so far we have learnt a lot about conditional formatting through various examples and in this last tutorial on Conditional Formatting, we will learn see how conditional formatting is used in real world – you must trust us, it’s applications are awesome.

As mentioned above, in this section we will explore the most common and important applications of conditional formatting through examples.

Highlighting A Particular Value Using Conditional Formatting

Let us suppose that our database of product sales contains one more field “Region of sales”. Now our aim is to highlight sales in the “West” region for all the products. In other words all the cells which contain the text “West” should be highlighted. We can use a simple formula to achieve this!

conditional formatting in excelGo to “New Rule” and select “Use a formula to determine which cells to format”. Write down this formula:

=$F5=”West”

Select the highlight color.

This formula will traverse all the cells raging from F5 to F24 and will highlight the cells where the value is “West”. This is a wonderful application of conditional formatting to analyze a specific value in our data and draw useful insights.

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

conditional formatting in excel

Highlighting Unique And Duplicate Values Using Conditional Formatting

Let us consider that we have a client database which contains the names of clients whom we acquired last month and also the names of all the clients whom we acquired this month. Say our aim is to find out the clients which are unique to this month and also our repeat clients (who were with us last month also). Analysis of this information can be very easy if our data set is small but if our data set is very large as in most of the cases conditional formatting is a very good option.

conditional formatting in excelLet us first find out our repeat clients. We can do it like this:

Step 1: Select the cells where conditional formatting has to be applied. Go to “New Rule” and select “Use a formula to determine which cells to format”.

Step 2: Enter this formula.

=COUNTIF($D$6:$E$13,D6)>1

This formula will traverse the entire dataset and count if an item occurs more than once. Choose the desired format color from the color palette and click “ok”.

conditional formatting in excel (3)Step 3: We will get our list of repeat customers highlighted in “yellow”.

conditional formatting in excel (2)Now let us suppose that we have the same database and we need to find out the newly acquired customers this month or the unique customers.

The steps remain more or less the same except for the second step where a slightly different variation of the same formula needs to be applied.

Step 2: Use this formula

=COUNTIF($D$6:$E$13,D6)=1

This formula will traverse the entire data set and find the items which occur only once. Choose your formatting color and we are done! conditional formatting in excel (1)

conditional formatting in excel (4)Comparing Values In Two Columns Using Conditional Formatting

There might be many situations when our motive is to compare the values in two columns and then accordingly highlight them. For e.g. let us suppose that our aim is to highlight the values where the sales is less than the operational costs. This information is very important from a business perspective.

conditional formatting in excelStep 1: Similar to other examples we first need to select the cells go to “New Rule” and select “Use a formula to determine which cells to format”.

Step 2: Use this formula:

=$E5<$G5

This formula will compare the values in the column “Sales” to the corresponding values in the column “Operational cost”. If the value in column “Sales” is found lower than the corresponding value in the column “Operational cost” it will highlight the cells with our specified color. We have chosen the color “Red” to draw attention.
conditional formatting in excel (2)

conditional formatting in excel (1)In a large data set this way of doing conditional formatting is very useful because one can immediately be alert about the information which requires attention.

Highlighting Incorrect Values Or Formulas Using Conditional Formatting

This is another useful application of conditional formatting. This is particularly useful when we have large excel sheets with multiple formulas. There is always a possibility of making mistakes in the formulas and getting wrong results. Highlighting errors in formulas can help us in understanding where the errors are actually taking place and we can keep a track of the errors.

Let us consider this database where we have calculated the average sales per salesperson by dividing his total sales by the no. of customers acquired by him.

conditional formatting in excelThe fourth row in the last column shows the error “DIV/0” as the total sales of Celia is 1000 but the no. of customers acquired by her is 0. Our aim is to highlight such errors so that when we have a large dataset we get to know at one glance, where the errors are happening.

We can simple write a simple formula and apply conditional formatting. The formula is:

=ISERROR($G6)

This formula tells excel to check the values in the column average sales. If there is an error it should be highlighted with the color of our choice. In this case we have chosen “red”.

conditional formatting in excel (1)We are done!

conditional formatting in excel (2)

How To Use Conditional Formatting In Excel charts

There is no direct way of applying conditional formatting to charts. One has to use simple tricks to manipulate the dataset accordingly and create charts. The chart which is created appears as if it is conditionally formatted.
Let us try to understand this technique through a simple example:
conditional formatting in excelLet us suppose that we have a sales database which contains the names of our salespersons and their sales (INR).Assume that we need to highlight the sales persons with sales less than 40,000 in a different color. This is how we can do it:

Step 1: Manipulate the dataset. For doing this we have to create two columns and use a formula to extract the sales<40,000 and the sales>40,000.

conditional formatting in excel

The formula which has to be used for finding out the sales<40,000 and the sales>40,000 is:

=IF(F5<40000,F5,” “)

This formula tells excel to check if the values in the cells F5 to F10 are less than 40,000. Then it says that if the value is less than 40,000 then print the contents of the cell otherwise leave a blank space. When this formula is applied across all cells ranging from G5 to G10,in our example the values less than 40,000 are retrieved.

Similarly the column for Sales>40,000 will use the formula:

=IF(F5>40000,F5,” “)
conditional formatting in excel (1)

Our Table will look as shown below. We have retrieved the values less than 40,000 and the values more than 40,000 using a simple IF formula.

conditional formatting in excel

Step 2: Now select the entire dataset and go to “column” charts. As soon as you insert a column chart using this data you will get a chart which shows the sales>40,000 in “red” and the sales<40,000 in “blue”.

conditional formatting in excel (2)

This chart appears as if it has been conditionally formatted. But the trick is that we have just broken down the required data into two separate columns and created a simple bar chart!

So we have come to the end of tutorial on conditional formatting in Excel. We hope that you liked the series and learn quite a bit about conditional formatting in Excel. Please feel free to leave your feedback, suggestions and queries in the comments section below. We appreciate your time…!!

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