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

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

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

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

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

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



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