How to Use Conditional Formatting in Excel
In order to understand How to Use Conditional Formatting in Excel, you need to first understand the reason why you are required to use it.
Say you are comparing two cells against each other in Excel. You want to check whether the data in both of them are same or not. How do you do that?
You can make use of a simple EXACT formula to nail that sucker like this:
=EXACT(A1:B1)
where A1 and B1 are those cells you need to compare.
So for example, if you have something like this:
which is basically the same stuff in both A1 and B1. We can use the formula of EXACT and then provide the cell addresses that need to be compared, here A1 and B1.
Now when you press enter, you will notice that the comparison of cells is made and the result replaces your formula. So in this case both the cells A1 and B1 have the exact same data and hence you get the boolean value TRUE:
NOTE: The above-used formula is case sensitive. If you want to use a case-insensitive formula you can make use of:
=AND(A1=B1)
Let’s take one more case to see if it works like a charm or not.
I am putting two different kinds of data in A2 and B2 now. Now you don’t need to write the formula all over again. You can simply drag the small square dot on the selector after you have selected C1. Something like this:
When you let go you will notice the result being shown in C2:
Hey, but it is giving us the result in the form of TRUE or FALSE. What if we wanted something different altogether to be printed there?
That’s where conditional formatting in Excel comes into the picture.
How to Use Conditional Formatting in Excel
Conditional Formatting, as the name already suggests, is about setting things as per your preference. So I want something else altogether in place of TRUE and FALSE. Let us replace TRUE with ‘CIERTO’ and FALSO with ‘FALSO’. That’s Spanish BTW.
Here are the steps to learn how to use conditional formatting in Excel:
Step 1: Select the cell where you wish to perform conditional formatting. I have made the selection on TRUE.
Step 2: Click on Conditional Formatting and then on New Rule. Conditional Formatting might be located in the top bar.
When you click on New Rule… it will open a New Formatting Rule dialog box.
Step 3: Click on the Format only cells that contain
Step 4: Click on the second drop-down. Then choose the value ‘equal to‘ as shown in the figure:
Step 5: In the third text box provide the Cell Value as “TRUE”. Then click on the Format button.
Step 6: Select Number tab. Then in the Category box select Custom. Then click on the Type textbox.
Step 7: Remove everything that’s in there, and type the following instead:
"CIERTO";"CIERTO";"CIERTO";"CIERTO"
where CIERTO can be replaced by anything you wish to show there.
NOTE: Make sure you don’t miss the double inverted commas.
Step 8: Once done, click on Ok.
The same process has to be repeated for FALSE, meaning we have to create yet another rule to replace FALSE with FALSO. Let’s do that as well.
Conditional Formatting FALSE
Step 9: Bring your cursor to FALSE. Then click on Conditional Formatting once again.
Step 10: Select Format…in the above screen.
Step 11: Click on the first tab which is the Number tab. Then select Custom in the Category box, and remove everything there is in Type textbox.
Type the following then:
"FALSO";"FALSO";"FALSO";"FALSO"
Step 12: Click on Ok then to see the change:
Voila! Works like a charm.
Put random data in the following rows and then pull the bottom right cell box + symbol from FALSO cell to see the change:
See! Works right? The last one A6 and B6 are both empty and hence it still prints CIERTO there which is the expected behaviour.
The Better Alternative
In cases wherein you are supposed to compare two cells, you can make use of IF condition as well. You can provide custom messages simply in the third and fourth parameter like this:
=IF(A1=B1, "CIERTO", "FALSO")
As you can see, you can replace CIERTO and FALSO with whatever data you wish to display.
It will give you the same result.
I will just go ahead and pull the D1 down to see the changes.
Same! Good alternative eh!
Conditional Formatting by Using Colors
Now while working in a testing environment, you might be required to highlight certain data in a certain color code. Like Pass is often decided by the Green color and Fail with Red.
In order to make you understand how to use conditional formatting in Excel I will take the example of painting our cells in desired colors.
Let us try and make our CIERTOs Green and all the FALSOs as Red using Conditional Formatting in Excel.
Step 1: Just go to C1 where our first CIERTO is. Then click on Conditional Formatting then on Manage Rules…
Note: We don’t need a new rule now since we already have an existing rule.
It will open the Conditional Formatting Rules Manager dialog for you.
Step 2: Just click on the Edit Rule… button.
Doing so will take you to the same old Edit Formatting Rule dialog box.
Step 3: Click on Format… button.
Step 4: Navigate to the fourth tab Fill and select any color of your choice. I will go with Green to show that it is a pass.
Once done click on Ok.
The next screen will take you back to the same window but this time you will see green color in the Preview pane like this:
Step 5: Click on Ok.
Step 6: In the next screen Apply and Ok.
See the change?
Awesome right?
Repeat the above steps and make FALSO red.
I have done that! Here’s the final result:
Looks good? Now that you have learnt how to use conditional formatting in Excel time to go experiment!
Check out more cool tutorials on our website.
