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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  20. I want use to Conditional formatting

    If A Column is "Sunday",

    Then B Column Fill Red color. Is this possible

  21. Thank you Irina.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  49. Hi Svetlana,

    How do you add a drop down table to a cell

    Thanks.

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

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