by Learn how to quickly highlight the entire row based on a single cell's value in Excel. Tips and formula examples for number and text values.
Last week we discussed how to change the background color of a cell based on its value. In this article you will learn how to highlight entire rows in Excel based on a value of one cell, and also find a few tips and formula examples that will work with numerical and text cell values.
How to change a row color based on a number in a single cell
Say, you have a table of your company orders like this:
You may want to shade the rows in different colors based on the cell value in the Qty. column to see the most important orders at a glance. This can be easily done using Excel Conditional Formatting.
- Start with selecting the cells the background 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 option "Use a formula to determine which cells to format" and enter the following formula in the "Format values where this formula is true" field to highlight orders with Qty. larger than 4:
=$C2>4
And naturally, you can use the less than (<) and equal to (=) operators to find and highlight rows that have Qty. smaller than 4 or equal to 4:
=$C2<4=$C2=4Also, pay attention to the dollar sign $ before the cell's address - it is needed to keep the column letter the same when the formula gets copied across the row. Actually, it is what does the trick and applies formatting to the whole row based on a value in a given cell.
- Click the "Format…" button and switch to Fill tab to choose the background color. If the default colors do not suffice, click the "More Colors…" button to pick the one to your liking, and then click OK twice.
You 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 rule will look similar to this:
- If this is how you wanted it and you are happy with the color, click OK to see your new formatting in effect.
Now, if the value in the Qty. column is greater than 4, the entire rows in your Excel table will turn blue.
As you can see, changing the row's color based on a number in a single cell is pretty easy in Excel. Further on, you will find more formula examples and a couple of tips for more complex scenarios.
How to apply several rules with the priority you need
In the previous example, you may want to highlight the rows with different values in the Qty. column in different colors. For example, you can add a rule to shade the rows with quantity 10 or greater. In this case, use this formula:
=$C2>9
After your second formatting rule is created, set the rules priority so that both of your rules will work.
- On the Home tab, in the Styles group, click Conditional Formatting > Manage Rules… .
- Choose "This worksheet" in the "Show formatting rules for" field. If you want to manage the rules that apply to your current selection only, choose "Current Selection".
- Select the formatting rule you want to be applied first and move it to the top of the list using the arrows. The result should resemble this:
Click the OK button and the corresponding rows will immediately change their background color based on the cell values that you specified in both formulas.
How to change a row color based on a text value in a cell
In our sample table, to make follow-up on orders easier, you can shade the rows based on the values in the Delivery column, so that:
- If an order is "Due in X Days", the background color of such rows will turn orange;
- If an item is "Delivered", the entire row will be colored in green;
- If an order is "Past Due", the row will turn red.
Naturally, the row color will change if the order status gets updated.
While the formula from our first example could work for "Delivered" and "Past Due"(=$E2="Delivered" and =$E2="Past Due"), the task sounds a bit trickier for "Due in…" orders. As you see, different orders are due in 1, 3, 5 or more days and the above formula won't work because it is purposed for exact match.
In this case, you'd better use the SEARCH function that works for the partial match as well:
=SEARCH("Due in", $E2)>0
In the formula, E2 is the address of the cell that you want to base 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 first example, and you will have the below table, as the result:
Highlight row if cell starts with specific text
Using >0 in the above formula means that the row will be colored no matter where the specified text is located in the key cell. For example, the Delivery column (F) may contain the text "Urgent, Due in 6 Hours", and this row will be colored as well.
To change the row color when the key cell starts with a particular value, use =1 in the formula, e.g.:
=SEARCH("Due in", $E2)=1
in this case, the row will only be highlighted if the specified text is found in the first position in the cell.
For this conditional formatting rule to work correctly, make sure there are no leading spaces in the key column, otherwise you might rack your brain trying to figure out why the formula does not work :) You can use this free tool to find and remove leading and trailing spaces in your worksheets - Trim Spaces tool for Excel.
How to change a cell's color based on a value of another cell
In fact, this is simply a variation of changing the background color of a row case. But instead of the whole table, you select a column or a range where you want to highlight rows and use the formulas described above.
For example, we could create three such rules to shade only the cells in the "Order number" column based on another cell value (values in the Delivery column).
Highlight rows based on several conditions
If you want to shade the rows in the same color based on several values, then instead of creating several formatting rules you can use the OR or AND functions to set several conditions.
For example, we can color the orders due in 1 and 3 days in the reddish 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")
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 10:
=AND($D2>=5, $D2<=10)
Naturally, you are not limited to using only 2 conditions in such formulas, you are free to use as many as you need. For example:
=OR($F2="Due in 1 Days", $F2="Due in 3 Days", $F2="Due in 5 Days")
Tip: Now that you know how to color cells to differentiate between 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 good news is that you can automate this too and you will find 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's value that will respond to change of data in that cell. If you need something different for your data set, drop us a comment and we will try to figure this out.
546 comments
Hi, If I want to highlight a final result cell based on certain inputs, say "Yes" or "No" in a column. how can i do that?
If the column C has Yes or No as inputs against some conditions placed in column B then how does it reflect in the final result column D as Yes or No?
Hi AG,
If my understanding is correct, you want to copy "Yes" or "Now" from column C to column D and then highlight rows in 2 different colors. If so, then enter the following formula in cell D2 (assuming that your table has headers) and then fill that down until the end of your table: =C2
After that select your table and create 2 rules using these formulas:
To highlight rows with Yes: =SEARCH("Yes", $D2)=1
To highlight rows with No: =SEARCH("No", $D2)=1
If you are looking for something different, please clarify.
I have a spreadsheet in which I have a number of columns. In column a I have a Code that may repeat for 4 rows, then change to another value, then another. How can I easily Highlight the row for each change in column A? Here is a simple example of the data. In the case below, I would want to highlight the first 4 rows in let's say blue, then I do not need to highlight rows 5-8, but then highlight rows 9-12 in blue again, the next 4 no highlight, etc...
6870meri Property Administrator (Payables)
6870meri Property Administrator (Receivables)
6870meri Property Manager
6870meri Superintendent
aberdeen Property Administrator (Payables)
aberdeen Property Administrator (Receivables)
aberdeen Property Manager
aberdeen Superintendent
albionc Property Administrator (Payables)
albionc Property Administrator (Receivables)
albionc Property Manager
albionc Superintendent
alexandr Property Administrator (Payables)
alexandr Property Administrator (Receivables)
alexandr Property Manager
alexandr Superintendent
alkenton Property Administrator (Payables)
alkenton Property Administrator (Receivables)
alkenton Property Manager
alkenton Superintendent
arcadia Property Administrator (Payables)
arcadia Property Administrator (Receivables)
arcadia Property Manager
arcadia Superintendent
Hi Peter,
If all of your codes repeat for 4 rows, then you don't even need to use conditional formatting. An easier way to zebra stripe your data would be using Excel's table styles:
1. If you have a range of data, convert it to table (Insert > Table).
2. Go to the Design tab, right click on the table style you want and choose Duplicate.
3. Name your new style, select "First Row Stripe" and set it to 4. Do the same for "Second Row Stripe".
4. Click OK, save your style and apply it to your table by selecting it from the Table Styles gallery.
You can achieve the same result by creating a conditional formatting rule with this formula =MOD(ROW($A2)+8-2,8)>=4 (supposing that your table has headers and row 2 is your top row with data; if not, then replace -2 with the number of your 1st row).
If there may be a different number of rows in the Codes column (A), then you will need a bit more complex formula. Create an additional column, say column K, and enter the following formula in cell K2, then copy it across the entire column: =MOD(IF(ROW()=2,0,IF(A2=A1,K1, K1+1)), 2)
The formula will populate column K with blocks of 0 and 1, every new block staring with the Code change. After that create a conditional formatting rule(s) using the formula =$K2=1 (and =$K2<>1 if you'd want a second color to alternate blocks). Hope this helps.
Much thanks...this will be simple to implement and works like a charm.
Hello,
I was able to use your directions to change the color of one cell based on the values in another. I would like to repeat this on every row in my document. Each row is in an independent decision. Is there an easy way to copy my rule for one row to the others or do I have to set up each row individually? Thank you.
Hi Carl,
If you want to apply the same rule to all the rows, then you can simply modify the range it applies to. You can do it in the following way. Select the row to which the rule applies, click Conditional Formatting > Manage rules, select your rule and enter the needed range directly in the “Applies to” field. If you are loking for something different, then please give me an example.
I don't think setting a range will work because the color of cell A1 (in my sheet) is dependant upon a value in A10 and/or A11. The value in B1 will be dependant upon my value in B10 and/or B11 and so on. Each row is an indpendant decision of the other rows. If I apply a range then won't all the colors be the same? I don't want that. A 1 could be anyone of three colors, B1 could be anyone of three colors and so on.
Hi Carl,
I am not sure I understand your task correctly. Anyway, for the colors not to be the same, you can use a relative reference (without the $ sign) or a mixed reference (relative column and absolute row). For example, if you create a conditional formatting rule with this formula =AND(A1>A$10,A1<A$11) for range =$A$1:$D$9, it will color all cells in column A (A1:A9) whose values are greater than A10 and less than A11; cells in column B with values greater than B10 and less than B11 and so on. If you are looking for something different, then you can send us your test workbook (support@ablebits.com), it's always much easier to come up with the right formula when you can see the real data.
Hi Svetlana,
I am trying to use the AND function to highlight rows. I want the row to be shaded when column K and column M contain "Yes". I highlight my whole table and apply the conditional formatting rule =AND($K5="Yes",$M5="Yes")it works but only for the top row? How do I make it so the rule is applied to the whole table?
Also if possible, I would like to shade a different colour when the above is applied and also column I contains any value (either text or number). Is this possible?
Thank you, I hope this make sense!
Hi Shannon,
Your formula is correct and it worked just fine on my test sheet. It might not work on your side if for some reason your conditional rule was created for the top row only. You can check it in this way:
On the Excel Ribbon, click Conditional Formatting > Manage rules. In the Conditional Formatting Rules Manager, choose "This Worksheet" next to "Show formatting rules for". You will see your current rule and the range it applies to. If it is not correct, you can change it directly in the "Applies to" field.
Also, please check if row 5 is really a top row with data in your table, it not, modify the formula accordingly.
As for your second condition (when columns K and M contain "Yes" and column I contains any value), it is also possible using this formula: =AND($K5="Yes",$M5="Yes",$I5<>"") Hope this helps.
Thank you, thank you! I have been trying to find a way to make this work based on the dates in the last column of several spreadsheets for a week. I guess I was just asking the wrong questions of Google, but today I found your explanation and was able to quickly (and EASILY) alter it to my needs using the TODAY()feature and adding/subtracting the # of days that would determine a different color.
Your explanations and the screenshots were fantastic!
Thank you so much Matt!
is it possible to format a cell that contain numeric and text value on its numeric value only?
Regards
Regrettably, this cannot be done either with conditional formatting or Excel formulas because they change the format of the entire cell.
hi! i am traying to format each duplicate pair in diffrent color, is this possible? infact, it is a date format and i want to have each date duplicates in same color.
regards
Hi Muzaffar,
I don't know a way to do this with conditional formatting. Possibly, there is a way using a VBA macro.
Hi,
I have created a spreadsheet with a list of contracts including expiry dates when known - I am trying to create a conditional format for the entire row when the contract has expired - I have tried several different combinations including creating a hidden column with the TODAY() formula and using the =$L1<$M1 in the conditional formula L - being the expiry date and M - the today date, but none of the combinations I have tried are working (I have checked and all the dates are entered in the same format - but some dates that are greater than M are being highlighted also....I have also created a conditional format for when the expiry date is empty (L) to leave the format unchanged but it seems to work for some and not for others - I am completely stumped?
Hi Theresa,
Your formula is correct. I can think of only one possible reason why it may not work properly. If your table has headers, then you need to put it =$L2<$M2 to avoid the "shift" because the real data starts in row 2. Is this the case?
Hi! You know the formula to highlight the row that have the same values in total score column and rank column? Thanks! :D
Hi Josiah,
You can create a rule with this formula: =$A2=$B2
Where A and B are your total score and rank columns and 2 is the first row with data. Hope this helps : )
I mean, in my case, T9 and T10 have the same rank. Is there a formula to highlight them? C:
Hi Josiah,
Sorry, I am not sure I can exactly follow you. I thought you needed to color rows if values in 2 different columns (total score and rank) were the same. But T9 and T10 are 2 different rows in the same column.
I have one help
in a cell i have keying one letter example A, and i selected same row its reflect full rows A.
how is i do. need to help me.
Thank you so much for your help. I am trying to use conditional formatting to create two separate colored sections. Here is an example of my data:
.5 .5
.5 .5
1.0 0.0
.75 .25
The rule that I would like to set up is a green row appears when the first column is greater that 0 and the second column is equal to 0
When both columns have numbers that are greater than 0, I would like to set up a blue column.
Thanks!
Hi Ryan,
Supposing that your first column is A and your 2nd column is B and your table has headers, you can use these formulas:
=AND($A2>0, $B2=0) - for green rows
=AND($A2>0, $B2>0) - for blue rows
As you understand, you need to replace A and B with the columns where your numbers are located. And if your table does not have headers and your data starts in row 1, change A2 to A1 and B2 to B1.
Awesome, that worked. Thank you!
Hi Svetlana, I am running into an issue and I hope there is an easy fix. The formula below the formatting rule works.
Example 1:
=AND($B2="Success", $F2="Approved") the formatting works.
However when I add an additional formula the results don't format even when true. The issue is my spreadsheet contains the words TRUE in some cells and FALSE in others. I'm thinking the formula think these are some type of operators. When I try and use these values in the formula no formatting takes place.
Example 2:
=AND($B2="Success", $F2="Approved", $C2="TRUE") the formatting fails.
I have multiple columns that have a value of either TRUE or FALSE.
How can I get around this? I have tried applying different rules but I haven't found a solution yet.
Thank you,
RC
Hi RC,
You are right, Excel perceives TRUE and FALSE as Boolean values rather than text values. So, you simply need to remove quotation marks from TRUE, so that the formula reads: =AND($B2="Success", $F2="Approved", $C2=TRUE)
I am trying to add a rule that will change the color of the cell (C column) if the return date (B Column) is the same (green), one day later (yellow) and over 2 days later (red). I entered the formula on the first row but cannot paste it to any other cells. I need all of column C to reflect column B as I enter the dates.
Hello Sabra,
If you have already created a conditional formatting rule for 1 row, then you can extend it to the entire table in this way. On the Excel Ribbon, click Conditional Formatting > Manage rules. The Conditional Formatting Rules Manager will open an you choose "This Worksheet" from the drop-down list next to "Show formatting rules for". You will see your current rule and edit the "Applies to" range as you need it either by typing or use the standard Excel button to select the range to the right on the "Applies to" field.
Also, be sure to use the dollar sign ($) before the column letter in your formula, e.g.
Green =($C1-$B1)=0
Yellow =($C1-$B1)=1
Red =($C1-$B1)>1
Hope this helps.
Thanks, it helped me format the document easily.
Hello Svetlana
Im wondering if you can help me i have a spreadsheet set up for work and i need to make it then when i put in a g it turns green and so on with different letters and colours. I can get it that i can change the colour however i need the number to disapper
Hope you can help
Thank you
Hello Sarah,
If my understanding of the task is correct, you want to change the color based on a cell's value and after that the cell's value should disappear. I don't know any way to delete cells' values using conditional formatting. And even if there's such a way, most likely this would eliminate the cell's color too (no value > no color). As an alternative, you can "hide" the value by making the cell's color and font color alike. In other words, in your conditional formatting rule you can set the font color exactly the same as the fill color.
Thank you for looking into it.
Hi Svetlana
I would like cells colored based on ID numbers they contain.
What would a formula look like that colors cells with the same values ( ID's) in a list, so the cell colors alternate, but by their ID numbers not just alternating cell by cell or by Company name as ID is linked to different locations of the same Company.
Example:
ID No Company
60416 Company A
60416 Company A
60419 Company A
60420 Company A
60420 Company A
60420 Company A
60420 Company A
60423 Company A
60489 Company B
60489 Company B
60489 Company B
60490 Company B
60490 Company B
60490 Company B
60490 Company B
60159 Company C
60159 Company C
60159 Company C
60159 Company C
60434 Company D
60434 Company D
60434 Company D
60434 Company D
60909 Company E
60909 Company E
60909 Company E
60909 Company E
Thanks
Hi Colin,
The only solution that I can suggest is to create a separate conditional formatting rule for each ID. In your example, there are 10 different IDs, so you'd need to create 10 different rules. VBA code might probably work too, but it should allow for all the specificities of your real data, so we cannot give any advice.
i have a huge data's and each cell texted mixed color.
i have done a formula to when ever i mark No.1 on cell will take the target cell which i want (simple : =if(=IF(A1=1,C2," ").
The problem :
the problem is the target cell givving me a black color text data, how i can get it as colored as original cell which it have?
Hi Vic,
If you color your original cells with conditional formatting, then you can simply extend the rules to the column to which you copy the target cells' values. If you color the target cells manually, then you can create new conditional formatting rules to change the font color based on a cell's value.
Hi Svetlana,
Is there any way to change the background colours of cells when text in those cells is changed to a date? The dates are to go on for the rest of the year(end of 2014).
Hi Vanessa,
To color cells with dates, first you need to identify the format code of your date, e.g. d-mmm-yy or dd-mmm-yy is "D1"; d-mmm or dd-mmm is "D2"; m/d/yy or m/d/yy h:mm or mm/dd/yy is "D4" etc. You can find the complete list of date codes in this article.
And then, select a table or a range where you want to change the color of cells, and create a conditional formatting rule using a formula similar to this =cell("format",$A1)="D4" where D4 is the date format and A in the name of the column that contains dates. If your dates may be in 2 or more formats, then use the OR operator, e.g. =OR(cell("format",$A1)="D4",cell("format",$A1)="D2",cell("format",$A1)="D1")