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

Get FREE Advanced Excel Exercises with Solutions!

While dealing with a huge database you may need to change the row color of some specific cells based on a text value to identify them quickly. Excel has many features to do that. Conditional Formatting is one of them. It is a fascinating way to reduce your workload and it can improve your efficiency. Today in this article, we will demonstrate how to change a row color based on a text value in a cell in Excel.

Preview of change a row color based on a text value in a cell in excel


Change Row Color Based on Text Value in Cell in Excel: 3 Suitable Ways

Think of a situation where you are given the ID, Name, Region, Rank, and Salary of some Sales Representatives. Now you have to change some row colors based on their names, regions, or salaries. In this section, we will demonstrate 3 different ways to do that.

dataset


1. Changing Row Color Based on Text Value

You can change some specific row’s color based on a text value. Using conditional formatting will make your job a lot easier. You can change the row color for either a single condition or multiple conditions. We will discuss both of them in this method.

1.1 For Single Cell Criteria

Let’s say we have to color the rows that have George’s name in them. To do it, create another table anywhere in the worksheet and insert the name in it. Then follow the steps below.

change a row color based on a text value in a cell in excel

Steps:

  • Firstly, select the entire dataset. In your Home tab, go to Conditional Formatting in Style Group.
  • Then, click on it to open available options and from them click on New Rule.

using conditional formatting

  • Consecutively, the New Formatting Rule dialog box opens. Select Use a Formula to determine which Cells to format to continue.

new formatting rule dialog box

  • Further, in the formula section, insert this formula.
=$C5="George"
  • This formula will compare the dataset cells with the name George.
  • When the value will match, it will color the row.
  • For that, click on the Format option.

fill up new formatting dialog box

  • Moreover, we need to format the matched cells. The format section will help you out. We have chosen the color of the text as Automatic. The fill cells option will help you to color the rows with a specific color. In this case, we chose the Yellow color.

selecting color for conditional formatting

  • Afterward, click OK on the next window to get the result.

preview in new formatting dialog box

  • Finally, our row colors are changed based on a text value in a cell.

changed row color based on text


1.2 For Multiple Cell Criteria

Following the same instructions as discussed in the previous method, we can color rows based on multiple conditions. Consider a case where you have to color the rows that have Asia and rank A in them. Follow the steps below to learn this technique.

Change Row Color Based on a Text Value For Multiple Cell

Steps:

  • Firstly, go to the New Formatting Rule dialog box following the steps we did in Method 1.1.
  • Then, select Use a formula to determine which cells to format.
  • Afterward, write down the formula to specify the cells that contain Asia. The Formula is,
=$D5="Asia"
  • Lastly, select the color format for your matched cells. Click OK to continue

setting new formatting dialog box

  • Finally, the conditional formatting feature successfully colors the rows.

Changed Row Color Based on a Text Value For Single Cell

  • Now, we need to color the rows that contain rank A in them. For that, go to

Home → Conditional Formatting → Manage Rules

adding new conditional formatting

  • After that, the Conditional Formatting Rules Manager window appears. Click New Rule to add another one.

adding new rule

  • Again, set the formula for the second condition. Write down the formula in the formula box.
=$E5="A"
  • Further, set the format and you are good to go.
  • Finally, click OK to change the row color based on multiple conditions.

new formatting rule box

  • Again, press OK in the next window.

final set of rules

  • Thus, the result is here.

output of changing row color based on multiple cells

Read More: How to Change Cell Color Based on Date Using Excel Formula


2. Altering Row Color Based on Number Value in Excel

We can change row color based on numbers too. In this given situation, we have to change the row colors with a salary of less than 40,000$.

Change Row Color Based on a Number Value in Excel

Steps:

  • Primarily, Insert the formula in the formula box of the New Formatting Rule which can be opened as in Method 1.1.
=$F5<$H$5
  • Afterward, specify the formatting and click OK to continue.

settinf new formattinf rule box

  • Finally, our job here is done.

Changed Row Color Based on a Number Value in Excel

Read More: How to Highlight Row Using Conditional Formatting


3. Applying Formula to Change Row Color Based on Text Value

You can apply functions to change row color based on a text value. The OR and the AND functions can help you in this situation. Let’s learn those methods.

3.1. Using OR Function

We want to color George or Asia containing rows using the OR function. Insert those texts into your reference table.

Apply Formula to Change Row Color Based on a Text Value

Steps:

  • So, write down the OR Formula in the New Formatting Rule window which can be opened as in Method 1.1.
=OR($C5="George",$D5="Asia")
  • Here, the OR formula will compare the cell values with George and Asia, and then it will color the rows that matched the conditions.
  • Again, select a formatting style according to your preferences.

settinf new formatting rule box

  • Lastly, click OK and your job is done.

Apply OR Formula to Change Row Color Based on a Text Value


3.2. Inserting AND Function

The AND function also helps you to change row colors. Here we will apply a new condition. We will change row colors that have both Africa region and B rank in them.

Insert the AND Function to change row color

Steps:

  • Primarily, following the same procedures discussed in Method1.1, go to the New Formatting Rule window and apply the AND formula which is,
=AND($D4="Africa",$E4="B")
  • Then, set the formatting styles and click OK to format the cells.

using AND functiuon in formula of new formattinf rule box

  • Finally, the rows have changed their colors according to the conditions.

changed row color using AND function

Read More: Conditional Formatting on Multiple Rows Independently in Excel


Things to Remember

👉 You can clear the rules once the formatting is applied

👉 Use the Absolute Cell references ($) to block the cells.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

We have discussed three suitable ways to change a row color based on a text value in a cell in Excel. You are most welcome to comment if you have any questions or queries.


Similar Articles to Explore

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo