Let us suppose that you have a list. In this list, you want to identify the cells that contain a certain text. Figure 1 shows the initial setup of our problem:
Figure 1 - Conditionally formatting based on text in a cell
What we plan to do is to identify the products that contain the word “used” (for now). We show you two different ways to get this done.
In the first example, we type in the word directly into the formula. In the second example, we do this by using a validation list from where you can choose what to look for and then the CF is applied.
To begin with, choose the area containing the data (starting from the top-most cell). Add a new rule and choose the option Use a formula to determine which cells to format. In the formula box, you must enter the following formula: =IF(NOT(ISERROR(FIND("used",A1,1))),1,0)=1. Choose the formatting of your liking. Figure 2 shows the step visually:
Figure 2 - Entering the conditional formatting formula
Obviously that, as it is, the conditional formatting is pretty much useless to us. What if we want to format whenever the word “new” comes up? Or when “repaired” comes up? In theses case it does not take a genious to figure out that we’re in serious trouble. It is simply not practical keep changing the value around everytime we need to conditionally format based on a word found in the text.
The solution is to implement a truly functional formula. In this case, we can use a data validation list to choose from (we discuss data validation in details in the second part of this eBooklet).
Here are the steps:
· Type in the word “repaired, used and new” in the range F1:F3;
· Select cell D1 where the data validation will be inserted;
· Open the data validation tool (Data tab à Data tools group à Data Validation splitbutton à Data validation button);
· From the Allow dropdown list, choose List;
· In the Source field, choose the range F1:F3;
· Click OK to finish.
Now, when you click on D1, you will get the validated list as shown in Figure 3:
Figure 3 - Validation list to be used in our conditional formatting
Now, all you need to do is to change the original formula to point to cell D1 as shown in Figure 4:
Figure 4 - Edited formula
If you are wondering above the “=1” at the end, we only added this to make it explicit that the formula is evaluating to 1 (true) so that the conditional formatting can be applied. There is not need to have the “=1” at the end of the formula. It is entirely up to you.
You can now select from the validated list any of the values and see your conditional formatting at work as shown in Figure 5:
Figure 5 - Final step o four conditional formatting with data validation
You may also want to read this article (http://www.excelguru.ca/blog/2009/04/23/making-an-icon-set-show-only-two-conditions/) from my fellow MVP, Ken Puls.