Previously we finished with the “IF” Function, if you have any questions about that, do not hesitate to ask in the comments. In this tutorial, we will start with the use of “And” & “OR” Functions with “IF”. We will also see how to use IF, AND, & OR in Conditional Formatting.
To practice Excel, you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
I have made and downloaded an Excel data file of people having different model cars to practice the AND & OR functions.
Revision of the IF Function
As described in the previous part that an IF function will be written as:
We can also use nested IF functions within one another. Excel will look at the first IF, and if the condition is false will look into the next IF function and so on.
What is the use of AND Function?
As the name is suggesting, in the place of one “logical_condition”, we want to give excel two conditions to check at the same time. When we want Excel to check two conditions at one time and to check that both the conditions are true at the same time, we will use the AND function.
The format of using AND is as follows:
The text written in red is for the AND function. As with all other functions, writing the function will always be proceeded by a parenthesis “(“. In the case of AND, each condition is separated by a comma. You can use as many as 20 or more conditions (please check your version of Excel as to how many conditions can be used within one AND function). When you want to end the conditions for AND, always put a closing parenthesis “)”. So, the rule remains the same for the format of all the functions. Please remember that all the conditions within the function AND must come true to get a value for “True”. If, for example, you have given 4 conditions within AND, and even one condition is false, the AND function will return a False value.
For example, in our data of cars, we will put the following formula:
In simple English, the above formula can be translated as:
Look into Cell E2 (Car model) whether it is a “Ford”, and look into F2 (Car color) whether it is “Yellow”, and look into G2 (Resident country), whether it is “Philippines”. If all these conditions are met, then pick “Yes” as the answer. As we have used the AND function, even one of the conditions is not met, the answer will be picked as “No”. Let’s copy the formula down to all the rows and check.
As we can see, as there is no Ford, with Yellow color, belongs to the country Philippines, that is why all our results are No.
What if we use OR instead?
The format of OR is the same as AND:
As you can see that it is written just like AND, but the difference lies in the interpretation by Excel. When OR is used all those conditions written with OR will be checked, if even one of the conditions is true, the result will be returned as a True value. False will only be returned if none of the conditions within the OR function is true. Let’s try the same formula as above but now with the OR function.
You can see the conditions we use here are the same as AND, only the Function OR is used here.
Let’s copy this formula in our Excel sheet and see the results:
Now you can see at least 3 “Yes” results in the image above. You can see more Yes if you scroll down. Look at the conditions, of all the three conditions we put within OR, if one of the conditions is true, the result is Yes.
That is how you will use AND & OR functions.
Also, keep in mind that these two functions can also be used in combination with other functions and not only IF. We will check them out when we will be doing other useful functions of Excel.
How to use IF, AND, & OR in Conditional formatting?
Do you remember the conditional formatting we have done in the earlier parts (part-2) of this series of Excel training?
You can find this under the “Home” tab, as shown above. Conditional formatting is used to change the color of the text, background, or the fonts and size of the text within a cell, whenever a condition you have provided is met. For more details on conditional formatting, please click here.
If you do remember, we have left the formula part of the conditional formatting, narrating that we will do this when we will be doing the functions. So, as we have now learned the basics, I will show you how you can use IF, AND, & OR in conditional formatting.
Suppose we want to apply conditional formatting to the “Car color” column, so first, we will select the whole column. Easy way to do it to hover your mouse above the Car Color column, in this case over the “E” column. You will notice a downward arrow is formed, click and the whole column will be selected. Now open the conditional formatting by clicking on it, and click “New Rule”:
When you click there a dialogue box will open up. You have to click “Use a formula to determine which cell to format”. Other functions of the conditional formatting we have already done in the Part-2:
In the box “Format values where this formula is true”, you have to write a formula. As we are using the IF function here, we will write a conditional formula to check the car colors. To write within conditional formula you don’t need to write the “IF” function. You just have to write the condition; Excel automatically determines that it is for the IF function.
What will be the format for IF in conditional formatting?
You can see in the above image the format to write an IF condition formula. One thing you must have noticed that there is no IF. Condition just started with an “=” sign and then the condition itself.
As we have said that we want to apply a condition to the E column of car colors, so we used E2. You can ask, why just E2, what will happen to other rows. Now, this is called automation. If you know how to properly use functions, etc., you can automate your work and make everything easy. You just have to write this formula once for E2 and then apply it to the while column of E, Excel will automatically check the relevant row to apply conditional formatting.
You also have noticed that we have used the “$” sign with E, why? Because we want Excel to recognize that we are just interested in the E column, so $ sign will designate it as an “Absolute” reference for only the E column. But we haven’t used any $ sign for the row number. This is because we want the row number to change when Excel is checking the cells downward. So $E2, will become $E3, automatically for row number 3, and so on.
Now click on “Format”, then on “Fill”, and select a color for the background, if the condition is true. We have selected a Green color. Then click OK.
As there is no “White” colored car, so nothing happens after we clicked OK. Now change the condition to “Green”, for example.
Now after pressing OK, we will see this:
Now you can see when the condition is met the background color is changed to green.
Use of AND & OR in conditional formatting
The idea is very similar to when we use these functions in the cell. Suppose we want Excel to make all those cells green, which contains car colors of green, white, or yellow. So will use the OR function here.
Now after clicking OK, you can see that all those cells which are either, green, yellow, or white, will be having background green.
Why haven’t we used AND in here? If we have used AND in here, the formula will look like this:
What does it say?
Oh Yes, you are right! It says that if E2 contains all these words, i.e., Green, Yellow, and White in any cell together then the condition will be True.
I hope you understood.
Wisely use AND and OR to accomplish what you want out of the formula.
This will be all for this part. In the next part, we will continue with other useful and important functions.
Any questions, shoot in the comments.