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 8. Total comments: 1062

  1. Hi Svetlana.
    I have a conditional format running that changes a cell blue if the letters GI are entered or red if the letters AFM are entered, I also need the cell to the left of GI or AFM cell to format blue or red. I have tried many formulas but without any luck

    1. Hi Garry,

      Just apply the same rules to two columns instead of one.

  2. I have several series of data validation dropdowns, and lets say when E5 = text_value i want G5 to be highlighted

    because under specific conditions (depending on what previous choices are made) there needs to be an additional dropdown. i am not concerned with making a conditional data validation (probably not possible anyway) but i was hoping someone could help so that i could just highlight it so it sticks out when additional information is required

    1. nevermind!! i got it! after reading other situations i realized that the rule would be specific to the selected cell and wouldnt necessarily be a general formula for the entire sheet

  3. Hi guys, can someone help me please..

    How To use conditional formatting for example... in cell A1 Sheet 1, based on the value from cell A2 in Sheet 2.

    So...
    If Cell A2 (Sheet2) = "DA"....then Cell A1 (Sheet1) background is RED.

    Thank You

    1. Hi Ivan,

      Create a rule for cell A1 Sheet 1 with the following formula:
      =Sheet2!$A2="DA"

  4. Does anyone know how to make a conditional format to create a specific number based on another cells number?
    Example:
    Cell 1 shows the number 3
    I need cell 2 to then show the number 1.

    All in increments of 3, if it shows 6 then it will show 2.
    9 = 3
    12 = 4 etc. Also, If it is between 3 and 5 it will still show a 1, between 6 and 8 it will still show a 2 and so on.
    Any help would on this conditional formatting would be appreciated.

    1. Hi Shawn,
      I believe a Lookup Formula would be your best option.

      If you have your data starting in cell A2, you can add the following formula in cell B2:

      =LOOKUP(lookup_value, lookup_vector, [result_vector])
      =LOOKUP(A2,{3,6,9,12},{"1","2","3","4"})

      You can keep expanding the formula by increments of 3, but make sure you update the lookup_vector and result_vector.

      I hope this helps.

  5. Hi,

    I have been trying to do cell formatting for following condition:

    =$Q3$AS3

    I want to highlight the cells of column Q3 if the value doesn't match with values in column AS.
    I used above mentioned condition but somehow it's highlighting all the rows and columns from Q until AS. I tried to select following ranges "Q3:Q100" as well as "Q3:AS3, Q100:AS100", but no use.

    Please suggest.

    Thanks,
    Shipra

    1. Hi Shipra,

      I have just created a rule for Q3:Q100 with the following formula and it highlighted only cells in column Q that are not equal to values in column AS in the same row:
      =$Q3<>$AS3

      Please double check the applied range via Conditional Formatting > Manage Rules.

  6. Hey there,

    I'm trying to set a conditional format where I want to change the colour of column A if the column width does not equal "10.29".

    Would you be able to help out?

    Thanks,

    Jon

    1. Hey Jon,

      I have been trying to attempt the same thing with cell formulas. I have put in =cell("WIDTH") but it does not show the exact amount. Let me know if you have any luck with finding a formula.

      Matt

  7. Hi Svetlana,

    First of all Congratulations for your blog! I find it 'state of the art' and a precious on-line resource. Of course I will spread the word about it...!

    As I wasn't able to find a solution that works among the tutorials and examples you posted above, please allow to present my question:

    How to apply conditional formatting to columns F, G, H and I (by instance change font colour to 'red'...) when it met a given criteria (comprised of the word string IMP) in the same line in column A (in Microsoft Excel 2007)...? Or in another way, which formula to use...?

    I deeply appreciate your help. Thank you.

    Kind regards,

    Carlos

    1. Hello Carlos,

      Thank you very much for your kind words.

      You can select the entire columns $F:$I and create a rule with the following formula:
      =$A1="IMP"

      The above formula works for the exact match. In case you are looking for a partial match formula, i.e. highlight columns F:I if column A contains values like "IMP 2" or "xIMP", then use the SEARCH function as follows:
      =SEARCH("IMP", $A1)>0

  8. Im doing a data bar formatting for a cell, and I would like the color of the data bar to change relative to the value of another cell.

    1. Hi Luis,

      Select the cells where you want data bars to appear, click Conditional formatting > Data bars > More rules. In the "New Formatting Rule" window, select Formula in the "Type" box and enter your formula in the "Value" box. Just keep in mind that relative cell references are not allowed in Data bar formulas.

  9. Hey, guys.

    I would like to conditionally format cells which are contained in a formula.

    Ex: If A11 equals the Sum of A1:A10, I would like A1 through A10 highlighted a certain color.

    This would allow me to quickly see what cells are in the summation without putting the cursor in the formula bar up top.

    Thanks.

    1. Hi Ben,
      Select cells A1:A10 and create a rule with the following formula:
      =$A$11=SUM($A$1:$A$10)

  10. I have this table, it show like this:

    Given no. USL LSL Measured value Deviation Out of Scope
    (GN) + - (MV) (GN - MV) (This I want to get the answer) /
    42 42.3 41.7 42.38 .38 .08 (to get this, must choose / which is more nearer, to the
    / Given no. the USL or / LSL

    1. Hi Alexxander , can you please advice me something in comment # 269

  11. Hi Svetlana,

    I have a table with some name positions and values for them in 3 currencyes ($, Euro, GPB)
    Have already made a function that whenever I select one of the currency to bring the value just for the respective currency that I have selected. The problem is that it will bring just the value.

    Is there any format function that when I select the currency type to format the cell in (either $, E, or GPB - what I selected), so that it will not birng only the value that I need, but to be formated and contain the currency?

    Many thanks

    1. Hi Vladimir,

      It's difficult to advise anything without seeing your formula. In most cases, you can use the concatenation operator, like this:

      =IF(A1="$", B1&"$", "")

      or

      =IF(A1="$", B1&A1, "")

  12. Hello,

    I am hoping you can help me with a question I have in regards to conditional formatting a cell.

    I am try to conditional format cell D3 so that it is green if it is more than 50%>B3*20, red if it is <B3*20, and yellow in between.

    Is this possible?

    Thanks,
    Jackie

    1. Hi Jackie,

      Here are the green and red formulas as per your conditions:

      Green (if D3 is more than 50% greater than B3*20): =D3-(B3*20)/B3*20>50%

      Red (if D3 is less than B3*20): =D3<B3*20

      And exactly what "in between" means? :)

      1. Thank you so much! :)

        There is no in between.

  13. Hello!
    I'm working on a sheet. In my G Column I have whether the task is "Resolved" Or not.
    The two options are either yes, or no.
    Columns A-F is the corresponding information for that task. Each row is a different task.
    I can get Column G to go green for yes and red for no, but I can't get the corresponding information in rows A-F go green or red for the corresponding Yes/No in Column G. What is the formula I should be using here?

    Thank you for your help!!
    Julia

    1. Hi Julia,

      You use the same formulas as you used to highlight column G but apply the rule to columns A-F, e.g. A2:F100. Given that row 2 is your first row with data, not including the column headers.

  14. Hello Svetlana,

    I am creating a workbook to track and rank various statistics for my office's summer golf league. I am running into a handful of formatting snags.

    Some of the stuff that I want to do is a bit more complex than I anticipated but I know it can be done in Excel. My hope is that the workbook can very automated with regards to data input.

    Please contact me if you are still available......I see the last post was from almost 1 year ago.

    Thank you so much for your time.

    Brad Sanok

    1. Oh.....

      I must have been looking at the first post :/

  15. Thanks Svetlana,

    Yes, that's exactly what I typed, before it got mangled :)

    Originally it applied to the whole column ($E:$E), but I changed it to E2:E1000. This made no difference.

    The E column is composed of values obtained by a vlookup() from another sheet. Will this mess things up?

    Thanks

    Nick

    1. Just an afterthought. Did you check the formula after changing the applied range? Excel sometimes changes the references as it thinks is appropriate, so please double check.

  16. Hi,

    I'm trying to highlight cells in one column where they differ from the corresponding cell in another column.

    So if E2C2, I want it highlighted, similarly if E3C3 etc. I've tried applying a rule to all of column E using the formula "=$E2$C2" (there are headers in row 1), but this produces random results. I've tried changing the fixed portion of the cell reference - E$2, $E$2, etc, but none of them works as expected.

    Can I do what I'm trying to do, or am I approaching it the wrong way?

    Thanks

    Nick

    1. Hi Nick,

      I believe you meant =$E2<>$C2 (formulas often get messed up after posting, sorry for that).

      This is a correct formula and I can think of just one reason for it not working - a rule is applied to a wrong range. Please open the Rule Manager and check if it applies to the range beginning with cell E2.

    2. The comments function has swallowed the symbols. Originally this said "if E2 not equals C2" using chevrons, etc.

  17. Hi,
    Is there any formula that will define the function of one cell depend upon the result of another cell. If the result of one cell is "Yes" then the formula in another cell will be executed accordingly, otherwise not.

    Can you help me?

    Thank you

    1. Hi Deepesh,

      You can wrap your formula in the IF function to have it executed only when another condition is met. For example:

      =IF(A1="yes", formula, "")

      A real formula may look similar to this:
      =IF(A1="yes", SUM(A2:A4), "")

      1. Thank you So much!!!

  18. I have been using a simple formula (=D3>C3) and its reverse (=D3<C3) to decide whether a cell is coloured red or green. The rule applies to all the cells in the column (=$D$3:$D$45). I have applied this rule to three columns in my spreadsheet, Columns C,D and E. The formula works fine in Columns C and D, but does not produce any formatting in E. In every way that I can assess, there is absolutely no difference in the cells in the E column than in the others. I have checked everything I can and I just cannot figure out why it will not work.
    Can you help me?

    1. Hi Rob,

      Just use absolute column references (with the $ sign) like this:
      =$D3>$C3 and =$D3<$C3

  19. Hi Svetlana,

    I am trying to do highlighted conditional formatting for certain cells. When each cell is equal to 9.5%, it will be highlighted green. I pressed selected each cell using ctrl and entered the conditional formatting desired. However, it is working for every cell but one (it is being highlighted red when the cell is equal to 9.5%). I doubled checked in manager and the rule is correct, so I'm not sure why this is happening!

    Any help you can provide would be much appreciated.

    Thank you!

    1. Hi Nicole,

      I would advise to double check 3 most obvious things:

      1. The rule applies to the correct range of cells (not including the column headers if any).

      2. If you created a rule with a formula, the formula is written for the left-most cell in the rage. For example, if you highlight rows 2 through 10, you should write the formula for row 2, e.g. $A2=9.5%

      3. Check the value in that stubborn cell. It may happen that the cell displays just 1 decimal place, while the real value in the cell is, for example, 9.51%. So, select that cell and view the actual value in the formula bar.

  20. Hi Svetlana,
    Very helpful! In your Formulas to compare values (numbers and text), I didn't see anything to indicate "between." I need conditional formatting in Col C to reference numbers in Col D. Cells in Col C should be light red if the numbers in Col D are between 51-79. How would I write that in a formula? =D1>=51...<=79

    1. Hi Fylum,

      Oops, that way my omission. You are right, the AND function works for Between rules, just added it to the corresponding section in the post, thanks!

    2. Think I got it:
      =AND(D1>=51,D1<=79)

  21. Hi
    I am trying to show icons for what I would think would be a simple task but I can't seem to make it work.
    In column "N" I have a current rate percentage. In column "O" I have a target rate percentage. I want to use the icons to show a green check if the target has been met, a yellow exclamation point if the target has not been met, and a red stop light if the current rate is 50% or more below the target rate. Basically just showing which rates have been met, are almost there, and are in danger of not being met.
    Can anyone help with this?
    Thanks,
    Heather

    1. Hi Heather,
      If you're content with just using color indicators instead of icons, the following should help:
      ***Please note, I'm using Office 2007, so this answer may vary depending upon which version you're using.***
      Assuming your data starts on row 2 (N2 & O2 in the example you describe; perhaps because you have a column header title), you should be able to get the appropriate colors to show by entering the following formulas into the Conditional Formatting -> "Use a formula to determine which cells to format" in this order:
      1) =$N2 Fill -> Background Color to RED)
      2) =$N2 Fill -> Background Color to YELLOW)
      3) =$O2 Fill -> Background Color to GREEN)
      Unfortunately, it'll take me a bit more time to figure out the solution using the icons you requested. I'm actually at work right now, was on this blog searching for an answer to a question of my own, ran across your question & figured I may be of service within a reasonable amount of time.

      I hope that helps.

      Best regards,

      Randy

      1. Hi again Heather,
        I see that the equations I typed got messed up (not sure why), so I'm going to attempt to type these again, this time with spaces between items. Please note that the other info remains the same.
        1) = $N2 < $O2 / 2
        2) = $N2 < $O2
        3) = $O2 <= $N2

        Best regards,

        Randy

        1. Thank you Randy! I will give that a try. My boss had her heart set on using the specific icons instead of colors, however I think I have convinced her that it just doesn't seem possible. :)
          Sorry for the delayed response, been in training.

          Heather

  22. I am wanting to conditionally format case sensitive values. For example, I want upper case "F" to be in green text and lower case "f" to be in blue text. I am using Excel 2010 and so far have not been able to google-cure my problem. Can you help?

    1. Hello Sarah,

      Please use the Exact function and create two rules using these formulas:
      Green: =EXACT(H8, "F")
      Blue: =EXACT(H8, "f")

      1. Alexander, thank you for the help. I discovered that my problem was not related to the formula (as I had tried the one you gave above) but that in place of the cell number I was putting in the full array of cells i wanted the formula applied to. I should have left it as exactly the formula you have above and then applied that formula to the array of cells, not actually include the array of cell names in the formula.... if you get what i mean.

        Thanks for your help!!!

  23. I have two columns, column A has various dates entered, column B has a formula =A1+30 meaning I want to see a date that is 30 days past the date in column A. I would like to add a conditional format to highlight the cells in column B when they have reached that 30 day mark. How do I do that?

    1. Hello Betty,

      Select column B and create a rule using the formula below to mark only today's date:
      =B1=today()

      If you need to mark today and past dates, then please use this formula:
      =B1<=today()

  24. Hi Svetlana,

    A very happy new year to you!!

    Please can you help on below query.

    Date From Organisation Details/Requirements Sent to Report Status
    10/02/2015 Pending
    14/02/2015

    A is a date column that records the email receive date and F shows the status pending.

    Now i want F to turn "pending" word in red if it crosses 5 days from date in A column. Please can you advise if i can conditional format it and how.

    Thanks,
    Rachana

    1. Hello Rachana,

      Please select your column F from F2 to the end and then create a conditional formatting rule using the following formula:
      =AND($F2="pending",$A2+5

  25. Hi Svetlana, Thanks for this article, it's very helpful. I just wanted to ask you how to highlight one empty cell in a column (say "B") when other cell(s) (one or more, say "A" & "C") in the same raw is filled with some values. And the highlight remains until the cell in "B" is filled with some values. Is that possible? your reply will be very helpful and is highly appreciated. Thanks in advance..

    1. Hello John,

      Select column B and create a rule using this formula:
      =AND($B2="",$C2<>"",$A2<>"")

      1. Hello Alexander,
        Thank you very much for the formula, this is the exact one which I've been looking for. It really works !!! Thank you once again for your time for helping me...

  26. Hi. This is a very helpful article. I am having difficulty solving this. I have a table where every cell is a formula. Some are straight references to cells, "=C2", and some are vlookups, "=VLOOKUP(C2,...)".

    I would like to highlight the cell only if the formula is a vlookup. Everything I see indicates that the conditions refer to the calculated values not the formulas themselves. I cannot use ISFORMULA() since all cells have formulas.

    I appreciate any help.

    1. Hello Curt,

      If you use Excel 2013, then you are lucky. To fulfill your task, just select the table and create a rule using this formula:
      =ISERROR(SEARCH("vlookup(",FORMULATEXT(A2)))=FALSE

      Where A2 is the top-left cell in the selection.

      If you have Excel 2010 or earlier, then you need to replace FORMULATEXT with VBA user-defined function (UDF)

      Function formulatext2(cellSrc As Range)
      formulatext2 = cellSrc.Formula
      End Function

      If you are not quite familiar with VBA, please see how to add a VBA macro (UDF) to your worksheet here:
      https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/

      Replace FORMULATEXT with FORMULATEXT2 correspondingly:
      =ISERROR(SEARCH("vlookup(",FORMULATEXT2(A2)))=FALSE

      1. I appreciate the quick and helpful reply. I do have Excel 2013 and your solution worked perfectly. Unfortunately, my client uses Excel 2010 and will not allow vba in the workbook I am creating. So I am back to square one. If you have other suggestions I would be anxious to try them.

        Thanks.

        1. I am sorry, but I don’t have any other suggestions.

  27. Hi, I've setup a training spreadsheet and added conditional formatting so that the cell turns red if the expiry date has passed (but only if the cell contains a value.

    =IF((AND(K11<TODAY(),K11"")),TRUE,FALSE)

    Trying to setup an additional rule that turns it yellow if that expiry date comes within 2 months of the current date but not sure on the details of the formula. i thought something like this maybe but its not working.

    =IF((AND(K11<TODAY+60(),K11"")),TRUE,FALSE)

    Any help would be greatly appreciated.
    Thanks

    1. Hi Steven,

      The formula is correct except that parentheses should be after TODAY(), not after 60. Please try this one:

      =IF(AND(K11<TODAY()+60, K11<>""),TRUE,FALSE)

  28. I am trying to figure how to format the first cell in each row so that it will change to a color when all the cells are populated within that row (columns A-R). Some of the cells have text, some have numbers, and some have both. I am wanting to do this so that I know which rows are complete, without having to scroll back and forth and visualizing each one.
    Each month the number of rows I have varies, so it may be 90 one month, it may be 130 another. The first row is a title/header row. The first column is populated with names. Any ideas?
    Thanks!

    1. Hello Christine,

      Select the column A (from A2 till the end) and create a conditional formatting rule using this formula:
      =COUNTBLANK($B2:$R2)=0

  29. This is a great list of conditional formatting uses! I have one I would like to see if you can tackle. I would like to highlight a range of values (item numbers) for a list of specific item numbers that qualify. The list may be small or it may be quite large. Would I need to do an OR function for each of the items in the list or is there a simpler way to get this done? Thanks!

    1. GREAT columb. More insightful than the videos. Thanks.

  30. conditional formatting for cell B1- if cell A1=Optimize and B1 is greater than 0, red (or whatever)

    A1 B1
    Optimize 1

    1. Use this Formula

      =AND($B1>0, $A1="Optimize")
      Select B1 Column.

  31. I would like to change my text value in column E and F based on the text entered into any cell in column F.

    For example: If I enter in any cell in column F "BBL" I would like the "BBL" in column F to be red as well as any text I enter in column E but only in that specific row. I would like this formatting to work for the entire spreadsheet.

    1. Hi there..
      Been stuck with my project already.
      I have a value in column A.
      And i need to apply a 3-color scale on column B depending on the value of column A.
      Condition:
      If column A=<1 - column B turns red
      if column A=the value of column A - column B turns dirty white

      i think this is simple but im getting invalid formula.
      why's that? this is what i've been doing

      on column B3, i enter this
      =$A$3=<1
      =$A$3=$A$3

  32. Hello,

    I am trying to run down a large list full of one's and zero's. If it is zero, then the corresponding row, 2 rows to the left, value I do not care about. If it is a one, I then want to essentially add it to a mixing pot with all of the other values that have 1 corresponding with them, then find the max value of all of them. HELP!?

    1. Hi Will,

      I understand that you have a list of 0s and 1s but I'm not sure I understand the task. Can you please try to describe it in the following way:

      -if cell A1 is 0, then do what?
      - if Cell A1 is 1, then do what?

  33. I would like to create a conditional formatting rule for the following:

    Cell C2: If cell C1=36 then the text in C2 would be 3x/8 weeks, 2x/6 weeks (36)

    Once that is completed, I'd like to create additional conditional formatting rules for the same cell.

    Is that possible?

    1. Hi Npratt,

      I'm not sure I exactly understand the condition. Assuming that you want to put the text 3x/8 weeks in cell C2 if C1 euqals 36, otherwise - 2x/6 weeks (36), here is the formula for cell C2:

      =IF(C1=36, "3x/8 weeks", "2x/6 weeks (36)")

  34. I am trying to highlight entire row based on a cell value

    Wxample: I want my formula to highlight entire row where cell value = MH000038, when I am giving the below formula excel is not considering the zeros it is removing

    Rule created = $C2=MH000038
    Excel converting it to =$C2=MH38 so no cells are highlighting

    any help will be appreciated.

    1. Hi Suma,

      Simply enclose the cell value in quotes so that Excel interprets it as a text string, like this:

      =$C2="MH000038"

  35. Hi, I am a bit desperate here... I have searched everywhere but I can't find a solution to my problem. I really hope you can help me! So here it is:

    My issue is with the conditional formatting using a formula. To summarize, I want G3 to be highlighted if F3 is more than or equal to 0 and also G3 is smaller than 90% of F3.

    This is the formula I have entered in the conditional formatting but it doesn't work:
    ="AND($F3>=0, $G3<$F3*0.9)"

    Thanks for your help in advance :)

    1. Nevermind! I Figured it out. It should have looked like this:
      =AND($F3>=0, $G3<($F3*0.9))

      I am such a noob :P

  36. Hi there,

    I'm trying to compare column A with a table, for example column B and if there is a match the match should be colored. Column B contains a output that had several decimals. The match does not need to be exact, ideally I would like to scan for a range of about +/-0.5 of column B. Does anyone have a suggestion how to approach this?

    ColumnA ColumnB ColumnC
    Output1 Output2 Addition to column B
    260,1 201,123 b2
    262,1 242,105 y4-NH3+2
    330,2 250,618 y4+2
    338,1 269,161 y2
    351,1 306,134 y5-H2O+2
    372,1 306,625 y5-NH3+2
    372,8 315,139 y5+2
    416,1 320,197 a3
    418,1 330,181 b3-H2O
    348,192 b3
    370,655 y6-H2O+2
    371,147 y6-NH3+2
    372,170 y3
    379,660 y6+2
    417,213 a4-NH3
    427,197 y7-H2O+2
    427,689 y7-NH3+2
    434,240 a4
    436,202 y7+2
    444,224 b4-H2O

    For example 330.2 matches 330,181 (with addition b3-H2O)and so they should be filled in with red. 372.1 matches with 372,170 and thus y3, the should be colored orange etc.

    Look forward to any help,

    Thanks, regards,

    G

    1. Oops the table didn't appear as it should be..

    2. Hmm the table didn't come out as it supposed to..

      Output 1 Output 2 Addition to column B
      260,1 201,123 b2
      262,1 242,105 y4-NH3+2
      330,2 250,618 y4+2
      338,1 269,161 y2
      351,1 306,134 y5-H2O+2
      372,1 306,625 y5-NH3+2
      372,8 315,139 y5+2
      416,1 320,197 a3
      418,1 330,181 b3-H2O
      348,192 b3
      370,655 y6-H2O+2
      371,147 y6-NH3+2
      372,170 y3
      379,660 y6+2
      417,213 a4-NH3
      427,197 y7-H2O+2
      427,689 y7-NH3+2
      434,240 a4
      436,202 y7+2
      444,224 b4-H2O

    3. Output 1 Output 2 Addition to column B
      260,1 201,123 b2
      262,1 242,105 y4-NH3+2
      330,2 250,618 y4+2
      338,1 269,161 y2
      351,1 306,134 y5-H2O+2
      372,1 306,625 y5-NH3+2
      372,8 315,139 y5+2
      416,1 320,197 a3
      418,1 330,181 b3-H2O
      348,192 b3
      370,655 y6-H2O+2
      371,147 y6-NH3+2
      372,170 y3
      379,660 y6+2
      417,213 a4-NH3
      427,197 y7-H2O+2
      427,689 y7-NH3+2
      434,240 a4
      436,202 y7+2
      444,224 b4-H2O

  37. Is it possible to give a colum like "stable" or "ascending" the directional icons?
    Like if stays "stable" then must be ↔ and for "ascending" ↑.
    I i want use now the directional icons, i must give a number. I want in text.

    1. I solved this problem with using VLookup

  38. Hi..
    I want to change a entire row color for a cell value of the row... If i have 'fail' in a certain cell (D1) then i want to highlight row 1. How can I do that?

    1. Hi Nazmul,

      Simply select all the rows you want to highlight and create a rule with this formula:
      =$D1="fail"

      1. Hi Svetlana,
        Thanks a lot..it works..

  39. Hi Svetlana,

    I am trying to do conditional formatting of email addresses to quickly identify addresses from gmail, hotmail, and yahoo. After a lot of looking online, I found a formula I could use:

    =NOT(ISERR(SEARCH(Sheet2!$A1:$A3,A1)))

    (Sheet 2 A1 - A3 are: A1= gmail.com, A2= hotmail.com, A3 = yahoo.com)

    However, when I put this in my conditional formatting - it only highlights those addresses with gmail in them and ignores the rest. I'm not sure what I am doing wrong. Any ideas?

    -Michael

    1. Hi Michael,

      The point is that you have an array formula and array formulas can't be used in conditional formatting rules. In your case, the formula turns into =NOT(ISERR(SEARCH(Sheet2!$A1,A1))) and highlights only gmail addresses.

      You can pick the domain name in a row and use the MATCH function to find matches in your lookup table using the following formula:
      =MATCH(IFERROR(RIGHT(A1,LEN(A1)-FIND("@",A1)),""),Sheet2!$A$1:$A$3,0)>=1

      1. You rock my world. Remind to me buy you a beer if we ever meet. :)

  40. Hi Svetlana, Great blog here!

    I think my problem is quite simple, and may have been covered in earlier examples but I am still unclear through trying!?

    I have a value of "Yes" or "No" in Column F, and want to colour this column/these cells based on a date in the same row, Column D:

    Amber: If 30 days overdue i.e. <NOW()-30
    Red: If 60 days overdue i.e. <NOW()-60

    Can you help??

    Many thanks!

    Ashley

    1. ...oops forgot to say, I wish to colour it when it says "Yes". "No" should be left unformatted

      1. Hi Ashley,

        You can combine these 2 conditions using the AND function, like this:

        Red: =AND(TODAY()-$D2>60, $F2="yes")

        Amber: =AND(TODAY()-$D2>30, $F2="yes")

        Please note that the Red rule shall come first in the list of rules.

        1. Great, that works a treat. Thank you for your timely help :)

  41. Sir,
    I am having spreadsheet containing D cell value K cell value , what is the formula for if K value is not equal to D cell value ?

    Please reply.

    K.R. Ranganath

    1. Hi Ranganath,

      Assuming that row 2 is your first row with date, here's the formula:

      =K2<>D2

  42. hi
    if i have two dates on one cell. i need to change the color if the send date added

    1. i mean 2nd date added

  43. HI,
    I want to color a cell based on another cell value.
    if cell D1 contains "false" I want to get red color in cell A1.
    How can i do that?

    Thanks
    Nazmul

    1. Hi Nazmul,

      Simply select entire rows and create a conditional formatting rule using the same formula.

    2. Hi Nazmul,

      If FALSE is entered in D1 as a logical value (Boolean), then you can create a rule for cell A1 (or multiple cells in column A) with the formula: =D1=false

      If you have "false" as a usual text, then enclose it in double quotes in the formula:
      =D1="false"

      1. Hi Svetlana,

        Thanks..it works.

        I have another problem. I want to change a row color for a cell value of those row..How can I do that?

        Nazmul

  44. Is it possible to include multiple conditions using both AND and OR operators in the formula for conditional formatting. Can you please provide a syntax for this.?
    Thank you in advance!

    1. Hello Ahamed,

      Of course, it is possible. One an AND function can be one of the conditions in the OR function and vice versa. For example:

      =AND(B1=2,OR(C1=3,C1=4))
      =OR(AND(B1=2,C1=3),C1=5)

  45. HI,

    I have a spreadsheet for cashflow that tracks down my everyday expenses. On another tab, i have a consolidated tracker that includes my businesses and investments. The problem is i keep on substituting a certain entry in the consolidated tracker because i dont know how to reflect the latest cash i have base on the cash flow tab as of the current date. Here is the example:
    Cell A Cell B

    20Nov14 $500
    21Nov14 $400
    22Nov14 $350
    23Nov14 $100

    cash as of today: ????

    I wanted excel to always pickup the cash based on the current date. Let's say today is 23Nov14.

    Thanks,
    Ben

    1. Hi Ben,

      Please clarify what exactly result you want to get: expenses for 23-Nov-14 ($100), a sum for the previous days in November or something else?

  46. Hi,

    Can you help me please?

    If cell A1 = 1, I want the maximum value in the range B1:B5 to be highlighted red. If cell A1 doesn't = 1 then no conditional formatting needs to take place.

    Any ideas?

    1. Hello Andy,

      Select the cells B1:B5 and create a rule using this formula:
      =AND($A$1=1,Max($B$1:$B$5)=B5)

    2. This worked:
      Select range B1:B5.
      Conditional Formatting > New Rule.
      >Format only cells that contain.
      Cell Value > equal to > =IF($A$1=1,MAX($B$1:$B$5))
      Format > Fill > Red.

  47. Hi, I want to apply conditional formatting for "not equal to" clause. The cell is getting highlighted if I choose from the provided options like equal, greater than but not getting highlighted if I manually enter a formula for not equal to. Im using this formula : =$D25490
    Thanks !

    1. $D25490

      1. Hi!

        This formula does not look correct (maybe was distorted when publishing). Anyway, if you can specify what exactly cell(s) you want to highlight and based on what exactly condition (not equal to a certain number or another cell?), I think I will be able to suggest the right formula.

        1. The not equal to operator did not get published! Anyways, I have a column named cost code under which I want to highlight all the values which are not equal to 5490.

  48. Hi Svetlana

    Great source of information. I came across it searching for a solution to a problem I have. In one column (say column 'A') I have a list of activities that need to be completed. In another column (say column 'B') I enter the percentage of the task in column 'A' that has been completed. I would like the first column to be filled with a data bar based on the percentage that has been entered in column 'B'. Probably dead easy but I can't figure it out. Be really great if you can help.

    Thanks
    Shaun

    1. Hi Shaun,

      Unfortunately, it is not possible to fulfill your task using Data bar, or at least we don't know how. Data bar works only with the information in the same cell.

      The only alternative we can suggest is to insert a helper column to the left or to the right of column A, enter the formula =$C2 there and copy it down to other cells. Then you can apply a conditional formatting rule to this column by selecting the Data Bar format style and turning on the "Show bar only" option.

  49. I am attempting to write a conditional format that allows me to see when the value in one cell is larger than another, then shade the larger cell darker as the difference becomes greater.

    A1 = 95
    A2 = 96

    In the above example, A2 is larger and would become filled with a color,the difference is 1. But if the difference were 5 (A2 = 100) then I would want cell A2 to fill a darker shade.

    I tried using standard deviation and color scales, but because there are only 2 number being compared, it does not seem to work the way I had hoped.

    Thank you for any advice you may have!

    Gary

    1. Hello Gary,

      I am sorry, I don't know a way to fulfill your task using Color Scales. As an alternative, you can try creating several rules based on formulas:

      =AND(A2-A1>=0, A2-A1<=2)
      =AND(A2-A1>=2, A2-A1<=5)
      =AND(A2-A1>=5, A2-A1<=10)

      1. Hi,

        I was having the same issue and I manage to solve it using Color Scales. :)
        Try the following:
        - Select the range (a1:a2 in your example) and enter in the Conditional formating > New Rule > Format all cells based on their values
        - Select 3-Color Scale
        - In the "Minimum", select Type "Formula" and write the following formula: "=MIN($A$1:$A$2;1)"
        - Let the Midpoint with the percentile
        - In the "Maximum", select Type "Formula" and write the following formula: "=MAX($A$1:$A$2;100)"

        The further the diference between the values of the cells, considering a potential maximum of 100 and minimum of 1, the darker de cell color.

        Hope this helps.

        Cheers.

  50. Drawing a blank.

    If I want to set a condition format with an icon set where I get a "check mark" for zero and and "x" for anything greater than zero.

    Seems very simple but I am not wrapping my head around it at the moment.

    1. Hi Ben,

      I have experimented a bit, and the following settings seem to work fine. Under Conditional formatting, click Icon Sets > More Rules and then set up:

      X when values is >o (type - number)
      "check mark" when <=o and >=0 (type - number)
      X when when <o (type - number) (X will also appear for values less than zero, if any; or you can choose any other icon for this).

      Hope this helps.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)