1. Working with AND / OR
Until now, we have been looking for one condition at a time. If we wanted to look for two conditions in the same range, we previously added multiple rules.
However, with a custom formula, we can use the OR function for multiple conditions within one rule.
2. OR function
Placing the OR function before two or more expressions means that either or both of them must be true for the entire formula to be true. It doesn't need to be limited to two conditions. You can use the OR function with as many conditions as you want, and only one of them needs to be true.
3. Example: enrollment
Let's go back to our previous example of working at a college. This time, you work in the academic department instead of admissions. Your college does not allow students to attend less than 6 credits per term, or part-time, so you need to ensure that the students are enrolled in classes at least part-time.
4. Credits
We need to design a conditional formatting rule that will highlight the credit hours for students that have less than six hours or some other non-numerical value. Here, that other value is NA and it signals that a student has not enrolled in any courses yet.
5. ISTEXT function
Since we want to see if the amount of credits is below 6 OR not a number at all, we will be using the ISTEXT function. This function returns true if it looks at a value that is text and false if it is not text.
6. Finished formula
This is the formula that we will be using in the custom formula input box. Let's break it down into two pieces. Either one or both parts will need to be true to trigger the conditional formatting.
The first part, C2 less than 6, checks to see if the number of credits is less than six. The second part, ISTEXT(C2), checks to see if the value is text and not a number.
7. Less than part-time formatted
After the rule has been applied, you can see that there are three students that need to enroll in additional classes before they begin the upcoming term.
8. AND function
Placing the AND function before two or more expressions tests that all of the expressions are true. You can use the AND function with as many conditions as you want, but all of the expressions must be true for the entire function to return the value of TRUE.
9. Full-time accounting students
Now the accounting dean wants to know how many full-time accounting students enrolled today.
10. Full-time accounting students
This formula will return true if the student is enrolled in the Accounting program and is attempting at least 12 credits. This formula's range is only C2:C15, but notice that it is looking at values outside of this range.
We only wanted the highlighting applied to column C, so that is the only range that we highlighted. Later on in this course, we will look at highlighting an entire row based on the values in one cell.
11. Give it a shot!
We've covered the basics of how to use the custom formula options for conditional formatting in one cell at a time. Now let's practice these concepts with a few exercises.