Asikul Himel
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.
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 Text Value in 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 colors based on their names, regions, or salaries. In this section, we will demonstrate 3 different ways to do that.
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.
- 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.
- Consecutively, the New Formatting Rule dialog box opens. Select Use a Formula to determine which Cells to format to continue.
- 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.
- 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.
- Afterward, click OK on the next window to get the result.
- Finally, our row colors are changed based on a text value in a 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.
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
- Finally, the conditional formatting feature successfully colors the rows.
- Now, we need to color the rows that contain rank A in them. For that, go to
Home → Conditional Formatting → Manage Rules
- After that, the Conditional Formatting Rules Manager window appears. Click New Rule to add another one.
- 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.
- Again, press OK in the next window.
- Thus, the result is here.
Similar Readings:
- Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)
- How to Highlight Row Using Conditional Formatting (9 Methods)
- Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)
- How to Do Conditional Formatting for Multiple Conditions (8 Ways)
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$.
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.
- Finally, our job here is done.
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.
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.
- Lastly, click OK and your job is done.
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.
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.
- Finally, the rows have changed their colors according to the conditions.
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 Excel tasks on ExcelDemy!
