Comments on: Excel Conditional Formatting tutorial with examples

Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and spot variances of cell values with a quick glance. Continue reading →

Comments page 9. Total comments: 318

  1. Hi, I am trying to create a conditional format the when Cell B2 = or contains a certain word then a particular number goes into cell C2, D2 or E2 depending on what Cell B2 contains.
    ie if B2 = cat then C2 = 1, if B2 = dog then C2 = 1 and D2 = 1 etc

    Thanks in advance

    1. Hi Jurgen,

      With conditional formatting you can only "format" cells, i.e. change their background or font color, add icon sets or color scales, etc.

      To put a certain value in a cell depending on another cell's value, you need an IF formula. For example:

      Formula for C2: =IF(OR(B2="cat", B2="dog"), 1, "")

      Formula for D2: =IF(B2="dog", 1, "")

  2. Hi can you help me with this problem?
    I want to separate the amount in an excel 2007
    For example:

    A1 = 12

    I want the answer would be
    A2 = 5 (Constant/or Keeps the value Lesser or equal to 5 but not negative)
    A3 = the excess amount from the value of A1

    1. Hi Kimoy,

      If my understanding of your task is correct, you can simply put 5 (or any other number you want) in A2, and =A1-A2 in A3.

  3. Hello, I am trying to do conditional formatting on a finacial scorecard. I need to do Red, Yellow Green based on the following criteria.

    Column B is my target revenue - $30,000 - Every column after that is for a week of revenue and I need to color them based on the following guidelines.

    Within 5% - Green
    Within 5-10% - Yellow
    More then 10% - Red

    So if Column D's revenue is $45,000, I need to color it Red. But if column E's revenue is $30,500 it should be green.

    I have tried everything and I am sure I am making a silly mistake. Can you please help?

    Thanks!

    1. Hey Tina,

      I don't know if you're still having this issue, but this may be helpful to other users if you've already figured it out!

      Assuming the following:
      - The first target revenue is in B1 (if not, simply adjust the formulas used below)
      - The first week's revenue is in C1 (same note as above)

      You can do the following to accomplish your goal:
      (With C1 selected)
      We need to make three rules, one for each case. All of the following rules will be added using the following process:
      1. Click the "HOME" tab on the menu bar
      2. Click "Conditional Formatting"
      3. Click "New Rule..."
      4. Click "Use a formula to determine which cells to format"

      ### For red: ###
      Assure that C1 is selected!
      1. Begin adding a new rule (using the process above)
      2. Paste the following formula in the formula text box:
      =OR(C1$B1*0.1)
      3. Click “Format…” and set the fill color to red
      4. Personally I set the font color to white to make it more readable, but this isn’t necessary
      5. Click “OK”, you’re done!
      Now when the revenue in C1 is outside a 10% margin of the target revenue, the cell will be filled red! (Note that blank cells will also be filled red, this is covered in the section “Catching blank cells”)

      ### For yellow (very similar to process for red): ###
      Assure that C1 is selected!
      1. Begin adding a new rule (using the process above)
      2. Paste the following formula in the formula text box:
      =AND(C1>=$B1-$B1*0.1, C1=($B1-$B1*0.05),C1<=$B1*1.05)
      3. Click “Format…” and set the fill color to green
      4. Click “OK”, you’re done!
      Now when the revenue in C1 is within a 5% margin of the target revenue, the cell will be filled green (be sure to check out the “IMPORTANT” section to make sure this works)!

      ### Catching blank cells: ###
      Assure that C1 is selected!
      You can skip the next section if you want blank cells to be filled red (or don’t mind either way)
      1. Begin adding a new rule (using the process above)
      2. Paste the following formula in the formula text box:
      =ISBLANK(C1)
      3. Click “OK”, you’re done!
      Now blank cells will simply be ignored!

      ### IMPORTANT!! ###
      To assure that the rules act as intended please do the following:
      1. Click “Conditional Formatting”
      2. Click “Manage Rules…”
      3. Make sure that the rules are in the following order (distinguishable by the fill color):
      Blank
      Green
      Yellow
      Red
      NOTE: Rules can be moved up or down using the arrow buttons to the right of "Delete Rule"
      4. Make sure that for each rule the “Stop If True” box is checked (far right column)
      This assures that only one rule will be applied to any given cell

      ### Finishing up: ###
      Now that we have all the rules made for C1, we want to be able to apply the same rules to other cells!
      The process for copying conditional formatting is included in the main article, but the process is summarized below:
      1. Select C1
      2. Click “Formula Painter” under the “HOME” tab
      3. Drag from C1 to the end of the range of cells you wish to copy the formatting to (e.g. from C1 to L1 or from C1 to C10 or from C1 to L10)

      That’s it! Please let me know if you (or anyone else reading this) experience any problems!

  4. I have three rows of numbers with the highest number highlighted in each row. Can i highlight the lowest number of only the numbers that are already highlighted?

  5. Hello,

    Need some help with a formating rule. I have several times in one colum example:
    4:17:49pm
    2:05:03pm
    11:15:04am
    9:00:49am

    And I want to do a color rule to highlited red if the time difference is more that 2 hours apart. How do I do this? Someone please help me out.

    Thank you a bunches
    Alex

  6. Sorry Ms. Cheusheva,

    I just came across your comment to Stuart above. I will check out that link to see what I can do.

    Cheers,
    George

  7. Ms. Cheusheva,

    Thank you for an informative explanation on conditional formatting. I do have a question though with my Excel 2007. I have notice that visually if I use the conditional formatting to change the colour of the text in the cell to a colour like red, I can see it in the spreadsheet, but I can’t run any macros or functions against it (like countif = red)? After some checking to see where the problem is, I notice that the text in the cell that even though visually to the eye it is red it has the default code of black when I check the font format? Is this a bug or something that I am missing?

    Cheers,
    George

    1. I found your link to Stuart. I will read through that and see.

      Cheers,
      George

  8. Its worth a shot... but I am trying to run an audit on a a spread sheet, and am using the Conditional Formatting. I have been trying to high light cells in a Column that is used for Hours submitted on a time sheet. Our company policy is to round to the nearest 15 Minute increment. So I would like to highlight any cell that is not rounded to the nearest 15 minute increment. I read several places how excel divides an hour up in percentages, but cannot find a formula to highlight the field without Rounding it. I am new to the formulas so I need assistance, or, even if you can point me in the right direction...?

    1. Hey Amy,

      Assuming the hours submitted are in decimal form (e.g. 6 or 8.0 or 7.75) you can use the following to accomplish this for a single cell (then use the process outlined in the main article to copy the rule to other cells):
      1. Click the "HOME" tab on the menu bar
      2. Click "Conditional Formatting"
      3. Click "Add Rule..."
      4. Click "Use a formula to determine which cells to format"
      5. In the formula text box paste the following formula and replace "##" with the cell you want to apply the rule to
      =NOT(MOD(##,0.25)=0)
      6. Click "Format" and select a fill color (I picked yellow)
      7. Click "OK" for the format and then again for the rule
      8. Done!

      Now for any cell that the rule is applied to, if the hours recorded aren't rounded (e.g. 8.2) the cell will be highlighted!

      1. I should amend part my last sentence to say "if the hours aren't rounded to the nearest 15 minute increment"

  9. Hi Svetlana,
    How do I get a sum for shaded cells of varying colors (and just a shaded cell total not the actual value in it)

    Thanks.

  10. I have a spreadsheet several hundred lines of products with the prices from 5 vendors in column . I want to auto-highlight the column that contains the lowest vendor price for each product (that is not zero). How could I get this to work with conditional formatting?

  11. Thanks for all your insight, very helpful. I have a simple budget spreadsheet and have set up a conditional formatting rule that works well. What happens is column B is my target monthly budget. Columns C through M are what I have spent each month. When I go over the number in column B the cell highlights.

    Rule Format Applies to
    Cell Value > $B$2 AaBbCc =$C$2:$M$2

    Works great, what is bugging me is I can’t figure out how to copy and paste to the other rows and change the rule for the “cell value”. It keeps $B$2 but does change the “applies to” cells. I know I could have done each row separately in the time I have spent on this but like I said it is bugging me. Thanks in advance ;}

  12. Hi svetlana

    i have 5 columns with 5 different subjects.I need to show the student who has below 15 marks in all subjects.
    example
    St name col1 col2 col3 col4 col5
    xxxx 10 20 16 9 18
    yyyy 18 22 17 19 10
    zzzz 20 21 12 19 19
    AAAA 25 21 18 19 19
    BBBB 26 25 19 23 20

    i need to display like this (those who having below 15)
    St name col1 col2 col3 col4 col5
    xxxx 10 20 16 9 18
    yyyy 18 22 17 19 10
    zzzz 20 21 12 19 19

    help me....
    thanks in advance.

  13. hi svetlana

    i have 100 rows with different values,some of values are highlighted with conditional formating, i need to show highlighted values only, unhighlighted values are to be in hiding.

  14. Hi,

    I am looking for some pointers for the problem I have. There are three Columns which has values like

    Column_1 Column_2 Column_3 Result
    R239 G232 B110 RGB(239,232,110)
    G210 R134 B156 RGB(134,210,156)

    And the output should be what you see in Result column. Basically R, G and B values are scattered in the first three columns, but I need them in such a way that it looks like what is displayed in the Result column.

    I would really appreciate if someone can look into this. Thank you for looking into it.

    SK

  15. Hi Svetlana

    Hope you can help me...
    I'm using conditional formatting for two columns (ctr and ranking), but need an "if" statement to do the following...

    ctr => 20% + ranking =<3 = green
    ctr 3 = red

    ctr =>8% + ranking between 4-6 = green
    ctr 3% + ranking between 7-10 = green
    ctr <3% + ranking between 7-10 = red

    Can you please help??
    I am on skype if you can inbox me please

    Thanks
    Chantel

  16. the colour of the box which i edited should change the colour automatically to know me which i was edited is there any settings please help me
    TIA

    1. Hi Bunny,

      Sorry, it is not possible to do this using conditional formatting. Most likely a special VBA macro is needed.

  17. In my work book i am using 3 columns, first column contains value and second column contains dates of completion fist process and third column contains the date of completion of second operation. how can i use formula to filter what is completed and balance to complete (i meant Backlog) in excel 2013.Please suggest a formula .

    thanks

  18. Hi Svetlana,

    How do I highlight a cell that has a variance of 20% or more when comparing to another cell

    Thanks

    1. Hi Stuart,

      You can create a conditional formatting rule with the percentage change formula like this:

      =OR((B2-A2)/A2<-20%, (B2-A2)/A2>20%)

      Where column B contains new values and column A - old values.

  19. * Col-A = Col-B: Yes
    * Col-A Col-B: More
    I don't know why it did not post all 3 rules in here.

  20. (Question 1 above (#43) missing info)
    set 3 rules
    * Col-A Col-B: More
    Example for 2 rules: with "Yes" and "Less/More"
    =IF(A1=B1, "YES", "LESS/MORE")
    How can I break it to "Yes", "Less", "More"?

    Thanks,
    Son

  21. Hi Svetlana,
    I have 2 questions.
    1. I know how to use IF rule with 2 conditions for YES/NO but I don't know how to set a rule for 3 conditions.
    * Col-A Col-B: More
    Example: with "Yes" and "Less/More"
    =IF(A1=B1, "YES", "LESS/MORE")
    How can I break it to "Yes", "Less", "More"?

    2. Can I set multiple "Conditional Formatting" rule in one cell. Example,
    Col-A(number) Col-B(number) Col-C(Y/L/M)
    0 0 Y
    1 2 L
    4 3 M
    I can set Condition Formatting for Col-C with "Equal" to "Y" or "M" or "L" with different colors
    How can I set multiple Conditional Formatting rules for Cell in Col-A
    * A1 "Euqal to" B1: A1 = Green
    * A1 "Less than" B1: A1 = Yellow
    * A1 "Greater than" B1: A1 = Red

    Thank you very much for your help.

    Son

  22. Hi Svetlana,
    I have numbers in a row and i would like to have them summarized in a cell if something in the row has been deleted. For example:
    a1-20
    b1-50
    c1-30
    and i want if a1=0, b1=0, c1=50, then in d1 to have a1+b1 and c1=50
    Thank you,
    Zoya

  23. In a large spreadsheet I would like to apply conditional formatting to any cell that has an absolute value greater than or equal to 5,000,000. I'm having trouble developing that formula and would appreciate assistance if anyone is familiar with how to do this.

    Thank you - Tom

  24. Hello, I hope someone can help me with my problem. I have about 7 different sets of word banks. If a cell contains a word for lets sat word bank 1 I would like that sell to highlight yellow. If a cell contains a word for word bank 2 I would like that cell to highlight red, and so own. Is there a way to do with without making individual conditions for each word?
    Secondly, is it even possible to have a cell highlight any color if one column has three yellow highlighted cells in it? This is based off of my first problem.
    Thank you. I hope I was clear enough.

  25. I have three columns of data with 100 rows each and I want to check if the sum of the row in column A + the row in Column B = the value in the same row of Column C. If it doesn't then I want to RED highlight the row in Column C. For example: A(1)+B(1)?=C(1), A(2)+B(2)?=C(2), etc.
    Thanks,
    Dave

  26. Hi,Svetlana

    I HAVE 100 ROWS WITH ALPHANUMERIC.LIKE FOLLOWING TABLE
    11AS001
    12FB005
    12KA003
    12C40002
    12G3005
    12je001
    12bp005
    124j003
    129j0002
    122q005
    from these table i need to return a value based on the character.like that i have more than 10 values
    ex..
    if cell contains
    as------ A
    ka------B
    g3---C
    c4---D
    fb---E
    4j---F
    je---G
    bp---H
    9J---I
    2q---J

    I want to return a value like this
    Col A ColB
    11AS001--- A
    12FB005---- E
    ..
    Please help me.

  27. How can you edit multiple Conditional Format Rules at the same time?

    I have a row with 36 columns, each with a cell containing 7 conditional formatting rules linking the cell to conditions on cells in a corresponding row on another spread sheet.

    I need the next row to have the same conditional formatting, but referencing the cells in the next row on the other spread sheet. Is there some way to hi-light all conditional format rules on all 36 columns and do a search/replace on all of the formulas simultaneously, or is editing them one-by-one my only option?

  28. how to Automatically change the fill color in a row based on 3(m,n,o) columns current month

  29. Svetlana,

    I want cells in a column to become highlighted if the date listed in a cell is in the past. I tried:

    =$A2<TODAY()

    But this doesn't seem to be highlighting any cells with past dates. Any idea how I can set this up? If the first cell with a date is B4, should the formula go into B3?

    Thanks.

    1. Hi Peter,

      You should modify the formula for your first cell with a date, like this:

      =$B4

  30. Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:

    Sheet 1 Available List:
    And Then you have Sheet 2, Client List:

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    Greens
    Tanaro
    2
    1200
    95,000
    2500000
    Mid
    Yes
    Golf

    As everything Match his requirement.

  31. Area tower Br# Siza Rent Sale Height Furnished View
    Marina Murjan 1+s 985 120,000 high yes marina
    marina mesk 3 2200 340,000 5000000 high no sea
    JLT Goldcrest Studio 650 65000 low no road
    green tanaro 2 1200 95000 2500000 mid yes Golf
    marina majar1 1 1000 105000 low yes marina

    And Then you have Sheet 2, Client List:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf
    michel 115000-125000 marina 1 1000 low yes marina
    joe 200000-300000 marina 3 2200 high no sea
    charlie 55000-60000 JLT studio 650 low no road
    mike 120000-150000 marina 1+s 985 high yes marina

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    greens tanaro 2 1200 95000 2500000 mid yes golf

    As everything Match his requirement.

  32. Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:

    Sheet 1, Available List:

    Area tower Br# Siza Rent Sale Height Furnished View
    Marina Murjan 1+s 985 120,000 high yes marina
    marina mesk 3 2200 340,000 5000000 high no sea
    JLT Goldcrest Studio 650 65000 low no road
    green tanaro 2 1200 95000 2500000 mid yes Golf
    marina majar1 1 1000 105000 low yes marina

    And Then you have Sheet 2, Client List:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf
    michel 115000-125000 marina 1 1000 low yes marina
    joe 200000-300000 marina 3 2200 high no sea
    charlie 55000-60000 JLT studio 650 low no road
    mike 120000-150000 marina 1+s 985 high yes marina

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    Name Budget Area Br# Size Height Furnished View
    john 150000-165000 greens 2 1200 mid yes golf

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    greens tanaro 2 1200 95000 2500000 mid yes golf

    As everything Match his requirement.

  33. hi please help me to create excel sheet that works like this.

    Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:

    Sheet 1, Available List:

    Area
    Tower
    Br #
    Size
    Rent
    Sale
    Height
    Furnished
    View
    Marina
    Murjan
    1+S
    985
    120,000

    High
    Yes
    Marina
    Marina
    Mesk
    3
    2200
    340,000
    5000000
    High
    No
    Sea
    JLT
    Goldcrest
    Studio
    650
    65,000

    Low
    No
    Road
    Greens
    Tanaro
    2
    1200
    95,000
    2500000
    Mid
    Yes
    Golf
    Marina
    Majar 1
    1
    1000
    105,000

    Low
    Yes
    Marina

    And Then you have Sheet 2, Client List:

    Name
    Budget
    Area
    Br #
    Size
    Height
    Furnished
    View
    John
    150000-165000
    Greens
    2
    1200
    Mid
    Yes
    Golf
    Micheal
    115000-125000
    Marina
    1
    1000
    Low
    Yes
    Marina
    Joe
    200000-300000
    Marina
    3
    2200
    High
    No
    Sea
    Charlie
    55000-60000
    JLT
    Studio
    650
    Low
    No
    Road
    Mike
    120000 - 150000

    Now The Task:

    Your program, excel or whatever app you make shall be in the way that it gets John as input in the app, which has the below requirements:

    Name
    Budget
    Area
    Br #
    Size
    Height
    Furnished
    View
    John
    150000-165000
    Greens
    2
    1200
    Mid
    Yes
    Golf

    He’s budget is 150k- 165K and he looks for 2Br Apartment in Greens Area with Furniture
    And Shall display outputs as:

    Greens
    Tanaro
    2
    1200
    95,000
    2500000
    Mid
    Yes
    Golf

    As everything Match his requirement.

  34. Здравствуйте, Светлана,

    подскажите, пожалуйста, как сделать, чтоб ячейка выделялась красным цветом, если наступила определенная дата? (Excel интерфейс на английском)

    спасибо

    ---English translation---

    Hello Svetlana,

    Please show me how to highlight a cell in red if it contains a certain date? (Excel interface is English)

    thank you

  35. Hello,
    I have an excel sheet with with duplicated item# column and their quantities.
    how can i find duplicates and sum their vlues

    thanking you

  36. Hi..
    I am working on dropdown lists along with conditional formatting. I want the user not to be able to select anything from the next drop down unless the conditional formula is satisfied. Can you help me in this??

    Example:::
    Row 1 Column1 Column2
    Row 2 dropdown1 Condition 1
    Row 3 dropdown2 condition2

    My question is, If dropdown 1 is selected and condition 1 is satisfied, can access dropdown2, else no.

  37. hi my dear friends,
    how are you i need your help

    i want to apply conditional formating in my excel sheet exp:-

    in One Cell I put A ,i want to be in other cell get Apple automatically,how should i used formula & conditional formatting this ,
    can you suggest me please

  38. Hello,

    Is there a way to change the range in the following excel formula from 189 to 3500 in order to capture and sort all of my data throughout my worksheet? Here is how the formula is currently written =SUM(IF('F13 Data'!$BK$2:$BK$189="MATD", 1, 0))+SUM(IF('F13 Data'!$BO$2:$BO$189="MATD", 1, 0))+SUM(IF('F13 Data'!$BS$2:$BS$189="MATD", 1, 0))

  39. Hi

    Is there option to use conditional formatting based on the comment and the symbols(or symbols) used in the comment field on the excel cell?

    Tannk you

    Wojciech

  40. any one reply me ? how can i use the countif in conditional formating?

    Number
    1
    2
    3
    4
    5
    1
    5
    2
    3
    duplicate values will be highlighted. Even if i add new one ?

  41. Can anyone share a solution for this problem.

    Value A - 1000 (Sheet 1)
    Value B - 750 (Sheet 2)
    % From A to B - 75% (Sheet 3)

    The condition is highlight value more than 20%
    But the highlighted cell should be reflected on the Value A (Sheet 1) not on the Percentage Sheet (Sheet 3)

    I am using version 2013 Excel

    Thank you

  42. how to use the conditional formatting for Attendance.?

  43. I am using excel for making out a shift schedule for my company. How do I write a conditional formating formula that would highlight duplicated use of initials in a row then repeat, but independently, highlight of duplicate initials in the next row, the row after that, etc...

    Additionally, the formula would only look at the first 2 characters in a cell to check for duplication.

    Thanks.

    Example of data

    Site1 Site2 Site3 Site4 Site5
    1/1/2015 AA BB CC DD EE
    1/2/2015 BB CC BB AA EE
    1/3/2015 CC CC CC BB AA
    1/4/2015 BB CC BB-t AA EE

    First row has unique initials of AA to EE. There is no error with this row.
    Second row has duplicated BB in two columns that I want the formula to highlight.
    Third row has duplicated CC in three columns that I want the formula to highlight.
    Fourth row has duplicated BB in two columns when you look at the first two characters in the cell that I want the formula to highlight.

    Since this is a day by day basis, creating a conditional formating for each row individually would be unrealistic. The formula needs to look at every single row in the spreadsheet.

    Again, thank you for your help.

  44. Hi Svetlana,
    I am trying to apply conditional formatting for me excel sheet where I would like to highlight the whole row on single cell value for e.g. if $A5 = "Governance" then whole row should be highlighted. I have 250 rows and 10 rules to apply. And all of them are equal priority so not sure how can I use Stop if True rule. Couple of my experienced colleagues have told me that VBA can help you as there are more than 3 rules. What is your suggestion?

    Thanks in advance for your help.
    Sanjeev

    1. Hi Sanjeev,

      As far as I know, in Excel 2007 and later you can create more than 3 rules without any problems.

      Stop if true is applied only to speed up the rules processing. If you describe your rules in more detail, we'll probably be able to help you with proper formulas.

  45. I receive an updated spreadsheet every week. The spreadsheet contains approximately 1,000 rows and 77 columns. I am looking for a formula that will tell me which cells have been changed since last week. This would have to include all cells. How can I compare the data from week to week and come up with the items that have been changed (additions to the list, removals from the list, and any changes).

    Thanks so much!

  46. Hi All
    Please help, I need to conditional format one spread sheet with three rules:

    1. All date dates after: 11/11/2014: Green (no problem with this one)
    2. All date dates a month before: 11/11/2014(ie. 10/10/2014): Yellow (problem with this one)
    3. All date dates before: 11/10/2014 (ie before: 10/10/2014): Red (problem with this one)

    Thanks very much for your help

  47. Hi,

    I have some formulated output's in a cell and want to highlight the partial values in output by changing its color.

    Formula used =ROUND(E2,0)&" ( "&ROUND(D2*100,0)&" %)"
    Outcome = 573 ( 57 %)

    desired Outcome is, (57 %) should be in red color.

    Thank in advance

    BR//

  48. I apologize, for some reason it is not displaying the formulas correctly upon hitting submit. Please disregard.

  49. I am unsure if this is the right area, but I need assistance with an issue. I have a column and the cells contain the following formula:

    =IF(AND(K2>0,K20,K30,K4<H4,ISBLANK(M4)),"1","")

    etc.

    While the formula works perfectly for its intended purpose, the column will not sum at the bottom.

    =SUM($P2:$P696) This always equals zero, even though the cells contain a numerical value. Am I doing something incorrectly?

    1. I am unsure if this is the right area, but I need assistance with an issue. I have a column and the cells contain the following formula:

      =IF(AND(K2>0,K20,K30,K4<H4,ISBLANK(M4)),"1","")

      etc.

      While the formula works perfectly for its intended purpose, the column will not sum at the bottom.

      =SUM($P2:$P696) This always equals zero, even though the cells contain a numerical value. Am I doing something incorrectly?

      1. Hello Darcy,

        The reason why the SUM function doesn't work is that your formula returns "1" as a text value. To make it a numeric value, please remove the quotation marks around it, i.e.:
        =IF(AND(K2>0,K20,K30,K4<H4,ISBLANK(M4)),1,"")

  50. I have 2 Columns of data. I want to 1st column will have a data where the 2nd column's conditional formatting will be based. Is it possible to have this type of formatting:
    if Col1>0, Col2 = YES in red
    if Col1=0, Col2 = NO in green
    if Col1<0, Col2 = NO in green
    Is this type of formatting possible.

    Thanks in advance and all the help.

    1. Hello Roumel,

      Assuming Col 1 is column A and Col 2 is column B, create 2 rules with the following formulas for column B (do not include the column header in the rule):

      Red: =$A2>0
      Green: =$A2<=0

      Excel conditional formatting cannot put any text in the cell, but in addition to the above rules, you can enter the following simple formula in cell B2 and then copy it across the entire column B:
      =IF(A2>0, "YES", "NO")

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