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

  1. I need to highlight a cell that doesn’t contain 48

  2. Hello- I need your help with conditional format formula. I want it to highlight cell A1 if cell A1 is 50% less than B1. Also if I have a lot of cells with 0 i want to make sure there is no color.
    I after that I can create same formula different percentages.
    thank you,
    Le

  3. Good day.
    I have a spread sheet of 300+ line. I want to add a formula what hides values what is the same amount, but opposite (positive and negetive).
    A1 -35,00
    A2 -34,80
    A3 -33,26
    A4 -15,00
    A5 32,12
    A6 33,26
    A7 34,80

    Example:
    A1 - Stay
    (A2,A7) Hide Same number, but opposite value(positive and negative)
    (A3,A6) Hide Same number, but opposite value(positive and negative)
    A4 - Stay
    A5 - Stay

    Thank you

  4. Dear Concern,
    I am very novish in using Excel, I am a business man, for my own business I have to make my Inventory Management System.From viewing Youtube I have almost built my system but in some case I am stuck. In my "Stock Out" section when stock out command quantity is more than (Opening qty+Stock In qty) there I want "SHORT" word should appear. But this thing I cannot do. Can you Please help me in this issue,how can i solve it.
    Thanks with regards
    Wahid

  5. please help me ,
    if i am type 20-2 in excle 2013 then excle take 43151 value , not take correct value so how to resolve this

    1. Dinesh:
      How is the cell you're entering the 20-2 formatted?
      Try formatting it in another way.
      Right click select Format Cells and then choose another option from that list. Maybe text.

  6. What are the different number formats?How will you change the format of the number?write the steps

  7. I want to set the conditions of a cell to:
    today's date - 180 days turn red
    today's date - 135 days turn orange
    today's date - 90 days turn yellow
    today's date - 75 days turns green

    Can anyone help?

    1. Josh:
      You've asked to apply four different conditional formats based on four different conditions, so you'll need four rules all applied to the same range of cells.
      Select the range of cells to which you want to apply the rules.
      Select Conditional Formatting, select Highlight Cell Rules, then More Rules, then select Use Formula to determine which cells to format.
      Where the range is H48:H51, in the field that is displayed enter this formula:
      =AND(H48>TODAY(),H48=(TODAY()+75))
      Notice that the formula references the top left cell in the range H48:H51. Your range and top left cell is probably different, but this formula must reference the top left cell in the range.
      Notice that this formula will apply the formatting to the date that is 75 days from today.
      The H48>TODAY() ensures the date in H48 is not in the past.
      You asked for TODAY()+ 180, etc. so the formula includes H48=(TODAY()+75), etc. So it is only formatting the cells that are equal to 180 days (or whatever number of days)from today. Not greater than the number of days from today. This allows you to put different formatting to each rule and specific future date.
      Select format, then choose the fill color then OK and then apply and OK your way back to the dialogue window that displays the rules.
      In that window you'll want to click Add New Rule so that you can enter a new rule which will have the same formula except that you'll change the "+" number to the different number of days and different formatting for each different rule.
      Follow this same procedure until you have all four rules and their respective formatting the way you want them.

  8. Correction
    Kindly anybody has Idea, I have one text cell in Excel, hwo to divide that text into two cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do, Thanks in adv. and happy new year

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  9. Hi,

    I am not sure if it has been mention above but I am stuck with a formula.

    I want the following: If $X2 is not blank / empty and $W2 has a negative value (loss) then the entire row goes red. If $W2 has a positive value (revenue) then the entire row goes blue.

    I have tried few things but I don't know. I have tried this for example =IF($W2<0,ISBLANK=$X2=FALSE)

    1. Thanks in advance :-)

  10. Hello,
    I am trying to create a training document. The document will contain several employees, each having a date they were certified, and then a date that they will be due to re-certify. I am trying to figure out the formulas that I would need to input in order to change the row either red=overdue, yellow=due within 90 days, and green=91days>. The formula that seems to be closest to what I need is Rule1=datedif($A2,Today(),"d")>=1:89,Rule2=datedif($A2,today(),"d")>=90:275, and Rule3=datedif($A2,today(),"d")>=276 but for some reason the rules are keeping all three colors from showing. could you please point me in the right direction and thank you for your time.

    1. Hello, Ian,

      Please try the following formulas:

      Rule1=AND(DATEDIF($A2,TODAY(),"d")>=1,DATEDIF($A2,TODAY(),"d")<=89)
      Rule2=AND(DATEDIF($A2,TODAY(),"d")>=90,DATEDIF($A2,TODAY(),"d")<=275)
      Rule3=DATEDIF($A2,TODAY(),"d")>=276

      Hope it will help you.

  11. Hello Dear Experts..

    I have a table in which I already inserted formula & conditional formatting to get the result.

    Example:- In column O3 I have mentioned Expiry Dates & in column P3 I have mentioned =O3-TODAY() & inserted conditional formatting to know remaining days to get expired with Less than value using colour format. (Like, colour to be change as yellow if days remain less than 60 & Red if days remain less than 30) it is working.

    But What I want is if the expiry date has not been mention in column O3 then Column P3 should be left blank without any text.

    Awaiting for your response

    1. Hello,
      you can add IF function to your formula in P3:
      =IF(ISBLANK(O3),"",O3-TODAY())

      It will remain empty if there's nothing in O3, otherwise calculate the date.
      Please read this article to learn why and when IF can be used.
      Hope this is what you need.

      1. Yes it's working, Thank you very much

  12. How can we add value of two cell but show in one cell
    For exmpl
    A1 cell value is 10
    B1 cell value is 20 total 30(a1+b1)
    But these values calculated in b1 cell not another cell. Plz reply

    1. Hi,
      I'm sorry, but it's impossible to show the number AND the sum of numbers in a cell at the same time.

  13. How do I get excel to calculate the difference in dates in days?

    Thank you

    Kenny

  14. Hi,
    i want to learn how to make alert(thru highlight) when the stocks was below safe level. i was able to find out how for 1 row but my problem was how to do it if i'm monitoring 1 thousand items wherein i don't have to do it 1 by 1. 2nd, is it possible to highlight the entire row?

    example
    item on hand safety stock
    a1 10 15
    a2 11 14
    ....
    a1000 12 10

    1. Hi, Edilberto,
      if C column indicates safety stock and it should not (ideally) exceed the number of items on hand (column B) then the formula for conditional formatting rule is:
      =$C1<$B1
      and it applies to =$A:$C.
      It will also highlight the raw if C is empty.
      If you want something other than that, please, specify.

      Also, here is a great tutorial on how to highlight an entire raw.

  15. hi
    I need to be able to register in two cells the highest and lowest negative and positive results from a changing portfolio total in order not to have to manually monitor and record these myself. I am a complete novice when it comes to formatting cells so a simple abc approach would be very much appreciated.

    Many thanks

    1. Hi, David,

      let's say that your totals are in the 7th row (A7:G7). And you have two other cells where you want to see the highest and lowest results. So, for the lowest one you put the next formula in the cell:
      =MIN(A7:G7)
      (where A7:G7 is your range of the results)
      For the highest one, enter another formula in another cell:
      =MAX(A7:G7)
      Every time the results change, these formulas will adjust the highest and the lowest numbers.

      If you need to find the lowest AND the highest for negative results, the lowest AND highest for positive ones, let us know what Excel version you're currently using.

      1. Natalla

        Many thanks for your advice, much appreciated.

        I may not have explained the problem correctly. I have one cell with an ever-changing total, both post and neg, which I record in two other cells, one recording the highest positive and the other the highest negative. I would like to be able to record these automatically so need the formula for each cell to do that.

        I am using Excell for Mac 2011 version 14.7.3

        Apologies for any misunderstanding and thanks once again

  16. Hi there,

    I have list of codes in tab 1 (approx 100). I want to format the colum A in tab 2 that if I type the code other than mentioned in tab 1 than it should highlight.

    Will conditional format will work on this occasion?
    Regards,,

    1. Hi Sandeep,

      Sure, you can use the following formula for your Conditional Formatting rule to highlight values that differ:
      =AND($A2<>"",COUNTIF($A2, Sheet1!$A$2:$A$150)=0)

      You can also compare the date with Duplicate Remover add-in to find unique values in your second sheet.

  17. Hi,

    I need to give me a reminder or change color of the vehicle ID colomn as soon as it reaches the oil change mileage. I have to develop a worksheet for oil change of fleet of vehicles. The oil change is based on current mileage to next mileage which is (Current Mileage + 7000). Would you help me out with this one.
    THanks

    1. Hello Afaq,

      Could you please describe the structure of your table in more detail? Do you have columns with the IDs, current mileage, and mileage of the last time oil was changed? We'll do our best to assist you.

  18. Hello,
    I have a worksheet of inventory items. The first column is the stock number the second is the name of the items. The third one is how many we have received. The other columns are items that went to a project, we have approximately 13 projects and a formula for the last two columns which have what has been issued and what is available. The problem arises when my boss wants to lock the third column C which has the number of items purchased that has been sent to us. I have tried using the page protection function, but this locks the whole page not just the items in column C. I need the rest of the page to function while locking a single column. Is that possible and if so how do I do it?

  19. Hello experts!
    I want a conditional formatting in following context:
    1. I have 6 columns and more than 30 rows.
    2. Column A contains dates in English & column B contains dates in local language.
    3. Column C contains days as "Sun", "Mon"......

    Here, I need a conditional formatting of cells from Column A through Column F if column C contains "Sat". This condition should apply even in blank cells where I need "cell fill color".

    Problem: I can fill color in the cell containing "Sat" but I need the same in entire row from column A up to F.

    Can somebody help me out?

    Thank you

    1. Hello Bhagirath,

      You need to create a rule with a formula, here are the steps:
      - Select the range with your data, e.g. A2:F100
      - Click on "New Rule" under Conditional Formatting and select the last option: "Use a formula to determine which cells to format"
      - Pick format for the rows and enter the following formula:
      =$C2="Sat"

      This blog post describes how to format entire rows based on values in certain cells:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

  20. 884,00
    125,00
    4,15
    4,15
    1,00
    9,46
    1,56
    1,13
    25,00
    3,75
    1,00
    1,00
    1,13
    5,00
    6,88
    2,36
    1,06
    2,55
    2,48
    6,16
    7,50
    1,88
    102,50
    168,75
    0,37
    0,11
    0,92
    2,31
    7,50
    0,46
    1,13
    3,75
    6,50
    3,40
    1,31
    26,25
    1,31
    1,75
    25,00
    3,90
    hi above value which is i want to sum but the value occurs always zero,please get me solution

    1. Put these values column A
      use below formula and drag down

      =VALUE(SUBSTITUTE(A1,",",""))
      then Use Sum function

  21. which formula we used in condition formation to coloring the amount is greater than 30 and less than 61

  22. how can highlight small a in whole sheet not capital A

    1. Hello,

      To make your Conditional Formatting rule case sensitive, choose to "Use a formula to determine which cells to format" and use the EXACT function:
      =EXACT($A2,"a")

      Please replace column A with the name of the column where you have your values.

  23. Hi Svetlana,

    How do you add a drop down table to a cell

    Thanks.

  24. Hi Svetlana, Irina

    I am trying to use conditional formatting of cells using the rule definition as : =($CM:$CM="Red"), but couldn't able to achieve it MS Excel 2010, but it works smoothly in Kingsoft Spreadsheets. Any solution for this ?

    Thanks in advance to you 'Super Girls'.

    1. Hi Waleed,

      In Excel conditional formatting rules, cell references are relative to the top-left most cell in the applied range. That is why, instead of specifying the range in the formula, you should supply the top-left cell only. In your case, select the entire column CM, and create a rule with the formula =$CM1="Red"

      Excel will apply the rule to the whole column and adjust the row reference like it does when you copy a formula down a column.

      For more information about cell references, please see Relative and absolute cell references in Excel conditional formatting rules.

  25. hello, pls am trying to use a colour code for excel i want to use a data in one column to change the row colour based on the input on any other column like i have a 7 in column b and want it to change to a colour red when i input D in any other column

    1. Hello,

      Though you can't create a rule for all columns, you can try listing certain columns that the rule will check. E.g. select column A and create a rule with the following formula:
      =OR($B1="D",$C1="D",$D1="D",$E1="D",$F1="D",$G1="D")
      If any of the listed cells contain "D", it will highlight the cell in column A.

  26. First- I want to thank you for all the great tips. I am creating a workbook that has 6 tabs (goals) - each tab containing the goals for a particular metric for a particular region. Each tab is identical in set up but has different values for the goals. I also have 6 tabs with each regions report card. On those tabs i enter the actual result for a metric and use conditional formatting to look at the value to the goals tab for that particular region. If i create one report card i want to be able to copy it 5 times. My problem is the conditional fomatting will still look at the conditional formatting for the first one i created and look at the 1st goal tab. I want to be able to copy the original worksheet but want a quick way to change the worksheet reference in all conditional formatting formulas for a particular worksheet- can this be done?

  27. I want to compare one column A of Catalog Numbers containing lot numbers in column B to one column C of Catalog Numbers containing lot numbers in column D and populate the items that match in column E as 0 and the items that don't match in column E as-1 and highlighted in red,

    1. Hello,

      If we understand your task correctly and you are trying to compare the lot numbers in column D to lot numbers in column B, then enter the following formula into E1:
      =IF($D1=$B1,0,-1)

      Copy it down the entire column to get the results for all rows.

      Then create a conditional formatting rule with the formula below to highlight those cells in column E that contain "-1":
      =$E1=-1

  28. Hi,

    I have two rows, the first one with various targets/thresholds and the second onwards with achieved values. While I apply the conditional formatting it gets applied to the entire table. Since the targets are different for each row, I could not do it by just formatting the first row and pasting the format for the rest. There should be a work around which I'm not aware. Please help.

    1. Hello Karthik,

      Most likely there is an issue with absolute and relative references in the formula you use for the conditional formatting rule. You can add a dollar sign before the row or column reference to make sure it is not changed. Please see this blog post for a detailed description of using relative and absolute references in your conditional formatting rules:
      https://www.ablebits.com/office-addins-blog/relative-absolute-cell-references-excel-conditional-formatting/

      1. Thank you so much for the reply Irina. Your solution helped me a lot of time.

  29. I need help in inserting specific comment based on the value of certain cell. E.g.: If the value of cell A1 is greater than equal to 3 then I would like to generate a comment in B1 stating 'reporting not required', so as A1=4, comment in B1 should read as 'reporting required' and so on for different values in A1. Is this possible in Excel? Appreciate your guidance.

    1. Hello Shiv,

      You can add the following formula to cell B1 and copy it down the column:
      =IF(ISBLANK($A1),"",IF($A1<=3,"reporting not required",IF($A1=4,"reporting required","new rule")))

  30. Hi,

    WHAT FORMULA TO USE TO FILL UP THE CELL,THANKS

    SHIPREFT COUNTRY SHIP MODE
    JTWA555666 ? ?
    JSYS2555333 ? ?
    JBKT555999 ? ?

    Ship COUNTRY SHIP MODE
    JSYS Australia SEA
    JTWA TAIWAN AIR
    JBKT THAILAND TRUCK

    1. Hello Lisa,

      You need to use the VLOOKUP function for your task. Here is the formula you need to enter in the "Country" column:
      =VLOOKUP(LEFT(A16,4),Sheet2!A1:C4,2,FALSE)
      It looks at the first four characters in cell A16 (your SHIPREFT), compares them to the value in the first column in Sheet2!A1:C4, and returns the corresponding value from the second column in that range.

      You can learn more about the VLOOKUP function in this blog post:
      https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/

  31. Hi, is it possible to create a rule to give a value to a work.
    For example: I have a checklist and from a drop down list I can select a number of words, Conformance, Non-conformance and Partial non-conformance, now I would like to automatically have a value assigned to each of these in another column.
    i.e. Conformance = 3; Partial non-conformance = 2 and non-conformance = 0.
    How would I do this.

    1. Hello Lizl,

      You can enter the following formula into the cell where you want to get the value:
      =IF(F2="Conformance",3,IF(F2="Non-conformance",0,IF(F2="Partial non-conformance",2,"")))
      Here F2 is the cell with the drop-down list.

  32. hi anybody can provide me excel data for practise.

  33. Need to lock conditional formatting but still allow values to be pasted into those formatted cells from other sources.

    Problem: I have set up a spreadsheet that highlights cells that need to be populated or left blank. Certain cells that need to be populated are highlighted based on a particular part type (which is chosen from a dropdown menu for the cells located in column AF). Since we are looking over data that can be up to 2000 thousand rows and covering columns A-CL it would take hours to retype all the information to ensure the right columns are populated based on that part type. It is easier to just paste the values in from outside sources. However, it would make it easier to audit the data if the highlighted cells remained highlighted after those values are pasted in.

    Question: Is there a way to lock the formatting while at the same time allowing those conditionally formatted cells (that are highlighted) to have values pasted as an overlay from other sources without deleting the format?

    Thanks,

  34. Is there any way to use conditional formatting to show cells that have been edited? I cannot use Track Changes because I do not want to convert my table to range.
    If not conditional formatting, is there any other way to do this?

    1. Hello Shannon,

      Conditional formatting can't do this, you need a VBA macro. If you don't need to keep the changes after you close the workbook, you can use the code below:
      - Open your Excel file and press Alt+F11 on your keyboard;
      - Select the necessary sheet in the list and paste this code:
      Private Sub Worksheet_Change(ByVal rnUpdated As Range)
      rnUpdated.Interior.Color = CLng("&H00FF00")
      End Sub

  35. 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, "")

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

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

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

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

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

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

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

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

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

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

    подскажите, пожалуйста, как сделать, чтоб ячейка выделялась красным цветом, если наступила определенная дата? (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

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

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

  48. 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,"")

  49. 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")

  50. Svetlana,

    Thank you for your recent help with conditional formatting. It was very useful.

    I would like for a cell to be highlighted if the date listed in the cell is prior to today. What formula would I use? Conditional formatting only allows a cell to remain highlighted for a month if you format by date.

    Thanks in advance.

      1. will you please tell me that how can i pinup a hidden comment in excel's each cells, which highlight after enter a wrong entry. (which i customized)

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)