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 →

Table of contents

Comments page 4. Total comments: 316

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

  2. Hi,

    I am trying to highlight number of cell with the number i keyin, what is the formula for this situation?

    Thanks

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

  4. Hi,
    I want to keep conditional formatting in dates for vehicles record. when i enter the current it should check last 6 month records. if it's there will change automatically.

  5. Hi all,

    My problem is probably very basic in comparison with all of yours but i am not an excel bizz and I couldn't find the resolution in the above comments or tutorial...
    At work i have created a spreadsheet for all our new clients and all of the checks and ID they have top provide etc. before we can start working for them. When all of the checks have been passed and they have supplied sufficient information about themselves for our records the date of completion is put in the end column. How can I make this cell with the date in turn red after a year of the date? As this is when we have to request new up-to-date information from them... Any help would be greatly appreciated asap!

  6. Privet vsem!

    Sorry for disturbing you, but I am a newbie to Excel fomatting and need some help. I have an excel sheet where I keep the profiles of people(name, last name, performance, ....). I do have multiple copies of the same person(let's call him Andrey Goncharov) on my sheet as I am copying it from another source (every week). So last week I marked Andrey's "Performance" column as a "Top Performer". When I copy (from a different source) again this week, if there is Andrey Goncharov, I want Excel to auto-fill the "Performance" column of a new copy as a "Top Performer", because I already have done before for that person. I hope I could explain my issue.

    Thanks a lot for your help and support, you are a wonderful team :).

    Ben

    1. Hello, Ben,

      I'm sorry, it's difficult to come up with any suggestion, since we don't know how your data is stored and there are too many peculiarities that can affect the way your task should be solved.
      If you still require our assistance, feel free to send us your example workbook with the source information and the result you want to get to support@ablebits.com. Please include the link to this comment into the email.
      Our technical specialist will take a look at your task and try to help.

  7. Hi

    I have table shown below
    no low high b/s present
    01 105 125 b 105
    02 252 183 s 183
    03 453 522 s 522

    In need a formula for present column in such that if b/s column contain "b" then present column value should be low column otherwise if b/s column contain "s" then the value of present cell should be high . could you please give me the formula for the above case

  8. I cant seem to work this out, I know its simple but my brain is fried.

    I need to use conditional formatting to say Target met if there has been an increase of 5% in 2015 from 2014 and improvement needed if there has not been a 5% increase. Its the % that are confusing me. Thanks

    2014
    Strongly Agree
    A2 0.93
    A3 0.97
    A4 0.92
    A5 0.93
    A6 0.95
    A7 0.89
    A8 0.82

    2015
    Strongly Agree
    A13 0.91
    A14 0.91
    A15 0.9
    A16 0.89
    A17 0.88
    A18 0.8
    A19 0.75

  9. Hi

    Im trying to add a conditions into a excel sheet to highlight the cell in chosen colour.

    EG
    if figure is cell A is equal to or more than 25% of the figure in Cell B, then colour cell A Red, otherwise colour cell A Green.

    are you able to help me with a simple formular. I am using excel 2016

    thank you

  10. Hello,
    Perhaps someone might be able to help me. I'm an intermediate excel user but haven't extended much past simple formulas.

    I am working on a project with a column dedicated to using 3-4 single letters to represent different words. Is there a way to conditionally format so a blank column would automatically change the font color given the letter imputed into the cell?
    i.e. C=Card, B=Basket, K=Keep Brunch,F=Flowers, N=Nothing

    C=orange
    K=Blue
    B=Purple
    N=Red

    Thanks so much!

  11. Hello,
    I'm trying to conditionally format cells ONLY if the cell does not contain a formula and the cells is >0. I have a data set that pulls data from different places and I want to be able to easily tell when something is hard coded as long as it's greater than zero.

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

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

  14. Sir/Mam
    I have 2 question.

    1:- When I will enter date in shhet2 A2 column then the out put will show in sheet2 B2 column. My B2 column formula is =If(isblank(A2),"Please enter Date",if(isnumber(A2),vlookup(A2,Sheet1!A:C,2,0),"Invalid Date")).
    So I want, when will show "PLEASE ENTER DATE" in B2 then the font colour will be GREEN. When "INVALID DATE " will show then the font colour will be RED.And the result of vlookup font colour will show YELLOW.

    2:- The second question is- I put the formula in B6 =if(A6="Sun","Ashok,Deepak",if(A6="Mon","Rajesh,Ramesh",if(A6="Tue","Prakash,Dillip",""))).
    So I want when the name will show then the font colour will be blue.

    so please tell me how to solve it by conditional formating.

  15. Hello 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 mention in column O3 then Column P3 should be blank without anything.

  16. Hello Friends!

    I writing the figure 624 in a cell G6 and in cell H6 I writing =G6*1% in this case I got the correct answer, but I intend to get formula for multi figures in h6 like this =g6*1% with the new answer *10% and further with new answer *12% in just a single cell as H6. what I can do for the same. Guide me someone please.

    M. Munir

  17. KINDLY IGNORE A1 =.

    THANKS

  18. R/MAM,

    GOOD NOON,

    PL NOTE THAT A1 CELL HAS A VALUE "X", I WANT TO HIGH LIGHT CELL B1 WHEN IT'S VALUE LESS THAN 10% OF CELL A1.

    IN FORM OF MATHS
    CELL B1 SHOULD BE HIGHLIGHT WHEN
    A1 = < A1-(A1*0.1)

    HOW CAN I DONE BY CONDITIONING FORMATTING FACILITY?

    REGARDS,

    MONIK

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

    Thank you

    Kenny

  20. I have a training spreadsheet with expiry dates. I want to use traffic lights to turn the cells red, amber or green as they approach the date of expiry compared to todays date, in months. eg due in 12 months or more green, due in 6 months or more amber and less than 6 months red. Is there an easy way to do this in Excel 2010 please. I have formatted the date as eg Jan 2017, Mar 2019 to make it easier. Thanks

  21. Hello,
    I've been trying to make this work. I want that every time I have a negative number in column L, the whole row where the number is change the font to red. I can make it work for just one row but when I try to implemented in the worksheet is when I got stuck. In the conditional formatting option I select the "Use a formula..." option. I type =$L:$L<0, But it doesn't work. when I just select a specific cell (i.e. =$L$145), it works but that's not what I am looking for. On the other part I just type the rows I want to be affected by this (=$A:$R,$T:$AD).
    Any help regarding this matter, I really appreciate it.
    Thank you!

  22. Hi Guys, Can someone help me please.
    i need help with an example below.

    i want in column x. 13/06/05 , 14/08/16, 25/07/17
    in column y i want it to turn red if nothing is filled in a week after the dates in column x.

    Can someone please help

  23. im building 5 town houses, i owe the bank 2.5 million
    when i have sell for 5 townhouses lets say 600- 700 thousand each
    in excel i got
    2.5m in cell B3
    every time i sell a house i enter the price and it deducts from B3
    lets say i have 300 thousand left to pay off showing in B3
    i enter 400 thousand i want the B3 to be zero and what is leftover goes to a different cell
    hope this makes sense

  24. Hi
    i have names lised in a column 1-15.
    What i am trying to do is is, that if one of those same names appears in another cell in the same column, that the original name in the column 1-15 disappears and turns a different colour?
    Is this possible through conditionl formatting?
    Ray

  25. Hello
    I need help.
    I want to see only one cell in which conditional formatting applied on the basis of value entered in the cell.That means if i enter 10% then 10% of the cell will coloured, if i enter 50% then 50% will be colored,if i will enter 100% then full cell will be coloured.(This is for only one cell).

    Thanks in advance.

  26. I'm trying to make a timecard that calculates both regular and overtime hours. I want to make it where when I reach a total of 40 hours in regular time it stops entering data in the cells in the regular time column and any time over 40 hours will then start showing up in the cells in the overtime column.

    I apologies if this doesn't make sense

  27. Hello!,

    I am designing a hitmap using conditional formatting and I would like not to show the value in the cell. I have tried modifing the format of the cell by writting the comand ;;;; in the costum blog of the cell format... but does not work... any idea on how to "Hide" the cell value?

    Thanks

  28. How to Get? - If amongst 4 cells if I put 1 in any of the cells, rest of the three cell should be showing 0. How to do that?
    Which formula to use?

  29. hi

    i have series of rows employee wise , with character p entered for present on applicable dates in a month , i want to highlight the column where p consecutively appears in a row 10th time

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

  31. Hi,

    Can someone please help me. I need to do conditional formatting on values that are on different worksheets.Cell O5 in worksheet A if it's found in Cell I in worksheet B to be highlighted as whatever colour.

    Many thanks

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

  33. Hi team
    i have a doubt regarding the average of values, for suppose if we have two trials TS1 and TS2, in which i have got TS1 is 0.08% and TS2 is 0.03%, the average we required is from the values which are >0.05% it means the final value from above is 0.08%. for this i have kept a command that =IF(AVERAGE(D14,F14)<0.05,"<0.05",AVERAGE(D14,F14)). for suppose the values for TS1 and TS2 both are <0.05% it means if TS1 is 0.03 and TS2 is 0.04 then the Average value to be displayed is <0.05. but iam not able to get it, plz anyone can help me out.....

  34. Hy Svetlana
    Today I Read Your excel formula where i am very pleasant because you solve many people problem i need some help
    example
    i create timing report
    ( Like )
    Real In Time: Employ In Time (Remarks)
    9:00 9:25 late
    9:00 9:28 late
    9:00 9:18 OK
    9:00 9:22 late
    i want formula which show automatically status if employ 20 mint late come

  35. Good day. Please assist me as i have to insert an IF formula for an evaluation tool. I have an overall average calculated for the tool however i require an IF formula that states if a certain criteria = 0 then the overall evaluation average should =0.

  36. Hi Svetlana
    I used your instructions for conditional formatting, which worked wonderfully, thank you! However, I'm now stumped as to how to copy these colours over, I will try to explain:
    Used the formatting to fill colour in cells of varying "greater than" amounts relating to an average value which is based on a figure entered each week. So the names of people are down column A and each column after has their weekly score. Column AA has the average, which is coloured accordingly. I'd like to have the cell containing the name of each person to automatically fill with the same colour as the one containing their average score.
    I am a complete novice at this, hope you can help :)

  37. I have a column with data in it with 3 rules set up. The rules are if the value is less than 80, highlight the cell in red, if the value is between 80 and 89, highlight in yellow and if the value is between 90 and 100, highlight the cell in green. Everything works as it should. I would like to know if it is possible to create another conditional format rule for all cells to the right of my column with values in it that will highlight the cells for each line of data to change color based on the data column?

    I only want to input values in the one column all the way to the left. The rest of the columns to the right only change color based on that column of cells.

  38. please send your mail id. i want to send a spread sheet to you. That's my worksheet for deviation statement for govt sector. i need your hepl to fixing the formula in this worksheet. thanking

  39. Is it possible to get copies of the workbooks you use in your conditional formatting articles so I can practice?

  40. I want use to Conditional formatting

    If A Column is "Sunday",

    Then B Column Fill Red color. Is this possible

  41. Thank you Irina.

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

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

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

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

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

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

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

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

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

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