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.

Conditional Formatting

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!

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.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

121 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

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

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

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

    Thanks this helped my manager.

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

  22. 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 πŸ™‚

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

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

  25. 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 πŸ™‚

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

  27. 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 πŸ™‚

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

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

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

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

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

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

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

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

  36. 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: http://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

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

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

  39. 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 πŸ™‚

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

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

  42. 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 πŸ™‚

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

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

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

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

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

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

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

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

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

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

    Awesome article, thanks Jeff!

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

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

    Bravo! Bravo!

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



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

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

    Extremely detailed and very helpful. Thanks!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.