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".
I've just been asked by a reader, "Can we do the same for columns??"
ReplyDeleteYes 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.
I actually create the old greenbar in alternate rows based on the following scenario:
ReplyDelete=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.
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?
DeleteThank you,
Gary
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.
DeleteEric
ReplyDeleteThanks for the input.
That's a nice simple tip to quickly create that kind of formatting.
This isn't working for me.
ReplyDeleteHello,
ReplyDeleteAll 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!
Scott
DeleteHighlight 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.
Hi
ReplyDeleteThe 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?
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.
DeleteCan 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(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?
DeleteThe 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.
DeleteThe 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.
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.
DeleteOkay. 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.
ReplyDeletebasically
ReplyDeletein 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"
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.
ReplyDeleteinstead 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.
when you do not include a dollar sign in an entire workbook rule, use cell A1 in the formula,
ReplyDeletethis means that the formatting will look individually in each cell for the value
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