SEARCH

How-To Geek

Using Conditional Cell Formatting in Excel 2007

If you are working on a spreadsheet and want to be able to quickly see differences in numbers at a glance, you should use the new Conditional Cell Formatting in Excel 2007. With this you can change the background of a cell based on the data in the cell, almost like an inline chart.

Using Conditional Formatting

Once you select the cells that you want to format, you can find the Conditional Formatting drop-down on the ribbon, which gives you loads of pre-set rules to choose from, like color scales.

image

This will automatically color the items based on the range they are in…

image

You can also choose to use icons instead:

image

Reminds me of that commercial… more bars in more places…

image

You can also create your own rule to color only cells that fit within a certain range, or greater than a certain number.

image

This rule will only color cells greater than a certain amount, with the color you specify.

image

This can be very useful, and you should note that you can apply a second rule to the same range. For instance, I added a second rule that colored items larger than 2000 with green instead of red.

image

You can even create a completely customized rule by choosing the New Formatting Rule option.

image

If you’ve applied too many rules and want to start over, just select the cells and use the Clear Rules option to remove all the formatting.

image

This is one of those really useful features that make Office 2007 a lot better than the prior versions.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 08/18/08

Comments (38)

  1. Harry Bedford

    Conditional formatting is not new; it has been available since Excel 97. But it is improved and much easier to use in Excel 2007.

  2. The Geek

    @Harry

    Good point. Definitely much easier to use.

  3. Willie

    I have Excel 2007 and I need to conditally format (fill with color) rows based on text rather than a numeric value. I have five worksheets, and in all of those worksheets I need the rows highlighted based on BrandOne, BrandTwo, BrandThree… arrrgh… I can’t seem to get the formula right to get the entire row highlighted. When I say “BrandOne” it only highlights that cell and not the entire row… Heeeellllp!!!

  4. Jim

    Basic and very useful. Next question is: Why is my conditional format icon not accessible — screened back meaning not useable now.

  5. andi

    i cannot seem to find my conditional formatting button on excel 2007 vista

  6. Shawn

    I’m wondering if anyone knows how to apply conditional formatting to compare 2 cells and highlight the cell with the higher dollar amount? Any suggestions?

  7. raju

    how to make condition in two different column

    col 1 col2
    ex ex
    vg ex

    if both col if same than no change

    `if both column is different than highlight

  8. Ty Myrick

    Is it possible to copy or export conditional formatting rules? I find myself making the same rules over and over on new spreadsheets.

  9. Marc

    I have Excel 2007 and I need to conditionally format (fill with color) rows based on text rather than a numeric value. I can get the cell formula right but not the entire row to be highlighted.

  10. Umbreen

    I would like to set some conditional formats in one column based on what is another column. If the value is above, below, 1 number below, 2 numbers below and 3 numbers below should all come up as different colours. Can anyone help to do this please?

  11. Sarah

    Hey

    I am trying to set up a workbook which will change cell colour on each full row as details are added in subsequent columns. I am having trouble getting this set up with formulas as it doesn’t appear to work using text details….:( can you please help?

    Thanks
    Sarah

  12. Derek

    I have a similar question as Umbreen. How can I set a conditional format is column A based on what column B says? Basically I want the font in A:1 to be green if B:2 = “Paid” and A;1 is red if B:2 = “Owed”. Help would be much appreciated!

  13. Michael

    I have a spreadsheet which contains columns called Town and then columns for the months in the year with dollar amounts.

    When the town is selected, I would like the entire row to be selected and have formatting applied to differentiate from the other rows. When a different town is selected the previously selected row would go back to standard formatting.

    What is so frustrating about this is how easy it would be to do in a web page but the business requirements necessitate the use of Excel.

  14. Gurt

    All nice and dandy, except that Excel 2007 breaks Excel 2003 conditional formating. Take for example a cell A1 that should change color depending on the values in B1. Now, that does certainly work but the color in A1 only changes when you enter cell A1. Furthermore, the time-tested method of wrting a condition in relative terms, i. e. use B1 instead of $B$1, and then dragging the whole she-bang across a row or line and have Excel automatically adapt the formula is not working anymore. Excel 2007 happily extends the cells which will obey the rules but does not adapt the rules of the formula, yay!

    In my office we relied on Excel-sheets for data entry (whyever the boss is set against a simple Access-DB is beyond me) which would change cell colors depending on what input was expected next — this is now not working anymore and guess who is tasked with figuring this shit out?

  15. Rita

    I want to highlight the values in column H if they are greater than 8 but only if the value in column E is “yes”. I’ve tried many different ways but none return the results I’m looking for. Any help would be appreciated!

  16. None

    Rita,

    In 07 click cell and go to comditional formatting then new rule. select the last one Use a formula… Use the format button to make the cell look like you want if the condition is met. Then for the formula put =and(H1>8,E1=”yes”)

  17. matt hahn

    dear geek,

  18. matt hahn

    how do i add something from cell b14 to b15 it’s so hard can you please help!

  19. Darryl Long

    How many rules can you set for a cell or group of cells with the conditional formatting in Excel.

  20. shashi

    plz tell me how to calculate the amount in microsoft excel office and how to use the tey’s

  21. David

    Trying to input a conditional format to highlight cell (character) red if the date inputed is overdue.

    Example:

    1/28/12 I wan’t the cell to turn red on 1/28/12. And if possible turn Orange 30 days prior to expiration.

    Any suggestions?

  22. Cupcake

    Horrid

  23. Sarah

    Hi,

    I am struggling to use conditional formatting for an IF and AND statement for design and costing calculations for a chemical reaction vessel.

    Cell C13 contains the vessel diameter with specified values that the user can choose from (0.3, 1, 3 m). The length of this vessel is calculated based on the annual capacity, volume and diameter also specified by the user. If the diameter is 0.3 m, the length can only be 2-20 m; if the diameter is 1 m, the length can only be 2-40 m; if the diameter is 3 m, the length can only be 8-50 m. If the length exceeds the limits stated above, due to the diameter inputed by the user not appropriately matching the annual capacity, I want the length cell to be highlighted (cell C24), so the user can be alerted. I cannot however get this to work.

    I want to use a similar statement to this (below), so that FALSE produces the cell to be highlighted.

    =IF(AND(C13=0.3,2<C24<20),TRUE,IF(AND(C13=1,2<C24<40),TRUE,IF(AND(C13=3,8<C24<50),FALSE)))

    What is the correct statement to use for this situation?

  24. Greg G

    Try:

    =IF(AND(C13=0.3,2<C24,c24<20),TRUE,IF(AND(C13=1,2<C24,c24<40),TRUE,IF(AND(C13=3,8<C24,c24<50),FALSE)))

    or this:
    =IF(OR(AND(C13=0.3, 2<24, C24<20), AND(C13=1, 2<C24, C24<40), AND(C13=3, 8<C24, C24<50)), TRUE, FALSE)

    Both formulas produce the same result:
    if C13=0.3 AND if 2 is less than C24 AND if C24<20
    or
    if C13=1 AND if 2 is less than C24 AND if C24<40
    or
    if C13=3 AND if 8 is less than C24 AND if C24<50

    Now, wherever your formula is, just use conditional formatting to highlight the cell(s) if it equals FALSE.

  25. Greg G

    umm in that second formula, in the first AND statement, change then 2<24 to 2<C24

    because, 2 is always less than 24..

  26. Greg G

    @David:

    How are you determining if something is overdue?
    if it’s 1/28/2012 and it’s overdue, then when was it due? 1/27/2012?
    Do you have a column of due dates and another column with date completed?

  27. Greg G

    @Matt Hahn

    I know your question is a bit old, but hasn’t been answered here.
    If I understand you correctly, you want to append whatever your formula result is with whatever is in cell B14.
    So, in cell B15:
    =YourFormula&B14

    The ampersand (&) will combine your formual and whatever appears in B14. ie: =30+50&B14. If you want a space between the formula result and B14′s data: =30+50&” “&B14.

    Basically, use the & symbol to combine cells and text into one cell.

  28. naven

    I have to maintain the master price list for my company, and our selling prices change frequently, i need to submit these changes to the finance team, and often i forget which prices i have amended and it becomes very difficult for the finance office to recall what the new price change, she has to go through the master price list to see what i have changed. We use excel 2007, please tell me the way where the cells highlights if i change the old price to new price..

  29. Clarke

    Is it possible to save, copy or export conditional formatting rules? I find myself making the same rules over and over on new spreadsheets.

  30. Roxanne

    I would like to highlight cells if two values (i.e. columns A,B) in the same row are equal. I have Excel 2007. Thanks :)

  31. Roxanne

    I would like to highlight cells if two values (i.e. columns A,B) in the same row are equal. I have Excel 2007. Thanks

  32. Dave

    I need to highlight dates as in expiry dates. The expiry date is entered and if its within 30 days it needs to be red and within 90 days Yellow. I’m having trouble applying this to different rows and cells etc. Can anyone help?

  33. Roxanne

    Dave– in Excel 2007 you select the applicable cells, go to the Home tab, click Conditional Formatting, go to Highlight Cells Rules, and select “A Date Occurring.” Frustratingly, at distant dates you do not get many options; you could make “this month” red and “next month” yellow. In the second drop-down box, you will have to select “custom format” and go to the Fill tab.

  34. Jonathan

    I need to have my cell A1 in red when whatever is writen in B1. Can someone help me?

  35. Emma

    Hi there

    Please could you help me? I am trying to format a cell so that it highlights when there is a value in the adjacent cell, but no value in the cell I am formatting.

    Just in case that is not clear – cell A1 will have a value copied through from a different cell (say, D1) when an invoice needs to be raised. Cell B1 will hold the date in which the invoice was raised. I want cell B1 to highlight when there is a value in A1 and no date for a raised invoice. I think I am right in thinking that the conditional formatting needs to be in relation to the original cell information (D1) rather than A1, but I can’t seem to get any futher than that.

    Please could someone help?

  36. Alan

    *** Can someone please help!!!

    How do I use the Icon set if I intent show negative numbers red “stop” light and positive number in green “go” light. (I am in % for my calculation.)

    Would really appreciate your help

    Thanks

  37. Michelle

    =OR($O2>=25,AND($O2>=10,$P2>=55,AND($O2>0,$P2>=57)))

    what does the above mean?

  38. Michelle

    Can you help? I guess a more accurate question is what does the differences between these two formulas mean? Thank you.
    =OR($O2>=25,AND($O2>=10,$P2>=55,AND($O2>0,$P2>=57)))
    =OR($O2>=25,AND($O2>=10,$P2>=55,AND($O2>=0,$P2>=57)))

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!