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 38. Total comments: 1727

  1. I have a Excel sheet where attendance of various students are marked.Now I would like to highlight the cells in which "present" occurs more than 7 times consecutively (i.e a student is present continuously for 7 days excluding sundays) for each student...?

  2. Hi, I am referencing cells in a data entry worksheet to create coordinating filled cells in a separate sheet. However, when I use Format Painter to copy the conditional formats I created into 6 additional cells, and then edit the cell references to the coordinating cells in the data entry sheet, it changes all the cell references in all the "painted" formats. Is there a way to turn this off, or do I just have to write each conditional format independently?

    Thanks for your help!

  3. Hi, I have a question.
    I have an excel spreadsheet that I am trying to compare pricing on.
    So I have all the information of the vendor, product description, etc. In column L I have the price that the vendor charged us for that product. Each product description has a different contract price. However, the vendor does not always charge us the correct contract price. Is there anyway to apply this type of conditional formatting to a spreadsheet like this? Below is close to what my columns show.

    Invoice # Number/SUPC Item Desc Purchase(cs) Vendor Price
    5011210 157048861 PIE 2 cases $49.38

    So the vendor charged us $49.38 per case, but we should have only been charged $49.11 per case.

    Does this make sense as to what I am asking? I am having to go through this manually over 10,000 line items. Every lime item could be different though. I know excel has the tools and technique to help me with this, I am just not sure what formula to use.

    Any help or suggestions would be greatly appreciated!

  4. HI,
    I just need to get an formula if week cover is higher than 3 how I can block the cell in CTN,so that others cant input anything in that cell.

    Dept Article UPC Description Pac WHS CTN IN CTN IN SINGLES STR STK Incl Tst Avg Sale Week Cover
    F02 20001578 62886 CRINKLE CUT CHIPS 12 600 50.00 0 31 31 4.15 7.46
    F02 20002307 343121 CHUNKY COD FISH FINGERS 12 2076 173.00 0 28 28 5.76 4.87
    F02 20002345 323796 STRAIGHT CUT CHIPS 12 1128 94.00 0 8 8 3.03 2.64

    1. kindly covert all this in excel and let me know the fromula

  5. I forgot to mention that the number 34 is the raw value I am using as a divisor. This value can change. For example it can be a 12 or 15 or whatever number.

  6. I have a question.

    I have this formula.
    (ROUND(MROUND((RAND()*(99-75)+75), (100/34)),0) which correctly returns a random value between 75 and 100. However, I want it to return a value that does not equal 100. I am a bit stuck on this one. So for example, it will return a whole number but it will round the value up to 100 on some trials. That is not good. How can I reconfigure this forumla to react properly? Thanks.

  7. 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, "")

  8. Dear Svetlana

    Looking at all of these posts you certainly do know your stuff!

    I need to use duplicate conditional formatting to identify duplicates within a single column, however, I want this to apply to certain cells within the column, so E1-E3, then to start again at E4-E11 etc. I have ~1500 rows of data. The range of cells I want it to perform this on is dependent upon values in another column, which always start at 1 and increase in ones i.e.1,2,3 etc. I want the conditional formatting to start again every time it comes across a 1 for the second time within the other column:

    Presentation Chosen box
    1 4
    2 10
    1 4
    2 6
    3 8

    Here, Excel should not highlight the two cells containing '4', as these are within separate ranges (range 1: 1,2; range 2: 1,2,3). I only want it to identify duplicates within the range of cells depending on the values in column 'presentation'.

    I am not sure if this can be done. I have searched for a few hours and read various posts but it is looking bleak.

    Many thanks in advance, Claire

  9. Hi... I have to perform conditional formatting on a particular column, which should display the numbers entered into it, in 3 decimal format.

    My Condition is such that, a cell say example : E5, has some product name "ABCD", based on this text, column of say for example 50cells, has to display the data entered into it in decimal format, for example : 5 decimal places....If the product name differs, the decimal format has to change as per the product.. Product is an electronic product, each product provides output in decimal format of different decimal places(5, 4, 3, 2, etc,.).

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

  11. Hi Svetlana,

    First thanks for clear and more useful data u explained me to learn from this site.
    Please clear this my problem if you can.

    Problem:
    In Jan'08 month I spend $2000 for on marketing and I earned $200 each month till jan'2010.

    And I continue spending more every month till 2015.

    Now I want to highlight the month where my spend reached break even or my budget.

    For example: my spend $2000 reached in Aug'09 month.

    Please let me know if you understand my problem.

    Thanks & Regards
    Satish.V

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

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

  14. Hi Svetlana,

    =MATCH(TRIM($Q2),A:A,0)
    why does this return the correct cell number where the match is at, as to this
    =MATCH(TRIM($Q2),A2:A350,0) or =MATCH(TRIM($Q4),$A$2:$A$350,0)

    when using either of the above it makes the cell value off by 1. i.e. the actual cell where value is at: 3 but returns: 2

    thanks so much

    great stuff!!

    BE

  15. I have a list of different items (food menu) to cook, and on monthly basis I want to select from the list. but if I entered a manual item (that is not listed in the list) it should be a different colored text. I cant get result. I don't know how it could be.
    Please help

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

    2. Nick,

      In theory, this should make no difference, unless the numbers are formatted as text. Anyway, if you can send me your sample sheet (support@ablebits.com), we will try to figure that thing out. I am curious to know why it does not work on your data :)

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

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

  19. Hi Svetlana, my job is to keep track of approaching oil changes needed. I have the (A)column with the Vehicle #s, the(B)column with the current mileage on the vehicles, and the (C)column with the mileage when the oil change is due. I've been trying to find a formula that I could use to automatically highlight the current mileage cells that are within 500 miles of the mileage when oil change is due (C).
    I would greatly appreciate your help.

    Thank you!

  20. I am trying to create a spreadsheet using multiple numbers as status and I want the entire row to turn the correct color for that status. Example 1 new job want the color to be white, 2 contacted customer want the color to be pink 3 ordered parts want the color to be yellow, etc. to the final 6 green ready to invoice. I will have multiple columns in the row but I want the color to change based on the final column where I update status.

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

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

  23. I need to compare 2 columns in 2 tabs in excel
    Tab 1 & Tab 2
    Column D has project#'s
    Column L has dates
    - both tabs have the same amount of columns, but count of rows will change

    Project#'s are not necessarily in order, but if they do match and the dates in column L match, it is ok.

    If the date in column L changes, i would like to see it highlighted in column L in Tab 1.

    Any help would be appreciated!!

  24. Here is my dilemma:
    I have 1 spreadsheet with 2 specific columns containing times in minutes (Column V and W). I want to generate a conditional format that will highlight the entire row if both column V and W are greater than 60 minutes. I was successful if V alone is >60 using the following formula:
    =INDIRECT("V"&ROW())>60

    I just can't figure out how to get it to do it for both V and W.

  25. Thanks For the tutorial..

  26. Hi, my data starts in cell A23. I will be adding numbers to column A, that will range from 1 to 300 or sometimes more. Once all of the numbers are entered, I highlight my work sheet starting with Row 23 and Sort by Smallest to Largest. I'm trying to do conditional Formatting (Excel 2007) so that I can immediately spot if I have omitted a number in the sequence. The formula I'm using in A23 is:

    $A24-1

    Then I choose a color to format the cell if said condition is true.

    It shows up in the Conditional Formatting editing bar as the following and is not working:

    ="$A24-1"

    I also have a second condition in Column A for Duplicate Values which is working fine:

    =$A$23:$A$526

    Could the two be different conditions be the problem?

    Thanks!!

  27. Hi, I have spreadsheet with several columns and currently 151 rows. New rows are added regularly. After new row is added, I want to highlight lowest eight (8) values from the last 20 rows in one particular column. So right now, I want to highlight 8 lowest values in range (L132:L151). I know how to use array formula to find those 8 lowest values, but cannot figure out how to highlight them...

  28. Greetings! I read the areticle and most of the questions and responses above and still haven't a clue how to manage this conditional formatting issue.

    Working with dates makes it a little more complex, yet I think this can be done:

    For a row of data, compare the date in column F to the date in column C and the date in column E. Highlight cell in column F if either or both dates in C and E are later than the date in column F. In other words, the dates in C and E must have occurred before the date in column F.

    Example:
    C = 3/8/15; E = 3/8/15; F = 3/9/15 == no highlight
    C = 3/9/15; E = 3/4/15; F = 3/8/15 == HIGHLIGHT
    C = 3/15/15; E = 3/15/15; F = 3/15/15 == no highlight
    C = 3/19/15; E = 3/20/15; F = 3/18/15 == HIGHLIGHT

    Please help!

  29. Hello Svetlana,

    I do not know if this has been asked.

    In my spreadsheet I want to highlight rows based on the content of colomn H which contains the open (red) or closed (green) condition.

    -Denise

  30. hi,
    my data contains lots of row & columns containing Numerical value data like:-
    A, B, C, D
    90, 80, 50, 40
    65, 45, 54, 55
    54, 85, 45, 26
    what i want is highlight the lowest value in each row in one attempt.
    I can highlight the lowest value in each row by applying Conditional formatting formula
    =A1=MIN($A1:$D1).by this formula i can highlight each row one by one but i need a formula by which i can highlight all the lowest values in each row in one attempt please help me on this its urgent

  31. Hi Svetlana,

    I am trying to implement conditional formatting wherein if a cell contains any of the special characters it should be highlighted..i'm using the "text that contains" option for it..its working fine for every character except *..when i use it..it selects the entire column..any suggestions ?..another thing i wanted to ask was is it possible to do this entire thing through a formula using OR operator rather than setting a condition for each and every special character ??

    Thanks,
    Nemish

  32. Hi,

    can you help please?

    In an excel sheet like this and I want to highlight the highest value in each group, what formula should I use?

    Value Group
    819.23 1
    814.08 1
    808.47 1
    809.33 1
    805.7 1
    799.23 2
    796.79 2
    796.73 2
    791.25 2
    791.97 2
    784.52 3
    783.13 3
    788.61 3
    793.16 3
    789.4 3
    777.52 4
    773.03 4
    769.87 4
    760.69 4
    756.2 4

    Thank you so much!
    Maggie

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

  34. Hello,

    Your blog is very informational. Excel is fascinating. Thank you for posting.

    Is there any way to link cells with conditional formatting without Excel displaying the 0.00 and coloring the cells red? Excel 2010

    I have a spreadsheet where I have different tabs for each locations and then the last tab is all locations combined. I entered data in the locations combined tab and used the following conditional formatting to mark everything smaller that 0.85 red, but no coloring for the empty cells: AND(D1"",D1<0.85)

    It works perfect. HOWEVER, when I link these cells to the ones in the other tabs, all conditionally formatted cells turn red, and show the value 0.00. When I check the formula used for conditional formatting it is the same as the above with the cell name corresponding to the new one.

    Thank you in advance for your help.

  35. My requirement is ,

    I am trying to create check list . In column level I have all the days from JAN to dec. In row level I have assets to check. I need to highlight cells whcih will be 30 days next to the last updated cell.

    eg : I have ABC asset check on 1 st of JAN and the cell coresponds to 30 of JAn should be highlighted , which shows service is pending for that asset.

  36. I am using Excel 2013
    When I copy values to cells that already have conditional formatting I find that sometimes the CONDITIONAL formatting is duplicated.

    Is there a way of copy / paste that stops CONDITIONAL duplications??
    Is there a way to remove CONDITIONAL format duplications

    Your help would be appreciated!!

  37. I am almost positive this cannot be done with Conditional Formatting, but I figured I would ask anyways.

    Is it possible to Temporarily for a selection of Cells on Sheet2 based on Cell selection on Sheet1? Example: If Cell A1 is selected on Sheet1, I want Cells $A$1:$B$10 to be filled with Yellow, Bold, Italic, and Size 14 Font.

    Thanks in advance.

  38. Hi Svetlana, I have created rules that are now working just fine in a table I created (Rules 1-4 below). However, I want to create another formula that will override the first formatting when create a new rule (#5) so that the row turns purple when a new value is the designated column is entered.

    Rules:
    1) When YES=1.0, row from colA to colK turns GREEN
    2) When NO=1.0, row from colA to colK turns RED
    3) When ABSTAIN=1.0, row from colA to colK turns LIGHT ORANGE
    4) When ABSENT=1.0, row from colA to colK turns LIGHT GRAY
    5) When NP=X, row from colA to colK turns PURPLE

    Please help!

  39. any formula which help me if i change value of 1st column and then change in amount in other column .....

  40. Pretty great post. I simply stumbled upon your weblog and wished to say that I have really enjoyed browsing your blog posts. In any case I'll be subscribing for your rss feed and I hope you write once more soon!

  41. Helpful information. Lucky me I discovered your website accidentally, and I'm stunned why this twist of fate did not came about earlier! I bookmarked it.

  42. Hi,

    Good day!

    Need help here please. I have data that needs conditional formatting as follows:

    If Person 1 achieves 1 to 5 points it will show result "POOR"
    But if the Person 1 achieves 6 to 10 points it will show result "GOOD"
    But if the Person 1 achieves 11 to 15 points it will show result "VERY GOOD"
    But if the Person 1 achieves 16 to 20 points it will show result "EXCELLENT"

    Can anyone help me to have the right formula for this? Thanks in Advance guys!

  43. A B C D E F
    start end est. work time|work days|days complete|days remaining
    2/4/2015 3/18/2015 30 30 10 15
    3/5/2015 3/19/2015 10 10 16
    2/23/2015 4/3/2015 29 29 27

    I NEED COLUMN F TO AUTOMATICALLY CHANGE TO RED OR GREEN. I NEED IT BASED OF COLUMN C.
    if F is more than C then I need it to turn green, and if F is less then C I need it turned red......
    Please help

    1. I have the same issue. I noticed all her examples are based on ROWS but i need columns.... I need my Column L to be wither RED OR GREEN according to cell Q1 which is a formula for TODAY "=TODAY()" I tried everything it seems to only apply ONE rule but it does not want to apply multiple. have you figured this out?

  44. Thank you, this tutorial was very helpful to me!

  45. I'm trying to apply a conditional format to a cell based on the value of another cell. Here's what I'm trying to do:

    if the value in Cell G10 is 0 (based on this formula (f11-g9), then cellH11 will be green. HOWEVER, because cell G10 contains a formula, I can't make it work. If G10 is hardcoded with "0", then it will work. Any ideas?

  46. Hello,

    I am trying to use the conditional formatting tool. I have 2 columns quantity ordered, quantity shipped, and the percent difference. I would like the percent difference to be colored red if it is +/-10% and yellow if it is +/-5%. Can you please help?

  47. If some one wants expert view concerning blogging and site-building then i suggest him/her to go to see this blog, Keep up the good work.

  48. I have been exploring for a little for any high quality articles or blog posts on this kind of space . Exploring in Yahoo I at last stumbled upon this web site. Studying this information So i'm happy to convey that I've a very good uncanny feeling I came upon just what I needed. I such a lot for sure will make sure to don?t fail to remember this website and provides it a glance regularly.

  49. I wander if it is possible to conditionaly format the cells in a table where in a top row is writen which cell in a coresponding column (below) will be highlighted (the values in that column are not in direct relation with values in a heading row.
    Here is fragment of such table, where i want highlight 1st, 8th and 9th value (which coresponds to the first column (bins) - althogh that column is redundant)

    1 8 9
    bins A B C
    1 70 0 0
    2 53 16 0
    3 36 20 1
    4 32 20 6
    5 28 24 7
    6 25 27 10
    7 16 26 18
    8 17 25 21
    9 9 25 14
    10 7 16 20

    thanks a lot for your help.

  50. I was just wondering if it is possible to format a cell (or a whole row in a table ideally) based on an other cell's color? For instance I have a budget sheet with two tables. In my table where I add expenses, I created a Macro to add a new row, and pick a random color for that row. In that row, I have a cell indicating the day of the month that expense is due. I would like it if in my other table, the cell (or row) that corresponds to that date can automatically use the same background color excel formulas: https://www.youtube.com/watch?v=ayVH-Y9-0M4.

    I hope I am not being too confusing haha. If so, I would just like to know if I could say: If this cell is that color, then this cell is also that color.

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