Search Not Just Numbers

Tuesday 26 June 2012

Excel Tip: Using conditional formatting to format the whole row

Colour-coding can make it much easier for humans to read a spreadsheet, as our eyes and brains are wired up to treat differences in colour as important. For example, you may colour rows as red, amber or green based upon a status level - possibly, in a stock list, how close an item is to being out of stock.

If you do this, do you do it manually?


Many users know about Conditional Formatting, but do not know how to format whole rows in this way. I, for one, used it for years without knowing how to do this - but it's really simple when you know how.


It involves using Conditional Formatting's formula feature with Excel's ability to fix references using the dollar sign.


Let us take a very simple example of stock, where we wish to show lines with less than five items as red, less than ten items as amber and ten items or over as green.


Assume we have a heading row so the stock data starts at row 2, and the stock level is the last column of the data and is held in column H.


Highlight cells B2 to H1000, or down to whatever row more than covers the number of stock items you might have. Select Conditional Formatting (from the Home ribbon on Excel 2007/2010, or from the Fomat menu on Excel 2003) and select "Use a formula to determine which cells to format" (in 2007/2010) or "Formula is" (in 2003).


2003 and 2007/10 work slightly different in this respect, as 2003 allows you to add up to 3 conditions using the Add button and 2007/10 allows many rules to be added and managed.

The following formulae should be entered as the three conditions in Excel 2003, or as 3 separate rules in 2007/2010. In each case you will determine the format to be used if this condition is true using the Format button next to where you enter the formula. This works very similar to the normal dialogue box you get when formatting cells.

For the red:

Formula         =$H2<5
Format          Fill Red

For the green:


Formula         =$H2>=10
Format          Fill Green


For the amber:


Formula         =AND($H2>=5,$H2<10)
Format          Fill Orange

(read more on the AND function here, under combining conditions)

The most important thing to note here is the use of the dollar sign. What we are doing here is fixing the column (H), but leaving the row flexible, so that all cells in the highlighted range, look along their own row to column H to apply the criteria. Also note that you should enter the formula as if you were entering it for the first row of the range - this is why we have entered H2 as row 2 is the top row that we have highlighted.

One other thing to note is that the formula is always preceded by an equals sign, even if it has an equals sign in the criteria. So, if we had wanted to format red when H2 was equal to 5, we would have, rather oddly, entered =$H2=5.

This technique has many applications, and is really simple when you get the hang of it.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

19 comments:

  1. I've just been asked by a reader, "Can we do the same for columns??"


    Yes it works just the same for columns, just fix the row with the dollar rather than the column.

    e.g.

    =A$2<5

    Assuming column A is the first column of your data and the row you want to apply the criteria to is row 2.

    ReplyDelete
  2. I actually create the old greenbar in alternate rows based on the following scenario:

    =ROW()/2=int(row()/2)

    and the rows will use the formatting on every other row. It even works when you add or remove rows automatically.

    ReplyDelete
    Replies
    1. I have a scenario where I have two rows populated from different sources that get hidden and a summary row on top of them that shows. Suggestions on a formula change that would create the greenbar effect in this scenario?

      Thank you,
      Gary

      Delete
    2. Would this not work anyway? If there are two consecutive rows hidden, then they should be one green and one white, meaning that they don't interrupt the pattern.

      Delete
  3. Eric

    Thanks for the input.

    That's a nice simple tip to quickly create that kind of formatting.

    ReplyDelete
  4. This isn't working for me.

    ReplyDelete
  5. Hello,
    All I want to do is if my formula contains these numbers: 64,72,76,80,88 or 96
    to highlight them yellow. I've tried everything lol. If you can help it's most appreciated.
    Thanks in advance!

    ReplyDelete
    Replies
    1. Scott

      Highlight the range you want to apply it to.

      Select conditional formatting, New Rule, Use a formula to determine which cells to format.

      In the formula box, enter

      =OR(A1=64,A1=72,A1=76,A1=80,A1=88,A1=96)

      (if your range does not start at A1, replace A1 with the cell reference for the top left of your range)

      and then use the format button to set the yellow fill.

      I hope that helps.

      Delete
  6. Hi

    The above conditional formatting of an entire row is working well for me but when i filter on a selection of data (and the number of columns change) this also changes the conditional formatting. I should say that I am using this conditional formatting in a pivot table. Has anyone come across this issue?

    ReplyDelete
    Replies
    1. Conditional formatting in a pivot table works slightly differently. The above should still work, if the data being refrenced stays in the same place when the pivot table is refreshed. In more recent versions of Excel (certainly 2010 onwards), you can choose whether the conditional formatting applies to the cells, or to the pivot table field.

      Delete
  7. Can you explain what's happening when you include no dollar sings at all? Why does it only apply the formatting to the left most column?

    ReplyDelete
    Replies
    1. (Same Poster) In other words, why does including the dollar sign before the row you want the formatting to be based off of cause the formatting to be applied across the entire row?

      Delete
    2. The dollar sign is being used to fixing the column of the cell that it is checking against. Without the dollar sign, the reference is relative and will change.

      The reference applies to the top left cell of the range that you are applying the conditional format to (in this case B2).

      So, the condition =H2<5 will work in cell B2, but for cell C2, the reference will become =I2<5, which is why it won't work as we need it to look at column H, not column I. The dollar ensures it continues to look at column H.

      Delete
    3. Okay. I understand that including the dollar sign will cause the reference to look only at column H, but I'm still not understanding why including the dollar sign causes the formatting to be applied to the entire row, and not just to the left most column.

      Delete
  8. Okay. I understand that including the dollar sign will cause the reference to look only at column H, but I'm still not understanding why including the dollar sign causes the formatting to be applied to the entire row, and not just to the left most column.

    ReplyDelete
  9. basically
    in A1 Column(Letter)Row(number) notation,
    "$" before the column ($A1) means "only look at Column A"
    "$" before the Row (A$1) means "only look at Row 1"
    "$" before Both ($A$1) means "only look at cell A1"

    ReplyDelete
  10. so what happens in formatting is that if you apply the formatting to =$A:$I (columns A through I inclusive), it will look at every single Cell in that range.

    instead of having to key in separate rules for A1, B1, C1, D1, E1, F1, G1, H1, I1 to look at Cell A1, using $A1 tells the formatting system to base the cell format off the value in the referenced column.

    ReplyDelete
  11. when you do not include a dollar sign in an entire workbook rule, use cell A1 in the formula,
    this means that the formatting will look individually in each cell for the value

    ReplyDelete
    Replies
    1. Thanks for the input, although I wouldn't encourage applying conditional formatting to a whole worksheet, or even a whole column if you can avoid it, as this can really start to slow down a workbook.

      Delete