While working with a big dataset that contains numerous entries, we need to find texts based on their specific text values, conditions, and duplicate or unique values. Conditional Formatting text color is one of the convenient ways to highlight cells text to make them identifiable immediately.
Suppose we have a Product Sales dataset containing text values such as Regions, Cities, Categories, and Products.
Dataset for Download
You are welcome to download the workbook from the link below
3 Easy Ways to Conditional Formatting Text Color in Excel
Method 1: Conditional Formatting Text Color Based on Specific Text
In the dataset, we see multiple text values, we want to conditionally format text color depending on specific texts we choose. 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.
Step 1: Select the cells you want to conditionally format. Then Go to Home Tab > Select Conditional Formatting (from Styles section) > Select Highlight Cells Rules (from the option menu) > Choose Text that Contains option.
Select Custom Format from the with drop-down options box.
Step 4: After selecting font color, you can see the Preview of texts depicting how they will appear after applying conditional formatting.
Step 5: Executing Step 4 takes you to the Text that Contains dialog box.
In the end, you’ll discover all the texts that contain East get conditionally formatted with selected text color similar to the following image.
For keeping steps lucid and avoiding confusion we conditionally format the dataset using only East text. You can use any other text you desire.
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.
Step 1: Select a range of cells you want to format the text color. After that Hover to Home Tab > Select Conditional Formatting (in Styles section) > Select Highlight Cells Rules (from the option menu) > Choose Equal to option.
Step 2: Repeat Steps 2 to 5 of Case 1 of Method 1, you’ll encounter all the specific text (i.e., East) are 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 to Case 3, undo (i.e., Press CTRL+Z) all the outcomes achieved in Case 2.
Step 1: Select a range of cells. Afterward, Go to Home Tab > Select Conditional Formatting (in Styles section) > Select New Rule (from the options).
Step 2: The New Formatting Rule command window appears.
Select Format only cells that contain as Select a Rule Type.
Choose Specific Text and containing under Format only cells with drop-down option box.
Type the specific text (i.e., East).
Click on Format.
Step 3: Again, Repeat Steps 3 and 4 of Case 1 in Method 1 to select the font color in the Format Cells window.
Returning to the New Formatting Rule window, click OK.
Execution of all the steps returns a result similar to the image below.
Method 2: Using Custom Rule to Conditional Format Text Color
We can apply a custom rule to Conditional Format texts with color.
Step 2: New Formatting Rule window appears.
In the window,
Choose Use a formula to determine which cells to format as Select a Rule Type.
Type the below formula in Format values where this formula is true box.
Here, the formula formats all the rows that contain “East” in Region Column (i.e., Column B)
Click on Format.
Step 3: Once again, Repeat Steps 3 and 4 of Case 1 in Method 1 to choose font color in the Format Cells window.
Following Steps 3 and 4 of Case 1 in Method 1 returns you to the New Formatting Rule window.
In the end, you’ll get a result like depicted in the image below.
- 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
Step 1: Repeat Steps 1 to 3 of Method 2.
Just Select Format only Unique or Duplicate Values as Select a Rule Type and Choose duplicate under Format all box in Step 2 of Method 2.
All the duplicate values get formatted with the selected text color.
Case 2: Unique Values
Step 1: Follow the instructions derived in Step 1 of Case 1 in Method 3. Just Choose Unique in Format all box.
Execution of Step 1 results in formatting all the unique cells in the range.
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. Hope you find above mentioned methods handy to work with. Comment, if you have further queries or have something to add.