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

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


Download Practice Workbook

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


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

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 color based on their names, regions, or salaries. In this section, we will demonstrate 3 different ways to do that.

Change Row Color Based on a Text Value For Single Cell


1. Change Row Color Based on a Text Value

You can change some specific row color based on a text value. Using conditional formatting will make your job a lot easier. You can change 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 Row Color Based on a Text Value For Single Cell

Step 1:

  • Select the entire dataset. In your Home Tab, go to Conditional Formatting in Style Group. Click on it to open available options and from them click on New Rule.

Home → Conditional Formatting → New Rule

Change Row Color Based on a Text Value For Single Cell

  • A new window opens. Select Use a Formula to Determine the Cells to Format to continue.

 For Single Cell

Step 2:

  • In the formula section, insert this formula.
=$C4="George"
  • This formula will compare the dataset cells with the name George. When the value will match, it will color the row.

For Single Cell

Step 3:

  • We need to format the matched cells. The format section will help you out. We have chosen the color of the text Automatic. The fill cells option will help you to color the rows with a specific color. Choose any color you like to go.

Change Row Color Based on a Text Value For Single Cell

  • Now that we have completed all the actions, click OK to get the result.

Change Row Color Based on a Text Value For Single Cell

  • Our row colors are changed based on a text value in a cell.

Change Row Color Based on a Text Value For Single Cell


      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

Step 1:

  • Go to the New Formatting Window following these steps.

Home → Conditional Formatting → New Rule

  • Select Use a Formula to Determine the Cells to Format.
  • Write down the formula to specify the cells that contain the Asia The Formula is,
=$D4="Asia"
  • Select the color format for your matched cells. Click OK to continue

Change Row Color Based on a Text Value For Multiple Cell

  • The conditional formatting feature successfully colors the rows.

Change Row Color Based on a Text Value For Multiple Cell

Step 2:

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

Home → Conditional Formatting → Manage Rules

Change Row Color Based on a Text Value For Multiple Cell

  • The Conditional Formatting Rules Manager window appears. Click New Rule to add another one.

Change Row Color Based on a Text Value For Multiple Cell

Step 3:

  • Set the formula for the second condition. Write down the formula in the formula box.
=$E4="A"
  • Set the format and you are good to go.

Change Row Color Based on a Text Value For Multiple Cell

  • Finally, click OK to change the row color based on multiple conditions.

Change Row Color Based on a Text Value For Multiple Cell

  • The result is here.

Change Row Color Based on a Text Value For Multiple Cell


Similar Readings:


2. Change Row Color Based on a 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

Step 1:

  • Insert the formula in the formula box of the New Formatting Rule
=$F4>$H$4
  • Where $H$4 is the conditional value (40,000$).
  • Specify the formatting and click OK to continue.

Change Row Color

  • Our job here is done.

Change Row Color


3. Apply Formula to Change Row Color Based on a 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. Use the OR Function

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

Use the OR Function

Step 1:

  • Write down the OR The Formula is,
=OR($C4="George",$D4="Asia")
  • The OR formula will compare the cell values with George and Asia and then it will color the rows that matched the conditions.

Use the OR Function

Step 2:

  • Select a formatting style according to your preferences.
  • Click OK and your job is done.

      3.2. Insert the 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

Step 1:

  • Following the same procedures discussed above, go to the New Formatting Rule window and apply the AND The formula is,
=AND($D4="Africa",$E4="B")
  • Set the formatting styles and click OK to format the cells.

Insert the AND Function

  • The rows have changed their colors according to the conditions.

Insert the AND Function


Things to Remember

👉 You can clear the rules once the formatting is applied

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


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. You can also check out our other articles related to the Excel tasks!


Similar Articles to Explore

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo