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

  1. I want use to Conditional formatting

    If A Column is "Sunday",

    Then B Column Fill Red color. Is this possible

  2. Thank you Irina.

  3. I want to implement alternating rows conditional formatting only if a field in Column A (A5:A500). Please help how I can achieve that? The formatting that I want to include is adding a border and filling the cell with a colour. Cheers.

  4. Hi

    I have a column that I want to apply data bar conditional formatting to, however when I apply the formatting it doesn't appear. The data is calculated from other cells and contains formulas. Kindly assist

    Regards

  5. Hi,

    I am working on a spreadsheet which is to show delays in projects. I am using conditional formatting formulas to change the colour of the cell directly beneath the planned week number.

    Is there a way to copy this format so that the reference cells are automatically updated as they would be if a copying a normal formula?

    Thanks
    Steve

  6. Hello, I have a large spreadsheet with columns of data (example, K 39,041.00, L 34,584,.25, M 26, N 39,470.04)and would like to highlight cells in both column K & N if column N is greater than column K. I would like to do the same thing (different color) if column L is equal to column K. Can I accomplish this through Conditional Formatting?

    Thanks in advance,
    Jeff

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

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

  9. 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?

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

  11. I have some numbers,with seperate cells. if i select one digit, automatically
    that shows how many same digits in that page. with Bold & Colored. ?

    please

  12. I have created a sales sheet with description,price,quantity,and total with 5 different product that I sell. Now I want to create another, where when I sell any item the system will automatically calculate the total of so I can save. Thus POS but I will not print a receipt

  13. I want a cell to turn red base on a response in a combo box. The formatting works when it is in relation to any other cell or even a drop down list, but it seems as if it is not recognizing the combo box.

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

  15. I have at the top today date

    I have around 30 rows where i have typed different dates. I want to highlight if the row date is less than today date with red font. Kindly help me how to do this.

    Regards,
    Guddappa nadiger

  16. IN EXCEL ONE COLUMN MATERIAL SEND DATE,NEAREST COLUMN DATE TODAY ,NEXT COLUMN COUNT DATE FROM 1 TO 2 COLUMN,NEXT COLUMN DAYS>180 MEANS INDICATES RED
    DAYS<180 MEANS INDICATES GREEN

    NEXT COLUMN ACTUAL DATE OF RECEIVING,NEXT COLUMN STATUS OPEN/CLOSED(IF MANUAL ENTER ACTUAL DATE OF RECEIVING MEANS)THAT AUTOMATICALLY COMES CLOSE THEN DATE FORMULA WILL NOT UPDATE FOR THAT PARICULAR ROW

  17. how to running value control via condition formatting a1-10then b1 100,c1 200 but my total is 300 i want a1 greater then b1 then highlight b1

  18. Hi I excel sheet if there are 10 employees I'd in column and in other excel sheet i want the employee I'd twice. Is there any formula for same

  19. Hi
    why my file cannot save the custom conditional formatting?

  20. How to I highlight a column cell if the minute value is within 15 minutes of another column cell?
    Example:

    C4 is 9/30/16 12:11
    F4 is 9/30/16 12:23

    C4 is only 11 minutes before F4 and needs to be highlighted.

    Thanks

  21. Hi,
    Could you tell me how to use conditional formatting to turn one cell yellow 5 minutes before to five minutes after the time "now()" listed in second or different cell?

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

  23. How can I get the total in two cell's to reduce when I add an amount in another cell?

    For example cell H7 is at 100 and cells M7 and N7 are at 20. When I make cell H7 105 how can I automatically get cells M7 and N7 to reduce to 15? The cells are in hours and minutes.

  24. How can I apply a conditional format (the same one) to the same column (in this case column S) across multiple worksheets in the same workbook? I am trying to highlight all values over 1.0 found in column S across 25 different tabs of the same workbook. Thank you.

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

  26. Hi
    Is there a way to apply conditional format within a pivot table but not to values, but to regular columns, and have it stick upon refresh? I've searched and tried everything I can think of to no avail.
    Thanks!

  27. Please help, i want to put formula background of a cell, like if i enter the digit 10 then it should be automatically multiply with 200 and answer should come 2000.

  28. Please help, i want to put formula background of a cell, like if i enter the digit 10 then it should be automatically multiply with 200 and should come 2000.

  29. I am new to this so pls bear with me.

    I am creating a budget tool - the user will check one of 3 non-numeric options). that option will correspond to a specific budget line item on another sheet. For example my 3 items along with their corresponding values are:

    Fast - $100
    Slow - $50
    Standing - $25

    So I'm looking for a code that will allow me input the correct budget item once the user has checked the appropriate box. I've been looking for help on this and keep coming up with solutions that are simply numeric.

    any help would be appreciated

  30. Hi Svetlana,

    How do you add a drop down table to a cell

    Thanks.

  31. Hi people,

    Please help me with this calculation.
    I have 3 range of targets
    A - if the product sold is from 0 to 49 then the amount to be calculated per product is $40
    B - If the product sold is between 50 to 99 then the amount to be calculated per product is $60
    c- If the Product sold is more than 100 then the amount per product will $100

    Please let me know do i do it on excel where i have the column A1 with number of products and B1 with amount earned in total

    Thank You !!!

  32. hi want conditional formating in coloume B < coloum a
    10 9
    20 10
    30 25
    40 18
    50 27
    60 50
    70 55
    80 40
    90 30
    100 150

  33. There a list of 500 names
    Eg.
    Raju
    Ramesh
    Xyz
    X
    Z

    The name Raju is replaced by the name Ramesh in the whole sheet
    How do we rectify it ?
    In excel

  34. How do i apply this formula in conditional formatting to highlight cells SUMIFS(L:L,J:J,">="&Q7,J:J,"<="&$R7,G:G,$S7,H:H,$T7,$L:$L,$T$1,$L:$L,$T$2)

  35. its was good . thanks

  36. Hi Irina Pozniakova

    need write a value as grade if the student must who having minimum 25 in EM and based TM i need to write a grade
    Ex:
    if TM is 80-89 grade is A
    TM is 70-79 grade is B
    TM is 60-69 grade is C

    like dat i need grade values

    Roll nO Im EM TM Grade Points
    1002519 15 25 40 D 4
    please help me
    Thanks

  37. Hi svetlana,

    I need write a value as grade if the student must who having minimum 25 in EM and based TM i need to write a grade
    Ex:
    if TM is 80-89 grade is A
    TM is 70-79 grade is B
    TM is 60-69 grade is C

    like dat i need grade values

    Roll nO Im EM TM Grade Points
    1002519 15 25 40 D 4
    please help me
    Thanks

  38. Hi svetlana,

    I need write a value as grade if the student must who having minimum 25 in EM and based TM i need to write a grade
    Ex:
    if TM is 80-89 grade is A
    TM is 70-79 grade is B
    TM is 80-89 grade is A

    Roll nO Im EM TM Grade Points
    1002519 15 25 40 D 4

  39. Hi Svetlana,

    I am having trouble with this.

    I have a spreadsheet that I would like the row to turn green if I put a tick in the tick box I have added to each row.

    Can this be done using conditional formatting or will I need to look at this a different way.

    Thank you for your help.

    Ian

  40. Privet,

    I have a Column of cells with the following Conditional Formatting:
    =ADDRESS(ROW(),COLUMN())=CELL("address")

    Followed by the simple VBA code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    End Sub

    This allows me to hyperlink from one sheet to another and when landing on the cell the hyperlink is attached to it highlights that particular cell on that sheet. Now i was wondering if there is a way that i can link that highlighted cell to other cells so those highlight as well. So when the linked cell highlights the other ones id like to highlight with it, do as well.

    Spaciba

  41. Hi guys,

    I'm making a spreadsheet for a syndicate I will be doing soon and need help.

    This is the first table of the page

    Name 1 | 1 2 3 4 5 6
    Name 2 | 12 11 10 9 8 7

    This is the second table on the page

    Draw 1 | 1 54 12 6 5 7

    Ok, so on the second table what I want to happen is that when the numbers are drawn I need excel to automatically fill the number in on table one so I know that they've been drawn.

    Please can someone help, I would be truly grateful.

  42. we have give formula in column M that if column L in -figure than column M should show Amount Due, Same way if Column N have a received date than column M should change as Received

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

  44. Dear Developer, Often I use conditional formatting at my Excel spreadsheets, however the condition (color) used to explain the reason of a cell or a range, can differ based on the designer and the user of the product/workbook, in this case I have to first train the user, that why I had put that condition to get particular color, Here it is difficult for one to use the spreadsheet without explanation, is there way; where I can put my text Explaining the reasoning?
    Or suggest to add a text field at conditional module to add user thought.so that user can read the reasoning why this condition has been inserted/used

    E.g. We all Universally know that RED means Danger, Yellow means be alert or, Green means OK, but it may differ based on the user prospective, if he does not know what the RED stand for, or Green is OK or v/versa

    In my case color Blue can stand for RED

    Please let me know

    Happy New Year 2016

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

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

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

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

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

  50. Hello,

    Whether can we autocapture the formatted text such as (B,U,L) and replace with some html tags.

    Can i know how to write it.

    Regards,
    Navaneeth

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