Primer on custom formulas

1. Primer on custom formulas

Now that we have used many of the preset options available for conditional formatting, we're going to move into writing our own formulas to determine what is formatted.

2. IF function

The custom formula option operates in a similar fashion to an IF statement that you would type into a cell. If it is TRUE, the formatting will be triggered, and if it is FALSE, no formatting will be applied. The IF function checks a cell for a condition and, if it's true, returns one value and if it's not true, returns another. Conditional formatting is similar but it doesn't have the "return another" part. It either formats or doesn't format.

3. Example: Admissions department

Let's explore the following example. You are in the admissions department at a local community college. You are especially interested in the nursing program as the school is trying to build the program to meet a higher demand in the area. We'll start by using a basic application of a custom formula for conditional formatting, which is available in the preset menus. However, we'll be writing it ourselves to learn how.

4. Custom formula is

Go to the conditional formatting menu and scroll through the preset options until you get to "Custom formula is" at the bottom.

5. Custom formula box

After you click on Custom formula is, you will see this menu. You will need to fill in the input box, and specify the range as B2:B15 in the Apply to range box. Using the custom formula box gives us the flexibility we'll need to apply conditional formatting in unique ways. We'll be using this box to look for a certain string of characters, but you can also look for other types of data, such as numbers, dates, or percentages, or you can get creative and combine a variety of different functions.

6. Red formula box

Getting a red outline on the formula box indicates that you do not have a valid function in the box yet. If the red outline goes away, it doesn't mean the function will work the way you want, but it does mean that it's a valid working function.

7. Top down

This is our first custom formula and we can already see some oddities in action. We are highlighting an entire column, but we have to write the formula as if it is evaluating only the first cell in the range and it will be adjusted to B3, B4, and so on, automatically. So you can't just write equals "Nursing" in the Custom formula is input box, you need to write equals B2 equals "Nursing", just like you would when building a formula in a regular cell.

8. Let's try it out!

We have only scratched the surface of what you can do with the custom formula option. Let's practice its application a few times before we get more advanced.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.