There are times in our spread sheet that we need to format the cells or cell range based on certain conditions. These conditions however pertain to the contents of the cells that you are to format. This is when Conditional Formatting is very useful.
For instance, format the cell range G5:G50 if the values in the individual cells is less than 70%. If you are a teacher calculating the students grade in Excel and you want to automatically color the final grade with dark red if they are faling grades, like below 70% (if the passing grade is 70%). This will alert you who are getting the failing grades. In this case, it will be easier to use Conditional Formatting.
How about if the conditions in formatting a certain cell is based on the contents of other cells?
As a demonstration, consider this scenario. Suppose that the possible values in column E are:
- Passed
- Failed
- Incomplete
In cell A1, you have a trigger to warn you that there is an “Incomplete” mark in column E. That is, the condition is, if in column E5:E10, there is a mark of Incomplete, cell A1 shall be formatted with red fill, bold black text. Look at the screen shot of the final results:
To do that, you need to use Conditional Formatting for A1 in this manner:
Step 1: Select A1 to format it conditionally. In the Home tab, under Styles group, click on Conditional Formatting then point to Highlight Cells Rules. Click the bottom command named “More Rules“.
Step 2: In the “New Formatting Rules” dialog box, select the option called “Use a formula to determine which cells to format“.
Step 3: Click the Format button and perform the formatting that you want. Set the color to red then the style to Bold. It should look like this:
Step 4: In the field named “Format values where this formula is true”, enter this formula:
=COUNT(SEARCH(“Incomplete”,$E$5:$E$15))
Step 5: Click OK.
Step 6: Now, cell A1 will be formatted with red fill and bold text whenever “Incomplete” is present in the range E5:E15 as the first screen shot above.
The sample file can be downloaded below.