How To Make Another Color The Background

Learn how to quickly alter the colour of the entire row based on a single cell'south value in your Excel worksheets. Tips and formula examples for number and text values.

Concluding week we discussed how to change the background color of a cell based on its value. In this article y'all will acquire how to highlight entire rows in Excel based on a value of one cell, and besides find a few tips and formula examples that volition work with numerical and text cell values.

Change a row color based on a number in a unmarried cell
Apply several formatting rules with the specified priority
Change row'southward color based on text value in a cell
Highlight row if cell starts with specific text
Alter a cell'south color based on another prison cell value
Change the color of a row based on several conditions

How to change a row color based on a number in a single cell

Say, y'all have a table of your company orders like this: Source data - a table of the company's orders

You may desire to shade the rows in different colors based on the cell value in the Qty. cavalcade to see the nigh important orders at a glance. This can exist easily done using Excel Provisional Formatting.

Start with selecting the cells the groundwork color of which you want to change.
Create a new formatting rule by clicking Conditional Formatting > New Rule… on the Home tab.
In the "New Formatting Rule" dialog window that opens, choose the selection "Use a formula to determine which cells to format" and enter the following formula in the "Format values where this formula is truthful" field to highlight orders with Qty. larger than 4:
=$C2>iv

And naturally, y'all can use the less than (<) and equal to (=) operators to observe and highlight rows that have Qty. smaller than 4 or equal to 4:

=$C2<four

=$C2=4

Also, pay attention to the dollar sign $ earlier the cell's address - information technology is needed to proceed the cavalcade letter the same when the formula gets copied beyond the row. Really, information technology is what does the fox and applies formatting to the whole row based on a value in a given prison cell.
Click the "Format…" button and switch to Fill tab to choose the background colour. If the default colors practice not suffice, click the "More Colors…" button to choice the 1 to your liking, so click OK twice.
You lot can also use any other formatting options, such as the font color or cells border on the other tabs of the Format Cells dialog.
The preview of your formatting dominion will look like to this:
If this is how you lot wanted it and you are happy with the color, click OK to encounter your new formatting in effect.
Now, if the value in the Qty. column is greater than four, the entire rows in your Excel tabular array will turn blue.

As you tin can see, changing the row'southward color based on a number in a single cell is pretty piece of cake in Excel. Further on, you volition find more than formula examples and a couple of tips for more than complex scenarios.

How to apply several rules with the priority y'all need

In the previous example, you lot may desire to highlight the rows with different values in the Qty. column in dissimilar colors. For instance, y'all tin can add a rule to shade the rows with quantity 10 or greater. In this case, apply this formula:

=$C2>9

After your second formatting rule is created, set the rules priority so that both of your rules volition work.

On the Home tab, in the Styles group, click Provisional Formatting > Manage Rules… .
Cull "This worksheet" in the "Show formatting rules for" field. If you want to manage the rules that apply to your current selection simply, cull "Current Option".
Select the formatting dominion you desire to be applied showtime and move it to the superlative of the list using the arrows. The result should resemble this:
Click the OK button and the corresponding rows will immediately change their background colour based on the cell values that you specified in both formulas.

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

In our sample table, to brand follow-upward on orders easier, y'all can shade the rows based on the values in the Delivery cavalcade, so that:

If an order is "Due in X Days", the background color of such rows volition turn orange;
If an item is "Delivered", the entire row will be colored in greenish;
If an order is "Past Due", the row will turn red.

Naturally, the row color will modify if the lodge status gets updated.

While the formula from our first instance could piece of work for "Delivered" and "By Due"(=$E2="Delivered" and =$E2="Past Due"), the job sounds a scrap trickier for "Due in…" orders. Every bit you come across, dissimilar orders are due in i, 3, 5 or more days and the in a higher place formula won't work considering it is purposed for exact match.

In this example, you'd better use the SEARCH function that works for the fractional lucifer as well:

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

In the formula, E2 is the accost of the jail cell that yous want to base of operations your formatting on, the dollar sign ($) is used to lock the column coordinate, and >0 means that the formatting will be applied if the specified text ("Due in" in our case) is found in any position in the cell.

Create three such rules following the steps from the offset example, and you volition have the below table, every bit the result: The rows' color is changed based on a text value in a cell.

Highlight row if cell starts with specific text

Using >0 in the higher up formula means that the row will exist colored no thing where the specified text is located in the cardinal cell. For example, the Delivery cavalcade (F) may contain the text "Urgent, Due in 6 Hours", and this row volition be colored as well.

To change the row color when the cardinal jail cell starts with a particular value, use =1 in the formula, e.g.:

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

in this case, the row volition just be highlighted if the specified text is found in the commencement position in the cell.

For this conditional formatting rule to work correctly, brand sure in that location are no leading spaces in the key column, otherwise you might rack your brain trying to figure out why the formula does not work :) Y'all tin can use this gratis tool to find and remove leading and abaft spaces in your worksheets - Trim Spaces add-in for Excel.

How to alter a cell's color based on a value of another cell

In fact, this is merely a variation of changing the background color of a row case. Only instead of the whole tabular array, you select a column or a range where you desire to modify the cells colour and use the formulas described above.

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

How to modify row's colour based on several weather

If yous want to shade the rows in the same color based on several values, and then instead of creating several formatting rules y'all tin use the OR or AND functions to ready several weather condition.

For instance, we tin color the orders due in 1 and 3 days in the blood-red color, and those that are due in 5 and 7 days in the yellow color. The formulas are as follow:

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

=OR($F2="Due in 5 Days", $F2="Due in 7 Days") Use the =OR formula to change a row's color based on several conditions.

And you can use the AND function, say, to change the background color of rows with Qty. equal to or greater than 5 and equal to or less than x:

=AND($D2>=5, $D2<=x) An example of using the =AND formula

Naturally, you are not limited to using but ii conditions in such formulas, y'all are free to employ as many as yous need. For case:

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

Tip: Now that you know how to color cells to differentiate betwixt various types of values, you may want to know how many cells are highlighted in a certain color and calculate the sum of values in those cells. The adept news is that you tin can automate this too and y'all will observe the solution in this article: How to count, sum and filter cells by color in Excel.

These are only a few of many possible ways to zebra stripe your Excel worksheets based on a cell'due south value that will respond to alter of information in that jail cell. If you lot need something different for your data set, drop u.s. a comment and we will try to effigy this out.