While working with a big dataset in Excel that contains numerous entries, we need to find texts based on their specific text values, conditions, and duplicate or unique values. Conditional Formatting to color text is one of the convenient ways to highlight a cell’s text to make them identifiable immediately in Excel. In this article, we’ll demonstrate 3 convenient methods to do that properly.
Download Practice Workbook
You are welcome to download the workbook from the link below.
3 Easy Ways to Apply Conditional Formatting to Color Text in Excel
Suppose we have a Product Sales dataset containing text values such as Regions, Cities, Categories, and Products. In the dataset, we see multiple text values, we want to conditionally format text color depending on the specific texts we choose.
Method 1: Applying Conditional Formatting to Color Text Based on Specific Text
We can use three different options available in the conditional formatting feature to conditionally format a dataset based on a specific text.
Case 1: Using Text That Contains Option
Text that contains is an option offered under the Conditional Formatting feature. It conditionally formats texts that contain a specific text. In this case, for texts that contain East, we’ll format their text color.
Steps:
- Firstly, select the cells you want to conditionally format. Then Go to Home Tab > Conditional Formatting (from Styles section) > Highlight Cells Rules > Text that Contains option as in the screenshot given below.
- Then, the Text that Contains dialog box pops up. Type any existing text (i.e., East) in Format cells that contain the text box.
- Also, select Red Text in With section. Obviously, you can choose different options to highlight text.
- Finally, you’ll discover all the texts that contain East get conditionally formatted with selected text color similar to the following image.
Case 2: Using Equal to Option
Similar to Text that Contains, Equal to is another option offered in the Conditional Formatting feature. We can also conditional format text color using the Equal to option.
Before starting Case 2, undo (i.e., Press CTRL+Z) all the outcomes we achieve by performing the steps in Case 1.
Steps:
- Firstly, select a range of cells you want to format the text color.
- After that, Hover over Home Tab > Conditional Formatting (in Styles section) > Highlight Cells Rules > Equal to option as in the screenshot below.
- Then, Repeat Steps of Case 1 of Method 1, you’ll encounter all the specific text (i.e., East) formatted with text color as shown in the picture below.
Case 3: Using New Rule Option
Under Conditional Formatting, New Rule is an option to conditionally format texts with color. We have to select a rule type to apply conditional formatting in selected cells.
Before commencing Case 3, undo (i.e., Press CTRL+Z) all the outcomes achieved in Case 2.
Steps:
- Firstly, select a range of cells.
- Afterward, Go to Home Tab > Conditional Formatting (in Styles section) > New Rule.
- Then, the New Formatting Rule command window appears.
- Select Format only cells that contain as Select a Rule Type.
- Also, choose Specific Text and containing under Format only cells with drop-down option box.
- After that, type the specific text (i.e., East).
- Later on, click on Format.
- Further, in the Format Cells window select Font color and press OK.
- Again, you will return to the New Formatting Rule box. just click on OK.
- Finally, the execution of all the steps returns a result similar to the image below.
Read More: Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)
Method 2: Using Custom Rule to Conditional Format Text Color
We can apply a custom rule to Conditional Format texts with color.
- Firstly, Select a range of cells then move to the Home tab > Conditional Formatting (in the Styles section) > New Rule.
- Simultaneously, you will see a New Formatting Rule window appear.
- In the window, choose Use a formula to determine which cells to format as Select a Rule Type.
- Afterward, type the below formula in the Format values where this formula is true box.
=$B5="East"
- Here, the formula formats all the rows that contain “East” in Region Column (i.e., Column B)
- Later on, click on Format.
- Moreover, follow the steps in Method 1: Case 3 to set the font color for highlighting.
- Finally, you’ll get a similar result depicted in the image below.
Read More: How to Change Row Color Based on Text Value in Cell in Excel
Similar Readings
- How to Use Conditional Formatting Based on VLOOKUP in Excel
- Highlight Highest Value in Excel (3 Quick Ways)
- How to Highlight Lowest Value in Excel (11 Ways)
- Use Conditional Formatting in Excel Based on Dates
- Excel Conditional Formatting Dates Older than Today (3 Simple Ways)
Method 3: Using Duplicate or Unique Values to Conditional Format Text Color
To extend the conditional formatting, we can select a different rule type in the New Rule window to conditionally format text color. We can choose only duplicate or unique values.
Case 1: Duplicate Values
- Primarily, Repeat the initial steps of Method 2.
- Then, select Format only Unique or Duplicate Values as Select a Rule Type and Choose duplicate under Format all box.
- Also, set the Format as in steps of Method 1 Case 3.
- Afterward, click OK.
- Finally, all the duplicate values get formatted with the selected text color.
Case 2: Unique Values
Steps:
- Primarily, follow the instructions derived in the initial steps of Case 1 in Method 3. Just Choose Unique in Format all box.
- Then, set the format as in steps of Method 1 Case 3.
- Lastly, click OK.
- Finally, the execution of these steps results in formatting all the unique cells in the range.
Read More: How to Use Conditional Formatting in Excel [Ultimate Guide]
Conclusion
In this article, we conditionally format cells using text color. To do so, we use multiple options offered in the Conditional Formatting feature such as Text that Contains, Equal to, New Rule types, and Custom Rule to color text in Excel. Hope you find the above-mentioned methods handy to work with. Comment, if you have further queries or have something to add. Moreover, follow our website ExcelDemy, a one-stop Excel solution provider, to explore more.
Related Articles
- Excel Conditional Formatting Formula
- Conditional Formatting Dates
- Excel Alternating Row Color with Conditional Formatting [Video]
- How to Make Negative Numbers Red in Excel (4 Easy Ways)
- How to Compare Two Lists and Return Differences in Excel