How to Apply Conditional Formatting to Color Text in Excel

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.

preview of Conditional Formatting Text Color


Download the Practice Workbook


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. We have multiple text values and want to conditionally format text color depending on the specific text. dataset


Method 1 – Applying Conditional Formatting to Color Text Based on Specific Text


Case 1 – Using the Text that Contains Option

Steps:

  • Select the cells you want to conditionally format.
  • Go to the Home Tab and select Conditional Formatting (from Styles section).
  • Choose Highlight Cells Rules and select the Text that Contains option.

Specific text-Excel Conditional Formatting Text Color

  • The Text that Contains dialog box pops up. Type any existing text (i.e., East) in Format cells that contain the text box.
  • Select Red Text in With section. You can choose different options to highlight text.

Text that contains window

  • You’ll discover all the strings that contain East get conditionally formatted with the selected text color similar to the following image.

Result-Excel Conditional Formatting Text Color

Note: We conditionally formatted the dataset using only the East text. You can use any other text you desire.

Case 2 – Using the Equal to Option

Steps:

  • Select a range of cells you want to format the text color.
  • In the Home tab, go to Conditional Formatting (in the Styles section).
  • Go to Highlight Cells Rules and select the Equal to option.

Equal to option-Excel Conditional Formatting Text Color

  • 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.

result of Conditional Formatting Text Color using equal to option


Case 3 – Using the New Rule Option

Steps:

  • Select a range of cells.
  • Go to Home and select Conditional Formatting (in Styles section).
  • Choose New Rule.

New rule option

  • The New Formatting Rule command window appears.
  • Select Format only cells that contain as Select a Rule Type.
  • Choose Specific Text and containing under the Format only cells with drop-down option box.
  • Type the specific text (i.e., East).
  • Click on Format.

setting up new formatting rule window

  • In the Format Cells windown select Font color and press OK.

setting font color

  • You will return to the New Formatting Rule box. Click on OK.

closing new formatting rule window

  • This returns a result similar to the image below.

Result-Excel Conditional Formatting Text Color using new rule option

Read More: Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)


Method 2 – Using a Custom Rule to Conditionally Format Text Color

Steps:

  • Select a range of cells then move to the Home tab and choose Conditional Formatting (in the Styles section).
  • Select New Rule.

custom rule-Excel Conditional Formatting Text Color

  • You will see a New Formatting Rule window appear.
  • Choose Use a formula to determine which cells to format as Select a Rule Type.
  • Use the below formula in the Format values where this formula is true box.
=$B5="East"
  • The formula formats all the rows that contain “East” in Region Column (i.e., Column B)
  • Click on Format.

Selection in New formatting rule window

  • Follow the steps in Method 1, Case 3 to set the font color for highlighting.
  • You’ll get a similar result depicted in the image below.

changed font color of whole row if cell contain specific text

Read More: How to Change Row Color Based on Text Value in Cell in Excel


Similar Readings


Method 3 – Using Duplicate or Unique Values to Conditionally Format Text Color

Case 1 – Duplicate Values

Steps:

  • Repeat the initial steps of Method 2.
  • Select Format only Unique or Duplicate Values as Select a Rule Type and choose duplicate under Format all box.
  • Set the Format as in Method 1 Case 3.
  • Click OK.

New formatting rule window setup

  • All the duplicate values get formatted with the selected text color.

Duplicate result-Excel Conditional Formatting Text Color


Case 2 – Unique Values

Steps:

  • Follow the instructions derived in the initial steps of Case 1 in Method 3.
  • Choose Unique in Format all box.
  • Set the format as in steps of Method 1 Case 3.
  • Click OK.

Selections in New formatting rule window for unique value

  • This results in formatting all the unique cells in the range.

result of Conditional Formatting Text Color for unique value

Read More: How to Use Conditional Formatting in Excel [Ultimate Guide]


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo