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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  19. Is there any formula in this regard?

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

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

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

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

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

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

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

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

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

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

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

  31. In the,
    ****Excel formulas to work with text value****
    section,

    I found,
    >0 selects a string containing and beginning with the specified word.

    Whereas, >1 selects the string only containing and not beginning with the specified word.

    Correct me if I misunderstood something.

  32. Hello,
    I was hoping you can help create a formula/condition rule. I need an entire column “O” to return a value based off a digit I put in it a cell IN COLULMN O.
    For example:
    If I type 1 in O5 I want O5 to return a value of 1.08
    If I type 60 in O50 I want O50 to return a value of 65

    Similarly in column Q- If I type “Y” in the cell, I want the cell to return a value of 65. For this I set a condition rule =If(Q1:Q25=“Y”,65,0) to be applied over Q1:Q25. Needless to say- it didn’t work!

    Can you help me please???

  33. Hello,
    I was hoping you can help create a formula/condition rule. I need an entire column “O” to return a value based off a digit I put in it a cell IN COLULMN O.
    For example:
    If I type 1 in O5 I want O5 to return a value of 1.08
    If I type 60 in O50 I want O50 to return a value of 65

    Similarly in column Q- If I type “Y” in the cell, I want the cell to return a value of 65. For this I set a condition rule =If(Q1:Q25=“Y”,65,0) to be applied over Q1:Q25. Needless to say- it didn’t work!

  34. Hello

    I am trying to find a formula to find the greater value between two dates. between two columns in the same cell. I know I can use conditional formatting>Highlight Cells rule>Greater Than, however I am trying to do this on a spreadsheet with over 1000 lines. is there any way I can use a formula to help me to this in one go without having to do the above line by line

    thank you

    1. David:
      You don't have to apply conditional formatting line by line. You can select all the cells that hold the calculation that you need formatted and apply the formatting to them all at once. The conditional formatting rule is the formula.
      You can select all the cells in the column by clicking the column heading.

  35. Hello,
    I have two sheets in excel 2007, where I enter via barcode scanner serial numbers of devices, one sheet is for direct sales and the other sheet is for credit sales.
    I need a way to validate for duplicate s/n between two sheets. At this moment I am using the formula =COUNTIF(sheet1!d5:sj30000), like conditional formatting highlighting the duplicated cell. The system find duplicates very good, but becomes very, very slow.
    Now I want to validate at specific time, for example in the night, when the system is not used.
    Can you help me to setup time driven validation formula to trigger at specific time.
    Thank you very much for your time.

  36. I am looking for help on creating a conditional format where I am color coding a row based on a # the user inputs. These color codes (1 - 6) are used to indicate the order is in a particular status as they determine.

    Some cells on the row will already be highlighted in vbLavender. These are set by a different process to indicate something has changed with this data. I don't want the conditional formatting to cover this.

    So for example cell BB44 has the lavender background.

    Currently this is my VB code for the conditional formatting for color 5:

    Selection.FormatConditions(1).StopIfTrue = False
    Range("A2:BW99999").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BT2 = 5"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.399914548173467
    End With

    So if in cell BT44 I enter a 5 the row is shaded blue.
    This also shades BB44 as blue. If I delete the 5 the lavender color is still there. How would I change the above to not color cells that have an existing color or vbLavender?

    Any suggestions would be greatly appreciated :-)

    1. John:
      VBA help is beyond the scope of this blog, but there are many sites where you can find VBA help. Just Google "VBA Format Conditions" and you will find them.

  37. I have some formatting set up so, if for example,I have cell B8 highlighted green with the letter "w" in it, any cell I put a W in will turn green as well. Where I am stuck is how do I get an adjacent cell to turn that same color without putting a "w" in it. For example. if I put a "w" in cell D8 and it turns green, how do I get cell D9 to turn green based on D8's value while leaving cell D8 blank?

    Thanks!

    1. Todd:
      I think this will work. Select D9 then Conditional Formatting then New Rule then Use a formula to determine which cells to format then in the formula field enter =D8="W" then select the green formatting you'd like to see.

  38. I really appreciate you posting this tutorial Svetlana. It has made my work (and others here) a lot easier, keep up the great work! I will consider subscribing to Ablebits, you guys rock!

  39. hi
    i want to create a conditional formatting for following
    Column 1 Contains Date of shipment
    Column 2 Contains contains a (tick) which confirm if the shipment is sailed.

    Column 1 should turn red when Shipment date occurs
    and Column 1 thr should be no formate if Column 2 contains a Tick

    Please help

  40. hi everyone,
    i want to create conditional formatting for the following:
    Column 1 contains due date
    Column 2 contains actual date
    Column 3 would contain conditional formatting if Column 1 > Column 2
    please help
    Thanks in advance

  41. Hi all,

    I was wondering if anyone could help me with a conditional formatting formula for the following information?

    I want to compare a 'Grade(1-9)' column with a 'Tgt' column and have it showing as red if it the 'Grade(1-9)' column is less than the 'Tgt'.

    Here is an example of the information in both columns:

    Tgt: 8D 7D 6S 7D
    Grade(1-9): 8D 6A 6E 6E

    Therefore, the 6A 6E and 6E in the 'Grade(1-9)' column I would like to highlight as red as it is less.

    Is this possible?

    The current grading from highest to lowest is:
    9A 9S 9D 9E 8A 8S 8D 8E 7A 7S 7D 7E 6A 6S 6D 6E 5A 5S 5D 5E 4A 4S 4D 4E 3A 3S 3D 3E 2A 2S 2D 2E 1A 1S 1D 1E U

    Would the formula change for each different Tgt value or is there an one fits all formula?

    Thanks for any support or even just a no it wouldn't be possible!

    1. Sorry I forgot to mention that the 'Grade(1-9)' values start at cell E6 and 'Tgt' values start at B6.

  42. Restating my previous question for better clarity -

    I want to format cells in column based on two different conditions applying to the cell in the same row in column A, but the conditions are complicated and I don't know they would look like in a formula. The two conditions are:

    1. If the value in column A is the highest value in column A
    2. If the value in column A is a duplicate value with another value in column A

    ...in other words, if there is "a tie at first place" in column A, I want to make the cells in the NEXT column (column B)for those rows yellow.

    I know my formula for the conditional formatting has to include =AND($A1,)but I am at a loss as to how to describe my two conditions in the formula.

    Thank you for any help you can provide with this knotty problem!

  43. I want to format cells in column J based on two different conditions in applying to the cell in the same row in column I, but the conditions are complicated and I don't know they would look like in a formula. The two conditions are:

    1. If the value in column I is the highest value in column I
    2. If the value in column I is a duplicate value with another value in column I

    ...in other words, if there is "a tie at first place" in column I.

    I know my formula for the conditional formatting has to include =AND($I1,)but I am at a loss as to how to describe my two conditions in the formula.

  44. I am trying to apply conditional formatting based on the following and am getting very confused!

    I want column A to change colour based on the following:

    Col D has a date – YELLOW
    Col E has "Y" – ORANGE
    Col F has a date – PINK
    Col G has a date – DARK BLUE
    Col H has a date – DARK GREEN
    Col I has a value – LIGHT GREEN

    The process should be, as each column has a value or date entered it changes to that corresponding colour, can you help?

  45. Hello

    I am creating a vehicle fleet inspection chart. Vehicles are required to be serviced within every six weeks.
    I have plotted a row of six weekly reference dates for the year based on the last service date from the previous year (E18:N18). Giving me ten dates. The dates in this row indicate that the vehicle must be serviced by each of these dates.
    I have also added a todays date cell (C17)

    I have conditionally formatted these cells to:
    > change to orange if a date is entered that was later than the reference cell.
    > change to green if a date is entered that is less than or equal to the reference cell.
    > no formatting for blank cells and stop if true.

    However, I still require the cells (E21:N21) to change to red if the cell is blank and the reference dates above (E18:N18) are less than todays date. This would show me that no service took place. How can I do this please?

  46. Hi,
    I would like to know the formula for the following.

    Plan Submission Overdue Actual Submit Date

    9-Feb-18 13 13/2/2018
    8-Feb-18 14 13/2/2018
    5-Feb-18 17 5/2/2018
    5-Feb-18 17 5/2/2018
    1-Mar-18 -7

    Condition as follows :
    >>If Actual submit date is blank and overdue (due previous week) , Plan submission date highlight grey.
    >>If actual Submit date is blank and overdue this week, plan submission date highlight grey.
    >>If actual submit date not blank, no need to highlight.

    Hope you can help with this. thanks!

  47. Thanks so much, This article was very much helpful.

  48. My spreadsheet has a last revision date column with a date and a "Date of next review" column next to it with the formula of =S114+365. I would like to have the "Date of next..." to be highlighted if it is within 30-60 days yellow and less than 30 days in the future as red

    formula =AND($A2-TODAY()>=30, $A2-TODAY()=1, $A2-TODAY()<30)

    isn't cooperating, any help is much appreciated

  49. Hello,

    i m trying t create an auto border with conditional formatting, since my cells are havign formulas, the border are getting displayed, even though the cells are empty.

    i have to make the cells between B21 to h35, whenever my results there in the rows the border should be auto

    the following formula i have tried, with various methods, formats etc...

    =OR($B21"",C21"",D21"",E21"",F21"",

    Please guide me

    thanks in advance

  50. Hi,

    I am trying to format cells that are a formula =(A1/B1) with percentage as a result in the C-column.
    I am using Icon sets and have put in;
    red 80-100%
    yellow 50-79%
    red 0-49%
    I have tried both with Number, Percentage, Percentile and Formula.
    It works only when I have one cell that has become 100%, otherwise it will not show the right color. It is like the colours only works in relation to the highest amount put into the column? I do not always have the 100% but I still wish it to work according to the colors.

    Please help, I hope I have made this clear enough.
    Sofie

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