Excel Conditional Formatting Based on Another Cell

When you want to format a cell based on the value of a different cell, for example to format a report row based on a single column’s value, you can use the conditional formatting feature to create a formatting formula. This post explores the details of formatting a cell or range based on the value in another cell.

Objective

Here’s an example that will allow us to put this feature into context. Let’s say that you have an invoice listing and your objective is to identify the open invoices. Here is a screenshot of our sample invoice listing:

20131212e

Since this is Excel, there are many ways to accomplish any given task. One way to identify the open invoices is to simply sort the list by the Status column so that the open invoices appear in a group. Another way is to filter the listing to show only the open invoices. These techniques are fairly straightforward, so, let’s explore another method. We’ll highlight the transaction rows with cell formatting…or, more precisely, a conditional formatting formula.

Video

Conditional Formatting Narrative

Using conditional formatting, it would be pretty easy to highlight just the Status column. It would be simple because the cells we are formatting are the same cells that have the values to evaluate. That is, we would be formatting a cell based on the value within that cell. To perform this, we could simply highlight the Status column, and the use the following Ribbon command:

  • Home > Conditional Formatting > Cell Rules > Equal To

In the Equal To dialog box, we could enter the word “Open” and pick the desired formatting and click OK. Excel would then apply the formatting to the cells within the Status column that are equal to Open. While this technique is easy, it does not meet our goal which is to highlight the entire transaction row, not just the Status column.

To highlight the entire transaction row requires us to format a cell based on the value in another cell. That is, we want to format the TID, Date, Status, CustID, and Amount columns based on the value in the Status column. Considering a single cell for a moment, we want to format B7 based on the value in D7. This means that we want to format a cell, B7, based on the value in a different cell, D7. Expanding this to the entire row, we want to format B7:F7 based on the value in D7.

Excel makes it easy for users to format a cell based on the value of that cell, and the built-in conditional formatting rules use this logic. When we want to format a cell based on the value in a different cell, we’ll need to use a formula to define the conditional formatting rule. Fortunately, it is not very difficult to set up such a formatting formula.

Let’s highlight the entire transaction listing (B7:F36) first, and then open the conditional formatting dialog box using the following Ribbon icon:

  • Home > Conditional Formatting > New Rule
20131212b

The New Formatting Rule dialog box has many choices, allowing you to, for example, format a cell based on the value, if it contains a value, the top or bottom ranked values, values that are above or below average, and unique or duplicate values. At the bottom of the list we find the option we need. We want to use a formula to determine which cells to format.

The formatting formula needs to be set up so that it returns a true or false value. If the formula returns true, then the desired formatting is applied. If the formula returns false, the formatting is not applied.

The key thing to understand about writing the formula is that the active cell is the reference point for the formula. Since this concept is absolutely critical, I don’t want to just skip through it, I want to unpack it for a moment.

You want to write the formatting formula as if you are writing it into the active cell and use the appropriate cell references and reference styles, such as absolute, relative, and mixed. If you can visualize the idea that you are writing the formula into the active cell, and the formula will be filled through the selected range, then writing the formula becomes easier. The formula you write will not be used to compute the cell value, rather, it will be used only for formatting.

Let’s assume that when we selected the entire transaction range, B7:F36 that the active cell is B7. When you select a range, there is still a single active cell. Check out the screenshot below to see that the range is selected, yet, B7 remains the active cell:

20131212d

With this idea in mind, it is easy to write the conditional formatting formula now. We need to write the following formula in the New Formatting Rule dialog, as if we were writing it into cell B7:

=$D7="Open"

This simple comparison formula returns true when D7 is equal to Open, and thus, the desired format will be applied. Let’s take a quick look at the cell reference for a moment. We used a mixed cell reference, $D7, where the column part (D) is absolute and the row part (7) is relative. Here’s why. Remember we want to pretend that we are writing the formula into the active cell, in this case, B7. If we were only formatting B7, then, we could have used a relative reference D7, or an absolute reference $D$7, or a mixed reference. The reference style wouldn’t matter because the formula was used in a single cell only and was not filled anywhere.

However, the moment that we fill a formula down or to the right, we need to be careful to use the proper cell reference styles. In our case, as the formatting formula is filled right, we don’t want the column reference D to change. That is, for all cells, we want to reference the Status column, column D. To prevent Excel from changing the column reference as the formula is filed to the right, we lock it down with the dollar sign, resulting in $D. As the formatting formula is filled down throughout the selected range, we want to ensure that the row reference is updated accordingly. When formatting B7, we want to look at the Status column within the same row, or D7. However, when the formula is filled down to row 8, we want to format B8 based on the value in D8. Since we want Excel to update the row reference, we’ll leave it relative and not use the dollar sign to lock it down.

Here is a screenshot of the formatting dialog box with the formula:

20131212f

Once the formula is entered, you simply use the Format button to specify the desired format.

Once the conditional formatting is applied to the range, the resulting worksheet is shown below:

20131212g

Bam…we got it!


FREE: Excel Speed Challenge

If you enjoyed this post, please check out our free Excel speed challenge.

Watch one short Excel video a day for 5 days. Total video time is only 45 minutes. Learn the Excel skills that can help you save an hour a week.


Other Considerations

In the formula above, we hard-coded the value, “Open” however, we could have easily placed this value into a cell, and then referenced the cell either by name or A1-style notation. In addition, besides just determining if the cell value is equal to a value, other comparison operators are supported, for example, greater than (>) and less than (<). We could have highlighted all rows where the value is greater than $5,000 by using the following comparison formula:

=$F7>5000

Since the formatting is applied based on a formula, we can get very creative and use worksheet functions. We could format alternating worksheet rows by using the MOD and ROW functions. Or, we could highlight old transactions by computing the difference between the transaction date and today’s date with the TODAY function. Indeed, this formatting formula provides many fun options.

Multiple Conditions

We can also use the logical AND and OR functions in case we want to consider multiple conditions. For example, formatting those rows where the status is open and the amount is greater than 5000 by using the following formula:

=AND($D7="Open",$F7>5000)

Since the AND function returns true when all of its arguments are true, the formatting is applied only when the status is open and the amount is greater than 5,000.

If we wanted to format the row if either condition was met, then we’d want to use the OR function instead of the AND function since it returns true if any single argument is true.

Conclusion

Having the ability to format a cell based on the value of another cell is quite handy. The key is to imagine that you are writing a formula into the active cell. The formula needs to use the appropriate cell reference styles (absolute, relative, mixed) so that as the formatting formula is filled throughout the selected range, the proper cells are considered. The sample file below has the conditional formatting rules included, so feel free to check it out.

Hope this helps, and remember, Excel rules!

Sample File

Here is the sample file in case you’d like to open it up and reference it.

FormattingFormula

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

180 Comments

  1. Alison on April 22, 2014 at 4:05 am

    This is fabulous; thank you! Completely clear, worked first time and did what I expected – and I even understand how to apply this to other, similar formatting issues.

  2. VAKKALANKA SRIDHAR on July 21, 2014 at 5:00 am

    “YES” It is very useful

    Thanks

  3. kelly standridge on August 12, 2014 at 6:46 am

    Thank you! I have a long spreadsheet where I track production percentages by date, but sometimes it is hard staying on the correct row. I enter the data for the previous workday, so I highlighted that row using the formula =$A4=Workday(Today(),-1). When I open my sheet every day, the correct row is highlighted!

    • jefflenning on August 14, 2014 at 7:01 am

      Kelly,

      Thanks for sharing your application of conditional formatting…that is very cool and creative…I love it!

      Thanks,
      Jeff

  4. Sonja on October 24, 2014 at 10:46 am

    Thanks, Jeff. This is great. I have the perfect spreadsheet to experiment with this. Great tip!

    • jefflenning on October 24, 2014 at 11:01 am

      Welcome 🙂

  5. Gerry S on December 5, 2014 at 8:31 am

    Hi Jeff, great article. Is there any reason that the row would not update after changing the value? I change the value and I need to highlight the cells for it to show. I have one formula to set the color and another to clear the color . Your sample works fine for me but not in my spreadsheet.
    Thx

    • jefflenning on December 22, 2014 at 4:08 pm

      Gerry,

      Thanks! If the cell value changes, and the conditional formatting doesn’t update as expected, I would check the following items. First, I would remove the conditional formatting rule that clears the color (as it is not needed it may be confusing Excel). Next, I would double check that the conditional formatting rule uses the proper cell reference style, for example, $D7…that is…absolute column reference with a relative row reference. If it uses $D$7 or D$7 you may not be getting the desired result. Last, I would confirm that you initially set up the conditional formatting rule by using the row reference for the active cell.

      Hope these ideas help!

      Thanks
      Jeff

  6. HILTON JOHN on February 10, 2015 at 11:22 pm

    Hi Jeff Lenning, Thanks for this article. I just need to know how to get highlighted 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..

    • jefflenning on February 11, 2015 at 3:56 pm

      Sure, you can accomplish that by using the AND function in the conditional formatting rule. For example, to format B1 when B1 is empty, A1 is not empty, and C1 is not empty, use the following:
      =AND($B1=””,$A1<>“”,$C1<>“”)
      This AND function returns true when all three arguments are true, and triggers the formatting rule.
      You can easily adapt this basic formula as needed, for example, to add other cells in the analysis.
      Hope this helps!
      Thanks
      Jeff

  7. Austin Rentsch on March 4, 2015 at 9:51 am

    =”TOTAL “&’1_Category Trends’!B8&” “&TEXT(‘1_Category Trends’!C8,”##.#%”)&” VS LY”

    Above is my formula to concatenate a series of information to arrive at something that reads like this:

    Total Beef -2.9% vs LY

    How can I get conditional formatting to make this red if it’s less than 0?

    • jefflenning on March 5, 2015 at 9:44 am

      You would set up the conditional formatting rule based on the value in C8 rather than the concatenated text string. For example, =C8<0.

      Thanks
      Jeff

  8. Melanie on March 31, 2015 at 6:19 pm

    Thanks so much for this article. It’s one of the best I have read on this subject and, amazingly, it’s the only one that explains cell references correct. Most blogs (even Microsoft’s own one) state that cell references are relative to the top-left most cell in the Applies to range . You wrote “that the active cell is the reference point for the formula” – I tested this and you’re spot on! If I start selecting cells from the bottom (OK, most people wouldn’t do that, but still) and follow MS rule I end up with incorrect results. Starting my formula from the active cell I’m fine though. Someone should perhaps tell Microsoft, unless I missed something? Anyway, this article deserves much more attention. Well done!! 🙂

    • jefflenning on April 2, 2015 at 8:21 pm

      Thanks!! Glad it was helpful 🙂

  9. Uche Ofia on April 17, 2015 at 2:59 pm

    Wow! This was very helpful to me. Worked like magic. Thanks.

  10. Nick on April 20, 2015 at 8:04 am

    Jeff,
    Can I conditionally format a cell based on the value of a cell in another tab in the workbook? No problem getting the formatting to work within the same tab.

    • jefflenning on April 20, 2015 at 11:54 am

      Nick,

      Yep…you can conditionally format a cell based on the value of a cell in a different worksheet by referencing the sheet name in the formula. For example, instead of A1 you would use Sheet1!A1. The sheet name must be enclosed in single quotes if it contains a space, for example ‘Sheet 1’!A1.

      Hope this helps!

      Thanks
      Jeff

  11. Laszlo Honig on May 4, 2015 at 12:44 am

    Hi Jeff,

    great post with fine explanation, thank you!
    I am struggling with a bug-like feature of MS Excel when using CF.
    When I copy/paste a value from a cell to another, it drags the CF of the copied cell and applies it in paste destination unexpectedly.
    I have found two ways to work this out: one is using indirect cell reference as rule description, another is a VBA Worksheet_Change.
    Let’s see the first one.
    As indirect cell reference, I entered the formula in Conditional Formatting Rule as follows: ‘=INDIRECT(Sheet3!$D$2)’, where D2 on Sheet3 contains a reference like ‘Sheet3!$C$2’. C2 is the cell in which the actual formula is stored.
    There is only one single problem: nothing happens when I apply this method as CF.
    When I enter the formula directly as rule in CF, it runs perfectly, giving me the desired results always.
    Could you please kindly give me a hint where the error is, and how to proceed to fix it?

    Thank you in advance, Laszlo

  12. Laszlo Honig on May 4, 2015 at 12:50 am

    Additionally to above: I have either tried formula ‘=cell(“contents”,Sheet3!C2)’ that also refers to C2 on Sheet3 but this one is dead too.

  13. lazov on May 7, 2015 at 4:46 am

    Hi Jeff, have you had the chance to have a look at what I posted (still cannot see displayed anyway)?

    Thank you, Laszlo

  14. Kristyn on July 7, 2015 at 8:38 pm

    Hi Jeff, Great info. I’m looking for something pretty specific. I have cells in column A and B that may be shaded either red/amber/green. I would like cells in column Q to highlight red IF two of the cells in column A and B are both red, side by side. Is there a way to do that?

    Thanks,
    K

    • jefflenning on July 13, 2015 at 7:04 pm

      Kristyn,

      Rather than do the conditional format rule based on the formatting (red), you may want to consider setting up the conditional formatting formula to format the cells based on the underlying logic that shades the cells red. For example, if the cells are red when they are less than zero, rather than set up the conditional formatting rule based on the cell format (red), set it up based on the formatting logic (less than zero). In the case when the logic is not uniform, but rather applied manually or arbitrarily, you may be able to use the CELL function or a macro.

      Hope it helps!

      Thanks,
      Jeff

      Thanks,
      Jeff

  15. Lisa Granata on July 10, 2015 at 1:09 pm

    THANK YOU SO MUCH for the Conditional Format based on another cell!!!!!

    You wrote it so it’s very easily understood. Really appreciate the sample file, too.

    I have been thinking about taking some advanced classes and will Definitely check out your online option!
    I just noticed, your work is marketed as being for CPAs and Accounting Professionals… I’m an HR Analyst, would it not be suited for me?

    -Lisa
    Los Angeles

    • jefflenning on July 12, 2015 at 4:17 pm

      You are welcome, I’m glad it helped! Regarding the courses, I believe the Excel topics covered have broad applicability beyond accountants, but the course examples are designed to resonate with accountants. Check out the course descriptions for the exact items covered, and also the free evaluation course to get a feel for how they are set up.
      Thanks
      Jeff

  16. Sudarshan on August 18, 2015 at 3:00 am

    Hi Jeff,

    It’s really helpful topic thanks for sharing. But i have one question regarding this, I have placed some condition on your excel sheet but i am unable to get the desired result. The condition is:

    Date should be >= 03/15/2015 and Amount should be >=5000

    Formula which i have written is =AND($B2>=03/15/2015,$E2>=5000)

    Thanks in advance
    Sudarshan.

    • jefflenning on September 3, 2015 at 9:18 am

      Sudarshan,

      Based on your formula above, my best guess is that Excel is not understanding the date…so…I would recommend either storing the date in a cell or using a date function. For example, if you stored the date 3/15/2015 in cell A1, then, you would update your formula to this:
      =AND($B2>=A1, $E2>=5000)
      This will help Excel understand the date.
      Alternatively, you could use a date function to generate the desired date, like DATE or DATEVALUE. Something like this:
      =AND($B2>=DATE(2015,3,15), $E2>=5000)

      Hope these ideas help!

      Thanks
      Jeff

  17. Jeff on September 14, 2015 at 8:27 am

    Thanks for the info Jeff, it’s helped tremendously!! I’ve been able to create what I wanted but now I can’t get it to copy:

    I’ve created the conditional format:
    Cell Value $G$2 | (format red fill) | =$H$2

    So if the values in H2 and G2 are not the same H2 has a red fill. What I want to do now is do the same for the rest of my cells from H3:G3 on down the column. The only way I’ve been able to do this so far is to create a rule for each and every cell which gets quite tedious as some of my lists are 50+ items long. Is there a quicker way to just copy that conditional formatting where the cells (within the formula) automatically change with each row?

    • jefflenning on September 17, 2015 at 10:24 am

      Jeff,
      The key to making this work is using relative references. For example, instead of $G$2 and $H$2 you would want to use G2 and H2.
      This should enable you to copy the conditional formatting down…hope it helps!
      Thanks
      Jeff

      • Dries on May 21, 2022 at 10:40 am

        Hi Jeff,

        I realise this thread is from a while ago, but I hope this message finds you.

        I am trying to do something similar as above, let me share my formula:
        Cell Range: A2:L2
        Formula: =$AD$2=”YES”

        So if cell AD2 has the word “YES” in it, cells A2 to L2 will colour green. I would like to copy this formula to the rest of my spread sheet going to A500:L500 / $AD$500=”YES”. Is there a quick way to do this? I am using the online version of Excel which should have all the same features.

        For some reason the above solution doesn’t do anything for me.

        Hoping you can help,

        Dries

  18. Alan on March 8, 2016 at 9:29 am

    I’m looking for the formula to set a conditional format for a “range” in another cell:
    condition for cell A, (background fill) for a range of between 1 to 5 in cell B.

    Any ideas? Thanks

  19. Nduka on March 17, 2016 at 2:09 am

    Hi,
    How do you tell there was a First Expired First Out (FEFO) breach?.
    I have this very large data and am trying to determine if a product with a later (greater) BB date is posted before one with an earlier (less) BB date on excel. The BB dates are in a column while the posting date is in another column
    I have been doing this manually and will like to have a formula that is faster.
    I’ll appreciate your assistance

    • jefflenning on March 17, 2016 at 6:09 am

      You could use COUNTIFS to accomplish that. Basically, counting the number of rows above that meet the criteria (a) has the same product id as the current row and (b) has a date that is greater than the current row. If you set up the range references properly then you can fill the formula down. Assuming your product ids are in A, your dates are in B, and the first data row is 2, something like this should work:
      =COUNTIFS(A$2:A2,A2,B$2:B2,”>”&B2)
      You could then use conditional formatting based on the new formula column if you wanted to highlight any that are 1 or more.
      Hope it helps!
      Thanks
      Jeff

  20. Rishikesh Joshi on April 7, 2016 at 12:14 am

    Thanks a lot for this article. It was very useful. So well articulated. Thanks once again.

  21. Akash on April 18, 2016 at 2:30 am

    Thanks this helped my manager.

  22. ahmad on April 26, 2016 at 8:05 am

    Hello,

    Need help on below. If Column A = 0 & Column B <150 highlight the complete row in red. how can i achieve this?

    A B C
    Y 115 OK
    Y 87 OK
    0 115 OK
    0 0 OK
    0 115 OK
    0 0 OK
    0 0 OK
    Y 110 OK
    Y 110 OK

    • Kurt LeBlanc on July 11, 2016 at 6:36 am

      Hey Ahmad!

      You can highlight thee table and apply a conditional formatting rule. Use a formula to determine which cells to format:
      Conditional Formatting>New Rule>Use a formula…
      Write the formula, locking in the columns since the rule will be applied to every cell and format like you want:
      =AND($A1=0,$B1<150)

      I hope this helps:)
      Kurt LeBlanc

  23. mexmike on May 26, 2016 at 5:07 pm

    Finally I can format the text colour/bold etc in one column based on values contained in two or more other columns. Using AND and OR really does the trick. Thanks so much.

    • jefflenning on May 26, 2016 at 6:03 pm

      Welcome 🙂

  24. Pat-t on June 9, 2016 at 3:40 pm

    Hi Jeff,

    do you know a formula or way to get the cell’s format to changed, based on text from a different cell? For example, if cell A1 says “$ AMT”, then I want cell B1 to format in currency. However, if cell A1 reads % PCT, then I want cell b1 to format in percentage. Any help would be much appreciated!

    • Kurt LeBlanc on July 1, 2016 at 8:11 am

      Hi Pat-t

      Yes you can format cells with a number type:) You will just need to do a new rule for each type.

      When you click to format a cell based on a formula, click “Format” and the number tab is before the default font tab.

      Hope this helps!
      Kurt LeBlanc

      • danny on April 10, 2017 at 12:54 am

        I have such a spreadsheet and the conditional format is working great in my tests though i am still building it.
        Consider the following example please.
        If A1 = “xxxx” Then format is $00.00 into cell B2
        If A1 = “yyyy” Then format is 00000 into cell B2
        If A1 = “zzzz” Then format is 00-00 into cell B2

        This sort of formatting in my sheet works if i manually put in the number or string.

        However if I copy a number into A1 with a previous format like 0-000 then the format does not work.

        What can i do to strip off the “-” in 0-000
        then apply the format of that cell to be $ or 0000 or 00-00 ?

        Appreciate all of you and the author to bring this up.
        danny

  25. Adam on June 22, 2016 at 9:53 am

    If I have a table of percentages and I want them to be formatted with a two color scale based on the rank of their volume compared to the others (numbers in an identical size table down below) how would I do that?

    • Kurt LeBlanc on July 8, 2016 at 8:18 am

      Hey Adam,

      Excel doesn’t seem to apply the rule to both tables in conjunction. Can you connect the tables?

      Hope this helps!
      Kurt LeBlanc

  26. Laura Laundre on June 22, 2016 at 5:47 pm

    Nice! This will come in handy. Thanks, Jeff!

    • jefflenning on June 22, 2016 at 5:51 pm

      Welcome 🙂

  27. nasim on June 30, 2016 at 1:58 am

    i have the following datas
    column1 column2
    row1 a b
    row2 d b
    row3 a g
    row4 d b
    row5 d c
    row6 n j
    i want to find “b” in column 2 and then find the amount in front of “b” in column1 as you see the amounts here are “a” and “d” then after finding “a” and “d” turn the colors of all the row that contain these two amounts for example to red even though the amount in front of “a” and “d” are not equal to “c”

    • Kurt LeBlanc on June 30, 2016 at 10:22 am

      I believe what you want is the OR() with AND(). I don’t understand the last part about not equaling “c” though if you can clarify that. The following addresses the highlighting rule based on the other requirements:

      You would select the entire data set. Then

      Conditional Formatting>New Rule
      >Highlight based on a formula and use:

      =AND($B2=”B”,OR($A2=”A”,$A2=”B”))

      then select your desired format.

  28. Brad Baker on July 7, 2016 at 5:05 am

    Thank you! Had so much trouble getting my formula to work, and I was missing one key piece of information – that the active cell mattered. I thought I could just type in the formula and correct ranges, with correct absolutes, etc and it would work (It does work like this is google sheets, and defaults to the top-left cell of the range as what excel apparently considers the active cell).

    Anyway, thanks for highlighting that point – my formatting is working now!

    • jefflenning on July 7, 2016 at 5:40 am

      Welcome 🙂

  29. Rachel Hammond on August 19, 2016 at 2:55 am

    Hi, Im having trouble conditionally formatting cells based on two criteria, one is on the cell contents being 0 and the other on a formula =J11=”100″ I think it’s because both results could be true. Can you please advise if this is possible.

  30. Ashley on August 24, 2016 at 2:34 pm

    I have 2 columns of dates and I want to highlight cells in the second column of dates that are 3 days past (out of window) of the dates in the first column. What conditional formula can I use. I found the greater than function but how do I do greater than plus 3?

    • Kurt LeBlanc on August 25, 2016 at 6:12 am

      Hey Ashley,

      To handle your issue, highlight the entire second column and apply a new rule. the new rule should be a formula that compares the second column’s date to 3 days past the first column’s date: =second date>first date+3

      Excel puts absolute referencing by default so make sure you notice that, and format it like you want! That should work:)

      Let me know if it doesn’t and you need more help,
      Kurt LeBlanc

  31. Linda Fauteux on August 31, 2016 at 6:12 pm

    Hi Kurt,
    I have an ‘initial and recurrent training’ table of all employees for a specific course.
    Column A has employees names.
    Column D has dates when recurrent training is due.
    When training is due 60 days prior to today the cell turns ‘yellow’, 30 days turns ‘orange’ and if = to or bigger then today it turns ‘red’.
    So far this works perfectly.
    Now, i need to be able to re-enter an employees name below the list once employee receives the due training and need to maintain this table to provide evidence that employee received training every 2 years.
    The problem i have is that i need a formula that will remove the above conditional formating if the new entry of the employee name matches a name above and that the new date in D is bigger then the date above when matched.
    Is that possible?

    • Kurt LeBlanc on September 6, 2016 at 8:31 am

      Hi Linda

      I’m sorry, but I don’t see a way to turn off conditional formatting, even with a macro…Macros can change traditional formatting if that helps.

      Kurt LeBlanc

  32. Linda Fauteux on September 1, 2016 at 6:15 am

    Would it be simplier to add a column with a check box that will be checked on the initial row next to the due date. When checked, it would remove the conditional formating In the adjacent cell. How di i do that??

  33. Peter Scobbie on September 2, 2016 at 2:55 am

    Many thanks for a good explanation into the conditional formatting functionality – helped me get my head around it for the first time

  34. Linda Fauteux on September 2, 2016 at 7:12 am

    Ok here’s a better explanation.
    Column A has ‘Employee Name’
    Column H has ‘Certification Date’
    Column I has ‘Next Due Date’
    ‘Next Due Date’ cells have this formula:
    =If(H2=””,””,Edate([@CertificationDate],36))
    Reasonning: When certification date is entered, it adds the next due date which is every 3 years.
    Also, the same cell is conditionnally formatted so that it changes color when:
    60 days prior to turns ‘Yellow’
    30 days prior to turns ‘Orange’
    Equal to or past due date turns ‘Red’

    I want the column I cells to change when I:
    a) Re-enter an employee’s name, the previous entry (of that same employee) next due date weather colored ir not to turn ‘Pink’. This way I know his in process of receiving the training and /or registered. Then,
    b) That same previous entry color coded ‘Pink’ needs to be cleared/white when I enter on the new entry row his new certification date.
    Oufff hope this clarifies it. Lol
    Thanks for all your help!!

  35. Sk Raquib on September 4, 2016 at 10:26 pm

    Hi..I am Sk Raquib..i have a problem in excel..the question is :

    District Product Qty sold/kg

    Howrah Rice 150
    Hooghly Wheat 400
    Burdwan Sugar 350
    Howrah Rice 300

    Que:- Highlight the District where maximum quantity supplied of Rice Supplied.

    I always put this formula “=IF($A2=”a”,IF($B2=MAX($B$2:$B$7),”true”,”false”))” and no results came out against Rice.

  36. Eric Jones on September 14, 2016 at 5:00 pm

    Thanks for your clear explanation of an issue that I’ve been having. I’ve applied the conditional formatting as you’ve shown to a cell so that if cell E6 has an S in it, G6 highlights as yellow. This works. My only problem is that I have to either scroll down and then scroll back up for the formatting to take effect. Also, If I do the same thing for a different row, it removes the formatting until I scroll down, then back up. Any idea why this is happening? It is a large template with many sheets, but nothing else has any delay. I’m stumped. I would appreciate any help. Thanks.

  37. Ganesh on September 15, 2016 at 2:41 am

    Hi,
    Guys need urgent help..

    I am trying to change row color based on corresponding text in same row but at different cell.
    E.g.
    Values from Column A selected using dropdown and only for few of the values from the dropdown the corresponding row color should change to “White” and for rest of the values the row color should remain same.

    Note- I am selecting values for all rows in Column A using dropdown and above should applicable to all rows in column A

    Thanks in advance

  38. GaneshV on September 21, 2016 at 3:41 am

    I have different colour cell ,in each colour I have given different “name” in front of that colour cell, now I want where ever in that sheet if there is same (cell which have given different “Names”) Colour cell ,when I click on that cell it should show that “name”.

    • Kurt LeBlanc on September 28, 2016 at 11:43 am

      Hey Ganish

      I worked on this for a few hours and can’t anyway to retrieve a cell’s color…I can get the color code with a macro, so I tried converting that to a color name with a function. No luck unfortunately. I can get the font name, but not a color.

      What I did was write the code:

      MsgBox ActiveCell.Font.Name

      And assigned that to a button. Then I selected any cell and checked the name with the macro. I’ll put the other code for the color code if you’re interested:

      MsgBox ActiveCell.Interior.ColorIndex

      Maybe someone knows something I don’t and can help you get EXACTLY what you want, but let me know how this works for you:)

      Always happy to help
      Kurt LeBlanc

  39. Karen on October 6, 2016 at 5:11 am

    Thank you, thank you, thank you!! I spent hours trying to get conditional formatting to work, I even started looking at troubleshooting pages thinking there’s a bug that was keeping my formula from working. Your very clear, very precise explanation of the active cell and why it is important saved the day! Formatting works and I can move on with my life (well, maybe I’m not that obsessive, but I do hate a problem).

    Thank you. I have bookmarked Excel University and it will now be my go-to site for my Excel questions!

    • jefflenning on October 6, 2016 at 5:34 am

      You are very welcome! I’m glad the post helped, and am glad you got it working…way to go!
      Thanks,
      Jeff

  40. Jim Smith on October 6, 2016 at 5:27 am

    i have a problem
    Trying to color one cell if any of the a range of adjacent cells are populated?

    tried this and did not work
    rule formula:
    =AND(NOT(ISBLANK($I8:$AG8)))
    applies to: =$H$8

    HELP

    • Kurt LeBlanc on October 6, 2016 at 11:17 am

      Hey Jim

      You are on the right track:) The ISBLANK() and NOT() functions can only reference one cell, so in your NOT()’S you want to put each ISBLANK() and wrap everything in OR() instead because AND() is only TRUE if everything is TRUE so:

      =OR(NOT(ISBLANK($H$7)),NOT(ISBLANK($I$8)),NOT(ISBLANK($H$9)),…each adjacent cell)

      that should yield the desired result. Let me know how that works out!
      Kurt LeBlanc

  41. Dylan on October 14, 2016 at 8:41 am

    Hello,
    Excel is not my strongest point…I am needing to format “If column k is more than column j highlight it red” can you please help me?

    • Kurt LeBlanc on October 17, 2016 at 7:35 am

      Hey Dylan

      This is the post Mr. Jeff wrote on the issue you’d like to solve: https://www.excel-university.com/custom-conditional-formatting-rules/
      The formula can be any formula that returns a Boolean (True/False) value, so you’d simply have “=k>j”, or something similar, and choose your format.

      Let me know how that works for you:)
      Kurt LeBlanc

  42. Ross on October 26, 2016 at 8:42 am

    Expanding on Dylan’s question, how can I make one cell in column B change color based on if its greater or less than the corresponding cell in column A? Say if A is the amount of scheduled orders, and B is the amount completed.

    For example here’s my range:

    A B
    10 9 (LESS THAN RED)
    11 12 (GREATER THAN GREEN)
    11 13
    12 9
    8 9
    8 7
    11 10
    12 13

  43. Dorothy on November 7, 2016 at 12:00 pm

    Hello,
    I think I am doing something wrong!! I keep trying to do this and its not working for me!! I am trying ti get column N drop down boxes to change formatting on regular cells in column O!! The drop down boxes contain USD and EURO and in the cell in column O I want the formatting to change with the drop down list! Example: Column N row 1 is USD I want column O row 1 to change the format to $ sign in front of number, but if it said EURO I want it to change the sign to the euro sign! How would I do that exactly!

    • Kurt LeBlanc on November 8, 2016 at 7:14 am

      Hey Dorothy

      The formatting rule must be a Boolean (True/False) test, so what you do is select O1 and conditional formatting>new rule When you do, select the option for using a formula (all these steps per blog). Your formula would check if N1 is a certain text so

      =N1=”USD”

      You would then select the formatting you’d like and repeat the rule for each different drop-down choice, replacing the text in the formula.

      Let me know if that helps:)
      Kurt LeBlanc

  44. george on November 12, 2016 at 6:34 pm

    Im literally so confused by this article. I have a bunch of tables vertically.

    in each table, there are two rows baseline values and right under it the new values obtained during my experiment. Ive tried this every which way and it WILL NOT WORK. i need to compare the obtained new data to the baseline data in the row above it.

    • Kurt LeBlanc on January 4, 2017 at 10:22 am

      Hey George

      Sorry you got so confused…

      If you select the whole row of new values and start a new rule, create a formula that references the base value (be sure it is a relative reference because it is a locked reference by default) and choose your formatting preference. This should help you fix the issue:)

      Let me know if that helps,
      Kurt LeBlanc

  45. Charles on January 9, 2017 at 3:46 pm

    This is a very useful page! My question is can I format column “A” if column “F” contains duplicate values?

  46. Aquil Goodman on February 2, 2017 at 1:10 pm

    Trying to highlight cell if value is more than 5 of the previous values.

    Ex.
    100
    250
    251
    255
    265
    250 and 265 should be highlighted

  47. Mary Martino on February 3, 2017 at 5:27 pm

    I just want to say thank you. This was the exact information I needed. I appreciate the clear writing and sensible presentation of the knowledge. My spreadsheet is now highlighted the way I want it! Thanks!

    • Jeff Lenning on February 3, 2017 at 5:28 pm

      Awesome 🙂

  48. Melissa on March 2, 2017 at 12:09 pm

    Greetings, I am trying to format cells in column J based on the values of cells in columns A and J.

    I have used this and found a lil bit of success I however can’t seem to get it to work on the entire column.

    What more do I need to do?

    =AND($A3=”Risk”,LEFT($J3,1)=”L”)

  49. BJ Fehr on March 9, 2017 at 12:45 pm

    Hi,
    Additional question to above. What if I have multiple cells in a table that is color coded randomly dispersed throughout (infilled with the color red) and I want to create a separate table that plucks content from the red colored cells and lists them separately. Is there a formula that I could use to automatically update this content onto a different table?

    Thanks.

  50. Umesh Kadam on April 3, 2017 at 5:05 am

    Hi I have below two columns and I need to match the date from 1st column to 2nd and need to highlight only date from 2nd column.
    Can some one help me on this how do I do with using any excel formulas.

    Day High Dates
    14 13,14,21,22,23
    27 16,27
    28 16,17,27,28,29
    16 16,27,28
    31 08,30,31

  51. Neil on April 9, 2017 at 12:05 pm

    Great explanation!! I have been trying to figure this exact issue for last two days. Thanks!

    • Jeff Lenning on April 21, 2017 at 7:29 pm

      Awesome, glad you got it!

  52. Sojib on May 25, 2017 at 8:20 am

    Hi, please solve my problem. cell B1 contains 1 to 6 which are display in H1 category wise. F1 contains total value of C1:E1. Now I want to do….. if (B1=6,F1=0,G1>0) true. Again if F1=G1, true. otherwise false. how can I get it together in a single formula.

    • Jeff Lenning on June 3, 2017 at 12:31 pm

      Sojib, I’d recommend using an AND function. When all arguments of an AND function are true it returns true, otherwise false. Excel also has an OR function which returns true when one or more arguments are true. Hope these help!
      Thanks
      Jeff

  53. Kris on June 6, 2017 at 7:43 pm

    Great write up. Helped me out ages ago and I’ve returned to it often.

    I am curious to know if it’s possible to set up a CF to locate all instances where there is 7 or more adjacent cells in a row containing data, then highlight those cells as well as the row header?

  54. Kim Waton on June 9, 2017 at 6:29 am

    This is great information and easy to follow. I have a slightly different scenario that I need help. I have an Excel file that contains 2 Template (Summary and Individual) tabs that are used to create new tabs for each month. The conditional formatting on the summary tab is based on the Individual tab of the same month. How do I update the conditional formatting each month when the new tabs are created without going in to each conditional formatting formula and update them manually?
    For example, July Summary C18 is fed from July Individual C19 with formula =if(‘July Individual’!C19 >=90, TRUE,FALSE) format = Green, next rule is =if(and(‘July Individual’!C19 >=70, ‘July Individual’!C19 =0, ‘July Individual’!C19 <70),TRUE,FALSE) format = Red. I have similar throughout the Summary file, however with differing value ranges. How do I systematically change the formula in the tabs for the next month when they are created.

  55. Paul on July 13, 2017 at 3:30 am

    Best site I have come across for explaining things in plain English.

    Thanks Jeff

    • Jeff Lenning on July 13, 2017 at 6:24 am

      Thanks 🙂

  56. Jeremiah on July 22, 2017 at 8:40 pm

    I’m pretty new at conditional formatting but although it seems like it should be easy, I’m stumped! I have two columns of %’s such as:
    A B C
    1 19% 17%
    2 18% 22%
    3 43% 10%

    All I’m simply trying to do is conditional format column C with a colored flag or colored circle icon if A1 is larger than B1 or the other way around…ultimately row C would be nothing but a row of icons. Specifically what I’d like is: If A1 is larger than B1 then a red flag and if A1 is less than B1 a green flag. I appreciate any and all help for a beginner.

  57. Anoop on July 31, 2017 at 2:58 am

    pls clarify my doubts:
    A B C
    item cost price of combo pack
    pencil 25
    sharpener 35
    eraser 45
    total 105 110

    I am making a combo pack of 1 product with three products and for assuring the price of combo pack should never be less than total cost of individual items.
    I just want to compare total of column C5 (110) (total cost of 3 products) with total of column B5 (105) (price of combo pack) and show the B5 column in ‘Red’ if C5 is less than B5.
    Pls explain how I can highlight in red if if the total cost is less than price by using conditional formatting.
    thanks

  58. Mike on August 1, 2017 at 9:00 am

    Jeff,
    I would like to write a conditional script that formats a group of cells based upon the value that I enter into a cell outside of the value.

    In essence it accomplishes the look up function but much more quickly. So for example, I have 170 employees in a complex schedule and I want to find the employee with initials MM (I list all staff by their initials in the field. I could, in theory enter MM into a cell that is the “call cell” and that then would highlight any cells in the field that match.

    Any help is appreciated.

    Mike K

    • Kurt LeBlanc on August 7, 2017 at 7:27 am

      Hey Mike!

      I think that I can help you:) Hopefully you are looking for a correct formula like the blog does? Conditional formatting formulas evaluate to a Boolean value (true/false) only. You can have that input cell you mentioned where you would enter the initials you’d like to highlight through your table, and just set it equal to each cell in the table. It would only highlight cells that make the formula TRUE.

      To do that, highlight the table, and create a new rule with a formula. Lock the input cell you reference, then make sure there is a total relative reference to the first cell in the selection. Excel will then cycle through each cell to determine which cells return TRUE to that formula:)

      If the input cell is A1 and the table is C1:I50, the formula would be

      =$A$1=C1

      Hope that helps:)
      Kurt LeBlanc

  59. Rose Stephenson on August 2, 2017 at 7:21 pm

    Hi, I’m trying to apply conditional formatting to balances between certain ranges based on the criteria in another column. Eg I need to highlight leave balances for part time and full time staff for 6 week, 8 week and 10 week ranges. The formula I am using for part time hours is =IF($J6=”Part Time”, $K6>240) (6 weeks) which works fine if formatting everything over 240 hours however if I try to apply for the range 240-320 or 320-400 and over 400 the formatting doesn’t change. The criteria for Part Time & Full Time is in column J and the hours of leave are in column K. The formula for Full Time is based on the same principal. I have tried using an AND formula and I’m having the same result. I currently use conditional formatting to highlight on cell values between two ranges () but only for Full Time hours but also need to identify when Part Time hours are over the 6, 8 & 10 weeks balances. Please help.

  60. Tom on August 15, 2017 at 5:51 am

    Hello,
    I cannot seem to get this to work. I’m looking to highlight blank cells based on two different text phrases found in another row. For instance, i want the cell to recognize “Sat” and “Sun” and grey out the cells that correlate. However the cells that I’m using as a reference point are technically DATE formulas but display as the day of the week, i can get it to format if i type the phrase in as text but not from the cells with the formula in it. I have it built so that when the year in a drop-down on the workbook changes, so does the days and dates of the months. This is going to be used for attendance tracking and i want to make sure Sat and Sun are grayed out. Because obviously the days of the week change each year i cannot simply just lock out the cells. I hope this makes sense! Please help!

    • Jeff Lenning on August 15, 2017 at 7:18 am

      Instead of using the text strings sat and sun, try using the WEEKDAY function to see if the date value is equal to 1 (sun) or 7 (sat).
      Hope this helps.
      Thanks
      Jeff

      • Tom on August 15, 2017 at 8:16 am

        Thanks for your response. Forgive me but how would i apply this as a formula, can you give an example please?

    • Mike on November 18, 2022 at 4:22 am

      Use 2 rules, one for Sat and one for Sun. Worked for me!

  61. gayan on August 15, 2017 at 10:03 pm

    Dear Jeff,

    i need to color a range of cell (ex B1: c11) base on the value of another cell, if i remove the “$” from the rule in formula it may not work properly please help me to solve this

    if the is another method please explan

  62. Natacha on September 12, 2017 at 4:39 pm

    Hi Jeff,

    I need your precious help please: i have a worksheet with about 70 rows and 52 columns. Each row represent a store/client, the column represents the weekly quantity ordered. Most of the time the quantity doesnt change – except for certain periods. I need to see clearly when the number change. i though conditional formatting could help me but i cannot make it work. I need to compare all the cells to their adjacent cells. I tried the formula = B$3C$3, then i tried = $B3$C3, then without dollar signs … nothing worked (it highlight entire columns rather than cells).
    Many many thanks in advance!

  63. mike on October 2, 2017 at 5:39 am

    Hello,

    How would I highlight the value in column B, if the value in A was “sat” OR “sun”? I wrote =$A2=”sat”,which works for “sat” only but i cant get it to work using both.

    Thanks for your help!

    • Jeff Lenning on October 2, 2017 at 3:59 pm

      You could use the OR function, which return true when either condition is true.

  64. Johnny 5 on October 3, 2017 at 11:49 am

    Thanks for the article Jeff.

    I have a question. Is there a way to conditionally protect a column if something is entered in the same row of another column? I don’t even know what to google, but I’m coming up blank.

    Thanks!

    • Jeff Lenning on October 4, 2017 at 5:43 am

      In the current versions of Excel, conditional formatting doesn’t support cell protection or styles. So to my knowledge, this isn’t something that can be done with the built-in options. One possibility would be a macro, but, I don’t have any sample code to share. I will however add this topic to my “things to blog about” list, because, I think it could be pretty cool.
      Thanks
      Jeff

  65. Jayanna G on October 10, 2017 at 8:40 am

    Hello Jeff,, I need a help ..
    In excel, when cell ‘A’ is blank, I want cell ‘B’ to be turned to different color,, Please advise.

    • Jeff Lenning on October 11, 2017 at 5:02 am

      Assuming you are using a version of Excel that includes the ISBLANK function, you could select column B, and then use a conditional formatting formula like this one:
      =ISBLANK(A1)
      Then, select your desired formatting to apply.
      Hope it helps,
      Jeff

  66. Riaan Gerber on October 25, 2017 at 3:22 am

    HI Jeff Lenning

    I want to apply this rule but instead of having a true reference I want to use “text that contains” rule to highlight a complete row.
    Is it possible?

  67. Lindsay on October 25, 2017 at 6:07 pm

    Hi,

    Thank you for the article, which is very helpful. I’m also trying to highlight cells that are duplicates based on information in two different cells. For example, the cells should be highlighted if the first and last name in one row both match with the first and last name in another row. Here’s the formula I have so far (that isn’t working):

    =COUNTIFS($A$2:$A$75800,$A2,$B$2:$B$75800,$B2)>1

    Since this is a big database, if there’s another more efficient way to deduplicate with the option to review records first so I can keep the most complete entry, that would be even better.

    Thanks!

  68. Derok on November 2, 2017 at 9:15 pm

    Thanks for the article Jeff, it is of great help.

    I want to color the rows with 2 conditional words. Column D has Priorities (High, Medium and Low) and column H has Status (Open, Working and Closed). I want the rows to be red colored if cell D2=High and cell H2=Open, similarly rows to be orange colored if cell D2=Medium and cell H2=Open.

    How can I do the conditional format?

    Thanks,
    Derok

  69. Deborah Leifheit on November 13, 2017 at 6:16 pm

    Awesome article, thanks Jeff!

  70. TJ on November 15, 2017 at 2:31 pm

    Very well done Jeff!
    After searching the web for a while, I found that you have the best explanation for this purpose.
    Thank you for sharing.

  71. Alisha on November 28, 2017 at 9:35 am

    Hello,
    I’m hoping that somebody can help me with why my conditional formatting is not working correctly. I have one column that has a phone number in it and another column that is calculated for one week away from a certain date (for me to call that phone number). I want my “Date I should call” column to turn black if my “Phone number” column is blank. I used the following rule:
    I highlighted my “Date I should call” column, went to conditional formatting, input the following formula =ISBLANK($D$3) and then clicked on the black fill. My “D” column is my “Phone Number” column. Currently no black is showing in any cell, even though the majority of my phone number column is in fact blank.

    Thank you for your help!

  72. Heidi on December 1, 2017 at 10:11 pm

    Hi! This is great-
    Semi related question. What if I wanted to do a data validation drop down menu in one cell- and when the selection was made it would auto populate a different cell with a dollar amount? Would I use conditional formatting? I don’t need a highlight- but data to appear.
    (Sorry I’m a noob and I google how to spreadsheet but I can’t find the answer to this. I suspect that is because I don’t know the correct terminology. Thank in advance!)

  73. Will on December 20, 2017 at 3:02 pm

    Jeff,

    I have a list of values in a column that I would like to search their existence in a much larger list of values in another column and if so I would like the value highlighted in the large list. How would I do that?

    i.e. Column A has 112 values ranging from 1 to 1000, column B has all 1000 values from 1 to 1000 in numerical order. I would like the value in column B to be highlighted when it correlates with it’s respective value in column A.

    Thanks,

  74. Jason on January 6, 2018 at 12:48 am

    Thanks Jeff! Question for you… If A1 contains certain text, let’s just say “apple” for example, I’d like to have conditional formatting that makes cells A2 through A5000 all highlight the color blue. In other words the cells A2 through A5000 might all be blank, or have some other text in them, but i still want them all to highlight blue because A1 says apple. Is this possible?
    Thanks!

  75. Sylvain on January 11, 2018 at 12:45 pm

    Hi, the Universe called, it wanted to thank you for all the knowledge distributed to us, unworthy excel users 🙂

    haaa yes, I am also looking for any advice 🙂
    Line 1 has a serie of dates
    Line 3 is a formula outputing numbers of remaining task to do in time
    I am trying to highlight in red remaining tasks that are “in the past” (re: before today’s date)… have tried many things, no luck yet…

  76. Marv on January 17, 2018 at 1:10 pm

    Bravo! Bravo!

  77. James on March 22, 2018 at 12:28 am

    G’day Jeff,

    I have a spreadsheet that automatically updates a graph when i put info into it. I am trying to make it so that if i highlight a particular cell a certain colour, that its corresponding point on the graph will have an X or dot or something, as opposed to the other just being influencers on the graph? Any idea about this?

    Thanks,
    James

  78. Ryan on April 15, 2018 at 2:02 pm

    Hello Jeff

    Fantastic article, however I have a question I cannot solve myself and cannot find a solution for.

    Column F contains numerical data. I can format column F to fill with colour if they are in the bottom 5 numerical scores using the conditional formatting tool for “bottom 10 items”. However I am trying to run an additional condition to the formula and cannot find a solution anywhere.

    Column B states whether the data in each row belongs to a male or female, inputted “Male” or “Female”.

    I am trying to highlight the lowest 5 males scores green in the column F cell where the numerical data is in putted.
    Additionally, I would like to highlight the lowest 5 female scores red in the relevant column F cell where the numerical data is inputted.

    Any assistance please?

    Many thanks
    Ryan

  79. Akshatha on April 18, 2018 at 1:31 am

    Hi,
    I have this budget sheet where Column B is ‘Planned Expenditure’ and column C is ‘Actual Expenditure’. I want the cells where the Actual expenditure is more than planned, to be highlighted in Red. Problem I am finding is how to apply conditional formatting using formula but to cover the range of relative values.
    B – Planned C – Actual
    4000 5000 => Here, this cell should turn red as Actual > Planned
    5000 3000 => Here, this cell should not turn red as Actual < Planned
    Imagine there are hundreds of such entries.
    Any help in this would be greatly appreciated 🙂

    • Kurt LeBlanc on April 24, 2018 at 5:16 pm

      Hi Akshatha!

      To have the entire rows turn red, highlight the entire table (Be sure that the top-left cell is the active cell.). Then you’ll apply the conditional formatting with a formula comparing the actual to the planned values. The formula is cycled through every highlighted cell, so you’ll want to lock down the column references when you write it, like below:
      =$B2<$C2

      So when this formula evaluates to TRUE, Excel will apply the formatting. Try it out, and let me know how it goes:)

      Thank you,
      Kurt

      • Akshatha on April 25, 2018 at 1:42 am

        Hi Kurt,
        Thank you for your reply but the requirement is slightly different. I don’t need the entire row/column to be highlighted in red but only those specific cells where the actual value is greater than the planned value.
        Planned (Column B) Actual (Column C)
        4000 (Cell-B1) 5000 (Cell – C1) => Here, this cell C1 should turn RED as Actual > Planned
        5000 (Cell-B1) 3000 (Cell – C2) => Here, this cell C2 should NOT turn red as Actual < Planned
        So within the same column C, there may be several cells with red and several without red based on their actual values being more than/less than planned values.

        • Kurt LeBlanc on May 20, 2018 at 11:39 am

          Hey Akshatha,

          Thank you for letting me know! This will actually be almost the same. All you need to do is highlight the column of cells you want to apply the rule to and enter a formula that tests if the actual values are greater, like below, and add your custom formatting Excel will apply if the test returns TRUE.

          =C1>B1

          Excel will go through every cell and run its test. Let me know how that works!

          Thank you,
          Kurt

          • Akshatha on May 28, 2018 at 2:47 am

            That worked! Yayy! Thank you very much!



  80. Michael on April 20, 2018 at 8:32 am

    Hey I read through everything and I still can’t figure out my problem. I am trying to change a single cell in column E when something from a drop down list in column B is selected. For example in cell B3 I select “Supervising” I would like cell E3 to highlight green. but I would like the Cell E3 to change even if cell B6 is changed to supervising.

    I hope this make sense.

    • Kurt LeBlanc on April 24, 2018 at 5:07 pm

      Hi Michael!

      When you’re entering a formula to conditionally format cell E3, try using the OR function as follows:
      =OR(B3=”Supervising”,B6=”Supervising”)

      That way it should light up no matter which cell is changed to Supervising:)

      Thank you,
      Kurt

  81. Colin Roberts on April 25, 2018 at 6:43 am

    Hi Jeff,
    I’m trying to use conditional formatting to highlight a cell based on the following:- If a specific status (MoU / GoR / Proposal Submitted) is selected from a drop down in U2 then a date will be required in W2 and the cell turns red. If any other status is selected then the corresponding cell in W remains blank. Once the date is supplied in W2 it returns to no fill. I have achieved the first part of this, i.e. cell turns red, without a problem but am baffled as to how to achieve the rest. Your kind assistance would be greatly appreciated.

  82. Charles Crasto on April 25, 2018 at 7:02 am

    Extremely detailed and very helpful. Thanks!

  83. Tammy on May 18, 2018 at 9:16 am

    what formula can I enter in “conditional formatting” to highlight the cell based on whether or not another cell’s value (let say the other cells value is 7/7) does not contain a 12? In other words, I want the cell T6 to format if cell A6 value does not contain a 12.

  84. Desiree on May 31, 2018 at 8:29 pm

    How do I apply conditional format in one cell when there is a drop-down list in another cell? For eg, cell A2 has drop-down list containing items “one, two, three.” I want cell A1 to highlight yellow for “one”, blue for “two” and green for “three.” So if I select item “two” from the drop-down list in cell A2, I want cell A1 to highlight blue. Hope this makes sense. Thank you!

    • Kurt LeBlanc on July 12, 2018 at 11:10 am

      Hi Desiree,

      To apply CF in this situation, you would just have to create rules for each color checking the selection, i.e. =A@=”one”, and apply the formatting. You would have to do that for each color. Excel doesn’t allow you to create one rule to apply 3 colors:/

      I hope that helps!

      Thank you,
      Kurt

  85. Derok on August 16, 2018 at 11:45 pm

    Hi Jeff,

    Thanks for the tutorial blog.

    I have a sheet wherein I will be calculating the time spent by my team member.

    Time-in will be enter in column B and time-out in column C, I will be calculating the total time spent in column D by formula =TEXT(c2-b2, “hh.mm”).

    I want to highlight the column D with one color, if the time spent is more than 9 hours and with another color, if the time spent is more than 10 hours.

    What formula should I use in Conditional Formatting? Or is there any other way of highlighting the column D cells based on their values.

    Thanks in advance.
    Derok

  86. Kush Jhingan on August 26, 2018 at 7:43 am

    Hi Jeff, Thanks for the article.
    I have a problem that am not able to resolve……..
    I have a spreadsheet with data, and the last 8 columns have data that shows absolute change and %age change over the last period.ie.,
    O P Q R S T U V
    141431 1.07 133 0.50 291 2.42 58 0.71
    Columns P,R,T & V are the %age change over the previous period.
    I want to highlight only Column P. In one colour if the number therein is greater than all the other %age columns. And in a different colour if the val in col P is less than all the other 3 col’s. And no highlight, if neither is the case.
    That is to say if
    If Col P = 4, col R=3, Col T=2, and Col V=1, then highlight in one colour.
    And if
    If Col P = 4, col R=5, Col T=6, and Col V=7, then highlight in a different colour.
    But if
    Col P = 4, col R=5, Col T=2, and Col V=7, then No highlight.

    Seems like a bit much to ask, but would really appreciate your assistance.

  87. Rachel Tenney on October 12, 2018 at 1:30 pm

    I am filling in text in both Columns D and G. I want to shade the cell in column D, only when the respective cell in Column G is blank AND the cell in Column G is blank. How would I go about this?

  88. Louis on November 1, 2018 at 12:06 pm

    Good day,
    I am attempting to conditional format a cell based on 2 different cell references. Say column C3 is a date 1-Oct-18 and column AR has either OPEN GROUND BOARD INVESTIGATION, OPEN AVIATION BOARD INVESTIGATION, OPEN UAS BOARD INVESTIGATION, OPEN AGAR, OPEN AAAR, OPEN UASAR or CLOSED which are added into column AR by use of a drop down menu. What I am trying to do is based on the date in column C3 and having any one of the open statuses in column AR to have a color change in column C for 15 days and a different color for 30 days beyond the date in column C. I know I’ll have to use 2 Conditional Formats, one for each rule but that is fine. Also when CLOSED is selected in Column AR I would like for the color changes to disappear. I hope this is clear enough, and thank you in advance for your help.

  89. Bill H. on November 14, 2018 at 9:17 am

    Good Morning Jeff. I need to ask about formatting a cell with number. I want it to show up something different then what i entered. I know how to mask out the info in a cell by using ;;; . What i want to do and unsure how to this if possible is if I type in 2313, I want it to show up 2435 (yes adding 122 to that number i entered and same for every cell in a designated column). I want to do this in formatting if possible without doing a reference to another column or formula for the output. Is this possible.

    Look forward to your response.

  90. Rick Yagelski on November 16, 2018 at 9:06 am

    Worked great to create the colored filled columns that I desired. Is there a way to ‘lock’ the conditional formula, so that when I move cells around by cutting and pasting, the colored columns remain where they were initially created instead of moving to the new pasted address?

  91. Linda M on December 11, 2018 at 5:33 am

    Hi.
    I have a staff roster that uses colors to highlight the different roles that staff are rostered in for the day. The master roster is a simple table that has a column with staff names, the days of the week across the top, and then colored cells to show what day they are working and the role they will be in.
    On a different worksheet, I have a daily view of the same roster broken down into 15 min blocks. The color of each staff member’s role is spread across the day – showing the hours they are working and when their breaks are.
    What I was hoping to do, is have the daily roster automatically reflect each staff members role color by coloring the background of the cell with their name. (referencing this from the master roster). I will need it to auto adjust if I happen to change any role colors in the master roster.
    This will help me quickly fill in the daily roster in the right role colors without having to manually reference the master for the right role colors to use. Is there a formula for this?
    Linda

  92. Dereje Woldesenbet on December 11, 2018 at 7:47 am

    Dear Jeff, I tried all that you recommended but could not get the right highlighting. The concept is if the value is between 4 and 6, it turns to be acceptable, However, if it is out of that range, it is not acceptable. Here is my formula IF(AND(4=A3),”OK, ACCEPTALE”,”OUT OF RANGE, FAILED”) this is my formula.

    I want when the value turns acceptable to be green and when unacceptable to be Red. Please help!!!

  93. Mike M on January 30, 2019 at 8:40 am

    Hello I need help with the following: I have formatted (Sum) a cell to equal zero when it is a negative after add 2 other cells together. But now I want to take that the cell that is formatted to zero and add it to other cells but using zero as the number instead of the correct number before formatting to zero. See below
    A1 5 A2 -15 A3 sum A1:A2 equals -10 but formatted to zero because its a negative number A4 10 A5 10 A6 5 A7 sum A3 thur A7 equals 25 but because of the A3 equaling -10 before formatting to zero my sum equals in A7 is 15 instead. How can I format it to read A3 as a zero?

  94. Tom Tomlinson on March 5, 2019 at 4:37 pm

    This is a great article! I particularly liked your discussion on using cell references in the formula for the active cell. You said, “We used a mixed cell reference, $D7, where the column part (D) is absolute and the row part (7) is relative. Here’s why. Remember we want to pretend that we are writing the formula into the active cell, in this case, B7.”

    I have stumbled SO many times with the formatting not giving me the picture I expected because the formula as applied over the range did not lock down the active cell to evaluate.

    Attaching your example worksheet allowed me to recreate these on my system. Thank you for providing!

  95. Tom Tomlinson on March 5, 2019 at 4:39 pm

    This is a great article! I particularly liked your discussion on using cell references in the formula for the active cell. You said, “We used a mixed cell reference, $D7, where the column part (D) is absolute and the row part (7) is relative. Here’s why. Remember we want to pretend that we are writing the formula into the active cell, in this case, B7.”

    I have stumbled SO many times with the formatting not giving me the picture I expected because the formula as applied over the range did not lock down the active cell to evaluate.

    Attaching your example worksheet allowed me to recreate these on my system. Thank you for providing!

  96. Sherry Fox on June 7, 2019 at 11:16 am

    Thanks so much! I had to use multiple conditions, and it is something that I have not needed to do in a while… so I forgot the exact syntax to use. As soon as I saw you example I said “yes, that’s it!”!! This was a little different as I had created a drop down list using wing ding fonts… for an empty box and a checked box. My project file has a list of requested fixes/enhancements, so I wanted a simple way to indicate what was done. The CF was a requirement as it is then easier to see which items have not yet been completed.

  97. Chris Hall on June 17, 2019 at 9:21 am

    Great article and has really pointed me in the right direction! I just had a slightly different specification.

    I have a table with three columns.
    (A) Actual Revenue
    (B) Budgeted Revenues
    (C) Difference

    I want to format my actual revenue column with colors based on the difference column. If the difference < -2000000, then I want to format it a deep shade of green.

    What I have so far is: =$C$1< -2000000

    then I format it a deep shade of red. It doesn't work though. Any advice?

  98. Lori Myers on July 2, 2019 at 2:23 pm

    Jeff, I could use some help. I have a dollar amount in cell A1. In Cell B1 I will enter a date when the the Amount in A1 gets submitted. In Cell C1 I will enter a date when the Amount in A1 gets paid. How do I color format it so that A1 is red when submitted and turns green when paid? Any help you can give me would be great. I think I am having issues with how to enter the date in the formula. I’ve tried “mm/dd/yy” and several other options, but nothing works. Thank you!

    • Chris Hall on July 2, 2019 at 2:57 pm

      I resolved my issue, thanks!

  99. Jagannath Nayak on August 17, 2019 at 12:29 am

    Thanks a lot sir,
    It was very useful and productive.

    • Ijas Ahamed on January 4, 2020 at 7:56 am

      I found your code to ‘set cell color to equal to another cell color” and it works when I am using it on the same worksheet ( ex from cell A1 to cell A2). I am wondering if there is a way to have this same functionality from another worksheet (ex to copy cell color from sheet1!A1 to sheet2!A1? Any help you could offer would be appreciated!

      • Ijas Ahamed on January 4, 2020 at 8:00 am

        I used this formula

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Me.Range(“C1”).Interior.Color = Me.Range(“A1”).Interior.Color
        End Sub

  100. danonymous on January 10, 2020 at 9:09 pm

    Works great. Very clear. Thanks a lot. I wouldn’t have figured it out without this.

  101. DN on February 21, 2020 at 5:37 am

    How could a format a cell if it equals to anything in a cell range
    if anything from J17:J35 equals to anything in column “A” i want that cell in the J range to change color

  102. Serkan on March 17, 2020 at 3:14 pm

    If cell A1 contains specific text “monthly”, then highlight cell B1. How do you do this?

    • Jared Whitener on April 18, 2023 at 2:43 pm

      You can set up a new formatting rule for cell B1, with “Use a formula to determine which cells to format”. Then for the formula, you can have something like =SEARCH(“monthly”,$A$1)>0 Search returns the starting position of the word, and so if it returns a number, then your word has been found. You can also change the relative references and instead of $A$1, use $A1 and drag the conditional formatting down, and then any cell in A containing “monthly” would cause the adjacent cell in B to have the formatting you set.

  103. Sarah on July 31, 2020 at 6:19 pm

    This is so helpful! but I’m still having issues with multiple conditions. I want to highlight a cell if 1) it contains the specific text “Y” AND a different cell is blank. So far I managed =AND(D3=”y”,IFBLANK(E3)) but of course, it doesn’t work. Does anyone have any ideas?

  104. budreau on August 11, 2020 at 12:03 pm

    this is great and works well. however, when i apply the formatting, the last row in my data does not highlight. any thoughts on where my mistake is?

  105. Keyur Shah on October 18, 2020 at 7:35 am

    Can we have method to automatically “unlock” (for any operation) a otherwise “locked cell” in protected sheet depending on value of another cell ?

    Let’s say I have a worksheet in which all the cells are protected in column O.

    My column D has drop down menu to select from @ 58 list.

    What I want to achieve is if someone select from drop-down “subtotal” in any row in column D, respective cell in that row in column O shall be unlocked for subtotal.

    How we can achieve this.

    Looking forward and thanks in advance.

  106. Jasmine on December 10, 2020 at 5:27 pm

    I have struggled in the past with the formula based Conditional Formatting, so this blog was great!

  107. Wenvarya on January 4, 2021 at 5:56 am

    This is such a great explanation. However, I am experiencing sg strange. My formula copied into a cell individually gets a “TRUE” result. However, the conditional formatting considers it as “FALSE” and simple doesn’t do the conditional formatting. Anyone experienced sg similar?

  108. Scott on March 31, 2021 at 7:53 am

    Hello,
    What I’m trying to do is change the color of the cells in $A11-$A49 based off a condition in $B2. I want to type a name in $B2 (ex. John Doe) and have all instances of the name ‘John Doe’ to change in cell $A11-$A49.

    I highlighted cells A11-A49 and used ‘Use a formula to determine which cells to format’ in Conditional Formatting. I applied the following formula =$B2=’John Doe’, ‘Jan Doe’, ‘Mike Doe’ but when I pressed ok, excel said I must put a ‘ in from of the formula, so when I did and went back and checked the rule excel added =” to the formula so it now looks like =”‘=$B2=’John Doe’, ‘Jan Doe’, ‘Mike Doe’
    Can you help please?

  109. SARAVANAN KUPAN on June 5, 2021 at 7:34 am

    Hi Jeff,

    1. To make sure A1 cell not empty before choose to fill up B1 cell, i put =NOT(ISBLANK($A$1) in validation data. But how to put the same rule for the cells that already have formula drop down list? How to make sure drop down list can be choose only if previous cell filled up or else pop up error?
    2. How to do conditional format for example if fill up cell C6, next only cells D7,D8,D9 must fill up before save the file and if fill up B6, next only cells D7,D8,D9,D10,D11,D12,D13 must fill up before save the file?

  110. Lindsay on June 14, 2021 at 12:31 pm

    I’m so pleased you took the time to explain the active cell reference (and made a point about taking the time to explain it, which is what made me actually read it!), as that is exactly where I was going wrong! Problem solved, thank you!

  111. D Richardson on June 23, 2021 at 11:17 am

    I am not even sure this is possible. But here goes. I am setting up a table with a generic julian calendar. the top row has months, the first column has 31 days (yes, a few are blank cells). Anyways, I am trying to set it up so that a person can click one date (column n row match) and then put that number into another cell, and be able to do that twice, then have a formula to find the difference in days between the two numbers. Thank you.

  112. Thomas M Jayabhai on July 17, 2021 at 8:41 am

    Hello, I need your precious help –
    I need the totals to be in Cell A5, the sum of Cells F8, F9, F12, K16 and M20 if Cell A4 contains the word “BUY”
    Can I have the totals to be in Cell A5, if the same Cell (A4) if it contains “SELL”, to add a different set of Cells, F4, K20, M21 etc.
    Thank you.

    • Thomas M Jayabhai on July 17, 2021 at 8:56 am

      I am sorry, I should have used a formula instead of Formatting – sometimes brains stops working when working with Excel – Ha Ha Ha . . . .

  113. Maurice McHugh on November 4, 2021 at 4:05 am

    Thanks Jeff,
    Many times I had wished I could apply conditional formatting of one cell or range based on another cell.
    I had given up on the idea, assuming was not possible, until today when my google search led me to this page.

  114. Kane Olsen on December 14, 2021 at 7:16 pm

    Thank you, Jeff! I struggled with a conditional formatting issue that was solved by your fantastic explanation of what an active cell is.
    “If you can visualize the idea that you are writing the formula into the active cell, and the formula will be filled through the selected range, then writing the formula becomes easier.”
    Amazingly thorough while also being brief & straight to the point. Very thankful you made this website and a post from 2013 helped me in 2021!

  115. asim on December 19, 2021 at 3:54 pm

    Respected sir, I have a big data in excel worksheet there is an I have applied a (conditional formating all thing are going to good, but I want to where is my data direct Cell go to this conditional formating cell.

    Thanks a lot.
    Student.

  116. Iris on January 24, 2022 at 4:24 am

    Thank you, this helped me.

  117. setu shah on June 24, 2022 at 4:04 am

    i have one question exeample: Suppose C3 cell number is more then 15 result in D4 18 and C3 cell number is less then 15 result in D4 is 9..Please give formula

  118. Ruth McCartney on August 12, 2022 at 10:04 am

    Yes really helpful!! We did this with our stock management spreadsheet a few weeks ago and had been working perfectly….

    But just this week, when we change the value associated with the colour, only that cell and one either side change, but then if we double click on a couple of cells in the row, this then updates the whole row accordingly! Random!

    Any suggestions as to what is going on?

  119. Edward Iden on October 6, 2022 at 2:30 pm

    I have a spreadsheet with months in cells A4 through A15 and days of the month in cells B3 through AF3. Column AG has totals for each month to the right.

    In the sheet described above I have a =SUM formula in AG4 to calculate qty in range B4 through AF4 (days of the month) with an AND formula to show no value in AG4 if there are no values in any of the cells B4 through AF4 (see below).

    =IF(AND(B5=””,C5=””,D5=””,E5=””,F5=””,G5=””,H5=””,I5=””,J5=””,K5=””,L5=””,M5=””,N5=””,O5=””,P5=””,Q5=””,R5=””,S5=””,T5=””,U5=””,V5=””,W5=””,X5=””,Y5=””,Z5=””,AA5=””,AB5=””,AC5=””,AD5=””,AE5=””,AF5=””),””,SUM(B5:AF5))

    First: is there a way to simplify the above formula in showing no value in the total if there are no values in the days of that month?

    Second: I want to be able to AVERAGE the month totals in a year for only the ones that have past. Is there a way to show a 0 value if there are no values in the days of that month so it calculates into the AVERAGE cell for column AG only after that month has past since the AVERAGE formula omits cells with no value?

    Thank you in advance!

  120. Tony M on December 6, 2022 at 8:01 am

    Thank you for this lesson! Question, what is the process for highlighting just the value and not the entire row?

  121. Kyle on January 11, 2023 at 9:26 pm

    This is what I have been looking for, however the only change I need to make is have the formula to format the row based on if the target or reference cell is either an odd or even number. How would I go about doing this? I have tried =ISODD or =ISEVEN but doesn’t seem to work.
    Thanks

  122. D Webb on March 20, 2023 at 8:19 am

    Hi – thanks for this information which has been extremely useful. I still have one issue I can’t seem to resolve… I have a spreadsheet with overdue and upcoming dates formatted, however, I also need to be able to overwrite a cell if another cell is showing 100% (complete). As this is based on dates and not text not sure what to do.

    Basically, I need overdue tasks to show red unless 2 columns over it is showing 100%, which I have formatted to be green and I want the entire row to be highlighted green, even if it is showing red because the date is, in theory, overdue

  123. Mohshin Sarker on November 3, 2023 at 1:00 pm

    Hi,
    I have a excel grid where columns has week numbers and same with the rows headers.
    For example, columns reference week 33 should match rows 33 and the matched coordinate (for example sales of $100) should only be highlighted.

    Any suggestion how to do that.

  124. Mimi on February 28, 2024 at 12:58 pm

    HI,
    how can I highlight entire rows based on a cell with balances that are equal or greater than twice the amount of another cell (charge)? I hope this is understandable.

    based on the below the only row that will be highlighted is row 3

    Charge Balance
    1,121.00 1,121.00
    110.00 -5,399.00
    25.00 907.00
    485.00 389.00
    296.00 0.00

Leave a Comment