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

  1. I have 12 columns with turnover and a column with total turnover, would like to highlight, the total as follows;
    if col.1 and col.2 are empty and col.3 and col.4 have data and rest of columns are empty, fill with colour.
    Thanks

  2. What I wanted to achieve is cell formatting depending on the comparison of another two fields that have a date in them (one is a straight date, the other calculated date). If the month in those two dates matches (its equal) then to trigger the formatting (shading) of the third cell.
    A formatting rule of something like
    =MONTH($K21)=MONTH($R12)

    But because this is not a valid formula it doesn't work. I have added this invalid formula here for making my question easier to understand.
    Thank you

    1. Hello, Jack,

      actually, your formula for is correct, if it doesn't work, most likely there's something wrong in your table or you apply the conditional formatting incorrectly.
      If you still require our assistance, please send us your workbook with the data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Don't forget to include the link to this comment into your email.

      We'll look into your task and try to help.

  3. How would I create a conditional formatting rule based on the below formula. This formula is counting (then averaging in a separate, similar formula) worked hours but excludes login and logout times outside of the given range. I’d like to highlight the cells in another column (H:H) that meet the below criteria so that it’s easy to tell which times were actually used in calculating the final formula. For example. If 40 days were “worked” during the selected period, but only 34 days were used to create the result, I want to highlight those 34 cells.

    =COUNTIFS(F:F,">"&M2,F:F,""&M4,G:G,"<"&N4,A:A,"<6")&" days"
    Where:
    F:F = Log in time
    M2 = min log in time
    N2 = max log in time
    M4= min log out time
    N4 = max log out time
    A:A= day of the week in numeric form
    "&M2,F:F,""&M4,A:A,"<6")
    H:H=sum of hours worked (Logout-Login).

    Thank you in advance,

    Anthony

  4. I have a sheet that has various colors based on the cell character:

    "A" = Color Green font
    "R" = Color Purple font
    "C" = Color Blue font
    "I" = Color Red font
    etc..

    But in the sheet there are some cells that identify as "C/R"

    So the cell would have to be C in Blue / R in Purple.
    Two colors in the same cell separated by the "/"...

  5. I want to a cell to default to a text / value if another cell contains specified text/ value

    If cell a1 = text then cell b1 = 135,
    or similar scenarios.

    Thanks
    Pad

  6. Hello,

    I have a spreadsheet with 4 individual columns, then 15 column pairs. Each pair of columns is a set of data from two different sources, sorted by a common key value. What I need to do is highlight the value in the second column (of the pair) of a row that has inequal values.

    I know I can use, for example, =$E2$F2, applied to column $F:$F to highlight values in F that don't match corresponding value in E. But, how do I do this properly over 14 more pairs? Do I need 15 rules?

    Thanks

  7. Hi!

    I desesperally need your precious help please: i have a worksheet with about 70 rows and 52 columns. Each row represents a store/client, the column represents the weekly quantity ordered. Most of the time the quantity doesnt change – except for certain periods. I need to see clearly when the number change. i though conditional formatting could help me but i cannot make it works. I need to compare all the cells to their adjacent cells. I tried the formula = B$3C$3, then i tried = $B3$C3, then without dollar signs … nothing worked (it highlight entire columns rather than cells). I need to automatically highlight the cell that is not equal to the cell to its right.
    Many many thanks in advance!

  8. hi
    this Mohammad Dawood . i from Afghanistan . when i change an alphabeth it have to give a color in the Excell . please gave me Information thanks

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

  10. Hello,

    I have a issue that I need help with.
    Basically I have two sheets, one sheet that lists All Items and another that list the Items Sold from the all items sheet. I've used vlookup to pull all the relevant data from All Items for each item on the Items Sold Sheet. Now I want to highlight the SKU that appears in Items Sold in All Items. At the same time All items Sheet has more than one SKU whereas The Items sold has only one. I have used the below formula with Conditional Formatting on the All Items Sheet to try to accomplish this however it highlights all the Skus with a corresponding value from the SKU's column in Items Sold =COUNTIF(SKU, $B2)=1. I want it to highlight all the items sold but only one of the items. Can this be done?

  11. Hello,

    I have one issue,from a selected raw how I can highlight the whole column or selected portion if it identify a preset text or number?

  12. Hello

    Can anyone plz help me for following:

    I have 4 Columns A,B,C,D
    A= Customer Name
    B= Invoice Amount
    C= Due Date
    D= Formula to insert?

    In (Column D) I would like to sum the Invoice Amount(Column B) according to individual Customer(Column A) if due date is today or already past(Column C).

    Example: Today is "29th Aug 2017"

    A(Customer) B(Invoice Amount) C(Due Date) D(Due Amount)
    John $15 29th Aug 17 John= $15+50=65
    Smith $10 25th Aug 17 Smith=$10(Only 1 invoice due)
    Smith $13 10th Sep 17 Obama=No Due
    Obama $18 18th Sep 17
    John $50 28th Aug 17

    Please help.

  13. I'm sorry my question is not coming over correct, 2nd try: I have a spreadsheet with 10,000 rows and 10 columns. Data starts in Row 2, Row 1 are headers. I want all of Row 2 to turn Red if the value in column G, row 2 is =1 then the whole row is Green. I then have to repeat this for the next 9,998 rows.

  14. Hello I have a spreadsheet with 10,000 rows and 10 columns. Data starts in Row 2, Row 1 are headers. I want all of Row 2 to turn Red if the value in column G, row 2 is =1 then the whole row is Green. I then have to repeat this for the next 9,998 rows. Each row should look to it's own row's value in column G. But when I do conditional formatting in row 2 and drag down it will only look to G2 even if I'm in row 3,200 etc. I've tried $G2, and G2 but it will only look at the G2 no matter what row. In closing row 3543 needs to look at G3543's value to decide format. What am I missing? Thank you!

  15. HI
    I want to create a drop down menu that would carry out 5 rows of data.
    ie
    Cash
    100
    200
    300
    400
    500
    How would I achieve this?

    Thanx.

  16. Thanks that formula worked.

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

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

  19. Hi,

    I have a spreadsheet that contains information, I have two search criteria, then when they find a match they highlight the row.
    What I need to do though is - when I put in two search parameters, I want the row to highlight in a different colour.
    At present search one highlights in Green, the second blue.
    Each column after M has a keyword on which I search, and I have upto column AB and down to 20000.
    Hope that all makes sense.

  20. Hi, I'm trying to conditional highlight a cell based on the text in another cell which I can do but I'm having trouble in a particular situation. The formula I'm using is =c6="roller" with green fill format. This works fine except when c6 has its own conditional highlight, which is to display with a red fill with a zero value. If I right roller in an empty cell with no formatting and direct the formula to that cell it works fine. Any help would be greatly appreciated. Thanks in advance.

  21. Hello, I'm having a hard time getting my excel sheet to do what I want...

    If any cell in column G contains "YES" then it turns green. That much I have.

    Now, (if column G has YES) I want column P to highlight RED until it is filled, in which case I want it to go back to no formatting.

    I want the red to prompt me to enter data there when I receive it.

    Is there a simple way to do that?

    Thank you!

  22. Good afternoon
    my problem is
    i want to write in row (A1) 1000 and auto write Sunil in Row (B1) and
    then again write if row (A1) 1400 then auto write Omji in Row (B1) thru conditional formatting

  23. Thanks for the guidance.

    Using your tip, I can conditionally format to color a column depending on whether the next cell to each cell is blank or not.

    it helped in reducing my work.

  24. I'm trying to create a conditional formatting rule, but nothing seems to be working, could you help.

    I like to create attendance tracker sheet and almost completed but one condition format understand.

    In Automated attendance sheet in one employee Cell Condition applied as R (Staff Resigned)then i want to be next all cell range format blank or RRRRRRR..

  25. Hi,
    I want to change the color of the cell depending upon the sum of the column.
    For Example In the below salary column if I enter 10 the color should be green because the sum of column is less than 100.When I enter the next value 50 ,the color should be green since the sum(10+50) is less than 100,
    When I enter 70 the colour of that cell should be red since the sum (10+50+70) has exceeded 100.
    Salary
    10
    50
    70
    Please help me to find a solution.

  26. Hi Svetlana! Thank you for your examples. I have one issue with my excel. Lets say I have Supply time (the old one) and new supply time. I made a conditional formating in a separated row like this new supply time -old supply time and if the result is positivi we have increase in a supply time if it is negative we have a decrease and if the result is 0 we donot have any changes. BUT i have an issue with the decreased supply Hi Svetlana! Thank you for your examples. I have one issue with my excel. Let’s say I have Supply time (the old one) and new supply time. I made a conditional formatting in a separated row like this new supply time -old supply time and if the result is positive we have increase in a supply time if it is negative we have a decrease and if the result is 0 we do not have any changes. BUT I have an issue with the decreased supply time, because I still have some cells that are not fellfield with the new supply time and in my new row it is showing that there is a decrease. What rule to create to highlight the cells that are still not fellfield?
    After that i have to show some results like every week how may items I already fellfield with a supply time how many of them decreased or increased but how can I do this if I’m working with a filter. The function COUNTIF doesn’t work in this case.

    Thank you in advanced!

  27. I'm trying to highlight a cell only if it is a result of a formula. IF the cell is an entered value i don't want the cell formatted.
    I want to highlight only the cells in a column that have values derived as a result of a formula. If the cell has a numerical value entered in the cell i want the cell to remain unformatted

  28. one cell is highlighted with conditional formatting -the rule is if the date is in the next month it will be formatted & highlighted.Now i want to highlight the entire row on the basis of this rule applied on that specific cell.what is the procedure.

  29. Might not be the right section for this, but seems the most likely.

    I am constructing a costing spreadsheet with multiple criteria that will effect the cost of items I work with. What I would like is to have a drop down menu with various 'levels' of input, say 'Super Premium', 'Premium' and 'conventional'. I would then like to have that selection refer to and apply a different numerical value in a formula in another cell.

    I know how to create basic drop down menu's, what I need help with is applying the different numerical values to my formula based on the selection.

  30. Sorry mic-copied formulae I am using I have :

    =AND($P13="<100",$D13<TODAY())

  31. Hiya,

    I wondered if anyone could assist ? I am trying to do some conditional formatting as per below :

    IF values in P13 is less than 100% AND the date in Column D is Less than today - make the whole line red with black text. I am using the below formula and choosing the format but it just doesn't seem to do anything ! Confused !

    =AND($P13"<100",$d13<TODAY())

    Thanks in advance

    G

    1. Sorry mis-copied formulae I am using I have :

      =AND($P13="<100",$D13<TODAY())

  32. Hi there is there a way to have one cell state a specific text if another cell is below a certain number?

  33. I have starting date in cell A5 and ending date in cell G5. I want all the dates between A5 and G5 including dates in cel A5 and G5 in some range.

    Please get me the formula for the same.

    Thank You !

  34. HI i need a conditional format to state in a cell a certain value if it is within a range.
    for example:
    1 to 100, the cell should show A
    101 to 200, the cell should show B
    201 to 300, cell = C
    and so on...

    also,
    i have this dilemma:
    a cell should show "O" if a value is within +/- tolerance; or should show "Y" if the value is = to the maximum tolerance; or should show "X", if the value doesn't meet the tolerance
    example:
    tolerance is +/- 0.5
    the cell should show "O" if value is any of the following: -0.4, -0.3, -0.2, -0.1, 0, 0.1, 0.2, 0.3, 0.4
    or
    the cell should show "Y" if teh value is exact to the tolerance, +0.5 or -0.5
    or
    the cell should show "X", if the value is less than -0.5, and greater than +0.5

    any help is highly appreciated..thanks!

  35. Hi,

    I want to format a cell into three different colors based on condition provided to different cell, let's say if value in cell 7 it is red.

    Kindly guide me.

  36. I'd like to make the font in a cell change to white (or disappear) when the content in the cell next to it is also in white font. How do I go about doing this please?

  37. Hello,

    I am trying to apply conditional rule to highlight my dates ranging from monday-sunday to highlight current week, How can I apply this rule?

  38. Hello,
    I have a spread sheet that tracks vehicle hours. In column B is the vehicle hours which are updated weekly. In column G is the last service hours. Starting at row 2 I would like G2 to be green when B2 is equal to or greater than G2 and less than G2+499, would like G2 to be orange when B2 is greater than G2+500 but less than G2+999 and would like G2 to be red when B2 is greater than G2+1000.
    Could you help me with the formular please as no mater what I try I can't seem to get it to work.
    Thanks

    1. Hello, Carl,

      you need to create the rules for conditional formatting that would apply to G2 (G:G if for the column). Try the following rules:
      For green:
      =AND(B2=G2,B2<(G2+499)) For orange: =B2>(G2+500)

      For red:
      =B2>(G2+1000)

      Hope this helps!

  39. I have a spreadsheet for work, in which i record correspondence. I have a column for "days open" (C) and a column for whether the sender is VIP (G). The non VIP letters have a deadline of 15 days, VIP letters have 15 days. I want the days open cells to be highlighted when VIP correspondence goes over 5 days open, and non VIP goes over 15 days. I'm using =IF(G2="Yes", C2>5, C2>15), but for some reason it is highlighting all cells regardless of value. I also have a conditional formatting rule for any cell reading "Closed" but i don't imagine it affects this.

  40. THANK FOR GIVING SUCH GOOD SERVICES I WANT TO FOLLOW BY USING MY EMAIL ACCOUNT

  41. Hi, I want to use conditional formatting to highlight a cell a particular colour dependant on whether other cells are filled in.

    To put into context, I use to spreadsheet as a checklist for traders:
    Trader Name - Risk Assessment Received - Hygiene Certificate etc..

    Then once I have checked the box to show I have received all the information I want the cell at the end of the row to turn green after all the cells in the range are shown to not be empty.

    I have achieved this for one row using a method listed here. However, I have several rows I want this to apply to and if I copy/paste or drag down it doesn't apply to formatting to the specific row, it just bases it on the first row.

    I hope this makes sense.

    1. Hi, Dom,
      Supposing your data ranges from A to H column, try this formula as a formatting rule:
      =COUNTIF($A2:$H2,"")=0

      where "=0" checks whether the cell is blank. Notice, that we use absolute references for columns and relative for rows (to apply the rule to the next rows of the same column). Also, keep in mind that the formatting rule may multiply itself, when you drag it to another cell. For more info: COUNTIF.

  42. Is there a way to highlight 'A-F' if 'B' has specific text in it? To happen infinitely, not solely on one column. Database purposes.

    1. To highlight 'A-F' according to the text in 'B' create a formatting rule. To do that, go to Home tab, Styles box, Conditional Formatting > New rule. Use a formula below there and apply the formatting to the range of columns that you wish to highlight
      =$B1="ENTER_THE_TEXT_TO_LOOK_FOR"

      This point of the topic will provide you with example pictures on how to create and edit the formatting.

  43. If the value is more than 2000 or equal to 2000 write 2000. same as if the value is less than 20 or equal to 20 than write 0 in excle sheet.I need help please.

  44. if the value is 2000 write 2000. I would like to work above condition in excle sheet(i am using excle 2010)

  45. I'd like to make a To Do calendar with the Format
    as a meaning of
    08-09 From 8 to 9 but I got 08-sept as a result
    09-10 From 9 to 10 but I got 09-Oct as a result

    Please help

  46. My question is: Can Excel automatically insert a column with information from one book into another. This would either push a column over to insert the data or put in the column at the end of the current data. So this would be on going adding a new column up to a total of approximately 20 columns. The other book would always copy over the same column. This may be some high level stuff. Thank you very much in advance for your help.

    Kindest Regards,
    Todd Haig

  47. This is a very helpful article, Thank you.
    I do have one question. I am trying to fill in a cell with text based on the text in another cell. For example if A1=Apple put Applesause in D5, but if A1=Orange then put Orangejuice in D5.

    I am not the best with formula's or conditional formatting, and am hoping someone can help me out.
    Thanks - Tia

    1. Hello Tia,
      place the following formula in D5:
      =IF(A1="Apple","Applesause",IF(A1="Orange","Orangejuice",""))
      Keep in mind, that if there's neither apple nor orange in A1, D5 will remain empty.

  48. What a great article - Thanks!
    I have solved a lot of my requirements from the information I have learned here. However I have one formatting problem that has got me stumped. I have a row of data that contains numbers in some cells and text in others. What I really want to be able to do is automatically highlight a cell if the number in it is larger that the previous number in that row bearing in mind that number might not be in the immediately previous cell. e.g A1 - 1000, B1 - 1000, C1 - 1100, D1 - 1100, E1 - "text", F1 - "text", G1 - 1200, H1 - 1200 In this example I would want C1 to be highlighted as well as G1. C1 because it is larger than B1 (the previous number in row and it is larger than it) and G1 because it is larger than D1, again the previous number in the row.
    Hopefully that (a) make sense and (b) someone can put me out of my misery.
    Thanks in advance - AJ.

    1. Hi AJ,
      I'm afraid it won't be possible, because you will need to construct a complicated array formula, and array formulas can't be used in conditional formatting.

  49. hi i would like to know about the method to put color for the cell or cell content based on another cell value. Can you help me

  50. This is a great article, and has really helped me out. I do have one question that I can't figure out though that I thought maybe someone could help me with. I have column D filled with project numbers, and column F with either "projected" of "revised". I'm trying to find a way to locate the duplicates in column D, and from there pick out the "projected" to hide (or highlight with white on white) since it is not valid after there is a revised.

    I have come up with:

    =AND($D1=$D2,$F2="projected")

    though this highlights the "revised" for some reason, and also misses a number of duplicates in column D.
    Any help would be GREATLY appreciated.
    Thanks!

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