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 32. Total comments: 1727

  1. Hi,

    I'm trying to get a conditional formatting to work;

    =AND($C2='Calculation Sheet'!$C$32,$AF2<='Template Master'!$D$32)

    C2 is a cell that will change between values [R, 1, 2, 3, 4, 5, 6]
    and
    'Calculation Sheet'!$C$32 currently equals the value "1"

    $AF2 value is currently 5
    and
    'Template Master'!$D$32) value is currently 13

    I would like AF2 to change colour if both conditions are met;

    When C2 and 'Calculation Sheet'!$C$32 equal the same value "1"

    and

    The value entered into AF2 is less than or equal to 13

    Any help would be great!

    Thanks.

    Ben

    1. Hi Ben,

      Your formula looks correct and it works with the conditions you listed. Could you please specify if you don't get the expected results?

  2. You are the best I have seen so far. My question is, When columb "D" decreases, I would like columb "E" to reflect the date that it decreased. How would I do that? Thank you.

    1. Hi David,

      Thank you for your kind words. Regrettably it is not possible to reflect the data change by using conditional formatting. You can create a rule for column D being equal to some value, less than or greater than a certain value, but not for the value increase or decrease.

  3. in conditional formatting. how can compare in three column condition is Only in three which customer is % of growth do not consider -% value

    JAN FEB MARCH
    63% -22% -22%
    250% 45% 45%
    331% -8% -8%
    101% 20% 20%
    260% 34% 34%
    21% -63% -63%
    76% -6% -6%
    556% -59% -59%
    47% 116% 116%
    290% -74% -74%
    pls help

  4. Hi,

    If I wanted to fill certain amount of cells in a row to match a value kind of a progress bar how could I do that?

  5. Hi,

    I need to set a conditional formatting in cells. I need column m to be highlighted if (m1-n1)>10%

    how can i do that?

    Thank you!!

  6. You have a great resource here, just taught myself some simple conditional formatting. Now for the hard stuff.
    I'd like to use conditional formatting based upon where the cell's data comes from.
    I have a spreadsheet that displays information coming from MS Query (which itself gets it from a SQL ODBC).
    I just had an end-user supply incorrect information to a doctor because they had copy-pasted a row of old information above the query results.
    Is there a way to conditional format the cells based upon where the data comes from? In this case, the erroneous data didn't have an underlying "Edit Query" (Data/Properties) option.

  7. Svetlana, thanks for this great information! I have a question that may be simple, but after toying around for a bit, I can't figure it out. Can you help me with the formula?

    My situation is, I want all the cells in Column A to change color when the corresponding cells in Columns T through AC EACH have a Y entered in them.

    (The Y means Yes, and the Color Change in A, which contains applicant's names, would signify that the applicant has all parts of their application, where parts are respectively marked as received by a Y in the corresponding column T through AC. Until the cells in columns Y through AC have a Y entered in them, they are left blank).

    Are you able to share a formula or advice for this? I am open to further questions, and grateful for your time!

    Mike

  8. Hi
    I am wanting to use conditional formatting to make a cell D5 green if () 5% and carry this along the row, always using the cell above, so its never the same cell I am wanting to use- is this possible.

  9. Great site! Thanks for all the good info.

    I would like to use conditional formatting to color one cell (K7) based on the individual numeric values contained in one column (K8 thru K1000). The individual numeric values in the column (K8 thru K1000) are the result of a mathematical formula. Conditional formatting color of K7 would be based on the value of any individual column value. Example: green for any cell value >5 and red for cell value >10. Thank you in advance for your help.

  10. Hi -
    I'm pretty good with lots of excel formulas (counts, database, financial, and the basics), so I must be missing something really obvious here.
    I've been using excel all morning, and sent a file to someone for input. After I got the file back, any excel file I open and try to do a basic sum of two rows returns zero.
    Cell 1: 100
    Cell 2: 3
    Cell 3 =a1+a2 returns zero
    1. I've checked the calculation window and it is set to automatic
    2. I've checked the cell format and made sure it's numbers.
    3. I even cut and pasted the cells as values and numbers just in case
    what is up?
    Thanks!

  11. Hi,
    Thanks for the help so far. I'm far better off than when I started, but can't quite get this right.
    I'd like to highlight a specific cell; A60, containing text "beef" only if a cell in other ranges (B17:Z17, B19:Z19, B21:Z21, B28:Z28, B35:Z35, B49:Z49) contains text "beef". Is that possible?
    Thanks
    Amy

  12. I'm trying to count items on a list that are grouped together. For example:
    Column A
    Dog
    Dog
    Dog
    Dog
    Cat
    Cat
    Cat
    Fish
    Fish
    Fish
    Fish

    In column B I'm trying to get those numbered:
    Column A Column B
    Dog 1
    Dog 2
    Dog 3
    Dog 4
    Cat 1
    Cat 2
    Cat 3
    Fish 1
    Fish 2
    Fish 4
    Fish 5

    Is there a formula that would get me what column B looks like? Right now its a manual process and very time consuming with the amount of data I'm using.

  13. Can I attach and send a small file? Conditional formatting with % in 2013 does not seem to work.

  14. Is there a way using conditional formatting to highlight cells when cells in two adjacent columns are within 10% of each other? So for example if the value of d3 is within 10% of of the value in E3 and the value in e3 is within 10% of of the value in d3, then highlight both e3 and d3.

  15. Hai,
    Is it possible to provide conditional formatting from one sheet to another sheet in a work book. (office 2007 version)
    i tried with this below equation but i am getting error message.
    Please see the equation I tried =Sheet2!$B$1="NO" and the message is " you cannot use references to other worksheet or workbooks for conditional formatting criteria ".

    Any solution for this??

  16. Hello, I have a question about conditional formatting.
    I am trying to set up a spreadsheet for training at my company. What I would like to do is I want to set up a formula that if a cell does not match another cell, it turns a red color but when it does, it turns a green color. I am pretty sure it is conditional formatting but I am so inexperienced with excel that I have no clue what I am doing. Please help!

  17. You are amazing!

  18. Continued...
    So, if either one of the cells in 1 row, has a time that is not 5 min more or not 5 min less (even the same time) than any of the other cells in the same row, it must make them Yellow. So in the below instance all will be yellow.
    01-10-15 21:44 01-10-15 21:30 01-10-15 21:45 01-10-15 21:30

  19. I need to do a conditional formatting, where if the time (data format is dd-mm-yy HH:ss) in one cell is less than 5 mins more or less than 5 min less than the other cell.
    eg:
    A1 A2 A3
    01-10-15 21:44 01-10-15 21:30 01-10-15 21:45 01-10-15 21:30

  20. Dear everybody, I have a following problem, and I hope that somebody will help me with that :)

    I need to do the following in an Excel document:

    If in the column X I have a value "approve" (text value), than the corresponding value from the X-1 column has to be multiplied by 1000 (in the same row, off course).

    Is there a formula that can help with this? Thank you so much in advance! Regards, Valentina

  21. I am trying to create a formula for dates and am having a problem. I want to enter a day 1 and the rest of the days populate as needed. I have the rest of the dates formulated so that they populate correctly but I need to figure out how to formulate the day one date in order to make the others follow when I enter day 1. (day 1, day 14, day 21, day 28 and so on). This is a schedule to create dates in the future on specific dates. Like I said I can't get the dates to populate when I enter day 1. Can anyone help?

  22. hi there,
    i have created a condtionl formating in colum A, where it has dates, and it will highlight if day i experied in red, 5 days remining amber and 15days yellow "which works really fine. on next colum i want to have a formula which will help me to remove colours from previous colum (condtional rule) if i just type "OK".

    Can you help me please ?

  23. hi there,
    i have created a condtionl formating in colum A, where it has dates, and it will highlight if day i experied in red, 5 days remining amber and 15days yellow. on next colum i want to have a formula which will help me to remove colours from previous colum if i just type "OK".

    Can you help me please ?

  24. I have highlighted some lower and upper values in my cells using conditional formatting.
    How can i generate a chart with same colors which i highlighted using conditional format?(i want my chart also to automatically show the colors which i used for the cell)

  25. I'm trying to highlight a cell if it contains certain words: ie. if it says "apple or strawberry or orange" filling it in purple. I want to create multiple rules of the same type but using different words, so one set would be one color and the other set another. But I can't even get this to work! I was trying to use the OR function, this is the last formula I tried.

    =OR(strawberry,blueberry,banana,blackberry,orange,apple,raspberry,pineapple,melon)

  26. Hi

    If range of cells (all of cell in selected row) are blank,highlight a row
    using conditional formatting.

    I can highlight a row if a cell is blank but it does not work for range of
    cells blank.

    What I mean is, if any cell of the selected row has any value or text, I do not want to highlight. I only want to hight rows that are blanks (rage of cell).
    Would appreciate your help.
    Name Date Address Invoice No Amount
    Ganesh 1/1/2015 Uttar Pradesh, India 10215 50
    Prem 2/1/2015 Uttar Pradesh, India 10216 1500
    Radhe Since B4:E4="" highlight this row
    Krishna 4/1/2015 Uttar Pradesh, India 10218 300
    Hari 5/1/2015 Uttar Pradesh, India 10219 400
    Shiva Since B7:E7="" highlight this row
    Shanti 7/1/2015 Uttar Pradesh, India 10221 80
    Om Since B9:E9="" highlight this row
    Kiram 9/1/2015 Uttar Pradesh, India 10223 700

  27. Good afternoon,

    I need to change/increment the date in column 2 by 1, 2, or 3 years in column 3 based on the fill color in column 1.

    Name Date Date2
    Name1 January 1, 2014 January 1, 2015
    Name2 January 2, 2014 January 2, 2016
    Name3 January 3, 2014 January 3, 2017

    Thanks in advance

  28. Hi,

    I don't know if anyone will respond to this or not, but I'm working on an excel spreadsheet and have a tricky conditional formatting question.

    I have already formatted column D, but I need help with E. What I am looking to do is this: Column D is the status of a lab, column E is the overall facility status (My four criteria are: lab not being used, lab is down, lab is limited, and lab is up and operational). So, 1=lab is operational, 2=limited, 3=down, and 4=not being used. For column E, I would like to say if the overall facility is operation, limited, down, or not being used. So, if, for example, the labs for one facility (D2-D4) contain at least one status that is down, the facility should also be considered down....and so on and so forth. But, if one of the labs is not being used, but the other two are operational, the facility should be operational.

    Is there a way to do this? I have been trying to figure this out for a couple of hours and have had no luck. It seems like all of the formulas I come up with do not narrow down my criteria enough. I do not know SQL stuff...but any other help will be appreciated! Thanks!

  29. Which formulas or conditional formatting can I use to say that IF value in Column I,L,O,R,U,X is than value in Column AG, make it red?

  30. How do I write the code for a list of variables associated to one colour?

    So I have a list of codes in a single column - efx, mbc, dad, hif and I want to highlight them all in the same colour.

    Does anyone know how I do this?

    Thanks

  31. I want my conditional formatting to search column $E (E8:E250)and if it says "Waived" or "Exempt", $G (G8:G250) is crossed out. This is what I have found so far but can not seem to figure out how to add the "Exempt". Also when doing the formatting of the cell it will not allow me to do a cross border within the cell.

    =ISNUMBER(SEARCH("Waived",E8))=TRUE

  32. Hi,

    I've been searching for ages to get an answer to the following and being a complete novice at excel not having much luck in getting a resolve. Any idea how I can do the following:
    I have a s/s with columns of dates and then rows of chargeable and non-chargeble. In the table data I have hours. I need to highlight any non-chargeable work over 0 in red and then any chargeable work over 30 in blue. Can I do this in 1 go by conditional formatting? Please help :)

    1. Hi Keira,
      According to your description, it is possible. If you can send us your sample workbook to support2ablebits.com, we will try to help you.

      1. Thanks Alex. Is there an email address for the support2ablebits? Thanks again for looking at this.

  33. Hi,
    When using Conditional Formatting:
    In cell U2, I would like to populate a formula in the cell if K2 says "Income"
    I've tried different ways and I can't get it to work. Help?!
    A

    1. Hello ajensen,

      Conditional formatting changes a cell's format, but it cannot change the contents of a cell. Probably, it makes sense to enter the following formula in U2:
      =IF(K2="Income",YOURFORMULA,"")

  34. Hi Svetlana,

    Thx for the great tutorial regarding conditional formatting.
    I am having a specific scenario and struggling to find a solution.

    I have a data sheet that is sorted by column C and then F.
    In my column D, I have dollar values and I need to match the negative ones against the positive ones (but the column C and F values for both negative and positive number must be same) and highlight.

    If you could help me regarding this, it would be great.
    Thx

  35. I'm not sure if you've already answered this; I've gone through the comments twice and none seem to address my particular situation (unless I'm blind... twice over :p ).

    I've created the conditional format:
    Cell Value $g$2 | (format red fill) | =$H$2

    So if the values in H2 and G2 are not the same H2 has a red fill. What I want to do now is do the same for the rest of my cells from H3:G3 on down the column. The only way I've been able to do this so far is to create a rule for each and every cell which gets quite tedious as some of my lists are 50+ items long. Is there a quicker way to just copy that conditional formating where the cells automatically change with each row?

  36. Hi,
    I have a sheet that I want to format.
    Column B,C,D will have numbers entered manually (rows 10-50).
    Column E will be the sum of B+C+D
    Column L will contain a "Y" sometimes.
    Here is what I need to do....
    I know how to set it up to highlight the highest single value in B or C or D.
    And I know how to set it up to highlight the highest value in Column E.
    What I don't know how to do is to NOT include in the conditional formatting, any rows where column L has a "Y".
    Any assistance is greatly appreciated.
    Thank you!

  37. Hello, I’m trying to use Conditional Formatting to Highlight the Header Row cells if any cell in that column is not blank. So I used =ISBLANK(G2:G10000), but when I use the format painter to apply this rule to the adjacent header row cells, the G2:G10000 reference doesn’t change. So, each column header is looking back at G2:G10000. How can I build my Formula so that Conditional Formatting will highlight the Header Row if the cells each column meet the conditions, as I paint the formula across the header? -Thanks, Ron M.

  38. Hi,

    how to set the formula,

    if the sum of certain fields equal to or greater than 1 to show the number 5, and if it is less than 1 to show 0.

  39. i want that if i minus values from two cell the ans i get if less than 0 it should return the value to 0

  40. what is the formula,only one forwarding cell is highlight with color about the calculation of the total amount when it is highlight

  41. Hi,
    I want to apply conditional formatting on cells within a worksheet that start with the same letter. For example, I have H1, H2, ....., H20 as values in my worksheet and I would like to apply a rule that every time the value in a cell starts with "H", the cell is filled in a certain color.
    Can you help me ?
    Thanks,
    Marc

  42. That was Awesome, Thank you

  43. Hello, can you help me with below argument. what formula can i use for this.

    a b c
    4138.6 6 Tonner TRUE
    3188.6 4 Tonner TRUE
    3188.6 6 Tonner FALSE
    4138.6 4 Tonner FALSE

    the argument is (if b=6 tonner and a is greater than 4100 then its true) or (if b=4 tonner and is greater than 3100 then its true.

  44. Hello,

    I am trying to highlight a cell (C9) red if it is between 12 and 100 and if another cell(J9) is blank. also want C9 to turn green if it has the same number range but when J9 has "C/W" in there. I know I need to separate formats, but everything I've tried turns the cell green when the blank is there.

    thank you

  45. Hello,

    I have a string of numbers:
    4 3 8 9 4 6 6 10 6 15 1

    I want to higlight each case:
    1. in red(for example, or put an lower red arrow) if the number in the next case is lower than the previous,
    2. in green(for example, or an upper green arrow) if the number in the next case is bigger and
    3. in orange(or square orange arrow) if the number in the next case is equal with the previous.

    Thank you.

  46. Hello,
    I have the following formula and it keeps producing following error: "#NAME"

    =IF(OR(F6=YES, G6=YES, H6=YES), "At Store","Not at Store")

    What am I doing wrong? The same formula works if i replace "YES" with "10 or 20 or 30".

    Thank you so much.

    1. Hi Sarah,

      All text in a formula should be enclosed in double quote marks. Your formula needs to read as follows:

      =IF(OR(F6="YES", G6="YES", H6="YES"), "At Store","Not at Store")

      1. Thank you so much. This is great!!!!

        As a follow up here is my major issue. How do I add this formula to an if, iserror, index formula? I keep getting an error.

        Working Formula:
        =IF(ISERROR(INDEX($B$2:$C$41,SMALL(IF($B$2:$B$41=E3,ROW($B$2:$B$41)),COLUMN($A$2:$A$501)),2)),"",INDEX($B$2:$C$41,SMALL(IF($B$2:$B$41=E3,ROW($B$2:$B$41)),COLUMN($A$2:

        Non-Working Formula:
        =IF(OR(F3="Yes", G3="Yes", H3="Yes"), "Available", "Not Available", ISERROR(INDEX($B$2:$C$41,SMALL(IF($B$2:$B$41=E3,ROW($B$2:$B$41)),COLUMN($B$2:$B$5000)),2)),"",INDEX($B$2:$C$41,SMALL(IF($B$2:$B$41=E3,ROW($B$2:$B$41)),COLUMN($B$2:$B$5000)),2)))

        Am I adding to incorrect location?

  47. ok,

    i was hopping i would find the answer on the page but i haven't so her goes...
    what i want is for example in b7= if(b6>b5)=a3 however if (b6<b5)=a2
    i cannot figure out how to do this, it needs to be that if the value of cell b6 is greater than cell b5 i get the result as cell a3 however ig cell b6 is less than cell b5 the result is cell a2. how do i do this? (keeping a running tally on a competition and want cell b7 to prepopulate for the winner on any given day)

    1. Hi Shantal,

      You don't need conditional formatting for this. Simply enter the following formula in B7:

      =IF(B6>B5, A3, IF(B6<B5, A2, ""))

      Please keep in mind that if B6=B5, the formula will return an empty string (blank cell).

  48. Hi

    i would like to know if it is possible to format a cell depending from other cell, my case is as follows.

    i´m doing a vlookup from severals values but in some cases the values are percentages and other integers, in that cases a need to change the format to % or $ so i need the values keeps there format after the retrieve from the vlookup.

    thanks

  49. Hi Svetlana,

    I need to conditional format cells based on a changing value.

    B20 contains a percentage that changes each day (30%, 35%, 40%, etc).
    I want cells G23:G37 to conditional format if they are equal to value in B20, within 5% of B20, above B20, and below B20.

    Do I need a separate rule for each set of criteria I have or can I combine into one formula line?

    1. Hi Dillon,

      You will need a separate rule for each criteria because you would want to use different colors, right?

      And you can create the rules based on the following formulas:

      Equal to value in B20: =$G23=$B$20

      Within 5% of B20: =ABS(($G23-$B$20)/$B$20)>5%

      Above B20:=$G23>$B$20

      Below B20:=$G23<$B$20

  50. Hi,

    I want to get greatest value among two cells to be in new cell how do I do that.

    Regards,
    Ahmed

    1. Hi Ahmed,

      You can use a formula similar to this:
      =MAX(A1,A2)

      1. Thanks a lot.

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