Comments on: How to change the row color based on a cell value in Excel

Learn how to quickly change the color of the entire row based on a single cell's value in your Excel worksheets. Tips and formula examples for number and text values. Continue reading →

Comments page 2. Total comments: 102

  1. on the basis of blank cell in a column, I want the row being highlighted. plz guide

  2. Hi, Thank you for this detailed explanation, I was able to follow it easily. It seems mine isn't quite working properly though. We have a quote tracker, and we change the status to "Sold", "On Hold", or "Denied" in column P starting at row 5. When I change the status in cell P:5 to "Sold" it is supposed to change just row 5 to green but it changes all the rows down to row 500. My formula is =$P$5="Sold", turn to Green fill dark green text, applies to $A$5:$Q$500. There are 500 rows in our sheet and columns up to Q. If I change the formatting to =$P$5="Sold", turn to Green fill dark green text, applies to $A$5:$Q$5 it works correctly and only highlights row 5 when I change the status to "Sold". The problem with that is when I change the status to "Sold" in row 6 nothing happens. I hope I don't need to add a rule for each row?

    1. Hello Darren,
      If I understand your task correctly, please try the following formula:

      = $P5 = "Sold"

      You can learn more about Relative and absolute cell references in Excel conditional formatting rules in this article on our blog. Hope you’ll find this information helpful.

    2. I figured it out! My mistake was the fact I had =$P$5="Sold". The two dollar signs were the issue. It should be =$P5="Sold". Thanks for this tutorial!

  3. Hi, if I want to leave the cells with out a color until I fill in any data, how do I proceed with that?

  4. how can i highlight entire row based on one cell,
    Ex: Column c Named "Raheem" and it become red and if i change the name , it want to change another colour

  5. Hi there,

    How do I change a colour on a row of columns when a particular column is typed into eg in that column I will type YES and the entire column line turns green?

    1. Hi, Doreen,
      let's suppose you have a table A1:D7, you type "yes" into C2 and the entire row A2:D2 turns green.
      For that you need to create a formatting rule saying:
      =$C2="yes"

      and make sure the rule is applied to
      =$A$2:$D$7

      Please read the first point of the article above in order to see how to choose a colour for the conditional formatting rules.
      Hope this helps!

  6. Hi Sevtlana,

    Could I please request your help.

    I am having a trouble in colouring a cell , which is dependent on the value of other cell in different TAB but same worksheet. for example , if source cell has value between 90 & 100 , destination cell should reflect green colour..

    Can you help.

    1. Hi, Summit,
      to solve the task you need to use "AND" function in the formatting rule. If you mean that you work within one workbook, where the source cells are in the TAB2, column A, and the destination cells are in the TAB1, column A, the formula will look like this:
      =AND('TAB2'!$A1>90,'TAB2'!$A1<100)

      If you mean, that you work with different workbooks – change 'TAB' into '[Tab.xlsx]SHEETNAME' accordingly.
      To understand how the sheet references work, feel free to check this article out.

  7. Hello Sevtlana,

    I saved an excel sheet from the "searchmyfiles" app. It's an inquiry for duplicate file names on all my drives. I've sorted them by duplication number which range from 1 to over 1000 files that have duplicates. I have some files that have been duplicated up to 10 times. I'd like to highlight every other duplication number (NOT every other row). Can you help me with that, please?

  8. if a column contains "any date" i want to fill a color to the entire row in excel

    1. Hello Naseef,

      Select the rows that you wan to highlight, and create a conditional formatting rule with the below formula, where A2 is the top-most cell with a date:
      =ISNUMBER($A2)

  9. I am sorry. My question was a bit confusion. Here is the revised one: Are we only able to check one column at once? Is it possible to check entire worksheet for a keyword(s) and then highlight all the rows have hit in different columns?

    1. Hi Mike,

      I don't know a way to check the entire worksheet. However, if there are a limited number of columns to check, you can use the following formula to highlight rows that contain a specific text in any of the concatenated columns:
      =SEARCH("text",$A1&$B1&$C1&$D1)>0

      Where 1 is the top row that you want to highlight.

  10. Hi
    If I want to highlight any cell in the column that contains a number larger than the previous one in the same column, what should I do?

    1. Hello, Gloria,

      Please try the following:
      Go to Conditional Formatting -> New Rule -> Use a formula to determine which cells to format
      Enter the following formula:
      =AND(ROW(A1) <> 1, A1>OFFSET(A1, -1, 0))

      Hope this helps.

  11. I am trying out this example on my Excel 2013 and I get an error that "we found a problem with this formula.."
    I have defined a rule with the formula below:
    =OR($D2="Due in 1 days",$D2="Due in 7 days")

    1. Hello Boniface,

      Most likely the problem is in a different List Separator. This formula is written with a comma, which is the default List Separator in North America and some other countries. In European countries the comma is reserved as the Decimal Symbol and the List Separator is set to semicolon. So, try replacing "," with ";" like this:

      =OR($D2="Due in 1 days";$D2="Due in 7 days")

  12. I have entered a date and time in a cell and I want the cell next to it to turn green in exactly 24 hrs. How do I do this?

    1. Hi Reuben,

      Assuming you have a date and time in cell A1, you can create a rule based on the following formula:
      =(NOW()-$A1)*24>=24

  13. I need a formula that will make cell "A3" turn Green when cells "E3 through L3" contain the letter "Y"

    and also

    "A3" turn Red when cells "E3 through L3" contain the letter "N"

    1. Hi Chris,

      You just need to put all these conditions in the AND formula, like this:

      =AND($E3="Y", $F3="Y", G3="Y", etc.)

  14. Hi Svetlana Cheusheva,
    I wanted to send reminders to the stakeholders while exceeding 3 days from last reminder date. I have used the below formatting but I could not highlight the entire rows and also want to ignore weekends (Saturday and Sunday).
    Formula used, E.g.,
    =$D$2-TODAY()>=3
    Thank you.

    1. Hi Bharath,

      First off, please check whether your rule applies to the entire rows you want to highlight (without the column headers).

      Assuming that the last reminder date is in column D, you need to change your formula as follows:

      =TODAY()-$D2>=3

      Please pay attention that $D2 should be a mixed cell reference (absolute column - with $ and relative row - without $).

      Finally, create one more rule to exclude Saturday and Sunday (do not choose any fill color), move it to the top of the rules list and check "Stop if true" next to it:

      =WEEKDAY(D$2,2)>5

  15. Hi,
    Can you please help me with my formula?!

    =OR($D>=20%, $E=8%, $E>=4,$E=3%, $E>=7)
    COLOR GREEN

    =OR($D<=20%, $E<=3),OR($D=4,$E<=6),OR($D=7)
    COLOR RED

    1-3 ranking + ctr >20% = green
    1-3 ranking + ctr 8% = green
    4-6 ranking + ctr 3% = green
    7-10 ranking + ctr <3% = red

    Thanks
    Chantel

    1. Select your column(s) and create 4 rules using the following formulas:

      Green
      =AND($D2>=20%,$E2>=1,$E2<=3)

      =AND($D2=8%,$E2>=1,$E2<=3)

      =AND($D2=3%,$E2>=4,$E2<=6)

      Red
      =AND($D2<3%,$E2>=7,$E2<=10)

      Where D is the CTR column, E is the SERP column.

      You’ll find more details about the usage of AND/OR here:
      https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/

  16. Hello Everyone,

    I have a report that I am attempting to use a conditional format to highlight a cell when the date which is locate in a different column is prior to 06/17/13. Can anyone assist me in sharing the formula to use.

    1. Hello Toni,

      Select the entire column with the dates you want to highlight (not including the column header, if any) and create a conditional formatting rule using this formula:
      =$A2<"06/17/13 (where A2 is top cell).

  17. Hello Svetlana,

    It is possible to highlight current day from a calendar using conditional formatting.

    Thanks in advance

    1. Hello Poc,

      Of course, you can do this using the following formula:
      =$A2=TODAY()

      Where A2 is the first cell with a date in the column.

  18. What if I want to color an entire row with a cell that containing a specific text

  19. I work with students and I need to know if they are behind. If I have a cell that has a text value ("Y" or "N") and one that is counting days (45). How would I get the row to change to red if I have a "N" condition and a >21? Thank you for any help.

    1. Hi Erik,

      Try this formula:=AND($B2="N",$D2>21)

      Where $B2 is the first cell of the Y/N column, excluding headers; and $D2 is the first cell of the Days column.

      1. Thank you for the help. It works perfectly. I have over 140 rows. Is there a way to apply the formatting to work individually with each cell without having to manually go to each cell? Thank you again for your help.

        1. Simply apply that rule to all 140 rows. Click Conditional Formatting > Manage rules, and change the range in the box under "Applies to". Since you use relative row references in the formula (without the $ sign), the rule will be applied to each row individually.

  20. Hi Svetlana,

    I am struck at one point in my excel sheet, the client needs to detect all red colored cells in a column, and give them a code like "L" and copy in the corresponding empty row. I am not sure how I should move....

    I am looking for your kind guidance in this regard.

    Thanks
    Shikha

  21. Hi,

    I have a problem with icon sets. I'm supposed to obtain green flag in cell A5 if there is any value (combination of letters and numbers) in cell F5 and red flag in the case that there is no value. I need a solution for entire column A (flags) and F (values).

    Thank you in avance.

  22. I need a little help...

    I am trying to get the whole row text to change colour based on entries in column S.

    I go to conditional formatting and choose a new rule. I then pick use a formula to determine which cells to format. I then put the following formula in: =$S7="2015 callback"

    I change the format so that the text will change to purple.

    Once I have done all this some cells change but these are rows of cells where the information in column s is not "2015 callback"

    How can I manipulate this excel spreadsheet to do as I want?

    Thanks

    1. Hi Laura,

      Your formula is correct. Just make sure please that you apply it to the right range of cells. For example, if row 7 is your first row with data, then you should select the range starting with A7.

      If your data starts in some other row, e.g. row 2, then you should change the formula accordingly: =$S2="2015 callback"

      I have created a similar rule for column C and it works just fine:
      Conditional formatting rule

  23. How would I do this if I wanted it black out a row with any text in a column cell? For example a termination date. If there is any date entered in that column it blacks that employees info.

    Thanks

    1. Hi Kyle,
      You can select the entire table and create a rule with this formula (assuming that your table has headers and data starts in row 2):
      =$B2<>"" where B is your termination date column. Naturally, you choose the black fill color.

  24. I am trying to format a sheet so that when a date is entered in a row in column K the entire row is coloured yellow. However when a second date is entered in the same row in column L the row is changed to red. I know this should be simple but I am having issues getting it to work.

    1. Hello Lu,

      I think you can create 2 rules with the following formulas and apply them to the entire table:
      1) =$K1<>"" - yellow fill color
      2) =AND($K1<>"", $L1<>"") - red fill color. Check the option "Stop if true" next to this rule and make sure it comes first in the rule list.

      The rules will color your spreadsheet correctly if only dates are allowed in columns K and L, because as you understand, the formulas will work if any value is entered in those 2 columns. Hopefully, this solution is suitable for your task.

  25. Hi Svetlana

    Thank you for you good work.
    How do you change the color of a value in a cell that has changing (RTD) dynamic value:
    Example:
    Cell A1 contents 1000 Text color is BLACK time 00:01
    Cell A1 contents 1001 Text color is GREEN time 00:02
    Cell A1 contents 999 text color is RED time 00:03
    Thank you
    Ian

    1. Hi Ian,

      You can try to do this by creating 3 different rules of the type "Format only cells that contain" that apply to the entire table. Please see this example for step-by-step instructions: How to change a cell's color based on value.

      Though, I have to say I have never tested the conditional formatting rules on data returned by an RTD and cannot say with confidence whether this will work.

  26. Hi Svetlana,

    how to highlight first order row of every client? My spreadsheet contains client name, date of acquisition columns and has many entries per client.

    Many thanks!

    1. Hi Marcel,

      I cannot figure out a way to do this with conditional formatting. However, our Duplicate Remover add-in can help. You can download a trial version and then do the following:

      1. Select your entire table, and click "Duplicate Remover" icon on the ribbon, then click Next.
      2. Choose to find "Uniques +1st occurrence".
      3. Select only your "Client ID" column as the key column.
      4. Select the action "Fill with color", choose a color and click Finish.

      The first order row of every client will be shaded with the color of your choosing. Is this what you are looking for?

  27. Hi. Thank you for the helpful instructions. I am trying to set the color of a row based on the text in one column, but have a list of words to exclude.
    Therefore, I want the rows where certain words do not appear in a specific column to be highlighted. I would greatly appreciate your help.

    I have tried the following formula without success
    =AND($F17"BLUE", "RED")

    and
    =OR($F17"BLUE", "RED")

    1. Hi Abby,

      If my understanding is correct, you need to highlight rows where neither "BLUE" nor "RED" appears in column F. If so, select your entire table and you can use create a conditional formatting rule with this formula (assuming that your table has headers and row 2 is your 1st row with data):
      =AND($F2<>"RED",$F2<>"BLUE")

      If you want to highlighted rows that contain certain words, let's name them "GooodWord1" and "GooodWord2", but do not contain words-exceptions, say "ExcludeWord1" and "ExcludeWord2", then you will need a very complex formula like this:

      AND(false=ISERR(SEARCH("GooodWord1",$F2)), false=ISERR(SEARCH("GooodWord1",$F2)), true=ISERR(SEARCH("ExcludeWord1",$F2)),, true=ISERR(SEARCH("ExcludeWord2",$F2)))=true

      As you see this formula is very long and that is why your workbook may start working a bit slower than usually.

  28. I have a large spreadsheet that contains two date columns. One of the date columns is column F. I want to highlight every row that has the date 12/31/2013 in column F, but I can't make it work. Thanks!

    1. Hi Katie,

      The point is that Excel stores dates as numbers, starting from January 1, 1900. So, 1-Jan-1900 is stored as 1, 2-Jan-1900 as 2… and 12/31/2013 as 41639. To make the formula work, just enter your date in this numerical format, i.e. =$F2=41639 (assuming that row 2 is your top row with data).

      Another way is to convert the date to the number format is which it is stored: =$F2=DATEVALUE("12/31/2013")

  29. 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?

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

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

    1. 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)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)