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

  1. Hello, I am tring to color code a Rank of numbers in a single column. This column contains positive, zero, and negative numbers. I have 3 conditional rules, for 1,2, & 3 places
    1st Place the CF is (Cell Value equal to) =LARGE($G$235:$G$264,1)
    2nd Place the CF is (Cell Value equal to) =LARGE($G$235:$G$264,2)
    3rd Place the CF is (Cell Value equal to) =LARGE($G$235:$G$264,3)
    My test numbers are: -4, -2, 4, 0, 2
    First place is 4, correct. Second Place is 2, correct, Third place is -2, wrong. It should be zero.
    Can you help ?
    Thanks

  2. Show the names of employees in blue text color who have salary greater than or equal to 3000 and show the names in green text color for those earning less than 3000.

  3. i want select the one colour gradient bt only two colour is available how i can choos one colour or preset

  4. I want to color max value. but unable to color max value like that one (1). how to do it

  5. hi does anybody know how to make yes have a background value of 100%?

    So I am able link it to a dependent and can average everything out.

  6. Hello,
    I have a total of 50 employees. On a separate sheet, I have the list of employees who filed for leaves on a specific day however I'd like to highlight only the first 10% of 50 (which is 5) since the allowed number of leaves per day is 5 only. The basis is the Transaction ID:

    "Transaction
    ID" Employee # Name Date and Time Rqstd
    1 F025630 John Smith 4:30:03 PM
    2 F025631 John Doe 7:32:03 PM
    3 F025632 Jane Doe 12:30:03 AM
    4 F025633 Jane Smith 1:30:03 AM
    5 F025634 Tony Stark 1:35:03 AM
    6 F025635 Steve Rogers 1:36:03 AM
    7 F025636 Clint Barton 2:30:03 AM
    8 F025637 Natasha Romanoff3:30:03 AM
    9 F025638 Bruce Banner 6:30:03 AM
    10 F025639 Thor Odinson 9:30:03 AM

    How do I do that?

  7. please help me ,
    if i am type 20-2 in excle 2013 then excle take 43151 value , not take correct value so how to resolve this

    1. Dinesh:
      How is the cell you're entering the 20-2 formatted?
      Try formatting it in another way.
      Right click select Format Cells and then choose another option from that list. Maybe text.

  8. Please help.

    So I’m not sure what to use or what to do. So I’m trying to make an inventory sheet, so right now b9,c9, is 10 where e9,f9 is 10 and h9,i9 is 10. So when i9 goes to “0” I want h9 to Recognise and talk to f9 and say hey I’m giving my value of 10 to i9 so you will need to talk to e9 and Your Your 10 from him. Do I make sense?

  9. What are the different number formats?How will you change the format of the number?write the steps

  10. I want to set the conditions of a cell to:
    today's date - 180 days turn red
    today's date - 135 days turn orange
    today's date - 90 days turn yellow
    today's date - 75 days turns green

    Can anyone help?

    1. Josh:
      You've asked to apply four different conditional formats based on four different conditions, so you'll need four rules all applied to the same range of cells.
      Select the range of cells to which you want to apply the rules.
      Select Conditional Formatting, select Highlight Cell Rules, then More Rules, then select Use Formula to determine which cells to format.
      Where the range is H48:H51, in the field that is displayed enter this formula:
      =AND(H48>TODAY(),H48=(TODAY()+75))
      Notice that the formula references the top left cell in the range H48:H51. Your range and top left cell is probably different, but this formula must reference the top left cell in the range.
      Notice that this formula will apply the formatting to the date that is 75 days from today.
      The H48>TODAY() ensures the date in H48 is not in the past.
      You asked for TODAY()+ 180, etc. so the formula includes H48=(TODAY()+75), etc. So it is only formatting the cells that are equal to 180 days (or whatever number of days)from today. Not greater than the number of days from today. This allows you to put different formatting to each rule and specific future date.
      Select format, then choose the fill color then OK and then apply and OK your way back to the dialogue window that displays the rules.
      In that window you'll want to click Add New Rule so that you can enter a new rule which will have the same formula except that you'll change the "+" number to the different number of days and different formatting for each different rule.
      Follow this same procedure until you have all four rules and their respective formatting the way you want them.

  11. how can i add 2 cell the second cell will petch or getch the data of the first cell and after adding it the 1 cell will reset to zero and the second cell will stay its value
    for example
    1+0=1 then after the sum it will be like this
    0+1=1 then i will input new no. to cell 1
    2+1=3 the the the second cell will show the total and the 1cell will be reset to 0

  12. Hey,

    Can someone help me with the below one ?

    A reports to B and B reports to C and c reports to D and so on hierarchy..

    Now there are certain expectations wherein A reports directly to D .. in case I want to pull n represent this as per hierarchy how should I do it ?

    Endly, let’s assume 5000 employees hierarchy is X and 3000 hierarchy is Y how should we add them up together

  13. Thank you very much for these tutorials, they are so useful!!!!

  14. How To Highlight Row If Cell Contains Text/Value/Blank In Excel

  15. how do you compare figures that are in the same raw. e. g show how the deposits made perform against each other.

  16. Hello,

    I am trying to highlight cells in column C based on the second digit in a 10-digit code - I want each cell in that column highlighted one color if that digit is N, another color if that digit is not an N. I assume I will need two rules, and am trying to use the formulas =mid(c2,2,1)="N" and =mid(c2,2,1)"N". When I tested the formulas in a blank column the "true" and "false" come up correct, but when I put them into a Conditional Formatting rule, they are not highlighting the correct cells. Any suggestions?

  17. Hello i have a work list sheet with multiple items on then i have a date type in two different columns i have a 90 day wjndow from the date to complete the work by the date i was trying to figure out how to have the celss turn red after the 90 is past

  18. Correction
    Kindly anybody has Idea, I have one text cell in Excel, hwo to divide that text into two cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do, Thanks in adv. and happy new year

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  19. Kindly anybody has Idea, I have one text cell in Excel, who to divide that text into tow cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do

  20. Hi -
    I want to trigger a cell to change color depending on the % in another cell. I have been able to create rules for 0% in T8 to trigger U8 Grey, 80% T8 to trigger U8 orange, but when I add 100% T8 to trigger U8 Green it doesn't work and still triggers orange. Can you let me know what I am doing wrong?
    Thanks for any and all your help!!!

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

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

  22. Hi,

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

    Thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  37. KINDLY IGNORE A1 =.

    THANKS

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

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

    Thank you

    Kenny

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

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

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

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

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

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

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

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

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

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

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

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