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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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