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 16. Total comments: 1709

  1. Sir,
    I am trying to format my datasheet on the criteria based on only three types of values in an entire table (a range of cells). If one cell contains "A1" then it should be green background. If other cell contains "B1" then all cells that contains B1 should be color as yellow. And lastly, if other cells contains "C1" then it should be Red of all cells that containts C1.

    Please Help

  2. I am trying to conditionally format a data set based on a specific cell. I have data from A3:G78 and I want to highlight rows if AA3 is not blank. I have tried including the cell in the data, I've tried "=isblank($AA$3)=false", "=$AA$3""". Nothing seems to work. Any suggestions?

  3. Hi, I have some data in excel and this data is alpha numerical ( GST TIN) how Can I used conditional formatting for strikethrough for only 15 alpa numerical digit because GST TIN is only 15 digit.

  4. Was curious as to how would you go about finding duplicates in two different columns, then highlighting those matches based on a 3rd column's data, ex: I want to match two lists of sites, and find the duplicates once those are found I want to highlight sites on one of the columns based on a value on a 3rd column, thanks! Great post by the way.

  5. I have different data arranged in the same data array that runs from A1 to BQ158 on multiple sheets. I would like to highlight cells on Sheet 2 if the value on Sheet 1 is greater than a set number (e.g., 30). For example, I want Cell C100 on Sheet 2 to be highlighted if the value in C100 on Sheet 1 is greater than 30. I need all cells from A1 to BQ158 to be checked in a similar fashion across 5 different sheets. Is there a way to quickly do this so that each cell is checked with the same corresponding cell on another page. Thanks

  6. There are at least a third of the sales in a row painted yellow.

  7. Sir, in excel sheet one row contains 50 columns. I wand to check the cells with numbers greater than zero. Kindly send a formula. Also I need suggestion to check all rows in that sheet.

    1. Sir, send me a formula for conditional formatting for Blank and No Blank cells.

  8. Hi. I am trying to set a the background colour behind a pivot table. I have found a thread which used conditional formatting [Cell Value] [equal to] [=""""] and then selecting the colour... but I can't get it to work, is there another way to have a background colour on my dashboard which ensures that the colour remains when the pivot table is filtered

  9. I am trying to create a conditional formating for a report I generate for my team to compare 2 different cells and highlight when the two values are different by +/- 20%. I am not sure if this is possible based on everything I am finding though. Any recommendations or suggestions on the formula or where to look?

  10. Apologies for the duplication. There was an omission in the earlier post. this is the correct version. Thank you. I am working on a particular cell, say B11 that is to return or retain entered value based on conditions in cells F5 (Kings and Queens) and B7 (Low, Medium and High. when I enter a figure in this cell (B11) between 90 and 20 which are grades say 98, it must return 90 and 20 when I enter any figure below 20, say 17 when F5=Queens and B7=High. But when I enter 77, it returns 77. If F5=Queens and B7= Medium, I want to enter a figure in this cell between 60 and 15. If I enter 12, it must return 15 and if 65, must enter 60 in B11, but if I enter 48, it retains 48. Lastly, if F5=Kings and B7= Low, Medium or High, it must enter 20 no matter the figure I enter in B11. Can I use a single formula for each condition or combined into a single formula? I don't want to use a vba. Thank you

  11. I am working on a particular cell, say B11 that is to return or retain entered value based on conditions in cells F5 (Kings and Queens) and B7 (Low, Medium and High. when I enter a figure in this cell (B11) between 90 and 20 which are grades say 98, it must return 90 and 20 when I enter any figure below 17 when F5=Queens and B7=High. But when I enter 77, it returns 77. If F5=Queens and B7= Medium, I want to enter a figure in this cell between 60 and 15. If I enter 12, it must return 15 and if 65, must enter 60 in B11, but if I enter 48, it retains 48. Lastly, if F5=Kings and B7= Low, Medium or High, it must enter 20 no matter the figure I enter in B11. Can I use a single formula for each condition or combined into a single formula? I don't want to use a vba. Thank you

  12. Anyone? I would like to know if what i have in mind is possible
    I have a names on column A
    then b2:ab2 contains the dates for the whole month
    I would like to automatically highlight days of the week based on names on column A
    Example: if a3="Raymond", then highlight cells on row 3 which falls under thursdays and fridays based on cells b2:ab2(they contain the dates. ex Mar01, Mar02, Mar03...)

  13. Select Cell F21
    In conditional formatting, click on new rule.
    Select "use a formula to determine which cells to format"
    =$F$21>(0.02*$B$21)
    Format with the Format button
    Click OK

  14. I am trying to format a cell to return 0 if the number is less than 10 and return the exact number if equal or greater than 10. How do I go about it?

    1. =if(a1<10,0,a1)
      but that formula should be in another cell, ie: enter this in cell b1

  15. I'm trying to conditional format a cell based on the value of another cell. For example, Cell C2 is 50. I want C3 to show Q if greater than or equal to 50 and U if not. I thought I could use =IF(C2>=50, Q, U) but it doesn't work. What am I missing?

    1. You're just missing the "".
      If you want to enter a text from your formula, you should always have "".
      =if(c2>=50,"Q","U")

  16. I am trying to create the following conditional format: =IF(G5=100%,"COMPLETE") so when the % value reaches 100%, the cell will change to complete?

  17. Hello to every one!
    hope so you are good. I am not so so much good in excel but i have some issue.any expert who can help me will be highly appreciable.i tried my best to clear my point

    I have two workbook. in first workbook the name of sheet "sheet1". now data is as below: three columns a b and c
    a b c
    f01 0 1
    f02 8 9
    f03 7 0
    f04 10 6
    f05 5 5
    f06 3 0
    f07 8 7
    f08 0 0
    f09 8 6
    f10 0 0
    upto to f50.... i:e a column have values upto f1 to f50. colm ba nd c have numeric values which are may be zero.

    the second work book have data as below:they have only two column a and b:
    a b
    f05 ?
    f04 ?
    f08 ?
    f10 ?
    f15 ?
    f16 ?
    f20 ?

    1.now i want formula on question mark ins second workbook which is that it get value from fist workbook which is greater then zero(remember c column in first workbook is 1 and b is zero so it also should pick it...i:e greater then 0)matches the exact a column.

    2.And the second condition is which i m facing problem that value and also very important which is the formula picks all the values (a column only) from first work book which have only value greater then zero weather in column b or column c except it shouldn't get the value which contain both the 0.

    i know these are basically two different scenarios. it is possible that both scenarios work in one formula?? if yes then it will be good for me but if not then help in second scenario.

    waiting for your kind response.

    Advance Thank you!

  18. a please tell me is there any option in conditional formatting or elsewhere to insert or add a circle while value is true..........

  19. I am trying to create a conditional format based on a date calculated from a date in a different cell

    so lets say i need a patient to get a test on a specific date prior to the date in B1 06/08/2019. the date of test cell D1 is formulated (=B1-7*20) then in cell E1 has “done” typed cell D1 turns green. But if the date in cell D1 has passed and “done” is not typed in E1 then the cell turns red.

    I am just having a heck of a time because the conditional formatting doesn’t recognize the formula in D1 as a date thus it wont change.

  20. Hi,
    How to use If(isblank) formula when the cell i am linking has an existing formula? The result is always #value since the excel reads it not blank although the value is zero or blank since it has an existing formula. Anyone who can understand this situation? =). You're response will be so much appreciated! Thanks.

  21. Hi,
    I am using excel 2007, I'm trying to use conditional formatting using the below formula. Basically, I want to highlight column A if the other column has negative values.But is not working. It worked on excel 2016. Appreciate your help for any suggestion.

    =IFS($E7<0,TRUE,$F7<0,TRUE,$G7<0,TRUE,$H7<0,TRUE,$I7<0,TRUE,$J7<0,TRUE,$K7<0,TRUE,$L7<0,TRUE,$M7<0,TRUE,$N7<0,TRUE,$O7<0,TRUE,$P7<0,TRUE,$Q7<0,TRUE,$R7<0,TRUE,$S7<0,TRUE,$D7<0,TRUE,$T7<0,TRUE,TRUE,FALSE)

    Thank you so much.

  22. Hi,
    I am trying to highlight cell F21 if the amount in this cell is greater or less than 0.02 of the amount in cell B21. Can you help?

    1. Select Cell F21
      In conditional formatting, click on new rule.
      Select "use a formula to determine which cells to format"
      =$F$21>(0.02*$B$21)
      Format with the Format button
      Click OK

  23. Hi
    I wanted to know if I could use conditional formatting based on a predefined table in excel. The conditional formatting should be able to identify the text in that table, find the text in the defined sheet, and format it to a specific color based on the table's formatting.

  24. Hi Excel Wizards ,
    I am looking for help with creating a formula
    I am trying to format a cell to say PASS & also format the cell to change colour to green based on this answer.

    The cell I am trying to format has to read multiple text values based in a different row any ideas how to go about this or help would be greatly appreciated.

    Regards

    Seamus

  25. Hi
    I have two columns with the conditional format of one cell based on the cell further along the row. I would like to know if they are less equal to or more than. I can do this for one cell. However, when I copy and it down it still references the original cell. How do I make it so that it changes the reference cell?

  26. I have 2 columns, A and B. Column A should always be less than Column B. Can you please help me out?

  27. Hi.
    How do I automatically offset cell values between columns?

  28. Hi
    I really appreciate your site and clear layouts.
    Can you come to my rescue please.
    I would like to use conditional formatting and highlight lowest value in col as below
    A6.6
    A7.7
    A3.6
    A4.0
    I would like A3.6 to be highlighted as it is the lowest value. I've experimented because there is both text and number in the same col but no luck.
    Thanks in anticipation
    Alex

  29. Hi please help.
    I have three columns, ID, Date and Code.
    I want to highlight the rows that have the ID and Date the same and the Code on that date is only T. If on the same date there is a code that is T and T, I do want to highlight it. If on the same date there is a code that is C and T, I do want to highlight it. Thank you.

  30. I urgently need help to calculate and "bonus" on a commission structure.

    This is what I need for example:

    If the SUM of A1+B2 = between 1 & 9, then A1 needs to be multiplied by a certain % however if the SUM of A1 + B2 = between 10 & 19, then A1 needs to be multiplied by a different % and so on.

    I have a sliding scale of 6 percentages.

    0-149999 | 0%
    150000-199999 | 1,25%
    200000-299999 | 1,50%
    300000-399999 | etc...
    400000-799999
    800000 +

    I hope this makes sense and hopefully someone can help

  31. I urgently need help to calculate and "bonus" on a commission structure.

    This is what I need for example:

    If the SUM of A1+B2 = between 1 & 9, then A1 needs to be multiplied by a certain % however if the SUM of A1 + B2 = between 10 & 19, then A1 needs to be multiplied by a different % and so on.

    I have a sliding scale of 6 percentages.

    0-149999 | 0%
    150000-199999 | 1,25%
    200000-299999 | 1,50%
    300000-399999 | etc...
    400000-799999
    800000 +

    I hope this makes sense and hopefully someone can help

    1. I would use a nested formula.
      I would put all of my SUM Values in Column C, The Lowest Number of each range in Column D, and the corresponding percentages in Column E.
      Then, for each calculation, I would put the following formula into Column F.
      =IF($C1>=$D$6,$C1*E6,IF($C1>=$D$5,$C1*$E5,IF($C1>=$D$4,$C1*$E4,IF(...
      Continue Nesting until all sections are considered.

      I hope this helps.

  32. Thank you very much for the help in the topic "Excel formulas to work with text values"

  33. hi i have some sales value and contribution % of sales value and i want to highlight those articles which are contributing 80% of my total sales value how can i do it?

  34. I want conditional formatting to apply in the following instance.

    I want the blank cells in the Range B2:D10 to be highlighted red if the corresponding Cell in Column A contains a value. And if Column A does not contain a value, I want no conditional formatting in the Range B2:D10

    Is this possible?

  35. Hello,

    I have a spreadsheet I'm writing for a complaints database. Responses are meant to be issued within 10 working days.
    I would like the row to highlight if:
    the response deadline (auto-populated date, calculated from complaint date, in cell I3) has passed, AND no response has been made (J3- entered manually as the date of response). I have the whole table, minus headings, selected to format to a shade of pink, and in my conditional formatting formula (which I believe I sourced from somewhere online!) I have:
    =AND($I3<TODAY(),$J3"")

    this works in as much as it highlights the row, but it highlights it whether or not J3 is blank. I have fiddled with the absolute references but that just results in nothing being highlighted at all, even when it should be.

    Please could someone explain to me what the () after today, and the after J3 do to affect the formula, and how I can manipulate it to highlight only if both conditions are met?

    What is also bizarre is that I have rewritten this formula from scratch within the formula box (at the same position in the rules order), and it doesn't do anything, even if both conditions are met. I feel like my version of excel is a bit temperamental and does whatever it feels like!

    I can take screenshots to show what I mean if that's helpful.

    Thanks in advance :)

    Bel

  36. Hello,

    I was wondering if you'd be able to help me. I am trying to use a conditional formatting for two criteria, anything that has tomorrow's date and is a duplicate.

    My spreadsheet has other days from October, but since tomorrow is October 11th, I want the conditional formatting to show me any duplicate numbers for October 11th only and highlight those duplicates for October 11th.

    I appreciate any help you can offer me!

  37. Hi Guys,

    I'm trying to create a rule in which If the Column starts with the number "13", to then make all numbers in the adjacent row negative. Is there such a way to do this?

    Thank you.

  38. How can I use Conditional Formatting, when valve change in A1, B1 change the color.

  39. Is there any formula in this regard?

  40. Hai,

    During 37th week of 2018 [September], the first week of Hijri-1440 falls/starts.

    How to arrive at Hijri [Islamic] week number equivalent to Gregorian week number.

    Appreciate your valuable guidance, thanks,

  41. Conditional Formatting Problem: I have made 8 Rules to color code certain words. 6 of the 8 Rules work great, but 2 do not. I have deleted the 2 Rules & reentered, but still no luck. HELP!

  42. PLEASE FIND THE REQUIREMENT BELOW TO WRITE A FORMULA IN EXCEL,
    IF THE AGE IS BETWEEN 1-35, PRINT 1000,
    IF THE AGE IS BETWEEN 36-40, PRINT 2000,
    IF THE AGE IS BETWEEN 41-45, PRINT 3000,
    IF THE AGE IS BETWEEN 46-50, PRINT 4000

    1. Cell L18 Cell L19
      AGE VALUE
      1 - 35 1000
      36 - 40 2000
      41 - 45 3000
      46 - 50 4000

      =IF(L18<36,"1000",IF(L18<41,"2000",IF(L18<46,"3000",IF(L18<51,"4000",""))))

      1. Arif:
        I think you are looking for something like this:
        =IF(B55<=35,1000,IF(B55<=40,2000,IF(B55<=45,3000,IF(B55<=50,4000,"Out of Range"))))
        Where the value being tested is in B55.
        The "Out of Range" is needed to supply a display message if the value is above 50.

  43. i hava two coloum in excel sheet A and B.
    the value is different in both coloum.
    i need a conditional formatting formula to change color of col B when col A value is lower then col B.
    Single cell color change but when i select the coloum it is not working
    please help..........

    thank you.

  44. How can I use conditional formatting in a single column?
    For instance, using column A entering text in A1, then when text is entered in A2 and hit enter, A1 will have a strikethrough and A2 will not. Enter text in A3, A2 will strikethrough and A3 will not, so on and so forth.

    I appreciate any help.

  45. I have an Excel workbook with two sheets. I need to make column D in Sheet 2 format to fill the background a color if Sheet 1 has the exact same number in Column C. What is the function I type into Conditional Formatting New Rule on Sheet 2? Please and thank you!

  46. Hello, Svetlana

    I'm having some problems using "OR" function. What I need is something like this:

    =OR($D1 = "TEXT 1" , SEARCH("TEXT 2",$D1) > 0)

    "Translating" what I'm trying to do is "I want TRUE when either the D column has the exact value 'TEXT 1' or has 'TEXT 2' written with not necessarily exact match"

    The problem I'm facing here is that if I add the SEARCH function, it only matches the result of the SEARCH function. If I remove it, it matches the "TEXT 1" one.

    Am I missing something here? I'm a programmer and have to say Excel functions have a really terrible syntax. I'm sure it is a very simple issue, but I can't solve it.

    Thanks in advance

  47. Hi,

    I Have a question regarding conditional formatting. I know how to apply a rule that if the value is under a certain amount, the cell value will equal this amount. I now want to apply a rule that if a cell is between a range, the value s equal to a certain formula. Example; if value is between 10000 and 20000, the result is 10000 plus 90% of the portion in excess to 10000.

    Help?

  48. hello
    please I want the A1 and B1 to automatically give me an answer in cell C1
    A1 20 + B1 20 = C1 40
    please how do I format the cells
    I really need your help

    1. Emmanuel:
      If I understand your question you need to enter this formula in C1:
      =A1+B1

  49. Hi
    I have been trying to use AND formula in conditional formatting to change color of cell according to values but its not working.
    i need to change the color of the cell depending on the change in the value of the cell (eg. difference between A2 and B2 should be highlighted in yellow if the difference is between 0 to 2.99%. if its more than 3% difference it should be highlighted in red.) can you please help me out in this

  50. Hi,

    I am have an excel budget sheet with three important rows at the bottom.

    1. a sum of each column.

    2. a row with the copied value of the sum-value above to hava as a control.

    3. a formula that subtract the above values (I would like this value to be zero as a control).

    To the first and third row I would like to have conditional formatting, coloring them red if they are not the right value (i.e: the 1st row to colour red if its not the same as thr 2nd row, and the 3rd row to colour red if its not zero).

    This contitional formatting only applied correct to three of my columns, and three colours red even if the values are correct.

    Please help!! Going nuts here :) Thanks!

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