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 5. Total comments: 1074

  1. I have a concatenation formula that comines two cells into one, the result of the concatenation I want to colour the cell depending on the value, i.e. if the cell equals 1A, 1B, 1C, 2A, 3A etc then colour the cell green, or if its 2B, 3B etc then colour the cell yellow.

    I was using the formula within conditional formatting to say if cell value is equal to =OR("1A", "1B", "1C") but its not working...

    Any help will be appreciated.

    Thanks in advance!

  2. Hii,
    i want information to set conditional format. if B2<A2 THEN B2 get automatically and aslo apply for that entire row. if value B2=A2 then no changes. How to apply it for full Coloum ? pleaes help

  3. Hi, maybe you can help me with
    I have one row with the predefined conditional formatting scale colors (this row contains numbers)
    A row above contains text, and I want it to have the same colors as the numbers row.
    How I can do that without using VBA?
    Regards,

    1. Hello!
      I recommend creating another conditional formatting rule. Select the range to format, starting with the first line of text. For example, line 10 contains text, line 11 contains numbers. Select the $10:$17 row range.
      Write down the conditional formatting formula, for example, =$E11>100. If E11 contains the number 150, then line 10 will be filled in, and so on.
      I hope I answered your question.

      1. Thank you very much - one thing - the numbers in the number row, each is colored with different color - and I want the text line the same - if the number cell changes the color then the text cell above also changes the color :)

        1. Hello!
          You create two conditional formatting rules. The first one already exists for a row with numbers. The second rule for the row above is in the way I suggested.

  4. Hi
    I have conditional formatted data to "fill with a colour" :
    "=$D2>$E2" for range "specify =$E2:$E2000
    Formula — =$D2>$E2"
    I need the E column to be filled if greater than the D column which worked for spreadsheet one however I applied the same conditional formatting formula to another Excel S/S with a larger data selection and Excel has filled extra cells that should not have been filled or correspond to the formula and were not greater than the formula cell.
    What else is not working

    1. Hi,
      If column E is greater than D, then you need to apply the conditional formatting formula
      =$E2>$D2
      This formula can only be applied to these columns.
      Where did you apply it and what is S/S - I do not understand.

  5. I am trying to create a conditioning format formula that highlights a row when a due date reaches within 5 days of the current date. I am using excel 365.

  6. HI SIR,

    I AM TRYING TO DO CONDITIONAL FORMATTING FOR HIGHLIGHTING A ROW WHEN CELL VALUE (CELL 'L') EQUAL TO ZERO,I WANT TO APPLY IT TO ALL WORK SHEET HOW CAN I DO THAT

    1. Hello!
      In your Excel spreadsheet, select the cells you want to format. If this is the entire worksheet, then select it entirely, and then create a formatting rule.

  7. I am trying to use formulas and conditional formatting to have a spreadsheet automatically populate names based off of another name in separate column.
    For instance, If I am assigned a task and my name is in column B, I want column G to automatically populate my supervisor/manager's name. I have multiple personnel in this spreadsheet, but once I figure out the formula, I can change the names. I keep having to look up supervisors in a directory, so I would love if my spreadsheet did it automatically for repeat entries!
    Oh, also, if multiple personnel have the same supervisor, I am assuming I can have it look up multiple names by separating with a comma and quotes around the names using OR?

    1. Hello!
      If column N contains the names of employees, and column M contains their supervisors, then you can use this VLOOKUP formula in column G:

      =VLOOKUP($B2, $M$2:$N$100, 2, FALSE)

      You can learn more about VLOOKUP function in this article on our blog.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  8. I have a set of up to 12 5-digit random numbers in a column starting at B3.
    For each random number, the 5 digits are entered in columns starting at B3, and the individual digits are entered into a row starting at C3. So for B3=13579, C3=1, E3=3, G3=5, I3=7 & K3=9.

    When I enter a 1, 2, 3, 4 or 5 digit number into the row starting at C2, I want the conditional formatting to highlight a cell when a match is made. So for the example above, 13579, if I entered G2=5 & I2=7, I expect G3 & I3 to highlight.

    I created the rule in C3 for the 1 (one) digit, =AND($C$2=1,$C3=$C$2), and used “copy” “paste special” “format” to copy the formatting down to the remaining 11 columns. The conditional formula works great for C3 & C4, but the copied format in C5-C14 is =AND($C$2=1,$C4=$C$2). The $C4 never increments! But, the formatting rule indicates it applies to C3-C14.

    I cannot figure out the correct way to "copy." All help would be appreciated. This is for playing Kakura so I need 100+ “tables” that I don’t want to write a conditional formula for each cell!!

    I can send the spreadsheet if a visual would help.

    Thanks

    1. Hello!
      If you want to create conditional formatting for cells C3:C14, then first select this range, and then create formatting with the rule

      =AND($C$2=1,$C3=$C$2)

      No need to copy conditional formatting.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  9. I have a calander which vlookups leave types from another tab. Although it inserts the whole leave name such as 'Recreational Leave'. I would like it to change the result to a shorter name and change colour. I have set up conditional formatting as follows:
    Equal to "Recreational Leave"
    Formatted the background to blue as well as in the number tab I have selected custom and written "REC".

    The conditional formatting has changed the cell to blue but it hasn't changed the word to REC.

    I have tried typing Recreational Leave into the cell to see if it the formula causing the error but it still doesn't change the word to REC. In another cell I have a drop down list (1-12) and have used conditional formatting to change the number from 1 to JAN, 2 to FEB etc by using the technique mentioned above and it worked fine.

    1. Hello!
      With conditional formatting, you can show Jan instead of 1. To do this, you need to change the cell format. The cell will write 1, but it will show Jan. Conditional formatting does not change the value in the cell. Therefore, you can change the color, but you cannot replace 'Recreational Leave' with REC.

  10. Hi
    I have conditional formatted data with "=$D2>$E2" for range "=$E:$E"
    However excel has filled cells that do not correspond to the formulae?
    What have I done wrong?
    I need the E column to be filled if greater than the D column.

    1. Hello!
      If you are formatting the entire column $E:$E, then the condition must start at the first cell. Use the formula

      =$D1>$E1

      Hope this is what you need.

      1. Hi
        The first cell is the title for the row which is why row 2 is used.

        1. Hi,
          Then do not use the entire column =$E:$E in the link, but specify =$E2:$E2000
          Formula — =$D2>$E2

          I hope I answered your question.

          1. Hi Alexander
            Thank you, changing the entire column to a specific range seems to have worked.
            Is there a reason why this messed up the Conditional Formatting?

            1. Hello!
              If you select the entire column E for conditional formatting, then the format is applied to all cells starting with E1. The formatting rule should be written on the first cell of this range. You formatted cell E1 with the data in cell E2. Cell E2 was formatted based on the value of E3. Etc.

              1. I applied the same conditional formatting formula to another Excel S/S with a larger data selection and Excel has filled extra cells that should not have been as were not greater than the formula cell. What else is not working as I now have the correct formula to put on the conditional formatting but this yields incorrect results?

  11. In my workbook I need to show that the date in column s is either equal to or 120 days before column c and if column s is more than 120 days before then I need it it to show up in red fill. It needs to start on the second row on in the spreadsheet. Can you help!

  12. Please help: value is any alphanumeric combination in Excel
    If A & B have no values: display as Red
    If A & B both have values: display as Red
    If A only has value: display as Green
    If B only has value: display as Grey
    Needed for a 3D component!

    Any help would be greatly appreciated.

    Many thanks in advance

    Best regards

    1. Hello!
      Use formulas for conditional formatting:

      =AND(ISBLANK(A1),ISBLANK(B1))
      =AND(NOT(ISBLANK(A1)),NOT(ISBLANK(B1)))
      =AND(NOT(ISBLANK(A1)),ISBLANK(B1))
      =AND(NOT(ISBLANK(A1)),ISBLANK(B1))

      I hope I answered your question.

  13. Dear Mr. Alexander
    have a good day!
    Can you please help me more regarding the above formula.
    below is the table i have, there is five(5) Colom as below & two(2) rows.
    actually i have two(2) warehouses- which name is (P1 & OP )
    I have assigned the formula which you help me.
    if you can see the below table i have Colom "A" name is storage("WH"& "OP") .
    what i want in my sheet once i enter in Colom "A" in first (1) Row- WH, so Colom "D" should be show as P1-F171 & Colom "E" should disappeared & once i enter in Row(2) as OP so Colom "E" with OP-F172 Should show and Colom D should disappeared.

    we need to combind the formola i hope...i think.. please support..

    i hope you have understand my question

    A B C D E
    Storage Location Qty WH-1 Open Yard

    WH F-171 62 P1-F171 --
    OP F-172 62 --- OP-F172

    regards,

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(A1="WH","P1-F171",IF(A1="OP","OP-F172","" ) )

      I hope it’ll be helpful.

  14. Hello Dear,
    can anyone help me below my query?
    actually i have my one excel file i jus give below small example ,
    i have to Colum A&B …
    once i enter any value in Colum A (like mention in Below "A" ) then Colum "B" Should change automatically with some action value, like i have mention in below.?

    Example :- If i enter "Colum A" (C-030) "Colum B" should be change "P1-C030" (without - after C)
    A B
    C-030 P1-C030
    B-129 P1-B129

    Thanks, Regards,
    Khan

    1. Hello!
      Please use the following formula:

      ="P1-"&SUBSTITUTE(A1,"-","",1)

      Replace "-" with nothing using the SUBSTITUTE function.
      I hope it’ll be helpful.

      1. Dear Alexander.
        many thanks lot its working well.
        i don't have words you to said...thanks you very much.
        god bless..

        br,
        regards,

  15. I know there is a way to set up a code that will automatically add the color code in a column throughout my excel sheet. I just don't know how to do it. Can anyone help?

    Example

    Color Color Code
    Grey TS21

    1. Hello!
      Custom colors can be added using the Format Cells - More colors - Custom menu. This can be done in conditional formatting when creating a condition.

  16. I have two rows with benchmarks as below, wherein i need conditional formatting as Results for all categories that are 4% or more above in GREEN
    results 4% or more below the applicable benchmarks are shaded yellow. I need to compare Row 2 with Row 1: for e.g.: 73% is 4% above benchmark of 69% and should be formatted in Green color.

    Row1 : 73% 76% 63% 69% 87% 64%
    Row2 : 69% 76% 71% 63% 83% 59%

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Try the following conditional formatting formula:

      =(A1-A2)>=4%

  17. Hi there

    I'm trying to set rules within a column, to highlight specific cells in that column if they are greater than, equal to or less than the figure in the cell next to it. Can you help?
    Any help would be appreciated.

    FYI - I am only a basic excel user - apologies.

    1. Hello!
      Highlight the columns you want and use something like this conditional formatting formula:

      =$A1>$B1

      I hope it’ll be helpful.

  18. On sheet 1I have data in columns A1 to A80 , B1 to B80 and D1 to D80. When I enter a value into columns, not all of them, C1 to C80 I would like the entire row to appear on sheet 2, is this possible?

    1. Hello!
      In cell A1 on sheet 2, enter the formula

      =IF(Sheet1!C1<>"",Sheet1!A1,"")

      Enter similar formulas in B1 and D1

  19. Good day,
    I must admit it is a gift to stumble on this article, but however my challenges was not addressed. I have a work sheet with just a single column. I need a formula that will highlight the row above the cell if the cell contains a specific text. For example if A2 contains "great" A1 should be highlighted. Thank you so much while i await your response.

  20. Can anyone help me?

    IF(ISBLANK(S10),””,(H10*S10)+(V10*W10))?

    The formula is in cell X10.

    I’m getting the #Value! error in all my empty cells in the x column, as V10 & W10 values may not have any input sometimes. How can I fix this to reflect that??

    1. Hello!
      You get the #Value error! if V10 & W10 contains text or space. If the cell is empty, there is no error. You can handle this error with the IFERROR function -

      =IF(ISBLANK(S10),"",(H10*S10)+IFERROR((V10*W10),0))

      I hope it’ll be helpful.

      1. Hello Alexander, thank you so much this worked like a charm. Out of 30 cells maybe 10 cells will require the formula using the v10*w10 calculations, so when I dragged the formula down it would give me that value error. Thank you sooooo very much!

  21. Please help me make a formula for my data, scenario is this:
    Where column B2 is the Release Date and column B5 is the 1st Amort Date.
    f the release date (B2) Range from 1 - 25 of the month the 1st Amort Date (B5) should be the 10th of the following Month,
    then if the release date (B2) Range from 26 - 31 of the month the 1st Amort Date (B5) should be the 5th of the following Next Month.

    Example: B2 - July 15, 2020
    B5 - August 10, 2020

    then
    B2 - July 26, 2020
    B5 - September 5, 2020

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(DAY(B2)<26,DATE(YEAR(B2),MONTH(B2)+1,10), DATE(YEAR(B2),MONTH(B2)+2,5))

      I hope it’ll be helpful.

  22. If range A2:A30 has 2 data validation, "selected, not selected". I want respective B Column to be N/A, if respective A column is "not selected", by using conditional formatting, is it possible?

    1. Hello!
      Conditional formatting may not prevent a cell from being edited. You can use Data Validation tool. For example, for cell B2, you can write the rule

      =A2="not selected"

      After that you can copy this cell down along the column B.

  23. Hi Alexander! I'd like to show a red RAG status in a separate cell, based on the values quoted within five other cells, where none, or only one, of the five cells has a value of "4".

    1. Hello Paul!
      You can also use the formula to conditionally format a cell:

      =SUM(--(B1=4),--(B2=4),--(B3=4),--(B4=4),--(B5=4)) > 1

      I hope this will help

  24. Hi,

    I need to help to highlight the values.
    I have data set in Column B1 to B10. If I enter value in column A1, then excel should highlight to tell the entered value is between particular cells.
    B1=449.25
    B2=445.25
    B3=438.29
    B4=435.27
    B5=427.28
    B6=425.29
    B7=420.3
    B8=415.31
    B9=410.29

    If I enter the value in A1 as 417.25, the cells B7 & B8 should highlight. Please help on this.

    1. Hello!
      Using conditional formatting, you can select all the cells in a column that are larger than some value. A second rule can be created for all cells that are smaller. For each rule, set its own color.

  25. I have 2 columns in my spreadsheet that are dates. Then, I have a third column (G) whose formula calculates the time between those two columns: =IFERROR(DATEDIF(E64,V64, "D"),DATEDIF(V64,E64, "D")). The condition i want applied If the dates from E is less than column V, then i want the column G cell to be RED. I would like the condition to apply all of column G, but its dependent on its own unique row's dates. Is there a way to get a condition to be dynamic like the drag down principle for equations?

  26. I have an excel worksheet I have a formula: =IF(G22="Y","CORROSION",""). What I need to do is add to this formula I need it to show that: IF (G22="Y" AND H22="X", "CLEANED"). I have tried several different ways of writing it but all I get is an error. The first formula works I just want to expand on it, is that possible?

  27. I am trying to copy a "Conditional Formatting" Cell. The line I am copying has the "Conditional Formatting" of "If N$4$>F$4$ then turn cell yellow". When I copy it to the next line it is "If N$4$>F$5$ then turn cell yellow". I want it to be "IF N$5$>F$5$ then turn the cell yellow". Is it possible to copy and get this result?

  28. Hello anybody.....
    I am trying to format a spreadsheet for a lot of people that I manage travel arrangements for, I need the cell to change colour on the date I input in to the cell (date of travel) and then change back to no colour 14 days from the inputted date...
    For instance ...John travels to Albania so I enter 20/03/2020 in the cell and it turns red. After 14 days has passed I want that cell to revert back to clear again until the next time I enter a date for travel.
    I have tried several formulas but it is frying my brain trying to get it to work.
    I am handling every country in the world and over 300 people so any help would be appreciated.

    Thanks

    1. Hello Mark!
      Please first select the entire column you'll be filling with travel dates, then go to
      Conditional Formatting- > New Rule -> Use a formula to determine which cells to format and put the following formula:

      =TODAY()-$A1<14

      Thank will do the trick for you.

  29. I have a column that each cell uses a formula to produce a value. I would like to apply conditional formatting to those cells based on the vale produced by that formula. Pretty simple really, =IF(A1+B1)>0.

    But what I am finding is that even if the product of that formula is 0 it still conditionally highlights it because there is something in that cell, a formula. I know I have successfully done this a thousand times before however now it is just fighting with me. Is there a setting or something that I can change so that the conditional formatting 'looks' at the cell value rather then the enclosed formula?

    1. Hello Robert!
      Please specify which conditional formatting type you are using exactly - Data Bats, Color Scales or something else? It'll help me understand the situation better and recommend you some solution. Thank you.

  30. Hi,
    I have coded column starting from cell A3:A56 with one or more text for each cell from another column starting from the cell B6:B71. I have changed the background color for each of the cells with a distinctive color and the same text for each cell in the column B6:B71. I would like to match any of the cell in the column A3:A56 that contains the same text in column B6:B71 with its color. At the moment the cells from the column A3:A56 have no background color(white). What sort of conditional rule should I apply in column A3:A56 that can use the color of the cell that contains the same text from column B6:B71

    1. Unfortunately, it is technically impossible for Excel to use colors as formula components or conditions. This can be achived with the help of VBA only.

      As a workaround, you may use the conditions you set for the conditional formatting as the conditions for your formula. That should work.

      Please check out this article, I beloeve you'll find this information helpful

  31. Hi,
    Found do I apply a conditional format for the difference between 2 numbers.
    I want to Highlight if the difference is less than 10 or less than 5.
    EG
    A1 is 10 less than A2 show as red, if it is 5 less than show as green.

    A1 100 A2 120
    thanks,
    Glenn

    1. Hello Glenn!
      You'll need to set the conditional formatting rule for cell A1 with the following formula applied:
      =$A$2 - $A$1<10
      And choose red as a color to fill the cell with.

      Then add one more rule to the same cell with another formula:
      =$A$2 - $A$1<5
      Green will be the color for this condition.

      I hope this is exactly what you need.

  32. Dear Sir
    I have 2 column Ordered Qty and Received Qty for this i wANT TO SEE IN ONE COLUM i want to get answer Received or Pending kindly help me

    1. Hi Ronald
      I believe you are looking for this: =IF(B22>=1,"Received","Pending")
      Where "B22" will be the cell (value) under Qty Received.
      However, you can also use:
      =IF(B22>=A22,"Received","Pending") Where "B22" is the Qty Ordered, "A22" is Qty Received. If number of items received is less than the amount ordered, it will stay on pending, until updated to reflect all items received.

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

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

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

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

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

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

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

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

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

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

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

  44. Need to colour based on other cell value like.

    If B2 value has a maximum valie in range of B1:B5 then only A2 cell colour with yellow.

    What formula i use in conditional formatting?

    1. Hello,

      Please create a custom Conditional Formatting rule for cell A2 using this formula:

      =B2=MAX($B$1:$B$5)

      Hope it will help you.

  45. Is there any way for me to apply conditional formatting to a column that highlights only specific cells in that column that have blanks in the row that the cell is in? Such as highlighting cells only in column B where there are blanks in specific rows in column A.

    1. Hi Dan,

      Simply, make a rule for column B with this formula:
      =$A1=""

      Where A1 is the top-most cell of the applied range.

      For example, if you are setting up a rule for B2:B100, then use the formula =$A2=""

  46. I'm trying to format a cell on a separate sheet to be highlighted based on if multiple cells have at least a value of 1 in it. For example, I have 4 cells and each cell needs to to have at least a value of 1 for the cell on the other sheet to be highlighted. Can this be done? If so, how? Thanks.

    1. Hello, Matthew,

      Try the following:
      Supposing that your 4 cells are in Sheet2, you need to create a rule for conditional formatting like this:
      =AND(NOT(ISBLANK(Sheet2!A1)),NOT(ISBLANK(Sheet2!A2)),NOT(ISBLANK(Sheet2!A3)),NOT(ISBLANK(Sheet2!A4)))

      Hope it will help you!

  47. Hi
    Desperately Need your help :)
    I have a dates column AC2:AC37 and i also have a price column M2:M37. Once a date in the AC column has got to today date i want the matching price cell in column M to be highlighted.

    looking forward to your response.

    1. Hi Danial,

      Select cells M2:M37 and make a rule based on this formula:
      =AC2=TODAY()

      This will highlight only the prices that have today's date in column AC.

      If you want to highlight the prices matching today's date as well as all previous dates, then use this formula:
      =AC2<=TODAY()

  48. Hi,
    I am using few rules for conditional formatting for .csv file. But after the closing the file all rules get deleted. why this is happing? Can we apply this rules for .csv file.

    Thanks in advance

  49. =MIN(IF(A4:A94,B4:B9)) =0
    =MIN(IF(A4:A9>4,B4:B9)) = 30.92

    Can you help me with this simple problem.I can't get the top
    formula to work it returns 0.The bottom formula works
    ok.Can't seem to get the 4 to work together.Can you help
    me with this
    A B
    5 30.92
    4 31.29
    2 31.11
    3 31.17
    6 31.29
    7 31.29

    1. =MIN(IF(A4:A9>4,A4:A94,B4:B9)) = 30.92

      Can you help me with this simple problem.I can't get the top
      formula to work it returns 0.The bottom formula works
      ok.Can't seem to get the >4<6 to work together.Can you help
      me with this, I used ctrl,shift,enter
      A B
      5 30.92
      4 31.29
      2 31.11
      3 31.17
      6 31.29
      7 31.29

      1. =MIN(IF(A4:A9>4,A4:A9<6,B4:B9)) = 0 .This is the formula i cant get to work.

        1. Hi, Harold,

          as far I can see, you're trying to use IF function incorrectly. Please take a look at this article of ours to learn more about the syntax of the function.
          You may want to check out this one as well, since it explains how to use IF and MIN together. You fill find a bunch of examples that'll help you to build your own formula properly :)

          1. I didn't make my example ,clear. I have column "A" with these numbers 5 4 2 3 6 7 - In column "B" I have these numbers 30.92 31.29 31.11 31.17 31.29 31.07 If i select a number in column A or an adjacent number I want the minimum corresponding number in column "B" I need a formula for this. I tried this formula but all i get is zero.=MIN(IF(A4:A9>4,A4:A9<8,B4:B9)). i used control, shift, enter.

            1. I didn't make my example ,clear. I have column "A" with these numbers 5 4 2 3 6 7 - In column "B" I have these numbers 30.92 , 31.29 , 31.11 ,31.17, 31.29, 31.07 If i select a number in column A or an adjacent number I want the minimum corresponding number in column "B" I need a formula for this. I tried this formula but all i get is zero.=MIN(IF(A4:A9>4,A4:A9<8,B4:B9)). i used control, shift, enter.I added some commas between some of the numbers to make it clearer.

              1. Thank you very much for the clarification, Harold.

                First of all, please take a look at this part of the article, to learn how to use AND and OR operators in array formulas (* for AND, + for OR)

                Then please try this formula:
                =MIN(IF((A4:A9<8)*(A4:A9>4),B4:B9))

                However, if you're using Excel 2016, the following should do as well:
                =MINIFS(B4:B9,A4:A9,"<8",A4:A9,">4")

          2. thanks i'll take a look

  50. Hello,

    How to make formula for the following:

    if A1=8 then answer is 2
    if A1=10 then answer is 4
    if A1=12 then answer is 6

    Thanks

    1. try this formula
      =IF(A1=8,2,IF(A1=10,4,IF(A1=12,6,"No Value")))

    2. For newer editions you can use =IFS(A1=8;2;A1=10;4;A1=12;6) .. That would save you from nested IFs. But it requires 2016 or newer.

      Old method, =IF(A1=8;2;IF(A1=10;4;IF(A1=12;6;""))) .. It can be shorter if there's no other option than these three, 8,10 and 12. If so use;

      =IF(A1=8;2;IF(A1=10;4;6))

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)