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

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

  2. Great info! Thank you for sharing!!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  32. I'm looking to highlight dates that are before today (using the "TODAY()" function) but only where the "Status" column is NOT "Complete".

    So in column G I have all the end dates of our projects, starting in row 3 (G3) and in column E I have the status of each project, again starting in row 3 (E3). The statuses in column E are:
    Complete
    In Progress
    Not Started
    Cancelled
    Overdue
    On Hold
    "BLANK"
    What I need is for column G to have highlighted all dates that are before todays date "<TODAY()" but will not highlight when column E displays "Complete". However it needs to be highlighted when column E displays any other value.

    What would be the best formula for this?

  33. I need to make a formula using less than or greater than ?????
    EG. 1-30 minutes = full rate ( make the cell green)
    31 to 40 minutes = -10%(subtract)(make the cell amber)
    41 to 50 minutes = -25%(subtract) make the cell purple)
    51+minutes = -100% (subtract) make the cell red

  34. I am trying to sort out a conditional formatting issue using multiple cells, The current formula I have entered is (=$X$2="SS4 1") which works exactly how I want it to on Cell X2, however I want the rule applied to multiple cells and anyway I have tried it doesn't work. I want it to apply to the following range of cells, ($X$2:$FK$2="SS4 1") I have tried entering like that and it doesn't work I'v tried entering like this (=$X$2+$AA$2+$AD$2+$AG$2+$AJ$2+$AM$2+$AP$2+$AS$2+$AV$2+$AY$2+$BB$2+$BE$2+$BH$2+$BK$2+$BN$2+$BQ$2+$BT$2+$BW$2+$BZ$2+$CC$2+$CF$2+$CI$2+$CL$2+$CO$2+$CR$2+$CU$2+$CX$2+$DA$2+$DD$2+$DG$2="SS4 1") and ive tried entering like this (=$X$2,$AA$2,$AD$2,$AG$2,$AJ$2,$AM$2,$AP$2,$AS$2="SS4 1") obviously I have not selected all cells in the last two examples but just wanted to show how I was entering it using + or , and still nothing. Please help.

  35. Hi,
    I have a sheet which is adding up data using sumif. The default is 0, due to no data to add. As data is added, I'd like to be able to differentiate the 0 as nothing matches from the data adds to 0. I thought this may be possible with conditional formatting, but am unable to work this out. Is there a way?
    Thanks.

  36. Hi
    I want to find a date in column L8: L3500 that corresponds to the value in E3 picked from column D8: D3500 (found by formula = MIN (D8: D3500). Which formula do I use? Thanks in advance for your help.

  37. Hi. I am trying to change the color of an empty cell in the first column of my table if columns f2 and w2 have the same value and if column e2 has the number 4390001 in it. I want the empty column to highlight red. I can get it to do it, but it is not highlighting in the right cell. It is one off on some and others it highlights a bunch without reason. This is the formula I have gotten to. =W2:W21617=F2:F21617=(E2:E21617="4390001") Please help!

  38. I want to conditionally format cells which has value less than Normal Range Males More than 13.6; Females More Than 11

    SN WN EN Sex Hb.
    1 3755 11098 M 16.5
    2 3756 11140 M 14.6
    3 3757 11153 M 14.7
    4 3758 10005 M 12
    5 3759 10166 F 9
    6 3760 10197 M 16.6
    7 3761 11149 M 12.7
    8 3762 0 F 10.2
    9 3763 10112 M 16.6
    10 3764 11123 M 13.6
    11 3765 10040 M 13.8
    12 3766 10195 F 10
    13 3767 11082 M 11.6
    14 3768 10117 M 13.9
    15 3769 10171 M 16
    16 3770 10025 M 14
    17 3771 11127 M 15.4
    I tried your formulas. It did not work. I don't know what mistake I am making.

  39. I have a table that I would like to apply conditional formatting to one row (lets call it column G). Column G contains the number of days away from start date (can also be a negative number if start date has passed). Another column (Column H) contains a percentage ranging from 0% to 100%, indicating the percentage of the task completed. I would like Column G to have conditional formatting that colors the cell as follows:
    Red - if Column G is less than 0 and Column H is less than 100%
    Yellow - if Column G is between 0 and 7 and Column H is less than 100%
    Green - if Column H is 100%
    Any help you can provide would be greatly appreciated!

  40. I have a drop down with the word CXL in it. How do I make other cells that I want to turn red when this is selected. The selected cells will contain diffrent data and can be anything from words to numbers.

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

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

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

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

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

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

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

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

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

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

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