Search Not Just Numbers

Tuesday 4 March 2014

Excel Tip: Conditional Formatting based on other cells

Excel's conditional formatting feature typically allows you to set the format of a cell based upon its contents, and allows you to define all sorts of rules upon which to do this.

This is great, but what if you want the cell's format to be based upon the contents of a different cell (or cells)?

A typical use for this might be for a traffic light graphic giving a quick visual aid to understanding a set of numbers.

Fortunately Excel provides a relatively straight-forward way of doing this.

The answer is to use the formula option in Conditional Formatting.

Highlight the cell, or range of cells, that the conditional formatting is to be applied to.

Select Conditional Formatting from the Home ribbon and select "New rule". Then select the bottom option from the list - "Use a formula to determine which cells to format":


You can then type a formula into the box that will be used as the criteria to determine whether each cell is to be formatted, or not.

The criteria should start with an "=" sign and then follow the same rules as the condition argument of an IF function.

Where you are applying the conditional formatting to more than one cell, the criteria should be written from the perspective of the top left cell of the range being formatted. This becomes important in how it will treat relative cell references. Cell references can be made absolute using the dollar sign, as in a regular Excel function.

Once you are happy with your criteria, click the Format button and set the formatting that you want to apply should the criteria be met.

Some examples should make this a little clearer...

Let us take a simple one first. Say we wished to fill cell C3 with a red background, if cell A1 was negative.

We would click in cell C3 and open the dialog box as above. We then enter the following into the criteria box:

=A1<0

We then click the format button and select a red fill.

As we are only formatting one cell, it doesn't matter whether we use dollar signs or not.

Now, let's say we want the whole range C1:G10 to be coloured red if A1 is negative.

We would select the range C1:G10, and then do the same as before, however now we need to consider how we want cells other than the top left cell of the range to be treated.

In this case we want them to still look at the contents of A1 so we will need to fix the reference to A1 by adding the $ sign in front of both the row and column reference. Our criteria should therefore read:

=$A$1<0

If we didn't add the $ signs, then the criteria would be looking at a different cell each time.

C1 would be looking at A1 (as C1 is the top left cell in the range), however C2 would be looking at A2, D1 would be looking at B1 and D2 would be looking at B2, etc.

We may want the reference to be relative in other cases. Say we wanted to apply the conditional formatting to the same range, C1:G10, but this time wanted to fill the row (columns C to G) red when the contents of column A is negative.

We would do exactly as above but this time apply the dollars as follows:

=$A1<0

We are still fixing the column but not the row, so now C1 will look at A1 (as before), and D1, E1, F1 and G1 will also look at A1 as we have fixed the column to always be A. However C2 to G2 will all look at A2 as the row reference is still relative.

The criteria you use can refer to more than one or a range of cells, so it could be any of the following:

=SUM(A1:A10)>=10,000
=A1-B1=0
=A1>B1
=SUM(A1:A10)>B1

Basically anything you could have entered as the criteria in an IF function (with an "=" sign in front of it).

You can also apply more than one rule to the same cell, so to apply our traffic light system, for example, we might have the following rules:



  • The ordinary format of the cell set to an amber (or orange fill) so that the fill is this colour if the other rules don't apply. You don't need Conditional Formatting for this;
  • New Rule =A1>=500  to give a fill of green
  • New Rule =A1<=-500 to give a fill of red

This will colour the cell red if A1 is less than or equal to -500, amber if it is greater than -500 but less than 500, and green if it is 500 or more.

There, you can now do pretty much anything with Conditional Formatting!

Excel Expert Course


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

52 comments:

  1. Great tip, this one Glenn - thanks!

    ReplyDelete
  2. Glen - thanks it is very useful. Kindly give us more such tips. Thanks in advance & regards.

    ReplyDelete
  3. If you have a large table and lose track of which line is which you can shade alternate rows using this.

    =MOD(ROW(),2) will return 1 on odd numbered routes and zero on even rows. Excel will interpret 1 as true so that row could be shaded by entering that function in conditional formatting dialogue box.

    ReplyDelete
    Replies
    1. Good top Phil. I usually use =ISODD (ROW ())

      Delete
  4. This blog is so helpful. Thank you! One thing that is driving me batty (I figured out how to do it last year and can’t remember how I figured it out. I'm going to try and explain it without confusing both of us! :D

    I have a 12 month calendar on the left side of the spreadsheet (there are about 4 months across and then the next 4 are under that.

    Maybe Jan 1 would be A2, Jan 2 would be B2, etc.
    On the right side of the spreadsheet I have (for discussion sake) a single column to track certain event dates - let's call the column YY and the event name in ZZ.

    Let's say I'm tracking classes I'm teaching.
    YY2 is 2/3/15 and ZZ3 may read "How to Make Doohickies 101"
    YY3 is 6/5/15 and ZZ is the next event.

    I'm trying to remember the conditional formula that would whichever date I TYPE into YY, it highlights the actual calendar date cell on the calendar I have on the left.

    So that if you are quickly wanting to eyeball the calendar, any colored date, I would see that the event is scheduled.

    Can you please help me with that formula? I really would appreciate it!

    ReplyDelete
    Replies
    1. As long as you have a corresponding cell with the date for that cell in it, or have the date in the cell on the calendar itself, then you can use COUNTIF in the conditional format.

      Say the date for this calendar cell is in A2, then the following formula in conditional formatting should do what you want:

      =COUNTIF($YY:$YY,A2)>0

      I hope that helps.

      Delete
  5. I need help with a if statement formulae

    The formula is:

    =IF(H18='Booking Date Log'!$J$13:$J$1048576, "Not Available", "Available")

    H18 contains a date e.g. 9/9/2015

    $J13:$J$1048576 contains a range of dates e.g. 9/9/2015 9/10/2015 ....

    True Value = "Not Available"

    False Value = "Available"

    what i need is that if H18 = any of these cells ($J13:$J$1048576) for it to come as "Not Available"

    and if H18 doesnt = any of these cells ($J13:$J$1048576) for it to come as "Available"


    Can this be done?


    thanks

    Fraz

    ReplyDelete
    Replies
    1. Dear Fraz

      The IF statement is just one condition, and you are trying to compare it to a range.

      You could use COUNTIF to count how many entries = H18 and then use IF to show "Available" if this is zero, otherwise "Not Available".

      i.e.

      =IF(COUNTIF('Booking Date Log'!$J$13:$J$1048576,H18)=0,"Available","Not Available")

      You may find my service for answering these types of questions useful:

      http://www.needaspreadsheet.com/excel-advice/

      Delete
  6. if i type 500 in cell(any Excel Files) but it showing 5 only ,last two digits gone,what is the problem

    ReplyDelete
    Replies
    1. That sounds like a number format issue. If you change the number format to General, that should solve your problem.

      Delete
  7. I am trying to change an existing number(all different) in a column B to be negative that number if the previous column number A is negative. any ideas? thanks

    ReplyDelete
  8. Ok so I have managed to format the cells, so all cells showing a Y in Column D colour blue along with the cell value in column A, same with Column E if it shows K it will colour dark green and M will colour light green and will fill 'A' cell the same colour. Now I want to format it so if column D or E show Y and K or M the cell in Column A doesn't fill with a colour.

    can this be done??

    ReplyDelete
    Replies
    1. Yes. You can use multiple conditions by using the AND and OR functions in the criteria formula.

      Delete
  9. Glen, I'm trying to create an IF and THEN formula and it just won't accept it. I'm trying to say IF A1-B1 is greater than ZERO, THEN enter the result in C1. IF it is not greater than zero, then leave C1 blank.

    ReplyDelete
    Replies
    1. Jennifer - that wouldn't be conditional formatting, you just need to enter the following formula in cell C1:

      =IF(A1-B1>0,A1-B1,"")

      For more detail on the IF statement, visit my earlier post:

      http://www.notjustnumbers.co.uk/2010/10/excel-tip-if-statement-made-simple.html

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

    ReplyDelete
    Replies
    1. There are a few ways but I think SUMPRODUCT might be the most efficient.

      If the BB dates are in the range B2:B1000 and the posted dates are in the range C2:C1000, then you could enter the following in D2 (and copy it down):

      =SUMPRODUCT(($B$2:$B$1000< B2)*($C$2:$C$1000 >C2))

      Obviously, change the ranges to suit your data. I would advise against using the whole column as this could lead to a lot of unnecessary calculation time. Just pick a number of rows that will comfortably cover your data.

      This will return the number of rows that exist where the date in column B is less than that in cell B2 AND the date in column C is greater than the date in cell C2.

      If this number is greater than 0, then a FEFO breach has occurred. You could then use a straightforward IF statement (based on column D) to conditionally format the row as per my post.

      The SUMPRODUCT works as follows:

      ($B$2:$B$1000< B2) returns an array of 1s and 0s depending on whether the condition is true (1) or false (0).

      ($C$2:$C$1000 >C2) does a similar thing with column C.

      The SUMPRODUCT multiplies the two arrays together and sums the result.

      The resulting array will only include 1s where both conditions are true, as if either condition is false, we will be multiplying by zero.

      I hope that makes sense.

      Delete
  11. I'm using this statement =IF(A1>0,"true",""). The problem is that if A1 is blank(which isn't greater than 1), it is still putting "true" in B1. How can I make it show cell B1 as blank if A1 is blank?

    ReplyDelete
    Replies
    1. Your formula should return bkank if A1 is actually empty, however if A1 contains a space for example, it will return "true".

      The following should do what you want even if there is text (such as a space in A1:

      =IFERROR(IF(VALUE(A1)>0,"true",""),"")

      VALUE(A1) will return the value of A1, or an error if A1 does not represent a number.
      Encasing the whole function within the IFERROR function will return the second argument of the IFERROR function if the first one returns an error.

      =IFERROR(YourFunction,"")

      Will return "" if YourFunction returns an error, otherwise it will return the result of YourFunction.

      Delete
  12. Hi great post.
    I have a table that has standard conditional formatting showing green if the figure in the cell is positive, and red if negative. I have another table underneath with the same formatting (1 table shows sales, the other shows profits). I now want to format the next column to green if both sales and profit are green/positive, and red if both are red/negative, and yellow if sales are positive and profits are negative. I have tried to insert a formula in conditional formatting option e.g. for green:
    =and(a2>0,a29>0)
    but its not giving me the right results. Can you please help me? Where am I going wrong? Thanks a lot

    ReplyDelete
    Replies
    1. Which cells have you highlighted to apply the formula to?

      Delete
  13. Thanks Glen
    You are amazing

    ReplyDelete
  14. Hi, I have a big table of data, is it possible to use conditional formatting (if any row in collumn A=1 the shade that row?

    Thanks
    Phil

    ReplyDelete
    Replies
    1. Phil, my earlier post should tell you all you need to do this:

      http://www.notjustnumbers.co.uk/2012/06/excel-tip-using-conditional-formatting.html

      Delete
    2. Thank you Glen, I hadn't seen that page, it works perfectly. :)

      Delete
    3. Great. It's just a specific application of this post, but a one that people want to do a lot.

      Delete
  15. Hi,
    Is it possible to add conditional formatting to shade a range of cells grey if one cell (which already contains a Data validation list and is a merged cell) says "Non-beneficial" (non-beneficial is one of the options available in the drop down list)?
    Thanks,
    Becky

    ReplyDelete
    Replies
    1. Yes, just follow the steps above.

      If the cell containing the dropdown is A1, then the formula to use in the conditional formatting is:

      =$A$1="Non-beneficial"

      Delete
    2. Yes, just follow the steps above.

      If the cell containing the dropdown is A1, then the formula to use in the conditional formatting is:

      =$A$1="Non-beneficial"

      Delete
  16. That's great thank you! Can I possibly add a tiny bit to the formula that means if anything other than "Non-beneficial" is selected from the drop down box the cells will all stay clear?
    Thanks,
    Becky

    ReplyDelete
    Replies
    1. The easiest way to do that is just format them as clear normally. The conditional format will only kick in if the dropdown does equal "Non-beneficial"

      Delete
  17. It works! Thank you! You've been a big help!

    ReplyDelete
  18. This is great thanks! One question though, if I wanted to conditionally format the cell based on the value after a decimal point, how would I do that?

    i.e. - if the value in the cell is 34.60 and I wanted to colour any cell red where the number after the decimal is within the range 50 to 70. Thanks.

    ReplyDelete
  19. Again, if the value was in A1:

    =AND(MOD(A1,1)>=0.5,MOD(A1,1)<=0.7)

    should do it.

    ReplyDelete
  20. Ah you are a lifesaver!! Thank you!

    ReplyDelete
  21. Regarding conditional formatting... If C3 is yes AND G3 is =>15 the format would turn the number(in G3) BOLD/red. Then IF C3 is no AND G3 =>30 turn the number (in G3) Bold/green.

    ReplyDelete
    Replies
    1. Just apply 2 different conditional format rules to cell G3.
      Condition 1:
      =AND($C$3="yes",$G$3>=15)
      Condition 2:
      =AND($C$3="no",$G$3>=30)
      Choosing your required formats for each.

      Delete
  22. Hi, I am trying to apply conditional formatting to a a cell that contains this IF(D6="Not Applicable", "Does Not Expire", IF(D6="","Pending", IF(D6>TODAY(), "Valid", IF(D6<=TODAY(),"Expired")))) and i would like to format as follows: Valid = Green, Expired = Red, Pending = Amber and Does Not Expire = Blue...How can i do it?

    ReplyDelete
    Replies
    1. You won't need a formula for that in the conditional formatting. Just apply four separate conditional formats to the cell, e.g.

      Cell contents equal to Valid, format as green

      Do the same for each of the four formats.

      Delete
    2. How do i do that when its a full column of items that have different result from the list of 4?

      Delete
    3. It doesn't matter what's in the other cells. If a cell doesn't meet any of the four criteria, its format won't change.

      Delete
    4. I am lost! I can only apply one conditional format per time. How can apply four at the same time?

      Delete
    5. You don't add them at the same time. Just add one rule completely, then add a new rule in exactly the same way.

      Delete
  23. I'm trying to create a results spreadsheet in excel that in one columb where the results is higher than 1000 it highlights red, which i have done, however the result can be <10, if this is the case this cell highlights in red as well. How to i prevent this? Any help greatfully appreciated

    ReplyDelete
    Replies
    1. I'm not clear on what you want. Are you saying that you want it to highlight red if it's less than 10, or that it is and you don't want it to.

      If you want it to, then change the rule to

      =OR(A2>1000,A2<10)

      If you don't want it to, that would suggest that there is another rule being applied, so click Manage Rules, and remove the offending rule.

      Delete
  24. I am having trouble coming up with a proper conditional formatting rule for a dataset. I am running a general ledger monthly report and I am looking to make it easier to identify values that change month to month by a certain dollar amount (250, 500, 1000, 2500). So in essence I want to highlight a cell red if the difference between its value and the previous cell is 2500 (either plus or minus). Then I want to do the same for the other three deltas. And to make matters worse I want to be able to copy that formula across many cells while referencing each cell's adjacent/previous cell.

    Please help and thank you. I am trying to get something done for tomorrow.

    ReplyDelete
    Replies
    1. Ty

      If the first cell in the range want to format is C2, then highlight the whole range that you want to format and use the following formula and add rule. Use the following formula in the box:

      =ABS(C2-C1)=2500

      and set your red format when you hit the format button.

      The ABS function ignores the sign, and it is important that you don't have the dollar signs on the references so that they will apply relatively over the whole range.

      Then just add anothe rile for each of the other deltas.

      I hope that helps.

      Delete
  25. I need help. I'm trying to make my cell populate with a negative dollar amount based on if there is an "x" in another cell.

    Column B is empty. Column C is name. Column D is total cost of the order. If an order is received online then "X" is entered in column B. If the "X" is present. I need column E to be the negative number from column E. The cash is never seen in hand and we don't want to count it.

    So far I have =$B14<>""

    What I have may be wrong but it is the only way I know to indicate that the "x" needs to be there for something to happen. I have no idea what to do now though.

    ReplyDelete
    Replies
    1. Cassie

      I don't think it's conditional formatting you want. You need an IF formula in Column E itself.

      If you try (in cell E14):

      =IF(B14<>"",-D14,D14)

      That's if you want the positive value of thev rder if the x isn't there. Otherwise change that last argument to whatever you do want when there's no x.

      I wrote a post on the IF statement at:

      https://www.notjustnumbers.co.uk/2010/10/excel-tip-if-statement-made-simple.html

      I hope that helps.

      Delete