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

  1. I HAVE A TABLE IN EXCELL I WILL FINDE CELLS WHIT COLOER FILL SPECIFIED AND VALUE ROW AND COLUMN HEDER THAT CELLS WRITE ON CELLS ONATHER WITH VBA CODE IS SOME ONE HELPE ME FOR

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

  3. I'm going insane, when i insert conditional formatting based on the value of another cell the formula works in some cells and not others for no reason I'm not sure why.

    =$BE2BD2

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred.
      It’ll help me understand it better and find a solution for you.

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

  5. I have my specifications for a finished product where the top row has the minimum and the lower row the maximum.
    Then I have actual values of each parameter that was observed on a daily basis when each batch was sampled and analysed.
    Let me show an example below:

    Date Product Parameter (%)
    Moisture Protein
    Specification Minimum 11.5 20
    Maximum 12.5 24

    02.12.2020 Broiler Starter 10 19
    03.12.2020 Broiler starter 11.8 25
    04.12.2020 Broiler Starter 12 20
    04.12.2020 Broiler Starter 13 24.7

    Please show me which formula I can use to highlight a red colour on an actual result that is below the minimum specification or above the maximum spec and green on a value that is greater or equal to the minimum or less or equal to the maximum specification.

    Thank you.

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

  7. thanks for your support..
    above formula very help me to combined the formula with (if+ SUBSTITUTE)

    below is the formula i have combined it

    =IF($B$15="WH","P1-"&SUBSTITUTE(C15,"-","",1),IF($B$15="OY","OP-"&SUBSTITUTE(C15,"-","",1)))

    thank you

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

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

  10. Hello
    I want in Google Sheets if the value of a cell is less than the value of the last 24 hours(I use to import data from a website on this cell and this cell is variable), the color of the target cell should be red, and inverse if it was more, the color of the target cell should be green.
    I do not know what its computational formula is!

  11. Hello to all
    i need this formula please
    i have a table of data about the weather for my country citiesand each city in a sheet sorted by the date
    and the last sheet i program it to be refresh and updated once i open the file and it contains all the cities (each city in one row)

    now i need the formula which is easier 1-for EXample .... (D) is the date line so if the Date is the same between D10 in sheet 1 and D2 in the last sheet copy E1 from the last sheet to the sheet 1 in E10 .

    2- check the date in sheet1 and date in the last sheet for rwo 2 if it is the same copy row 2 to sheet 1 in the last row or the first empty row which meen the last row.

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. If you need to conditionally fill in the value of cell E10 on Sheet1, then you can use the formula

      =IF(Sheet1!D10=Sheet100!D2,Sheet100!E1,"")

      If you need to replace an existing value in E10 with a new value, you need to use a VBA macro.

      Your second question is not completely clear to me. If you explain in more detail, I will try to help.

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

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

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

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

  16. Awesome!!!

    Many thanks

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

  18. I have two column A&B Column A have given some number Like,1,2,3,4,5,6,7,8,9
    and Column B have given some number like,1,3,5 according to Column B highlight
    the Column using the conditional Formulae.
    I need you pls sir,

    Thanks&Regards
    Niru Kumar

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear.
      Explain the phrase "Column B have given some number like,1,3,5 according to Column B highlight".
      Write an example of the source data and the result you want to get.

  19. The above formulas all return TRUE or FALSE, so they work perfectly as a trigger for conditional formatting.

  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. I have aaplied conditional formatting to the following situation:
    1. Column K has a percentage value derived by applying formula
    =(DATEDIF(O3,TODAY(),"d")+1)/(DATEDIF(O3,P3,"d")+1) with data from other cells.
    2. Column L is to be updated manually.
    I want to change the colour of column L with reference to the percentage value in the cell being greater than, less than or equal to Column K. Conditional formatting works well in first and second data cell of column L but starts faltering thereafter.
    I have tried the same logic in other columns and cells but in vain.
    Please help.
    Thanks
    Naveen Arora

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      What formula did you use for conditional formatting? What data is in column L?

  22. Great info! Thank you for sharing!!

  23. I am trying to format a group of cells to a specific color (ie 2 cells side by side with a first and last name) based on a third cells value ( ie Days) which represents what shift they are on.

    Suggestions?

  24. When conditional format activated in A1, B1 turns on with Red background and yellow Text saying "OVER BUDGET"
    is it possible without using macros a simple way? and it can be copied to the columns
    Thank you in advance

  25. How to show a text in a cell that is adjacent to the one that is formatted?
    Eg: I have a C.formatted cell in A1 that says, =A1>100 format red color. which it works. But I like to see the adjacent cell(B1) to turn RED bacground and yellow text saying "OVER BUDGET"
    When conditional format activated in A1, B1 turns on with Red background and yellow Text.
    is it possible without using macros a simple way? and it can be copied to the columns
    Thank you in advance

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

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

  28. Hello
    I have 8 different sheets in one workbook.
    Each sheet is named as " raw,process,issues,closed"
    example: When a product number 72 has completed its production, we will paste the details in "closed" sheet
    I would like to apply conditional formatting or find duplicates if
    I entered the same number in coloumn A of sheet1 or sheet2 or sheet3, it should highlight with any color.

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

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

  31. Hello Svetlana,
    I have the following question, I would like to create a formula in order to change the values in one of the columns (to be decreasing by 1) if the values in another column are increasing by one and if a different data is inserted to highlight it. For instance if in column A, that numbers start with 1 and increase progressively up to 30 in the other to start with 8654 and decrease with 1, tried with if and it partially worked out, still it does not register the deviations from the condition given. Thanks a lot :)

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

  33. I want to conditionally format all cells in column A based on corresponding values in column B.
    For example, format cell a1 if cell b1>10 or b110 or cell b2<-10, and so on.
    I want to create one rule to format all cells in column A based on the above logic.

    Please advise best solution. Thanks.

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

  35. Hi ,
    I have number of series of numbers like 1,2,3,9,-1,-3,-10 in a row then drastical changes like 1, 9,-10 should be highlighted in my row how to do it

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

  37. I need to copy the conditional formatting range as F2:AA2 to have at the below row the range as F3:AA3 and so on

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

  39. I can't use in cell conditional function AND. It doesn't work inside "tables". WHy?

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

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

  42. I want F2 to be formatted based on a check cell C2 and then H2 and I2. In basic terms, If "(F2(value) + H2(value) + I2(value)) = C2(value)" is true, cell should be formatted. If false format wouldn't apply and there is already a rule for if F2 and C2 aren't equal.

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

  44. Hello Svetlana Cheusheva!
    There is a great example for your expertise on Conditional Formatting sorted data!
    Hopefully its a simple overlooked solution for a large number of readers that can create more new traffic on your site with your response.

    The formula for formatting is written for the top-left cell and works great through the range but as soon as the data is sorted A-Z or Z-A, the conditional formatting for the rest of the range acts irrationally. Formatting of the rows are changed to the incorrect condition.
    (Sheet description is summarized below).
    Is this an example of troubleshooting step #3 "Write the formula for the top-left cell."?
    If so, how is formula conditional formatting achieved with data that will be frequently sorted?

    Please contact (via your preference) if there is a solution for this. Feel free to use this obstacle as new feed for your page for your audience with your solution. Hopefully it is beneficial for your goals.

    Thank you
    Pablo Vazquez

    PROJECT DESCRIPTION:

    Project names are listed on column A.
    Corresponding dates of the projects are listed on columns B through D (Start date 1, Start date 2, and End Date 3).
    Presentation dates of each project are listed on column F.
    Rows are conditionally formatted based on the condition if a given date on Column F falls within which start date and end date from column B through D.

    For example: If Project 1A (cell A1) with a presentation date of 1/31/2020 (cell F1) falls between Start date 1 (B1) and End date 1 (D1); therefore Row 1 highlighted green.
    If Project 1A (cell A1) with a presentation date of 2/28/2020 (cell F1) falls between Start date 2 (C1) and End date 1 (D1); therefore Row 1 highlighted orange.

    Once sorted A-Z by project names/numbers or start dates or presentation dates, the rows that are supposed to be highlighted green are either not highlighted or highlighted orange. Vice-versa with rows supposed to be highlighted green.

  45. I'm making a March Madness pool sheet. I would like it to be fully automated, other than updating round by round winner & losers. I have a source sheet (Teams tab) with all teams in tournament in column B cells 4-67, and corresponding columns C, D, E, F, G, H cells 4-67 to assign round 1, 2, 3, 4, 5, 6 results. I have a destination sheet (Participants tab) with all participants names, picks and final four teams. I have everything figured out except: How can I conditionally format Participants tab cells for final four picks to change format when that team loses, based on entry in one of the rounds columns for said team in Teams tab changes state?

  46. Hi,
    I'm trying to highlight rows where the date is greater than 6 months and using this formula that I googled.
    =$G2<=EDATE(TODAY(),-6)
    It works well and highlights the rows that I need it to, but then it is also highlighting the row if column G is blank. How do I stop it doing this?

    I've tried adding the format cells that only contain Blank, but then the rest of the row is still highlighted.

    Are you able to give me any suggestions?

  47. hi
    i have excel calendar. column (A) have date and Column (B) days and colomn (C) notes. when change the month or year dates and days changes but in coloumn (C) entered notes still remain. pls help how i can clear that notes when i change the month or year. pls

  48. I want to validate one sheet contains all the information exactly like the second sheet, and when they done the area is highlighted.

  49. I need help setting conditional formatting in a column, where if any of the values equals to "A", "B" or "C" colour the cell green. I've tried the following that didn't work.
    Cell Value/Formula = OR ("A","B", "C")
    Cell Value/Formula = OR ($G:$G="A",$G:$G="B", $G:$G="C")

    Any help would be greatly appreciated.

  50. Hi
    I have copy-pasted a data from another program to Excel. The cell value shows number only, but the format of this cell contains the category of Custom formatting.
    i.e The cell value is 5, but the display is 5 Dr ( Debit value in accounting).Format of this cell shows "0.00"Dr".
    Similarly some other cells contain Cr (Credit value in accounting),Format of this cell shows "0.00"Cr".
    My question is if I want to sort or apply conditional formatting to these values which contains Cr and Dr value, what is the method?

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