Learn how to quickly highlight the full row according to a single cell's value in Excel. Tips and formula examples for numeric and text values.

The previous week we covered how to alter the background color of a cell depending on its value. In this article, you'll discover how to emphasize complete rows in Excel based on the value of one cell, along with some tips and formula examples that work with numeric and text cell values.

Imagine you have a table listing your company's orders like this:

You might wish to shade the rows in varying colors depending on the value in the Qty. column to easily spot the most critical orders at a glance. This can be accomplished effortlessly using Excel Conditional Formatting.

1. Begin by selecting the cells whose background color you intend to change.

2. Generate a new formatting rule by clicking Conditional Formatting > New Rule… on the Home tab.

3. Within the "New Formatting Rule" dialog box that appears, opt for the option "Use a formula to determine which cells to format" and input the following formula in the "Format values where this formula is true" field to highlight orders with Qty. larger than 4: =$C2>4

   

   Naturally, you can utilize the less than (

   =$C2

   =$C2=4

   Additionally, take note of the dollar sign $ preceding the cell's address - it is essential to maintain the column letter consistent when the formula gets replicated across the row. It is precisely this that achieves the effect of applying formatting to the entire row based on the value in a given cell.

4. Press the "Format…" button and navigate to the Fill tab to select the background color. If the default colors are insufficient, click the "More Colors…" button to pick a preferred color, then click OK twice.

   You can also apply any other formatting options, such as font color or cell borders, on the other tabs of the Format Cells dialog.

5. The preview of your formatting rule will appear similar to this:

6. If this aligns with your preference and you're satisfied with the color, click OK to observe your new formatting in action. Now, if the value in the Qty. column exceeds 4, all rows in your Excel table will turn blue.

   

As you can see, altering the row's color based on a number in a single cell is quite straightforward in Excel. Subsequently, you will encounter more formula examples and a couple of tips for more intricate scenarios.

How to apply multiple rules with the priority you desire

In the prior example, you might prefer to shade rows with differing values in the Qty. column in distinct colors. For instance, you can add a rule to tint rows with a quantity of 10 or higher. In this scenario, employ this formula:

=$C2>9

Once your second formatting rule is established, arrange the rules' priority so both of your rules operate effectively.

1. On the Home tab, within the Styles group, click Conditional Formatting > Manage Rules… .

2. Choose "This worksheet" in the "Show formatting rules for" field. To control the rules that apply solely to your current selection, choose "Current Selection".

3. Select the formatting rule you wish to apply first and move it to the top of the list using the arrows. The outcome should resemble this:

   Click the OK button and the corresponding rows will promptly adjust their background color based on the cell values you specified in both formulas.

   

How to modify a row color based on a text value in a cell

In our sample table, to simplify order tracking, you can shade rows based on the values in the Delivery column, ensuring:

• Rows with an order marked as "Due in X Days" turn orange;
• Items labeled "Delivered" have their entire row colored green;
• Orders flagged as "Past Due" shift to red.

Naturally, the row color will update if the order status changes.

Although the formula from our initial example could handle "Delivered" and "Past Due" (=$E2="Delivered" and =$E2="Past Due"), the task seems trickier for "Due in…" orders. As observed, different orders are due in 1, 3, 5, or more days, and the aforementioned formula won't work since it targets exact matches.

In this case, it's better to use the SEARCH function that works for partial matches as well:

=SEARCH("Due in", $E2)>0

In this formula, E2 is the address of the cell you want to base your formatting on, the dollar sign ($) locks the column coordinate, and >0 indicates that formatting will be applied if the specified text ("Due in" in our case) is found anywhere in the cell.

Create three such rules following the steps from the first example, and you'll end up with the following table as the result:

Highlight row if cell begins with specific text

Using >0 in the above formula implies that the row will be colored regardless of where the specified text is positioned in the key cell. For instance, the Delivery column (F) may contain the text "Urgent, Due in 6 Hours", and this row will still be colored.

To change the row color when the key cell begins with a particular value, use =1 in the formula, for example:

=SEARCH("Due in", $E2)=1

In this case, the row will only be highlighted if the specified text is found at the beginning of the cell.

For this conditional formatting rule to function correctly, ensure there are no leading spaces in the key column; otherwise, you might struggle to understand why the formula doesn't work. You can use this free tool to detect and eliminate leading and trailing spaces in your worksheets - Trim Spaces tool for Excel.

How to modify a cell's color based on a value in another cell

In reality, this is merely a variation of the row color-changing scenario. Instead of the entire table, you select a column or range where you want to highlight rows and use the formulas described earlier.

For instance, we could create three such rules to shade only the cells in the "Order number" column based on another cell's value (values in the Delivery column).

Highlight rows based on several conditions

If you wish to shade rows in the same color based on several values, rather than creating multiple formatting rules, you can use the OR or AND functions to establish several conditions.

For instance, we can color orders due in 1 and 3 days in a reddish hue, and those due in 5 and 7 days in yellow. The formulas are as follows:

=OR($F2="Due in 1 Days", $F2="Due in 3 Days")

=OR($F2="Due in 5 Days", $F2="Due in 7 Days")

And you can use the AND function, for instance, to change the background color

The above is the detailed content of Excel: Change the row color based on cell value. For more information, please follow other related articles on the PHP Chinese website!