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

  1. He Alexander,

    I'm trying to get a cell to change color based on a review comment on another sheet of the excel file.
    The cell that I'm trying to change color is the reviewer and based on his response it should color green (approve) or yellow (remarks added).
    I've created a formula that evaluates to "TRUE" in a cell on the worksheet but as soon as I paste it in the conditional formatting formula field Excel states it is not a formula, what is happening here?
    The formula is =XLOOKUP($D718&"*";DRF[Title];DRF[Rem-MF];"";2)="Approve"

      1. Thanks for the answer Alexander, I've changed the formula to =XLOOKUP($D4&"*";DRF!$A$2:$A$5000;DRF!S$2:S$5000;"";2)="Approve". This seems to work.
        I've included the column ranges to increase speed of calculating the formatting since using full column references slowed down Excel a lot.
        Funny that I've never come across the structured reference restriction for conditional formatting anywhere on the web while searching for a solution to this.

  2. I need help with figuring out if what I'm wanting to do is possible. I'm trying to update a spreadsheet a co-worker created. It has all employee names in one column and then other columns have department names, and other business releated stuff. Each column has employee names listed underneath. All the data is in black font but I went in and changed the font color in the main employee column so that each department has a different font color. Is there some type of conditional formatting formula that I can enter so that all the data in the other columns will update to match the font color in the main column. For example, if Susie Smith has blue font I want her name in all other columns to change from black font to blue font. This list has over 3000 names on it so I dont want to do one at a time.

    I hope I explained good enough so you know what I'm wanting to do.

    thanks for the help!

    Scarlett Jo

  3. Hello there, great article.

    I am new to all of this and I am trying to get my head around all the information in your article. I cannot even get the =$C2>0 to work correctly yet and I am working through it all trying to teach myself.

    Having trouble subbing in some text value in the formula.
    I have a need for two cells to change colour, if one of those cells contains specific text.
    I have copied my formula into every cell for one of my rules.... hoping there is an easier way?

    Current Formula
    Apply to range : C11:D11
    Format all values where this formula is true: =$D$11="TBA"

    My Sheet is a Roster. Different departments in different areas of the sheet.
    I have a list of roster positions to the left (Column C). These cells will be empty or contain a name. The cell to its immediate right (Column D) will have more information about the shift or employee.
    Nadia (Column C) TBA (Column D)
    Right now I have a drop down in Cell D11 (I select the text TBA) and the cell (now containing the text "TBA") as well as the cell to its immediate left (C11) changes colour.
    I have copied this formula into every cell that I need it to work for.
    Format all values where this formula is true: =$D$12="TBA" Apply to range: C12:D12. =$D$13="TBA" Apply to range: C13:D13... and so on and so on.

    How I want it to function
    The cell on the left is a position in that department. It could have an employee's name or be empty. The cell on its right is a condition of that employee or position. Nadia (cell to the left) TBA (cell to the right) for example, means they are rostered to work, but there is more information to be advised (TBA). So I select TBA in the cell next to their name and that cell and their name change colour. Now I also have the need to overwrite the condition cell (on the right) with more information. Now I wish to include the shift time and a condition. For example, 0545-1945 RTW. Now that the cell on the right contains the text RTW, I want that cell and the cell to the left (with employee's name or empty) to change colour.

    I have spent quite a bit of time trying to make this clear and I am grateful for any help you can give. I just hope to be able to understand the assistance. Think I love this stuff and will need to go on a proper course!

    1. Hello Benjamin!
      If you want to apply conditional formatting to a range of cells, do not use an absolute cell reference in the conditional formatting formula. For example, for the range C11:D11, the formula could be
      =$D11="TBA".
      I recommend reading this guide: Relative and absolute cell references in Excel conditional formatting.
      To find a particular word in text in a cell, use the SEARCH function.
      For example:

      =ISNUMBER(SEARCH("TBA",$D11))

      For more information, please read: How to find substring in Excel

  4. Hi there,
    Love your articles as they are very helpful.
    Is there a way to create a conditional format whereby if a cell has a formula inside which includes a +1, then it colours it differently? I'd want to put this condition on specific columns for a pay sheet where if an employee works an evening shift and is paid an extra hour premium, once I enter the +1 in the formula of total hours, it automatically highlights that cell in a different colour.

    I.e. Formula in cell F205 is =(IF(D205>E205,E205+1,E205)-D205)*24
    result would be no highlight in cell F205
    but if cell F205 is =(IF(D205>E205,E205+1,E205)-D205)*24+1
    then cell F205 would highlight in green

    Any help would be appreciated

    1. Hello Margaret!
      Use the FORMULATEXT function to extract the formula text in the cell. Then determine if there is a partial match between the text string "+1" and the formula text. For more information, please read: How to find substring in Excel. Here is an example of a conditional formatting formula:

      =ISNUMBER(SEARCH("+1",FORMULATEXT(F1)))

  5. I need to format one column based on another column being blank. is there a format for that?

  6. Hello,

    I love your articles, & find them very helpful! Here's my problem, using conditional formatting.

    I want a cell A to change color when cell B meets a certain value, (say 25), and cell C is between a certain range, (say 1-10).

    I understand the range part =AND($C$1>=1,$C$1<=10)
    I just can't seem to get my head around adding the cell B condition to the formula?

    Any help would be greatly appreciated!

  7. Hi, I want to know how I could use the conditional formatting for the range of cells, considering their total sums. 
    (Ex: total value of A column selected cells; total value considered with B, C, and D column selected cells.) If the A column selected cell total value is the lowest, then it should be highlighted with green (using red-yellow-green color scale formatting).

    1. Hi! I don't really understand from your description which cells you want to compare. However, you can determine the lowest value using the MIN function. Here's an example of a condition:

      =A1=MIN(A1,B1,C1,D1)

      If this is not what you wanted, please describe the problem in more detail.

      1. 1 A B C D
        2 MALA SEETHA RADHA RAMA
        3 500 200 150 162
        4 600 300 600 150
        5 700 20 inlcuded 170
        6 800 250 included 180
        7 900 50 850 200
        8 600 400 900 400
        9 200 60 200 750
        10
        11 4300 1280 2700 2012

        I need to use the conditional formatting considering the total value of each and every column selected cells.
        Ex: What is the range of A4, A5, A6 total compared to the total of B4, B5, B6, Total of C4, C5, C6 & Total of D4, D5, D6

        1. Instead of one cell, use the sum of three cells. Here is an example of the formula:

          =(A4+A5+A6)=MIN((A4+A5+A6),(B4+B5+B6),(C4+C5+C6))

  8. I have a checklist in Excel. There is a box/cell next to each item. At the end of the list, there is a "done" box/cell. I want to use conditional formatting so that if I place an "X" in the Done box/cell, it'll put an "X" in all of the boxes/cells in the list above. But anything manually keyed in an individual item's box/cell would override the "X" that would appear if the Done box/cell had the "X".

    I realize I could use a regular formula, not conditional formatting, to achieve this, but I don't want the formula showing in the blank box/cells. I want it embedded there through conditional formatting.

  9. Good aftrenoon

    Can I please ask for some assistance? I hope that i am wording this correctly.
    I am stuck for a formular for a specific task that I am wanting to have on my excel sheet.
    I am wanting highlight cells on a row to enter a date between eg. D1 and K1.
    the highlight would be by having a date entered in D1 and have the cells highlighted up to K1. A date will then be put in
    at a later date in K1, once this date is put in the cells will then be unhighlighted.
    I hope that this can be achieved. Thank you very much for your help.

    1. Hello Cassy!
      To solve your problem, you can use conditional formatting. This is described in the article above.
      For the range of cells that you want to highlight in color (for example, E1:J1 or E1:J20), use the conditional formatting formula as follows:

      =AND(D1<>"",K1="")

  10. Hi I have drop down option in a cell and depending on the answer I want another to fill. How is this done please ?

    If the answer genuine is selected in cell how do I get cell next to it to fill black ?

  11. Does anyone know how I can formulate the following - I'm trying to set up a training event calendar and have two sheets in the workbook. One with a calendar with dates along the top and each department underneath -
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
    Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We
    Business op's
    Architects
    PMO
    Capital
    Property

    Id like to add conditional formatting along each department line colouring in the cell based on a date and the duration per each sub department. Id then like to formulate the coloured cells so when I click on them it takes you to the exact event in the event information tab -
    Description Event 1 Event 2 Event 3

    Capital Event Name Capital Group
    Number of days 5
    Time of Event 9am - 5pm

    Start Date 24 January 2024

    1. Hi! For information on how to format by date as condition, take a look at this article: Excel conditional formatting for dates & time: formulas and rules. If I understand the problem correctly, you will also find this article useful: Change the row color based on cell value.
      To move to another cell or worksheet after clicking a cell, you can use hyperlinks. See here for detailed instructions and examples: Hyperlink in Excel: how to create, edit and remove and 3 ways to insert a Hyperlink to another Excel sheet.
      I hope my advice will help you solve your task.

  12. I have 2 sheets (sheet1 and sheet2). I have set of words in both sheets same columns (B).

    I want to check words of each cell in sheet2 with range of words in sheet1 and change background colour of row whose word exists in sheet1's set of words.

    I tried using this lessons but I am unable to check value of each cells one by one.
    means that in conditional formating formula when I write this formula "=COUNTIF('sheet1'!B10:B45,'sheet2'!B$10)" then 'sheet2'!B$10 is remaining same and it is not changing to 'sheet2'!B$11 or B$12 as per cell.

    how can I do that?

    2. I have tried "sheet2'!B11" this also (relative value - means not using $ sign ) then also not getting desired output.

  13. if the below is typed in a cell
    80 x 80 x 3 x 6000
    can we get the product of these numbers in another cell . the answer has to be 115,200,000.

    is it possible

  14. Great resource! Only place I could find how to format based on multiple conditions from other cells. Thank you!

  15. We have a complex formula that produces post menstrual gestational ages into a cell.
    Example. Our formula provides a value 34.2 which is 34 weeks and 2 days. Our formula is also set to adjust this values every day so it’s up to date with the date change.

    When we follow instructions to a T, it doesn’t work right even tho our formula is correct. Is there a reason conditional formatting won’t work if it’s formatting a cell(s) that have a value that is produced from an already complex formula?

    1. Hi! I don't know what "instructions to a T" means. Conditional formatting works with the value that the formula returns. To give more precise advice, you have not given any information.

  16. Hi Alexander, Thanks for taking time to help those of us who are struggling with Con formatting BRAVO ZULU!

    My problem:-
    I have set of scattered cells, (that are percentages) in a column. I am looking to flag these cells red, if their sum is is not equal to 100%.
    say B18= 2%, B41=32%, B58=33% and B76=34%. The sum of these =100% so, no color change to b18/b41/b58/b76.
    If the sum is not equal to 100% change these 4 cells to red
    Thanks for your assistance.

      1. It works!!!
        Thanks so much, looks so simple when you set it out.
        I battled for hours and got nowhere- slowly.
        Thanks again

  17. Hi,
    I am struggling to conditional format cells. What i need is if the word in in column A that the content of the Cell in A and B changes font colour. ( like a colour code )
    Column A has the Identifying criteria, such as book Genre and Column B has the book title.

    1. Hi! Create a separate conditional formatting rule for each colour (each condition). Use the recommendations from the article above. For example:

      =A1="Genre"

      And if you want to change the colour of the whole row by condition, use these guidelines: Change the row color based on cell value.
      I hope it’ll be helpful.

  18. I managed to conditional manage cells .. for example
    if value of cell A1 = >0 .. then cell A2 is colored red
    BUT what to do if I want to formate ..
    if value of cell A1 = >0 .. then cell A2 has the value of 1
    (used for automatically counting the cells that have a value >0)
    Is that possible?

    1. Hi! What you want to do is not formatting a cell. To set a value in a cell depending on the value in another cell you use the IF function. The following tutorial should help: IF function in Excel: formula examples for text, numbers, dates, blanks.

      =IF(A1>=0,1,"")

      To automatically count cells that have a value greater than zero, use the COUNTIF function. Read more: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique. For example:

      =COUNTIF(A1:A10,">0")

  19. Whats formula in format cell to conver gujrati number to english

  20. Hi Abelbits Team

    I'm attempting to create a formula rule that will change a cell fill colour if the 5 digit number entered in that cell is at a later date entered on another sheet in the workbook.

    Is this possible?

    1. Hi! This can be done by specifying in a conditional formatting formula the exact cell on the other sheet where this 5-digit number can be written. Or, write a formula in that cell that returns TRUE if the value is found on another sheet. For a more precise answer, your question contains no data.

  21. Hello,
    I have to highlight duplicates Values in Column A, If value in the column is "No". Also, duplicate value is equal to "Yes" I dont want to highlight that cell.
    ID Received
    12 No
    12 No
    13 Yes
    13 No

    2. Also i dont want to consider ID's for duplicate values if its equal to "Yes".

  22. Hello there!

    I have a budgeting template I'm using and I can't really get the conditional formatting to work.

    I have columns in a table labeled: Amount, Budget, and Difference. I'm trying to use conditional formatting on the Difference column with Icon Sets. Whenever I clicked on the Icon Sets button and selected the one I wanted, it would place the icon in the column, but it wouldn't be accurate for what I was wanting it to do.
    I'll put an example below:

    Amount Budget Difference
    $25.00 $50.00 (Up Arrow Icon) $25.00
    $75.00 $25.00 (Up Arrow Icon) ($50.00)
    $100.00 $20.00 (Right-facing Arrow Icon) ($80.00)

    That's just a small example of what keeps happening to me. I wanted to see how much more negative I was compared to the amount I budgeted for; however, the icons are misleading. It'd put a green/good arrow where the number was negative and should have had a red down arrow. I hope this makes sense. I'd include a snippet of my screen for more clarity, but it won't let me paste the image here. Please help!

  23. Hello,

    I have a data set that contains text in the 1st cell of a row and then numbers in the next contiguous cells, depending on number of samples taken.
    I want to format the numbers based on the specific text in the 1st cell as a 3 colour scale.
    Each 3 colour scale would be different based on the text in the 1st cell.
    eg:
    bananas,3 8,2,6,9 --> highlight cells red if bananas value <4, Yellow if 6
    oranges,30, 80,50 --> highlight cells red iv value <4, Yellow if 6

    I cant figure from this tutorial, how this may be achieved.

    Any help would be greatly appreciated

    1. Hi! If you want to know how to use Color Scale correctly, have a look at this article: Excel conditional formatting Color Scales.
      Because Color Scale cannot use another cell's value for formatting, I don't think it can be used in your case.
      You need to create a separate conditional formatting rule for each condition, as described in the article above.

  24. Hi,
    Thanks for your time.
    I am after a formula for conditional formatting that relies on data from 4 other cells.
    Basically, I want cell A4 to switch colour based off whether the values in B1, B2, B3 and B4 all equal "Pass". If they all don't equal pass, then the colour doesn't change.
    The conditions for B1:B4 equal: Pass, Fail or "blank"
    I'm thinking a formula something like: =(B1="pass"+B2="pass"+B3="pass"+B4="pass")

    Thanks for your assistance.

      1. Thanks Alexander, that worked great.

  25. I want a simple calculation, of one value divided by the value in the next column, to highlight the first column where the value exceed a limit, e.g. =$C2/$D2>3600, but it is highlighting cells that do not meet this condition and missing cells that do. I am baffled as to why this doesn't work - there are other conditional formatting rules on the worksheet, but they only apply to different columns. Do you have any advice as to what I could be missing please?

    1. Hi! Unfortunately, you don't write what errors you are getting. If you apply the conditional formatting rule to the whole column, do the formula for the first row =$C1/$D1>3600
      If this does not help, explain the problem in detail.

  26. I am trying to format a single cell based on the contents of two other cells in the same row as each other. The cell that I need to format is J9. The cells in the other rows are C11 and J11. The rows end at 61. I can get my formula to apply the formatting to J9, but not in all scenarios.

    What I need to be true in order for my main cell (J9) to be formatted is the following:
    C11 or J11 must be empty.
    C11 and J11 must be empty (or not)

    Thank you for your help!

    1. Hi! Have you tried the methods described in this blog post? If you are not satisfied, please let me know and I will try to help you. Based on your description of the problem, I can't help you because the condition "C11 and J11 must be empty (or not)" doesn't make sense.

  27. =AND($L2>365,$M2<18)
    I am using this formula to get rows from L row which has greater than 365 and M row has less than 18. The M row contains % values. It highlighting this data - 5584 112.95.

  28. Hello,

    I want a formula that formats my cell based on a range value in another cell.

    Example: I want my cell to be coloured if the number in another cell is between 20,02 and 50.

    I can't make it work with ("and") and ("or") formulas. I don't know why.

    Can anyone help me?

    1. Hi! Please re-check the article above. Pay attention to the following paragraph: Formulas to compare values (numbers and text). For example: =AND($B2>5, $B2<10)

  29. I haven't used Excel in years. I'm using Excel 360's Year Calendar template. Column A has numbers 1-31, columns B-M are months that contain text of when a project is due. I used Conditional Formatting to change the cells color to yellow when text is entered into a cell. I want to use Conditional Formatting to change the cell color to orange when the due date is 14 days from today and red when 7 days from today. Can you help?

    I've used =$A3=TODAY()+7 nothing happens

    Thank you very much for your time

  30. Hello! I am trying to format cells based on a formula. In a row, I need to find the tree adjacent cells with maximum cumulative value and format them with a color. Here's an example of my data:
    A B C D E F
    1 120 42 201 207 210 111
    2 19 109 91 63 33 25

    In the first row, cells C1-D1-E1 make up the higest total value (i.e., 618). I find it by using a formula =MAX(A1:D1+B1:E1+C1:F1), then pressing Ctrl-Shift-Enter. In the second row, the respective cells will be B2-C2-D2 (263). Now I just need to color-mark these cells based on the above formula, and then spread this condition onto next rows downwards.

    When I am trying to do so, I keep getting color-marked all cells which are selected, not only those which are true based on my condition. I tried this also with other formulas, but ended up with the same result (all selected cells become marked).

    Would greatly appreciate your help.

    1. Hello! Your maximum value formula does not match the problem description. However, I think it is impossible to highlight these cells using conditional formatting. I'm really sorry, we cannot help you with this issue.

  31. I am trying to conditionally format column A or based on a values in column B. However, it does not do anything.

    Contact Information (A) Key (B)
    Contact Name 100
    Nickname / Preferred Name 2
    Relationship Name 100
    Title 72
    Department 5

    Rule
    =$B$2<20 applied to =$A$2:$A$6 (Trying to Conditionally highlight Column A values when its column B value is less than 20)

    The rule works when individually applied on each row. Example $B$3<20 applied to $A$3 works.

  32. Sorry i also need this formatting to only happen if the response date cell is still blank...

  33. Hi this has been really helpful but I am trying to do a conditional formatting formula - I have a cell with a date when a complaint is received and have a cell where the response date is put
    I want to highlight the response date cell amber if it is 7 days or more from the received date and highlight the response date cell red if it is 15 days from the received date to show when responses are due, can you help at all? thank you

      1. many thanks

  34. i need to use condition formatting to highlight the colour of D Column if the value of D is greater than 1.1 times of B Column.

  35. Hello, I'm trying to format a pivot table that says if $B15="(blank)" then format the font on that entire row 'white'. This pivot table will be using different size data sources, so how do I need to use the applies to section. Every time I enter a range it automatically changes to absolute values.

    1. Hi, what formula must i use if i want to carry over a value/data from once cell to another if that value is greater than an zero. I want to say: =A2, if the value in A2 is greater than 0, otherwise 0

  36. Hi, I'm trying to set up a sheet to compare pricing from 3 different vendors. I have over 400 rows and I want the cheapest one in each row to be highlighted. I set up a formula to look like this. =AB6=MIN($AB:$AD6) and I applied to all the rows. It went and highlighted each row, the problem is, in some rows it did not highlight the cheapest one and in some rows it highlighted more than one cell. Can you help me?

      1. Hi, I commented earlier. The formula you provided is the formula that I used, and did not work. Is there a way to fix it?

        1. If you look carefully, I suggested a different formula. MIN($AB:$AD6) - it's wrong and it's impossible. If you apply it to the entire range, e.g. A1:C4000, the minimum number in each row will be highlighted. I may have guessed wrong, as I am having trouble understanding your question: "I want the cheapest one in each row to be highlighted".

  37. Hi! I am trying to format a column if the conditions below are both met.
    U2 is less than or equal to Y1
    F1 is less than or equal to Y1
    All values are dates and F1 and Y1 are fixed.

    1. Hi! Read carefully the recommendations in the article above. Pay attention to the following paragraph: Compare values based on several conditions (OR and AND formulas). There is an answer to your question there. To have both conditions true, use the logical function AND.

  38. In one sheet there are ITEM#, QTY, PRICE, CAT#. In another sheet there are ITEM# & CAT# which is populated. I have to enter the ITEM# in the first sheet and using vlookup CAT# will be fetched from the 2nd sheet. Now I want to apply conditional formatting in the 1st sheet on ITEM# column so that if anything outside the range of ITEM# entered from 2nd sheet then the cell will be colored in sheet 1 on ITEM# cell. Please advise what formula should I apply in the conditional formatting here.

  39. Hi,
    I have a timetable where I want several cells to change color if something is written in one cell.
    So in row 1 are the days and in column A are the times.
    Each cell in the table corresponds to one hour.
    I would like someone to be able to write their name + 3h or 6h etc and have the cell written in plus the cells below change color.
    So for example if someone writes name + 6h in D4. D4 to D9 will change color.
    If someone writes name + 3h in F10. F10 to F12 will change color. And so on.
    Is that possible?
    Thanks in advance

    1. Hi! The conditional formatting rule is set for each cell separately. I'm really sorry, looks like this is not possible with the standard Excel options.

  40. Good day,

    I tried to apply your tutorial to my situation, but my brain is not connecting the dots. I want to format a cell in column H if cell D is "Yes" and the time in H is > 00:01:20. See my summary with the or condition below. I'm getting errors on the variations that I'm attempting.

    Format cell:
    If D2 is Yes, and H2 > 00:01:20
    or
    D2 is No, and H2 > 00:05:00

    I assume two different conditions with stop if true, but I can't even get one to work.

    Thanks for any guidance you can provide.

  41. I work a lot with conditional formatting, sometime up to 100's at a time.
    Since the excel is more and more used in the cloud, and VBA is not possible anymore, I stumble across this, which maybe, very maybe, can be resolved by conditional formatting:

    I change a value in a matrix, let's keep it simple $A$2:$E$2
    By a formula I can change the date in $F$2 to today

    Is it possible to do the following with conditional formatting (it is NOT possible with a formula):
    I want $G$3 to display the date of the last change in the matrix.

    Since everything is always updated, I think it must be impossible however, there is a menu 'review/show latest changes', is there way to access those values, then we are saved.

    thanks for any response
    Johan

  42. Compare 2 columns for duplicates
    for this part I used this formula
    for column A:=COUNTIFS($B1$:$B$5,A1)>=1
    for column B:=COUNTIFS($A1$:$A$5,B1)>=1

  43. Hi!
    This is very helpful. I do have a question, though: I'm trying to set up a formula for a material list in Excel at work. The material list tab has a column for Quantities, Materials, and Unit Prices. The Unit Prices for each material item are auto-generated into each cell on Column I based on a material cost database located on a separate tab in the workbook. The material cost database is pretty comprehensive, but it does not provide a unit cost for all material items, so if an item is selected on the Material List tab that does not have a unit price in the database, Column I will show pricing for that item as $0.00 on the Material List tab. I would like to set up a formula that highlights that cell so that it flags attention to the missing cost.

    Example: Let's say I want to price a quantity of five pieces of wood that are 2x4x16'. I first enter a quantity of "5" in cell A5. I then select "2x4x16'" in the drop-down menu in cell D5. The unit price for this item will automatically appear in cell I5 and the extended price for the total cost of five pieces will appear in cell J5. Now, let's say I want to change the length of the boards from 16' to 14'. So I use the drop-down menu to select "2x4x14'". However, let's say that a price does not yet exist for the 14' boards in the cost database, so the unit price in cell I5 shows $0.00 instead of a unit cost. In this scenario, since I'm asking it for a price on a quantity (five boards), I want cell I5 to highlight itself so that it shows a missing unit cost for this item. At the same time, I also don't want the cell to turn red if the quantity listed in A5 is set to zero. Does this make sense? I've tried using the formula =AND(A5>0, I5>=0), but it did not work as I was hoping because it is now turning cell I5 red whenever a quantity greater than zero is entered into A5. Any suggestions would be appreciated!

  44. can you please help in manage the following formatting:
    They should be conditionally formatted so X days to 20 days have a no background, 30-15 days are in a yellow background, and 15-0 days are in Red. Once it goes into active status, +1 days, the conditional formatting background should become green.

    As for the end date time delta:
    The conditional formatting should be green until 4 months from contract end, at three months the background should be conditionally formatted to be shaded yellow, and then at 1 month, the background should be red.

    Also, please use the start date, end date or extension dates in the tracker and not ones that are two weeks out. We need real countdown to when the call order goes live.

  45. This is all beyond me however, I am trying to learn. I want to change the colour of the font in a cell when 2 other cells have text in the cell. i.e. B2=when C2 and D2 have text. Is this something that is possible? Any help would be greatly received.

  46. Hi,

    Fascinating examples and too complicated for me. I have only used conditional formatting for finding duplicates in different columns. It works fine for most item but some items that appear to be identical are not marked in red. My case is addresses. I am very carful about spacing, spelling, and punctuation all of which if not identical will fail to fail two items as identical. Any idea why Excel refuses to recognize visually identical items in isolated instances? After copying one oveer the other they do turn red (obviously). This is frustrating in trying to elimiate duplicate addresses. Any? idea what is going on.

    Charles

  47. Hi,
    I wonder if you can help me as I am tearing my hair out. I am trying to highlight a cell when the date becomes overdue but then change say from red overdue to green done. My column with the wording overdue and done does not show in the formula. The cell I want to highlight (J4) has the formula

    =IF(AND([@[Status ]]="Complete",[@Complete]=1),1,IF(ISBLANK([@[Due Date ]]),-1,IF(AND([@[Status ]]"Complete",TODAY()>[@[Due Date ]]),0,-1)))

    I cannot see where these words are fetched from unless they are hidden somewhere.

    1. Hi! It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work. However, the conditional formatting formula must return TRUE or FALSE (1 or 0), not -1. For conditional formatting, your formula cannot work.

  48. I am trying to replicate a conditional format. For each new cell the conditional value changes. So for example the result in A2 is /= to A1 but the result in B2 is /= B1. So far I can only get the results for A2-F2 to be conditional on A1 and not change for each new cell. I hope I'm explaining this right! Apart from just doing over 100 cells individually is there a way to easily replicate. Thanks

  49. I am trying to highlight duplicated if column A and column B are the same. I am creating a spreadsheet with book and page Column A is the Book and Column B is the page I want the duplicates of the same book and page. So if book 135 and page 2 repeats I want that highlighted. Is there a way to do this?

  50. Hi, I am trying to highlight a cell if the value of one cell id not equal to AND the value of a cell is equal to.

    I tried this but is not working

    =IF(AND($C44"#N/A",$D44="#N/A"),TRUE,FALSE)

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