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 41. Total comments: 1726

  1. Hi
    I am trying to show icons for what I would think would be a simple task but I can't seem to make it work.
    In column "N" I have a current rate percentage. In column "O" I have a target rate percentage. I want to use the icons to show a green check if the target has been met, a yellow exclamation point if the target has not been met, and a red stop light if the current rate is 50% or more below the target rate. Basically just showing which rates have been met, are almost there, and are in danger of not being met.
    Can anyone help with this?
    Thanks,
    Heather

    1. Hi Heather,
      If you're content with just using color indicators instead of icons, the following should help:
      ***Please note, I'm using Office 2007, so this answer may vary depending upon which version you're using.***
      Assuming your data starts on row 2 (N2 & O2 in the example you describe; perhaps because you have a column header title), you should be able to get the appropriate colors to show by entering the following formulas into the Conditional Formatting -> "Use a formula to determine which cells to format" in this order:
      1) =$N2 Fill -> Background Color to RED)
      2) =$N2 Fill -> Background Color to YELLOW)
      3) =$O2 Fill -> Background Color to GREEN)
      Unfortunately, it'll take me a bit more time to figure out the solution using the icons you requested. I'm actually at work right now, was on this blog searching for an answer to a question of my own, ran across your question & figured I may be of service within a reasonable amount of time.

      I hope that helps.

      Best regards,

      Randy

      1. Hi again Heather,
        I see that the equations I typed got messed up (not sure why), so I'm going to attempt to type these again, this time with spaces between items. Please note that the other info remains the same.
        1) = $N2 < $O2 / 2
        2) = $N2 < $O2
        3) = $O2 <= $N2

        Best regards,

        Randy

        1. Thank you Randy! I will give that a try. My boss had her heart set on using the specific icons instead of colors, however I think I have convinced her that it just doesn't seem possible. :)
          Sorry for the delayed response, been in training.

          Heather

  2. I am wanting to conditionally format case sensitive values. For example, I want upper case "F" to be in green text and lower case "f" to be in blue text. I am using Excel 2010 and so far have not been able to google-cure my problem. Can you help?

    1. Hello Sarah,

      Please use the Exact function and create two rules using these formulas:
      Green: =EXACT(H8, "F")
      Blue: =EXACT(H8, "f")

      1. Alexander, thank you for the help. I discovered that my problem was not related to the formula (as I had tried the one you gave above) but that in place of the cell number I was putting in the full array of cells i wanted the formula applied to. I should have left it as exactly the formula you have above and then applied that formula to the array of cells, not actually include the array of cell names in the formula.... if you get what i mean.

        Thanks for your help!!!

  3. I have two columns, column A has various dates entered, column B has a formula =A1+30 meaning I want to see a date that is 30 days past the date in column A. I would like to add a conditional format to highlight the cells in column B when they have reached that 30 day mark. How do I do that?

    1. Hello Betty,

      Select column B and create a rule using the formula below to mark only today's date:
      =B1=today()

      If you need to mark today and past dates, then please use this formula:
      =B1<=today()

  4. Hi Svetlana,

    A very happy new year to you!!

    Please can you help on below query.

    Date From Organisation Details/Requirements Sent to Report Status
    10/02/2015 Pending
    14/02/2015

    A is a date column that records the email receive date and F shows the status pending.

    Now i want F to turn "pending" word in red if it crosses 5 days from date in A column. Please can you advise if i can conditional format it and how.

    Thanks,
    Rachana

    1. Hello Rachana,

      Please select your column F from F2 to the end and then create a conditional formatting rule using the following formula:
      =AND($F2="pending",$A2+5

  5. Hi Svetlana, Thanks for this article, it's very helpful. I just wanted to ask you how to highlight one empty cell in a column (say "B") when other cell(s) (one or more, say "A" & "C") in the same raw is filled with some values. And the highlight remains until the cell in "B" is filled with some values. Is that possible? your reply will be very helpful and is highly appreciated. Thanks in advance..

    1. Hello John,

      Select column B and create a rule using this formula:
      =AND($B2="",$C2<>"",$A2<>"")

      1. Hello Alexander,
        Thank you very much for the formula, this is the exact one which I've been looking for. It really works !!! Thank you once again for your time for helping me...

  6. Hi. This is a very helpful article. I am having difficulty solving this. I have a table where every cell is a formula. Some are straight references to cells, "=C2", and some are vlookups, "=VLOOKUP(C2,...)".

    I would like to highlight the cell only if the formula is a vlookup. Everything I see indicates that the conditions refer to the calculated values not the formulas themselves. I cannot use ISFORMULA() since all cells have formulas.

    I appreciate any help.

    1. Hello Curt,

      If you use Excel 2013, then you are lucky. To fulfill your task, just select the table and create a rule using this formula:
      =ISERROR(SEARCH("vlookup(",FORMULATEXT(A2)))=FALSE

      Where A2 is the top-left cell in the selection.

      If you have Excel 2010 or earlier, then you need to replace FORMULATEXT with VBA user-defined function (UDF)

      Function formulatext2(cellSrc As Range)
      formulatext2 = cellSrc.Formula
      End Function

      If you are not quite familiar with VBA, please see how to add a VBA macro (UDF) to your worksheet here:
      https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/

      Replace FORMULATEXT with FORMULATEXT2 correspondingly:
      =ISERROR(SEARCH("vlookup(",FORMULATEXT2(A2)))=FALSE

      1. I appreciate the quick and helpful reply. I do have Excel 2013 and your solution worked perfectly. Unfortunately, my client uses Excel 2010 and will not allow vba in the workbook I am creating. So I am back to square one. If you have other suggestions I would be anxious to try them.

        Thanks.

        1. I am sorry, but I don’t have any other suggestions.

  7. So if i want color fill in other cells by follow the date that i am input, How can i setup the formatting formulas?

  8. Hi, I've setup a training spreadsheet and added conditional formatting so that the cell turns red if the expiry date has passed (but only if the cell contains a value.

    =IF((AND(K11<TODAY(),K11"")),TRUE,FALSE)

    Trying to setup an additional rule that turns it yellow if that expiry date comes within 2 months of the current date but not sure on the details of the formula. i thought something like this maybe but its not working.

    =IF((AND(K11<TODAY+60(),K11"")),TRUE,FALSE)

    Any help would be greatly appreciated.
    Thanks

    1. Hi Steven,

      The formula is correct except that parentheses should be after TODAY(), not after 60. Please try this one:

      =IF(AND(K11<TODAY()+60, K11<>""),TRUE,FALSE)

  9. I am trying to figure how to format the first cell in each row so that it will change to a color when all the cells are populated within that row (columns A-R). Some of the cells have text, some have numbers, and some have both. I am wanting to do this so that I know which rows are complete, without having to scroll back and forth and visualizing each one.
    Each month the number of rows I have varies, so it may be 90 one month, it may be 130 another. The first row is a title/header row. The first column is populated with names. Any ideas?
    Thanks!

    1. Hello Christine,

      Select the column A (from A2 till the end) and create a conditional formatting rule using this formula:
      =COUNTBLANK($B2:$R2)=0

  10. hi,

    i have this check list with a checkbox, and i need to know how i can make the question status cell to display a text(done, pending) while changing the cell color (red to green),when i select one of the checkbox answer.

    ex. do you have a list of traces on PCB? []yes []no []n/a [question status]

  11. I am using MS Excel 2010. I want to use conditional formatting for B8 to B21 ( Format only Top-Green and Bottom-Red ranked values ) excluding 0.

  12. Hi,
    Greetings.

    in condition formatting check the cell value, if it is true assigned zero in another cell how?
    Please kindly send the formula

    Thanks
    Ali Ahammad.

  13. please help me
    I have this problem ( I create a conditional formatting to cell $I$2 (((cell value - less than - =$H$2-$H$2*10% )))

    is there away to create a conditional formatting to all cell below depend on the right cell like this

    (((cell value - less than - =$H$3-$H$3*10% ))) to cell $I$3
    (((cell value - less than - =$H$4-$H$4*10% ))) to cell $I$4
    (((cell value - less than - =$H$5-$H$5*10% ))) to cell $I$5

  14. This is a great list of conditional formatting uses! I have one I would like to see if you can tackle. I would like to highlight a range of values (item numbers) for a list of specific item numbers that qualify. The list may be small or it may be quite large. Would I need to do an OR function for each of the items in the list or is there a simpler way to get this done? Thanks!

    1. GREAT columb. More insightful than the videos. Thanks.

  15. conditional formatting for cell B1- if cell A1=Optimize and B1 is greater than 0, red (or whatever)

    A1 B1
    Optimize 1

    1. Use this Formula

      =AND($B1>0, $A1="Optimize")
      Select B1 Column.

  16. sorry for the double post. it changed.

    Hi there..
    Been stuck with my project already.
    I have a value in column A.
    And i need to apply a 3-color scale on column B depending on the value of column A.
    Condition:
    If column A=<1 - column B turns red
    if column A=<the half of A - column B turns yellow
    if column A= the value of A - column B turns dirty white

    i think this is simple but im getting invalid formula.
    why's that? this is what i've been doing

    on column B3, i enter this
    =$A$3=$A$3

  17. Hi there..
    Been stuck with my project already.
    I have a value in column A.
    And i need to apply a 3-color scale on column B depending on the value of column A.
    Condition:
    If column A=<1 - column B turns red
    if column A= the value of A - column B turns dirty white

    i think this is simple but im getting invalid formula.
    why's that? this is what i've been doing

    on column B3, i enter this
    =$A$3=<1
    =$A$3=$A$3

  18. Hi,

    I am in desperate need of some help with something if possible.

    I am trying to conditionally format cells in a column (E, range 3-96) to:

    -turn black if the cell next to it in column D is blank and the cell in column E is also blank.
    -turn orange if the cell next to it in column D is blank but the cell in column E contains a time.

    Any ideas?

    Thanks in advance!

  19. I would like to change my text value in column E and F based on the text entered into any cell in column F.

    For example: If I enter in any cell in column F "BBL" I would like the "BBL" in column F to be red as well as any text I enter in column E but only in that specific row. I would like this formatting to work for the entire spreadsheet.

    1. Hi there..
      Been stuck with my project already.
      I have a value in column A.
      And i need to apply a 3-color scale on column B depending on the value of column A.
      Condition:
      If column A=<1 - column B turns red
      if column A=the value of column A - column B turns dirty white

      i think this is simple but im getting invalid formula.
      why's that? this is what i've been doing

      on column B3, i enter this
      =$A$3=<1
      =$A$3=$A$3

  20. Hi, I want to conditional formating rows based off of what is in two different columns

    Column1 Column2
    1 NG
    2 NG
    1 OK
    3 NG
    2 NG
    4 OK

    I have it set up right now that if its OK it hightlights the row gray and if NG it shows orange. I want to make it that if another entry is entered and it is OK but the first time it is NG it will change the row to gray. So for the example the first row 1 is orange because of NG but when you add in the second 1 with OK it will change the first row 1 to gray also.

    Thanks for the help!!

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

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

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

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

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

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

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

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

  29. 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)")

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

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

  32. 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,

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

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

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

  36. 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!

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

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

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

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

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

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

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

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

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

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

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

  48. 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!

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

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

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