Maruf Islam
In datasets, often we have multiple text values to compare with in order to find a match. In this article, we discuss conditional formatting of multiple text values using several functions such as AND, OR, ISNUMBER, SEARCH, SUM, and SUMIF. We use some of the mentioned functions together to do the job.
Suppose, we have a dataset of Product Sale, where we have text value columns named Region, City, Category, and Product. We want to conditional format the dataset depending on the multiple text values of these text value columns.
Dataset for Download
4 Easy Ways to Conditional Formatting Multiple Text Values in Excel
Method 1: Using AND Function
In the dataset, we have four text columns to which we want to highlight the rows which have “East” as Region and “Bars” as Category.
Step 1: Select the entire range ($B$4:$G$21) you want to format. After that, go to Home Tab > Select Conditional Formatting (in Styles section) > Select New Rule (from the drop-down options).
Step 2: New Formatting Rule window pops up. In the window, select Use a formula to determine which cell to format (from Select a Rule Type dialog box).
Then Paste the following formula in the Edit the Rule Description box.
=AND($B4="East",$D4="Bars")The syntax of the AND function is
AND(logical1,[logical2]...)Inside the formula,
$B4=”East”; is the logical1 argument.
$D4=”Bars”; is the logical2 argument.
And the formula formats the rows for which these two arguments are True.
Step 3: Click on Format. The Format Cells window opens up. From the Format Cells window, Choose any Fill Color from the Fill section. Then click OK.
Step 4: You’ll return to the New Formatting Rule dialog box. Again, Click OK.
All the matching rows in the dataset get formatted with the fill color we selected.
If you look closely, you can see the formula conditional formats only the rows that have both “East” as Region and “Bars” as Category.
Read more: How to Change a Row Color Based on a Text Value in a Cell in Excel
Method 2: Using OR Function
Now, we want to take the conditional formatting a step further. In this case, we want rows to be formatted which have any of the entries such as “East”, “Boston”, “Crackers”, and “Whole Wheat” in the text value columns. We can use the OR function to achieve the desired result.
Step 1: Repeat the Steps 1 to 4 from Method 1. Just Replace the inserting formula in Edit the Rule Description with the following formula.
=OR($B4="East",$C4="Boston",$D4="Crackers",$E4="Whole Wheat")Here, we have checked whether B4, C4, D4, and E4 cells are equal to “East”, “Boston”, “Crackers”, and “Whole Wheat” respectively. OR will trigger the action if any of the conditions match.
Step 2: Click OK. You’ll see the formula formats all the rows that contain any of the text we mentioned earlier.
You can add or remove any text conditions as per your need to format the dataset.
Similar Readings:
- Excel Conditional Formatting Based on Multiple Values of Another Cell
- How to Apply Conditional Formatting to Multiple Rows (5 Ways)
- Excel conditional formatting based on another cell text [5 ways]
- Excel Conditional Formatting If a Cell Contains Any Text
Method 3: Using OR ISNUMBER and SEARCH Functions
What happens when we want to conditional format the dataset containing specific multiple Products? For Example, we have multiple products such as Chocolate Chip, Brian, and Whole Wheat. In this case, we want to highlight all the rows that contain these certain Products.
For better representation, we delete the Region and City columns to discuss this method especially.
Step 1: Insert the names of the Products in a new column (i.e., Containing Multiple Texts).
Step 2: Repeat Steps 1 to 4 from Method 1, for this particular case, Replace the formula in Format values where the formula is true dialog box with the following formula.
=OR(ISNUMBER(SEARCH($G$4:$G$7,$C4)))Inside the formula,
The SEARCH function matches texts existing in the Range $G$4:$G$7 to the lookup Range starting cell $C4. Then the ISNUMBER function returns the values as True or False. In the end, the OR function matches alternating any of the text within the find_value Range (i.e.,$G$4:$G$7).
Step 3: Click OK. The inserted formula formats all the rows in the dataset matching the texts with the Containing Multiple Texts columns.
Make sure you select the particular Range ($G$4:$G$7) as find_text inside the SEARCH function, any mismatch results in formatting the whole dataset or none at all.
Read more: How to Do Conditional Formatting for Multiple Conditions
Method 4: Using SUM and COUNTIF Functions
Now, we want to shorten Method 3 using an assigned name for all the Product Names and assign it as a criterion. In order to do so, we use a combination of the SUM and COUNTIF functions.
Step 1: Assign a name (i.e., Text) to all the Products to the Containing Multiple Texts columns.
Step 2: Repeat Steps 1 to 4 from Method 1, in this case just Replace the formula with the formula below.
=SUM(COUNTIF($C4,"*"&Text&"*"))In the formula,
The COUNTIF matches only one criterion (i.e., Chocolate Chip) to the Range starting from the cell $C4. Combining the COUNTIF function with the SUM function enables it to match all the criteria (i.e., Text) to the Range.
Step 3: Click OK. The formula formats all the rows containing texts that match with the assigned name Texts.
Conclusion
In this article, we use various functions and their combination to conditional format multiple text values. We use AND, and OR functions as well as two combined functions. One combined function is OR, ISNUMBER, and SEARCH. The other ones are SUM and COUNTIF. The AND function can match two random texts to format any dataset. On the other hand, the OR function matches any of the declared texts in its formula. The combined functions match as many texts you assign to and format them accordingly. Hope you find these above methods lucid enough to work with. Comment, if you need further clarifications or have something to add.
Hi i have a problem if containing multiple texts columns have empty cells how to exclude them.
You can exclude or ignore the blanks using 2 simple tricks.
1. Use an additional formula in conditional formatting. Go to conditional formatting > New Rule option > select Format Only Cell that contains rule type > Select Blanks from edit rule description > Keep Cell format as no cell format. Click OK.
2. Go to Conditional formatting > New Rule option> Select Use a formula to determine which cell to format rule type > type “=ISBLANK(Cell Reference)=TRUE” in the Edit the Rule description box > Keep Cell format as no cell format. Click OK.
Hope these tricks work for you.