Comments on: Excel conditional formatting formulas based on another cell

This tutorial explains how to use Excel formulas to format cells and entire rows based on the values you specify or based on another cell's value, and provides a handful of formula examples. Continue reading →

Comments page 42. Total comments: 1726

  1. I want to use the conditional formatting to color a column of cells based on "yes" or "no" in another cell. I tried walking through the steps above and got nothing. I also tried formatting it as a table and using the COUNTIF equation. I got it to work on a test cell but when I tried to duplicate it with the entire column I got nothing.

  2. Hi Svetlana, I'm having a problem trying to find if the numbers in a column are in a sequence or not.

    I want to highlight a cell whenever the it is not equal to the cell above it + 1.

    So:

    2
    3
    4
    2 <-- HIGHLIGHT
    5 <-- HIGHLIGHT
    6
    8 <-- HIGHLIGHT

    Thank you for your help

  3. hi
    tanx for your most useful tips
    I have a question
    I wanna write formula that says if you search a number in column b by Crtl+f
    and it finds it the near column (column a) type ok and it change the color of it automatically...
    is it possible anyway?

  4. Hello,

    I am trying to run down a large list full of one's and zero's. If it is zero, then the corresponding row, 2 rows to the left, value I do not care about. If it is a one, I then want to essentially add it to a mixing pot with all of the other values that have 1 corresponding with them, then find the max value of all of them. HELP!?

    1. Hi Will,

      I understand that you have a list of 0s and 1s but I'm not sure I understand the task. Can you please try to describe it in the following way:

      -if cell A1 is 0, then do what?
      - if Cell A1 is 1, then do what?

  5. Hello,
    I have a problem of replacing a cell value if th background color of the cell is Yellow: for example

    Column A B
    1 2
    1 2

    I would like in a new sheet to put in (column A) 1 or 2 depends on the Yellow color in A1 and B1,
    so if A1 is highlighted in Yellow then put in the new sheet A1 code 1 if not then put 2

    How I do that?

  6. Hello,

    I'm currently using 2 sheets to keep track of client information. sheet 1 (client info) has columns listed as A1-SSN, B1-Lastname, C1-Firstname, D1-Phone#, and E1-paid (which is blank since the service occurs before payment) and each column has about 100 entries of the information. Now in sheet 2 (payments) I have the same columns listed in the 1st row and a vlookup formula in all the cells so that when I enter the SSN number in A2, the rest of the 2 row fills in with the names, numbers, etc. My question is how do I make it that when I enter 'yes' under the payment column (E2), my sheet 1 with also fill in 'yes' in column E for the right client?

    Thank you for any help you can give

  7. Hello!

    Simple request. I have had trouble finding the proper way to do this. I want to be able to enter a name in a cell on Sheet 1 of the MS Excel Workbook (2010) and have that same name (or data) repeated in a corresponding cell on another Sheet in the same workbook as I enter it in the one place. Basically I need names of associates to appear in multiple places, but only want to enter it once. Any tips?

  8. Good day!
    Hope you can help, as I tried everything and nothing works:
    I have two columns (Sales in 2013 in one columns, and sales in 2014 in another column - this is made depending on customer). Now I have to figure out, from a glance, which customer bought more in 2014, which less

    Column A B C
    Sales in 2013 Sales in 2014

    Purchaser 1 2131442,93 1783657,38
    Purchaser 2 295638,22 2609,31
    Purchaser 3 1733348,19 865579,68
    Purchaser 4 1472991,01 418399,35
    Purchaser 5 1490602,99 120403,99
    Purchaser 6 2927921,82 2729046,74
    Purchaser 7 452169,39 392953,53
    THank you for your help.

  9. I would like to create a conditional formatting rule for the following:

    Cell C2: If cell C1=36 then the text in C2 would be 3x/8 weeks, 2x/6 weeks (36)

    Once that is completed, I'd like to create additional conditional formatting rules for the same cell.

    Is that possible?

    1. Hi Npratt,

      I'm not sure I exactly understand the condition. Assuming that you want to put the text 3x/8 weeks in cell C2 if C1 euqals 36, otherwise - 2x/6 weeks (36), here is the formula for cell C2:

      =IF(C1=36, "3x/8 weeks", "2x/6 weeks (36)")

  10. I am trying to highlight entire row based on a cell value

    Wxample: I want my formula to highlight entire row where cell value = MH000038, when I am giving the below formula excel is not considering the zeros it is removing

    Rule created = $C2=MH000038
    Excel converting it to =$C2=MH38 so no cells are highlighting

    any help will be appreciated.

    1. Hi Suma,

      Simply enclose the cell value in quotes so that Excel interprets it as a text string, like this:

      =$C2="MH000038"

  11. Hi ,

    I have a question, in my case 1 particular cell say A1 is done with a conditional color formatting so that the color of the cell changes with the value input in it. What I want is that the same cell color should be done for another cell say A2.
    Could you please advise.Thanks

  12. can somebody help to solve this for me please? In excel 2013 I have a column where I have put some values.
    e.g. In D1 cell and I want to give conditional formatting to E1 cell that it becomes red in background if the value in D1 is less then 50% and it becomes Yellow in background if the value in D1 is greater then 49% and it becomes Green in background if the value is equal to 100%.
    Note: E Column I want to keep empty for the specific color as explained above.

    Thank you in advance for your support. Regards,

  13. I want to color red a particular set of data if it contains data which is in a particular column using conditional formatting. what formulae should i use. i am not able to give an array in the formulae

  14. Hi Svetlana,

    I need help searching a range of cells for information that may be contained in another spreadsheet. I have two spreadsheets which contain information for hundreds of entities, one for 2013-2014 members and another for 2014-2015 members. What I would like to do is identify new members by searching the Tax IDs in both sheets. If the Tax ID is found on both sheets, I would like it to do nothing. If a new Tax ID is identified on the 2014-2015 sheet, I would like it to highlight red.

    Thank you in advance!
    Jen

  15. hi

    I need to conditionally format a cell based on the conditional formatting of three other cells.

    for example
    A1 is Green
    A2 is Green
    A3 is Red
    so I want A4 to show as Amber

    Is this possible and if so how do I do it?

    Thanks in advance
    Amber

  16. Hi Svetlana.
    My problem would not appear to be too difficult but I cannot find a way to make this work.
    I am working on a rota for staff. In cell 1 I have a time 8:00:00 which is the start time. In cell 2 I have a finish time of 16:00:00 which i want to highlight in red using conditional formatting if the start time is = to 8:00:00. I'm sure this is easy but I'm stumped!

  17. Hi,
    How can i make a calender in excel using formula (except using visual basic)?

  18. Hi Svetlana,

    I am trying to enter a formula that will color a row based on the values on two sheets matching. If I have a series of telephone numbers on one sheet and I have sheet with phone numbers on a second sheet, I want to highlight the row on the first sheet that has a matching phone number. I want to do this for the complete sheet.

    So far I have managed to highlight the matching cells on sheet 1 using the following formula:

    =COUNTIF('Sheet 2'!$D$2:$D$71,D2)

    with a format to color the cell yellow applied.

    That will obviously only highlight a cell if a matching number is found on sheet 2. I need to be able to color the row.

    Thanks

  19. Hi,
    Pls help me with this.
    Sheet 1 has to be a summary sheet that shows the consolidated values of # of open positions at onsite/offshore and for a particular month in sheet 2.

    Sheet1 resultant table should be something like this
    Status Dec-14 Jan-15 Feb-15
    ON|OFF ON|OFF ON|OFF
    Open 1 2 2 4 0 2
    Selected 0 2 1 1 2 3
    Identified 1 0 0 1 0 0

    Sheet2 values will be like this
    Name Status Location Start date
    X Open ONSITE JAn-15
    Y Open Offshore FEB-15
    Z Selected Offshore Dec-14
    XX Identified Onsite Dec-14
    YY Selected Offshore Feb-15

    Pls help me

  20. Hi, I am a bit desperate here... I have searched everywhere but I can't find a solution to my problem. I really hope you can help me! So here it is:

    My issue is with the conditional formatting using a formula. To summarize, I want G3 to be highlighted if F3 is more than or equal to 0 and also G3 is smaller than 90% of F3.

    This is the formula I have entered in the conditional formatting but it doesn't work:
    ="AND($F3>=0, $G3<$F3*0.9)"

    Thanks for your help in advance :)

    1. Nevermind! I Figured it out. It should have looked like this:
      =AND($F3>=0, $G3<($F3*0.9))

      I am such a noob :P

  21. sorry, formatting went wrong, hope you can read it
    for every space there should be a tab.

  22. Hi, thanks for the helpful posts. Though I am struggling with the following:

    I want to color column B depending on column A. And result columns (say E to X) should be dependent on B (it's like a matrix of column B). My guess is I mix up relative and absolute cell-links. Or I don't get how to copy the rule for a cell to others, i.e. I can create the rule for a single cell, but not the full column.
    A B ... E F G .... X
    Positive 44 =B1 =B2 =B3 ....
    Positive 43 =B13 =B14 =B15 ....
    Negative 40
    Positive 50
    Uncertain 42
    Positive 49
    Negative 20
    .... ...

    Very greatfull for any tips or solutions.

  23. I'm using Excel 2013. I have not found a solution to this problem. I have a spreadsheet with 100 rows. The values in column P are numbers that are independent of each other. They range in value from 1.00 to 60.00. The values in column O range from 0.00 up to the value in column P for that row. I want to have each cell in column O in my worksheet use a red-yellow-green color scale conditional format, where the low value is zero, the middle value is one-half the value in column P for that row, and the high value is the value in column P for that row. I can create this conditional format for any one row easy enough. I can't find any way to copy it to the other 99 rows because the cell references in the color scale conditional format need to be absolute. It would be a lot of manual work to create this conditional format for each row one at a time. Is there a lower-effort mechanism available to do this?

  24. in my worksheet, i have two columns, one is alphabet (column B) and the other one is numeric (Column C).
    my condition is:
    if column B is L, then column C should be 1
    if column B is V, then column C should be 2
    if column B is T, then column C should be 3
    I WANT TO SATISFY THIS THREE CONDITIONS IN A SINGLE FORMULA, IS IT POSSIBLE?
    PLS GUIDE ME

  25. Oops the table didn't appear as it should be...

  26. Hello,

    I have a workbook in excel and I want to know what article/type/value which is clicked or typed frequently in excel. But, I don't know how. can you help me?

    Thanks :)

  27. Hello,

    I have a workbook in excel and I want to know what article/type/value which is clicked or typed frequently in excel. But, I don't know how. can you help me?

    Thanks :)

  28. Hi,

    I want to format a column of cells to fill in depending on when a specified date in another column has passed. For example I input the date into column "b" and then Seven days after that date has passed, I want Columns D,E,F to fill green. I am having trouble creating a formula depending on a date, any help offered would be appreciated.

    Thanks!

  29. Hi,

    I want to format a column of percentages, dependent on the column of percentages to the left of them.

    Highlight in green if the value is between 2% and -2%
    Highlight in amber if the value is between 2% and 4%, and -2% and -4%
    Highlight in red if the value is above 4%, or below -4%

    I'm sure this is easy, but i cannot get my head round it. I think it could be to do with how the rules are ordered in the manage rules section, but not sure.

    Thanks.

  30. Oops the table didn't appear as supposed;

    Output 1 Output 2 Addition to column B
    260,1 201,123 b2
    262,1 242,105 y4-NH3+2
    330,2 250,618 y4+2
    338,1 269,161 y2
    351,1 306,134 y5-H2O+2
    372,1 306,625 y5-NH3+2
    372,8 315,139 y5+2
    416,1 320,197 a3
    418,1 330,181 b3-H2O
    348,192 b3
    370,655 y6-H2O+2
    371,147 y6-NH3+2
    372,170 y3
    379,660 y6+2
    417,213 a4-NH3
    427,197 y7-H2O+2
    427,689 y7-NH3+2
    434,240 a4
    436,202 y7+2
    444,224 b4-H2O

  31. Hi there,

    I'm trying to compare column A with a table, for example column B and if there is a match the match should be colored. Column B contains a output that had several decimals. The match does not need to be exact, ideally I would like to scan for a range of about +/-0.5 of column B. Does anyone have a suggestion how to approach this?

    ColumnA ColumnB ColumnC
    Output1 Output2 Addition to column B
    260,1 201,123 b2
    262,1 242,105 y4-NH3+2
    330,2 250,618 y4+2
    338,1 269,161 y2
    351,1 306,134 y5-H2O+2
    372,1 306,625 y5-NH3+2
    372,8 315,139 y5+2
    416,1 320,197 a3
    418,1 330,181 b3-H2O
    348,192 b3
    370,655 y6-H2O+2
    371,147 y6-NH3+2
    372,170 y3
    379,660 y6+2
    417,213 a4-NH3
    427,197 y7-H2O+2
    427,689 y7-NH3+2
    434,240 a4
    436,202 y7+2
    444,224 b4-H2O

    For example 330.2 matches 330,181 (with addition b3-H2O)and so they should be filled in with red. 372.1 matches with 372,170 and thus y3, the should be colored orange etc.

    Look forward to any help,

    Thanks, regards,

    G

    1. Oops the table didn't appear as it should be..

    2. Hmm the table didn't come out as it supposed to..

      Output 1 Output 2 Addition to column B
      260,1 201,123 b2
      262,1 242,105 y4-NH3+2
      330,2 250,618 y4+2
      338,1 269,161 y2
      351,1 306,134 y5-H2O+2
      372,1 306,625 y5-NH3+2
      372,8 315,139 y5+2
      416,1 320,197 a3
      418,1 330,181 b3-H2O
      348,192 b3
      370,655 y6-H2O+2
      371,147 y6-NH3+2
      372,170 y3
      379,660 y6+2
      417,213 a4-NH3
      427,197 y7-H2O+2
      427,689 y7-NH3+2
      434,240 a4
      436,202 y7+2
      444,224 b4-H2O

    3. Output 1 Output 2 Addition to column B
      260,1 201,123 b2
      262,1 242,105 y4-NH3+2
      330,2 250,618 y4+2
      338,1 269,161 y2
      351,1 306,134 y5-H2O+2
      372,1 306,625 y5-NH3+2
      372,8 315,139 y5+2
      416,1 320,197 a3
      418,1 330,181 b3-H2O
      348,192 b3
      370,655 y6-H2O+2
      371,147 y6-NH3+2
      372,170 y3
      379,660 y6+2
      417,213 a4-NH3
      427,197 y7-H2O+2
      427,689 y7-NH3+2
      434,240 a4
      436,202 y7+2
      444,224 b4-H2O

  32. Is it possible to give a colum like "stable" or "ascending" the directional icons?
    Like if stays "stable" then must be ↔ and for "ascending" ↑.
    I i want use now the directional icons, i must give a number. I want in text.

    1. I solved this problem with using VLookup

  33. Please help me, I have a spreadsheet,eg A1:P245, I would like to be able to do is delete/clear any cells that contains a particular text.

    For example the cells may contain the following data
    P1abc P1gvh p2erg P3fgh sro34 sro56 srj67

    How do I clear the cell that start with P
    How do i clear the cells that contains ro

    Thanks you.

  34. I have text from a drop down list (NO and YES) in Cell B2 that I want to use as the basis for conditional formatting elsewhere in the spreadsheet (to color a number of cells in a certain row red), but the formula (=$b2="YES") does not seem to recognize the text - if I insert the text manually rather than use the drop down list it works just fine. Any suggestions?

    Thank you.

  35. Hi..
    I want to change a entire row color for a cell value of the row... If i have 'fail' in a certain cell (D1) then i want to highlight row 1. How can I do that?

    1. Hi Nazmul,

      Simply select all the rows you want to highlight and create a rule with this formula:
      =$D1="fail"

      1. Hi Svetlana,
        Thanks a lot..it works..

  36. Hi Svetlana,

    I am trying to do conditional formatting of email addresses to quickly identify addresses from gmail, hotmail, and yahoo. After a lot of looking online, I found a formula I could use:

    =NOT(ISERR(SEARCH(Sheet2!$A1:$A3,A1)))

    (Sheet 2 A1 - A3 are: A1= gmail.com, A2= hotmail.com, A3 = yahoo.com)

    However, when I put this in my conditional formatting - it only highlights those addresses with gmail in them and ignores the rest. I'm not sure what I am doing wrong. Any ideas?

    -Michael

    1. Hi Michael,

      The point is that you have an array formula and array formulas can't be used in conditional formatting rules. In your case, the formula turns into =NOT(ISERR(SEARCH(Sheet2!$A1,A1))) and highlights only gmail addresses.

      You can pick the domain name in a row and use the MATCH function to find matches in your lookup table using the following formula:
      =MATCH(IFERROR(RIGHT(A1,LEN(A1)-FIND("@",A1)),""),Sheet2!$A$1:$A$3,0)>=1

      1. You rock my world. Remind to me buy you a beer if we ever meet. :)

  37. How do I conditionally format a table so that in column J when the word "Complete" is entered it highlights rows C to J in green?

  38. Hi Svetlana, Great blog here!

    I think my problem is quite simple, and may have been covered in earlier examples but I am still unclear through trying!?

    I have a value of "Yes" or "No" in Column F, and want to colour this column/these cells based on a date in the same row, Column D:

    Amber: If 30 days overdue i.e. <NOW()-30
    Red: If 60 days overdue i.e. <NOW()-60

    Can you help??

    Many thanks!

    Ashley

    1. ...oops forgot to say, I wish to colour it when it says "Yes". "No" should be left unformatted

      1. Hi Ashley,

        You can combine these 2 conditions using the AND function, like this:

        Red: =AND(TODAY()-$D2>60, $F2="yes")

        Amber: =AND(TODAY()-$D2>30, $F2="yes")

        Please note that the Red rule shall come first in the list of rules.

        1. Great, that works a treat. Thank you for your timely help :)

  39. Hi Svetlana

    I am trying to change the cell colour of rows based on the change in value. Similar to Subtotal.... for each change in column B....

    so if I have
    1
    1
    1
    2
    3
    3
    3
    3

    I would like the colour for Rows 1 - 3 to be red, then white for 4th row then red for row 5 - 8

    Is it possible?

    Thanks

  40. Hello,

    I am trying to format a cell if it is greater than another cell. Basically if the result is greater than the criteria?

    Criteria Results
    0.7 0.220 0.213 0.555 3.240
    5 0.800 0.064 0.324 0.585

    Any help on why this formula isnt working??
    =AND(ISNUMBER(B2),ISNUMBER($A2),B2>$A2)
    Where Cell A is the criteria and B is the result

    Thanks,

  41. if i have one date, that date is smaller than other date. for this which formula i can use

  42. Sir,
    I am having spreadsheet containing D cell value K cell value , what is the formula for if K value is not equal to D cell value ?

    Please reply.

    K.R. Ranganath

    1. Hi Ranganath,

      Assuming that row 2 is your first row with date, here's the formula:

      =K2<>D2

  43. Hi there,

    I don't know if you can help me but it's worth a try! I am looking for a conditional formatting formula that will help me do the following...

    If cell A1 is a colour (i.e. determined by conditional formatting rules) then make another cell the same colour without changing the original function in the second cell.

    Thanks!

    Ian.

  44. Hello

    If I want to write a conditional formatting that for example it should find for me if the fifth number in a certain cell is 0 then make the cell red
    12340
    or1,2340
    how do I write it

  45. hi
    if i have two dates on one cell. i need to change the color if the send date added

    1. i mean 2nd date added

  46. HI,
    I want to color a cell based on another cell value.
    if cell D1 contains "false" I want to get red color in cell A1.
    How can i do that?

    Thanks
    Nazmul

    1. Hi Nazmul,

      Simply select entire rows and create a conditional formatting rule using the same formula.

    2. Hi Nazmul,

      If FALSE is entered in D1 as a logical value (Boolean), then you can create a rule for cell A1 (or multiple cells in column A) with the formula: =D1=false

      If you have "false" as a usual text, then enclose it in double quotes in the formula:
      =D1="false"

      1. Hi Svetlana,

        Thanks..it works.

        I have another problem. I want to change a row color for a cell value of those row..How can I do that?

        Nazmul

  47. Is it possible to include multiple conditions using both AND and OR operators in the formula for conditional formatting. Can you please provide a syntax for this.?
    Thank you in advance!

    1. Hello Ahamed,

      Of course, it is possible. One an AND function can be one of the conditions in the OR function and vice versa. For example:

      =AND(B1=2,OR(C1=3,C1=4))
      =OR(AND(B1=2,C1=3),C1=5)

  48. HI,

    I have a spreadsheet for cashflow that tracks down my everyday expenses. On another tab, i have a consolidated tracker that includes my businesses and investments. The problem is i keep on substituting a certain entry in the consolidated tracker because i dont know how to reflect the latest cash i have base on the cash flow tab as of the current date. Here is the example:
    Cell A Cell B

    20Nov14 $500
    21Nov14 $400
    22Nov14 $350
    23Nov14 $100

    cash as of today: ????

    I wanted excel to always pickup the cash based on the current date. Let's say today is 23Nov14.

    Thanks,
    Ben

    1. Hi Ben,

      Please clarify what exactly result you want to get: expenses for 23-Nov-14 ($100), a sum for the previous days in November or something else?

  49. Hi,
    I have a formula for my sheet with vlookup..for ex:In the cell A2 if i type "Apple" than the rest of the cells in the row gets filled up automatically like B2 with "fruit"
    C2 with"Red".But if SOMEONE TRIES TO CHANGE THE FORMULA for ex.. enter something other than the "fruit" in B2 or other than "Red" in C2 the color of the particular cell should change to red and No fill when they change the values back to formula...

    Thank you very much in advance!

  50. The techniques you outlined work great unless you have data in merged cells. My "applied to" cells are merged. It appears that the formatting chosen in the merge dominates the conditional formatting. If I set up my conditional formatting, then double click in the cell, I can see the result of the conditional format, however as soon as I leave the cell, the formatting is gone. Any way to remedy this?

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 :)