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.
- 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
As mentioned above, in this section we will explore the most common and important applications of conditional formatting through examples.
- Highlighting a Particular Value
- Highlight Unique and Duplicate Values
- Comparing Values in Two Columns
- Highlighting Incorrect Values or Formulas
- How To Use Conditional Formatting in Charts
- How To Use Color Coding In Excel Drop Down
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!
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]
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.
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.
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”.
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
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.
Step 2: Use this formula:
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.
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.
The 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:
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”.
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:
Let 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.
The formula which has to be used for finding out the sales<40,000 and the sales>40,000 is:
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:
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.
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”.
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…!!