Comments on: Excel conditional formatting formulas based on another cell

This tutorial explains how to use Excel formulas to format cells and entire rows based on the values you specify or based on another cell's value, and provides a handful of formula examples. Continue reading →

Comments page 31. Total comments: 1726

  1. Great Article..
    I have a set of data with -ve and +ve values. I just need to highlight the least -ve value and least +ve value
    Ex: -20, -15, -8, 1, 3, 6, 15 . In this case only -8 and 1 needs to be highlighted with different colors. can you please help with any formula for this?

  2. Hi all, I have 2 columns each have a date. Column (1)being a test date and column (2) being a set date 7 days after the test date, for reoccurrence of the test. I need to format so that column (2) has to actions.

    1. it turns orange when it is 5ays from its next test.
    2. it turns red when it runs over the due date.

    any help would be appreciated.

    1. Hi Darren,

      If we understand your task correctly, you can do this by creating two conditional formatting rules:
      1) To color cells red when the date in column 2 is the current date or earlier:
      =$B2<=TODAY()
      2) To color cells orange when there are 5 days left to the test date:
      =AND($B2-TODAY()>0,$B2-TODAY()<=5)

  3. JANUARY 2016
    Week Sun Mon Tue Wed Thur Fri Sat
    1 2
    1 3 4 5 6 7 8 9
    2 10 11 12 13 14 15 16
    3 17 18 19 20 21 23 23
    4 24 25 26 27 28 29 30
    5 31
    I made this calendar manualy and I want to highlight the number date automatically and link to Friday, January 1, 2016 (=Today())on the other row. Can you please give an answer

    thanks

    ronald

    1. Hello Ronald,

      If your task is to highlight the current date, you can use the following formula in your conditional formatting rule:
      =IF(ISBLANK(B9),FALSE,DATE(YEAR(TODAY()),2,B9)=TODAY())

      This rule will work for February. You will need to change the month number for each month, e.g. replace "2" with "3" in the formula for March.

      As for linking it to January 1, could you please clarify your task? What way do you want to link the dates?

  4. Hi Svetlana, I have been wrestling with this problem for a while and I am completely stumped.

    I have a spreadsheet which uses a formula (=INDEX('TMA Tracking'!K:K,MATCH(HelpSheet!$D$5,'TMA Tracking'!A:A,0))), in order to pull data off other multiple sheets within the workbook and display all the data in one place on one page (based on a key field common to all spreadsheets).

    My issue is that sometimes there is a null value that the formula is trying to retrieve and, so for a date for example, it displays 1900-01-00. I would like to highlight the result, but the conditional formatting in the cell only looks at the INDEX formula and not the resultant data that it displays.

    How do I fix that? Thanks so much in advance!

    1. Hello Mark,

      Conditional formatting rules look at the values in the range. If there is no date, your formula returns number 0 (the number of days starting from 01/01/1900) that is displayed in the date format. If you are trying to find such dates in your range, please replace the date 1900-01-00 with 0 in your conditional formatting rule.

      If your task is different, please describe it in more detail and share the formula you use in your rule.

      We'll do our best to assist you.

  5. I have a row with 5 columns and each cell in the 5 columns have numbers in them. I have another column with serialized numbered rows (1-50) and want to color the cell's background only if a number in this column matches any of he 5 numbers in the row (described at the beginning of this problem). How to write the conditional rule(s)?

    Spa-see-ba

    1. Hello George,

      You can use the following formula with your conditional formatting rule:
      =IF(ISNA(MATCH(A3,B3:F3,0)),FALSE,TRUE)

      We hope this helps!

  6. How can I compare two e.g. a1:d1 with f1:i1 please need urgently

    1. Hello,

      If you are trying to find complete row matches, you can use our Duplicate Remover add-in:
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

      Click on the Compare Two Tables icon, select A1:D1 (or as many rows as needed) as your first table, and F1:I1 as your second table, choose the necessary key columns for comparison and the action for the results, e.g. you can highlight all matching rows in your main table.

  7. I am using conditional formatting quite extensively on a work plan template. I have set up for different sections to format according to about 7 or 8 exclusively alternate options.
    However, I don't understand why the applicable ranges for the formats seem to adjust themselves without prompt, splitting out across separate ranges. I have tried to remedy this by displaying the format list to show 'This sheet' and reducing each rule to just one appearance, so it shows one (comprehensive)range per rule. However, it soon changes itself again! I have tried playing around with the $s too and can't get it to stick.

    To add to that, I now have a problem that when you insert a row within one of the conditionally formatted ranges, it automatically adjusts the first choice format (no format for blanks). It changes it so that it no longer applies to any rows previous to, and including, the new one. So, where the format range might have been set as $F$1:$U$80, if you add a row at row 6, all of a sudden the first choice formula becomes $F$7:$U$80.

    Where am I going wrong??

    Many thanks.

    1. Hello Jo,

      Please try to convert your data to a table in Excel (Ctrl+T), it may help with different issues, such as when a range of data gets broken.

  8. Hi Svetlana,

    i am trying to do conditional formatting on multiple cells. For example, in row B i have 100 values, in row C i have another 100 values - i want to format the cells so that if the value in Row C is over the the value in Row B it will highlight green, and if the value is lower it will highlight in red. The problem i have is i can do this for individual cells but i've tried so many formulas and just can't seem to get it right for multiple cells.

    Please please help!

    Many thanks and kindest regards in advance

    Jamie

    1. Hi Jamie,

      Most likely you have absolute references in your conditional formatting rules. To compare values in each row, select the cells you want to format and add two rules with an absolute column and a relative row reference, e.g.
      =$C3>$B3

      You can find more information about the difference between relative and absolute cell references here:
      https://www.ablebits.com/office-addins-blog/relative-absolute-cell-references-excel-conditional-formatting/

    2. Hi, I am facing a similar issue as Jamie has stated. I need to check the values of cells in Row 2 and color code corresponding cells in Row 5. Individual formulas for each cell work, but I am unable to extend formatting collectively over a range.

      Please help.

      Best Regards
      Sreeram R

      1. Hi Sreeram,

        Similarly, please make sure you use absolute row references in your formulas, i.e. put a dollar sign before the row number so that it is not changed: C$5.

  9. Hi,

    Need help on excel, I would like to know if there any formula for the excel that if I click on a single cell the contents of that cell will reflect on the other cell but the same work sheet.

    Example:
    The format is

    Cell data
    A1 1
    A2 2
    A3 3
    A4 4

    if I click Cell A1, the data will reflect on
    E1 = 1, and if I click cell A2, the data will also reflect to E1 = 2, and so forth.... to which cell I click the data of that cell will still reflect on E1.

    Thank You

  10. I have many entities in cells and i want to sum some maximum out of all the given what i have to do.
    Thanks

    1. Hello Zia,
      Could you please describe your task in more detail? If possible, please send a sample spreadsheet with your data and the expected results to support@ablebits.com and include a link to this blog post and your comment number.

      We'll do our best to assist you.

  11. Anyone know how to compare conditionally formatted colored cells in a column and color a cell in another column? VBA, formula, anything that can do this?

    Thanks,
    Andrew

    1. Hello Andrew,

      Could you specify if you are trying to compare the values of the cells or their colors? If you have a sample spreadsheet you could share with support@ablebits.com, it would help us understand your task and the expected results better.

  12. I have data in 2 different sheets. I want to color the cell of sheet 1 which is not available in sheet 2.

    e.g. if sheet 1 contains
    Fruit-apple, mango, grapes
    Vegetable- cabbage,carrot,potato
    in sheet 2 it contains
    Fruit-apple, grapes
    Vegetable- cabbage,carrot

    then mango & potato should be colored out.

    Kindly suggest a way to solve this.

    1. Hello Dipun,

      You can use the following formula in your conditional formatting rule:
      =ISNA(MATCH(A2,Sheet2!A$2:A$5,0))

  13. I have a specific need:
    I need a Bar Chart where the colour of a Bar should change based on the text Value in particular column on the table
    For. Eg.
    Teh followign cell references:
    A1: Worktype A | B1: 30
    A2: Worktype B | B2: 40
    A3: Worktype C | B3: 60
    Should show up as WT A - Green Bar; WT B - Orange Bar and WT C - Red Bar
    Every time the Work Type repeats in the table, the bar should automatically be of the colour mentioned in the rule. Is this possible?

  14. I am trying to use a 3 color scale formatting rule where each row of data contains the low/medium/high standards that should drive the formatting rule. This type of formatting rule doesn't want a relative reference which forces all of the formatting rules to just the very first low/medium/high standard to set the color.

    Other web postings suggest highlighting the whole range that the formatting rule should be applied to and then change the cell references to relative from absolute. However, I can't get this to work, excel will not accept a cell reference that isn't absolute.

    1. Hello Tom,

      Unfortunately this is a limitation in Excel: you can't use relative references for 3-color scale formatting, icon sets, etc. You either need to create a rule for each line, or specify the start and end points of the range with absolute references.

  15. Hello,

    Maybe someone will be able to help me :) Thanks in advance!

    The thing is:
    I have a value in B5 and this value needs to be in the range "-1% of B3; +1% of B3" to be approved for use. Next problem, I have an empty raw between 3 and 5, that I cannot delete and still need to use the formatting (the same when I have two or three empty raws between values that need to be compared). If value between "-1% of B3; +1% of B3" then the B5 cell gets green.

    How can I make it that the formating take the good value to compare it with, and that it ignores empty cells but goes up to the cell where the first value is. This value will change in function of the previous B value.

    Is that clear?

    Thank you for your help!

    1. Hello John,

      You need to create a conditional formatting rule with the following formula:
      =ABS(B5)<=ABS(B3/100)

  16. Cannot get this working and am going mad. I've tried all three combos (i.e. $E26, E$26, $E$26 but none works.

    Across columns E to M, I want to white out cell(row 27) if cell(row 26) is blank.

    So I'm using:
    Formula: =$E26="" Applies to: $E$27:$M$27
    or
    Formula: =E$26="" Applies to: $E$27:$M$27

    but neither works. Any/all help would be apreciated. Thanks!

  17. Very informative article! Great job!

  18. Very informative article. However, I am still stuck. I have tried several formulas but I can't seem to get my spreadsheet to corporate with me.

    I am trying to do a conditional formatting formula for the following situation: if C1>1 and A1>10 the C1 text will turn red.

    Hope you can help me:)

    Thank you,
    Heather

    1. Hello Heather,

      You can use the following formula to combine both conditions in your rule:
      =AND($A1>10,$C1>1)

  19. Is it possible to use conditional format the Text Value of a formula result into two different colors?

    eg., if the formula of a cell result is ="GBPUSD", possile to format the "GBP" to Red color and "USD" to Blue color?

    1. Hello Ian,

      This is not possible, conditional formatting rules are applied to the selected cells, you can't apply them to values at the moment.

  20. This information was exactly what I needed.

    I needed to highlight two columns (Date and Days) if the Days value was < 9 and the Days value is NOT BLANK.

    Column E contains a Cutoff Date.
    Column F contains the difference in days between Today's date and the Cutoff date: =DATEDIF(TODAY(),E2,"D")

    1) Format the Cutoff Date (column E) if it is Styles > Conditional Formatting
    - New Rule > Use a formula to determine which cells to format
    - Rule: =AND(ISBLANK(F2)=FALSE, F2<9)
    -- Applies the rule if column F is NOT BLANK and is < 9

    2) Format the Days (column F) if it is < 9 days and is NOT BLANK; highlight the value in RED.
    - Applied the same Conditional Formatting as #1 above.

    It worked perfectly! Thank you for the great details.

    1. Correction to my initial comment...

      1) Format the Cutoff Date (column E) if it is Conditional Formatting
      - New Rule > Use a formula to determine which cells to format
      - Rule: =AND(ISBLANK(F2)=FALSE, F2<9)
      -- Applies the rule if column F is NOT BLANK and is < 9

      2) Format the Days (column F) if it is < 9 days and is NOT BLANK; highlight the value in RED.
      - Applied the same Conditional Formatting as #1 above.

  21. Hi,

    I'm trying to get a conditional formatting to work;

    =AND($C2='Calculation Sheet'!$C$32,$AF2<='Template Master'!$D$32)

    C2 is a cell that will change between values [R, 1, 2, 3, 4, 5, 6]
    and
    'Calculation Sheet'!$C$32 currently equals the value "1"

    $AF2 value is currently 5
    and
    'Template Master'!$D$32) value is currently 13

    I would like AF2 to change colour if both conditions are met;

    When C2 and 'Calculation Sheet'!$C$32 equal the same value "1"

    and

    The value entered into AF2 is less than or equal to 13

    Any help would be great!

    Thanks.

    Ben

    1. Hi Ben,

      Your formula looks correct and it works with the conditions you listed. Could you please specify if you don't get the expected results?

  22. You are the best I have seen so far. My question is, When columb "D" decreases, I would like columb "E" to reflect the date that it decreased. How would I do that? Thank you.

    1. Hi David,

      Thank you for your kind words. Regrettably it is not possible to reflect the data change by using conditional formatting. You can create a rule for column D being equal to some value, less than or greater than a certain value, but not for the value increase or decrease.

  23. in conditional formatting. how can compare in three column condition is Only in three which customer is % of growth do not consider -% value

    JAN FEB MARCH
    63% -22% -22%
    250% 45% 45%
    331% -8% -8%
    101% 20% 20%
    260% 34% 34%
    21% -63% -63%
    76% -6% -6%
    556% -59% -59%
    47% 116% 116%
    290% -74% -74%
    pls help

  24. Hi,

    If I wanted to fill certain amount of cells in a row to match a value kind of a progress bar how could I do that?

  25. Hi,

    I need to set a conditional formatting in cells. I need column m to be highlighted if (m1-n1)>10%

    how can i do that?

    Thank you!!

  26. You have a great resource here, just taught myself some simple conditional formatting. Now for the hard stuff.
    I'd like to use conditional formatting based upon where the cell's data comes from.
    I have a spreadsheet that displays information coming from MS Query (which itself gets it from a SQL ODBC).
    I just had an end-user supply incorrect information to a doctor because they had copy-pasted a row of old information above the query results.
    Is there a way to conditional format the cells based upon where the data comes from? In this case, the erroneous data didn't have an underlying "Edit Query" (Data/Properties) option.

  27. Svetlana, thanks for this great information! I have a question that may be simple, but after toying around for a bit, I can't figure it out. Can you help me with the formula?

    My situation is, I want all the cells in Column A to change color when the corresponding cells in Columns T through AC EACH have a Y entered in them.

    (The Y means Yes, and the Color Change in A, which contains applicant's names, would signify that the applicant has all parts of their application, where parts are respectively marked as received by a Y in the corresponding column T through AC. Until the cells in columns Y through AC have a Y entered in them, they are left blank).

    Are you able to share a formula or advice for this? I am open to further questions, and grateful for your time!

    Mike

  28. Hi
    I am wanting to use conditional formatting to make a cell D5 green if () 5% and carry this along the row, always using the cell above, so its never the same cell I am wanting to use- is this possible.

  29. Great site! Thanks for all the good info.

    I would like to use conditional formatting to color one cell (K7) based on the individual numeric values contained in one column (K8 thru K1000). The individual numeric values in the column (K8 thru K1000) are the result of a mathematical formula. Conditional formatting color of K7 would be based on the value of any individual column value. Example: green for any cell value >5 and red for cell value >10. Thank you in advance for your help.

  30. Hi -
    I'm pretty good with lots of excel formulas (counts, database, financial, and the basics), so I must be missing something really obvious here.
    I've been using excel all morning, and sent a file to someone for input. After I got the file back, any excel file I open and try to do a basic sum of two rows returns zero.
    Cell 1: 100
    Cell 2: 3
    Cell 3 =a1+a2 returns zero
    1. I've checked the calculation window and it is set to automatic
    2. I've checked the cell format and made sure it's numbers.
    3. I even cut and pasted the cells as values and numbers just in case
    what is up?
    Thanks!

  31. Hi,
    Thanks for the help so far. I'm far better off than when I started, but can't quite get this right.
    I'd like to highlight a specific cell; A60, containing text "beef" only if a cell in other ranges (B17:Z17, B19:Z19, B21:Z21, B28:Z28, B35:Z35, B49:Z49) contains text "beef". Is that possible?
    Thanks
    Amy

  32. I'm trying to count items on a list that are grouped together. For example:
    Column A
    Dog
    Dog
    Dog
    Dog
    Cat
    Cat
    Cat
    Fish
    Fish
    Fish
    Fish

    In column B I'm trying to get those numbered:
    Column A Column B
    Dog 1
    Dog 2
    Dog 3
    Dog 4
    Cat 1
    Cat 2
    Cat 3
    Fish 1
    Fish 2
    Fish 4
    Fish 5

    Is there a formula that would get me what column B looks like? Right now its a manual process and very time consuming with the amount of data I'm using.

  33. Can I attach and send a small file? Conditional formatting with % in 2013 does not seem to work.

  34. Is there a way using conditional formatting to highlight cells when cells in two adjacent columns are within 10% of each other? So for example if the value of d3 is within 10% of of the value in E3 and the value in e3 is within 10% of of the value in d3, then highlight both e3 and d3.

  35. Hai,
    Is it possible to provide conditional formatting from one sheet to another sheet in a work book. (office 2007 version)
    i tried with this below equation but i am getting error message.
    Please see the equation I tried =Sheet2!$B$1="NO" and the message is " you cannot use references to other worksheet or workbooks for conditional formatting criteria ".

    Any solution for this??

  36. Hello, I have a question about conditional formatting.
    I am trying to set up a spreadsheet for training at my company. What I would like to do is I want to set up a formula that if a cell does not match another cell, it turns a red color but when it does, it turns a green color. I am pretty sure it is conditional formatting but I am so inexperienced with excel that I have no clue what I am doing. Please help!

  37. You are amazing!

  38. Continued...
    So, if either one of the cells in 1 row, has a time that is not 5 min more or not 5 min less (even the same time) than any of the other cells in the same row, it must make them Yellow. So in the below instance all will be yellow.
    01-10-15 21:44 01-10-15 21:30 01-10-15 21:45 01-10-15 21:30

  39. I need to do a conditional formatting, where if the time (data format is dd-mm-yy HH:ss) in one cell is less than 5 mins more or less than 5 min less than the other cell.
    eg:
    A1 A2 A3
    01-10-15 21:44 01-10-15 21:30 01-10-15 21:45 01-10-15 21:30

  40. Dear everybody, I have a following problem, and I hope that somebody will help me with that :)

    I need to do the following in an Excel document:

    If in the column X I have a value "approve" (text value), than the corresponding value from the X-1 column has to be multiplied by 1000 (in the same row, off course).

    Is there a formula that can help with this? Thank you so much in advance! Regards, Valentina

  41. I am trying to create a formula for dates and am having a problem. I want to enter a day 1 and the rest of the days populate as needed. I have the rest of the dates formulated so that they populate correctly but I need to figure out how to formulate the day one date in order to make the others follow when I enter day 1. (day 1, day 14, day 21, day 28 and so on). This is a schedule to create dates in the future on specific dates. Like I said I can't get the dates to populate when I enter day 1. Can anyone help?

  42. hi there,
    i have created a condtionl formating in colum A, where it has dates, and it will highlight if day i experied in red, 5 days remining amber and 15days yellow "which works really fine. on next colum i want to have a formula which will help me to remove colours from previous colum (condtional rule) if i just type "OK".

    Can you help me please ?

  43. hi there,
    i have created a condtionl formating in colum A, where it has dates, and it will highlight if day i experied in red, 5 days remining amber and 15days yellow. on next colum i want to have a formula which will help me to remove colours from previous colum if i just type "OK".

    Can you help me please ?

  44. I have highlighted some lower and upper values in my cells using conditional formatting.
    How can i generate a chart with same colors which i highlighted using conditional format?(i want my chart also to automatically show the colors which i used for the cell)

  45. I'm trying to highlight a cell if it contains certain words: ie. if it says "apple or strawberry or orange" filling it in purple. I want to create multiple rules of the same type but using different words, so one set would be one color and the other set another. But I can't even get this to work! I was trying to use the OR function, this is the last formula I tried.

    =OR(strawberry,blueberry,banana,blackberry,orange,apple,raspberry,pineapple,melon)

  46. Hi

    If range of cells (all of cell in selected row) are blank,highlight a row
    using conditional formatting.

    I can highlight a row if a cell is blank but it does not work for range of
    cells blank.

    What I mean is, if any cell of the selected row has any value or text, I do not want to highlight. I only want to hight rows that are blanks (rage of cell).
    Would appreciate your help.
    Name Date Address Invoice No Amount
    Ganesh 1/1/2015 Uttar Pradesh, India 10215 50
    Prem 2/1/2015 Uttar Pradesh, India 10216 1500
    Radhe Since B4:E4="" highlight this row
    Krishna 4/1/2015 Uttar Pradesh, India 10218 300
    Hari 5/1/2015 Uttar Pradesh, India 10219 400
    Shiva Since B7:E7="" highlight this row
    Shanti 7/1/2015 Uttar Pradesh, India 10221 80
    Om Since B9:E9="" highlight this row
    Kiram 9/1/2015 Uttar Pradesh, India 10223 700

  47. Good afternoon,

    I need to change/increment the date in column 2 by 1, 2, or 3 years in column 3 based on the fill color in column 1.

    Name Date Date2
    Name1 January 1, 2014 January 1, 2015
    Name2 January 2, 2014 January 2, 2016
    Name3 January 3, 2014 January 3, 2017

    Thanks in advance

  48. Hi,

    I don't know if anyone will respond to this or not, but I'm working on an excel spreadsheet and have a tricky conditional formatting question.

    I have already formatted column D, but I need help with E. What I am looking to do is this: Column D is the status of a lab, column E is the overall facility status (My four criteria are: lab not being used, lab is down, lab is limited, and lab is up and operational). So, 1=lab is operational, 2=limited, 3=down, and 4=not being used. For column E, I would like to say if the overall facility is operation, limited, down, or not being used. So, if, for example, the labs for one facility (D2-D4) contain at least one status that is down, the facility should also be considered down....and so on and so forth. But, if one of the labs is not being used, but the other two are operational, the facility should be operational.

    Is there a way to do this? I have been trying to figure this out for a couple of hours and have had no luck. It seems like all of the formulas I come up with do not narrow down my criteria enough. I do not know SQL stuff...but any other help will be appreciated! Thanks!

  49. Which formulas or conditional formatting can I use to say that IF value in Column I,L,O,R,U,X is than value in Column AG, make it red?

  50. How do I write the code for a list of variables associated to one colour?

    So I have a list of codes in a single column - efx, mbc, dad, hif and I want to highlight them all in the same colour.

    Does anyone know how I do this?

    Thanks

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