Excel 2003 used to offer an obscure conditional formatting option called Formula Is. It is still obscure in Excel 2007. Episode 933 will show you the ROW, MOD functions and a dynamic solution to formatting every other row.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Alright. It's another Where Is It Wednesday.
We’re still talking about how to do the every other row formatting.
This was Lisa's problem from Monday, and I want to use something called conditional formatting using FORMULA IS.
Let me just show you briefly.
Excel 2003.
We could choose this data and go into FORMAT, CONDITIONAL FORMATTING, and the very first dropdown here allowed us to change to FORMULA IS.
That was a very powerful way to do conditional formatting and the problem is I can't seem to find it in Excel 2007.
Sure, they have given us so many new things in CONDITIONAL FORMATTING -- DATA BARS, COLOR SCALES, ICON SETS -- but there isn't a FORMULA IS option.
Turns out that we have to come in and use more rules but, first, let's talk about the components of this format.
I want to introduce a couple of functions to you.
One of them is called the row function, =ROW of this cell, I2.
Row of I2 is going to return a number 2 because I happen to be in row 2, and watch as I copy this down.
Boy, it’s a great way to number the rows -- 2, 3, 4, 5, 6.
Do you remember all the way back in grade school when you were learning about division, alright, and you would do division, like maybe 17÷2, and your answer would be that 2 goes into 17 8 times with a remainder of 1.
I remember we used to write 8R1, meaning 8 with a remainder of 1.
Well, I’m interested in that remainder.
I'm going to use a function here called MOD.
=MOD stands for modulo, and I'm going to take this number divided by 2, and what the MOD function is going to do is it's going to return the remainder, alright?
So, basically this is a great way to show 0, 1, 0, 1, 0, 1, 0, 1, and, of course, we don't have to use that as 2 different functions.
We could just ask for the mod of the row of I2 , 2 and copy that down and get 0s and 1s, alright?
Got all that.
Here's what we do.
[ =MOD(ROW(I2),2) ] We're going to go choose all of our data from A2 on down and go into CONDITIONAL FORMATTING, HIGHLIGHT CELL RULES, and I can choose MORE RULES at the bottom, or I could just come down here and choose NEW RULE.
NEW RULE, and here, all the way at the bottom, use the formula to determine which cells to format.
That basically gets me back to the old FORMULA IS option from Excel 2003.
Alright.
Now, I look back here.
I see that I'm in cell A2.
I have to write a formula that applies to A2 although they'll take that formula and apply it to everywhere.
So, I'm going to say =MOD of the row of A2 , 2 = to 1.
When all of that is true, then I want to use a fill of maybe green.
Click OK.
Now, if we just stop now, we're going to be green, white, green, white, green, white.
If you wanted the white rows to be something else, then you would create a second rule where you check to see if the MOD of row A2 , 2 is = to 0 and apply the color there, but I'll be happy with just the green and white.
[ =MOD(ROW(A2),2)=1 ] We’ll click OK, and you see that, very quickly, it formats all of the data in the selection, and while this is much more complicated than what we talked about on Monday and Tuesday, episodes 931 and 932, the beautiful thing about this method is that it is dynamic.
If I insert some rows, the conditional formatting keeps recalculating and you see we still end up with the perfect green, white, green, white, green, white all the way down.
So, conditional formatting, while it certainly is much harder to use, provides a great method for creating dynamic green bar formatting.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
We’re still talking about how to do the every other row formatting.
This was Lisa's problem from Monday, and I want to use something called conditional formatting using FORMULA IS.
Let me just show you briefly.
Excel 2003.
We could choose this data and go into FORMAT, CONDITIONAL FORMATTING, and the very first dropdown here allowed us to change to FORMULA IS.
That was a very powerful way to do conditional formatting and the problem is I can't seem to find it in Excel 2007.
Sure, they have given us so many new things in CONDITIONAL FORMATTING -- DATA BARS, COLOR SCALES, ICON SETS -- but there isn't a FORMULA IS option.
Turns out that we have to come in and use more rules but, first, let's talk about the components of this format.
I want to introduce a couple of functions to you.
One of them is called the row function, =ROW of this cell, I2.
Row of I2 is going to return a number 2 because I happen to be in row 2, and watch as I copy this down.
Boy, it’s a great way to number the rows -- 2, 3, 4, 5, 6.
Do you remember all the way back in grade school when you were learning about division, alright, and you would do division, like maybe 17÷2, and your answer would be that 2 goes into 17 8 times with a remainder of 1.
I remember we used to write 8R1, meaning 8 with a remainder of 1.
Well, I’m interested in that remainder.
I'm going to use a function here called MOD.
=MOD stands for modulo, and I'm going to take this number divided by 2, and what the MOD function is going to do is it's going to return the remainder, alright?
So, basically this is a great way to show 0, 1, 0, 1, 0, 1, 0, 1, and, of course, we don't have to use that as 2 different functions.
We could just ask for the mod of the row of I2 , 2 and copy that down and get 0s and 1s, alright?
Got all that.
Here's what we do.
[ =MOD(ROW(I2),2) ] We're going to go choose all of our data from A2 on down and go into CONDITIONAL FORMATTING, HIGHLIGHT CELL RULES, and I can choose MORE RULES at the bottom, or I could just come down here and choose NEW RULE.
NEW RULE, and here, all the way at the bottom, use the formula to determine which cells to format.
That basically gets me back to the old FORMULA IS option from Excel 2003.
Alright.
Now, I look back here.
I see that I'm in cell A2.
I have to write a formula that applies to A2 although they'll take that formula and apply it to everywhere.
So, I'm going to say =MOD of the row of A2 , 2 = to 1.
When all of that is true, then I want to use a fill of maybe green.
Click OK.
Now, if we just stop now, we're going to be green, white, green, white, green, white.
If you wanted the white rows to be something else, then you would create a second rule where you check to see if the MOD of row A2 , 2 is = to 0 and apply the color there, but I'll be happy with just the green and white.
[ =MOD(ROW(A2),2)=1 ] We’ll click OK, and you see that, very quickly, it formats all of the data in the selection, and while this is much more complicated than what we talked about on Monday and Tuesday, episodes 931 and 932, the beautiful thing about this method is that it is dynamic.
If I insert some rows, the conditional formatting keeps recalculating and you see we still end up with the perfect green, white, green, white, green, white all the way down.
So, conditional formatting, while it certainly is much harder to use, provides a great method for creating dynamic green bar formatting.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
