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 11. Total comments: 318

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

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

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

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

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

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

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

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

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

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

  11. I have 2 rows of dates one is a forecast and another is an actual. I would like to highlight dates that are past due in the forecast column as long as a date in the actual column is blank.

    Forecast Actual
    11/1/2014
    9/5/2014 9/5/2014
    11/10/2014
    10/20/2014 10/20/2014
    10/4/2014
    11/1/2014

    thanks for any help you can provide

  12. Hello Michell,

    Sorry, but your task is not clear. If you could provide more factual data, i.e. what columns contain what values and what the desired result is, we will try to help.

  13. I am trying to come up with a spreadsheet that has the prices that we are paying for products on one line, and on the following lines, the pricing for our customers, which we have different groups for. My question is, if next month certain prices change for us and I adjust the prices we were paying, what formula would i use to make it so the customers prices are automatically adjusted for all the groups i list?

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

  15. hi,
    i have 100 rows with a students marks. i want prepare separate list automatically based on their markslist those who had 0 marks.Please suggest me
    Example
    1.ramu 50
    2.raju 0
    3.suresh 10
    4.ramesh 0

    from the above table i need automatically those who having 0 marks like this
    raju 0
    ramesh 0

    1. Hi Purushotham,

      You can apply Autofilter (DATA > Autofilter), then filter the Marks column showing only 0. Alternatively, you can use a VBA script.

  16. Hello Svetlana,
    I was calculating issue date:
    e.g A1 cell has a proposed date.
    C1 cell has a confirmed date. my query is, once the C1 cell is filled A1 will show "Over"/"NA" or vice-verse.

    Pls guide me.

    Thanks.
    Rakesh

  17. How do I set conditional formating on a shared spreadsheet I did not create? The owner of it no longer works with me.

  18. Hello Svetlana,

    I have an Excel sheet with two columns. Each row in each column lists either Y or N. I want to format the sheet so that if a cell in the first column lists Y, and the cell in the second column lists N, the cell in the second column will be formatted so that it changes color. I presume this will require a formula, but I do not know what that would be. Please let me know if it can be done.

    Thanks!

    1. Hello PK,

      Select the entire second column and create a rule using this formula:
      =AND($B2="N",$A2="Y")

      Where B2 is the first cell in your second column, and A2 is the first cell in the first column.

  19. Helo all, can i get advice how to I can aplly contidional rule between cells in all rows, no just one, because if i aplly one cells is not work for others, how can i apply conditional rules for it.
    Many thanks

    1. Hi Lubo,

      You just need to select the entire rows, then create a rule as usually and write the formula for the top-left cell. Excel automatically adjusts the formula for other cells, changing cell addresses in the formula according to absolute or relative references.

      Please check out the following articles for full details:
      How to change the row color based on a cell's value in Excel

      Relative and absolute cell references in Excel conditional formatting

  20. Hello, I basically have 50 worksheets, each work sheet has 2 tables in it. An old one and a new one they all have the same titles etc, I need to compare the data. For instance A2-A43 is the old then A46-A87 is the new. Now what I want to do is if a change is present between the two of them highlight it red...

    Example

    A2 10
    A3 5
    A4 5

    A46 10
    A47 5
    A48 10

    How would I get it so that both A4 and A48 become highlighted, and also if I was to change the cell value to be the same would it then go away? As this is what I'm looking for.

    Regards , please reply today... I am struggling. Alex.

    1. Hi Alex,

      Sorry for the delay, I was on vacation. If you are still looking for a solution, here you go:

      - Select your 1st table and create a rule with this formula: =$A2<>$A46
      - Select your 2nd table and create a rule with this one: =$A46<>$A2

      Where A2 and A46 are the first data cells of table 1 and table 2, respectively.

  21. Hello, I'm keeping a database, basically one file is old and one file is new. Is there away that I can get Excel to highlight information which is different on the new file to the old one? Everything has the same titles but a slight code has been changed so this may have an impact of the actual results.

    Alex

  22. Hi there,

    I'm trying to edit XML files onto Excel so that I can read changes into certain events, example... An employee working less hours than they should be. This then needs to show up in some way throughout the whole database. Is this possible? I'm an amateur on Excel, please help.

    Alex.

  23. Hi,

    I'm trying use a formula for working time calculation
    IN OUT TT status
    9:30 AM 6:00 PM 8:30
    9:30 AM 9:00 PM 11:30
    9:30 AM 12:00 AM 14:30
    9:30 AM 3:00 AM 17:30
    9:30 AM 5:00 AM 19:30

    in states Colum i want if
    9:30 AM 6:00 PM 1
    9:30 AM 9:00 PM 1.5
    9:30 AM 12:00 AM 2
    9:30 AM 3:00 AM 2.5
    9:30 AM 5:00 AM 3

    what kind of formula i should use for this

    1. Hello Zabiulla,

      You can use Anant's solution, or one of the following two options:
      1) Create a helper column and enter the following formula:
      =IF(B10<A10,(B10+1-A10)*24,(B10-A10)*24)

      It will let you get numeric values for the status. Then you can insert a simple IF formula with all combinations of the conditions and copy it down a new column:
      =IF(D10=8.5,1,IF(D10=11.5,1.5,IF(D10=14.5,2,IF(D10=17.5,2.5,IF(D10=19.5,3,"")))))

      2) If you want to get the result right in the status column, you can use the following formula:
      =IF(B10<A10,CHOOSE(INT((B10+1-A10)*24*4/8.5),1,1,1,1,1.5,2,2,2.5,3),CHOOSE(INT((B10-A10)*24*4/8.5),1,1,1,1,1.5,2,2,2.5,3))

      We hope this helps.

    2. Suppose your in time is in a column and out time is in b column. And your data is in columns E F ang G ( G is that column where you have that 1 1.5 values updated) then paste below formula:

      =INDEX($G$1:$G$5,MATCH($A1&$B1,$E$1:$E$5&$F$1:$F$5,0),1)
      after pasting it press CTRL+SHIFT+Enter

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