Todays guest podcaster is MVP Zack Barresse. Zack shows how to apply a greenbar formatting using a cool conditional formatting formula. Episode 470 shows you how.
Transcript of the video:
Well come to MrExcel netcast.
I'm Bill Jelen.
We're still here in Seattle. I now have Zach Barresse.
Now, Zach if you hang out the MrExcel board.
You know, Zach as firefighter EMT. He's here. He's gonna show us a cool trick in Excel with Conditional Formatting.
Okay, Now, look at the screen. Hope they can see the screen.
Just formatting every other row you want highlight it.
So, would be highlighting every even row here and what we want to do is, do a formula in Conditional Formatting and for just formatting, go to Formula Is and we're going to make just a mod formula.
Which..
Takes two conditions. We want to leave a blank row a formula in there. I want to say mod to basically, divisor 2 and when I say every time that equals zero or which, I'm not compute we want it to come true.
We're working on a true condition here.
So, set our formula, set our format and every time this formula evaluates to true.
We will get in every other row on even.
Now, what happens if you insert a new row?
Insert a new row the Conditional Formatting won't change as its relative reference.
So, you'll still have every other row even though your data extends and the same will work for delete.
And you can do the same for if you want odd rows, you can still leave the same mod formula in there, but you just change the equal sign to 1.
It would be the opposite divisor, and you'll get every odd row.
That's so cool. Now, tell me about the ROW function.
What is the ROW function do without any grammars?
What the ROW functional do is...
Just put over there in D1 or it sinking in.
It'll just return the current row of the cell the formula is in.
That's row 2 very cool.
Row 2, yeah, and you can fill down, and it'll give you a dynamic row and when you insert the row will grow and return whatever row it's in.
So it's a Dynamic formula.
Now, Zach hang on. I never give two tips in one day.
They're going to start saying we want Zach to do the podcast.
That's good. Thanks Zach.
Thank you.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
We're still here in Seattle. I now have Zach Barresse.
Now, Zach if you hang out the MrExcel board.
You know, Zach as firefighter EMT. He's here. He's gonna show us a cool trick in Excel with Conditional Formatting.
Okay, Now, look at the screen. Hope they can see the screen.
Just formatting every other row you want highlight it.
So, would be highlighting every even row here and what we want to do is, do a formula in Conditional Formatting and for just formatting, go to Formula Is and we're going to make just a mod formula.
Which..
Takes two conditions. We want to leave a blank row a formula in there. I want to say mod to basically, divisor 2 and when I say every time that equals zero or which, I'm not compute we want it to come true.
We're working on a true condition here.
So, set our formula, set our format and every time this formula evaluates to true.
We will get in every other row on even.
Now, what happens if you insert a new row?
Insert a new row the Conditional Formatting won't change as its relative reference.
So, you'll still have every other row even though your data extends and the same will work for delete.
And you can do the same for if you want odd rows, you can still leave the same mod formula in there, but you just change the equal sign to 1.
It would be the opposite divisor, and you'll get every odd row.
That's so cool. Now, tell me about the ROW function.
What is the ROW function do without any grammars?
What the ROW functional do is...
Just put over there in D1 or it sinking in.
It'll just return the current row of the cell the formula is in.
That's row 2 very cool.
Row 2, yeah, and you can fill down, and it'll give you a dynamic row and when you insert the row will grow and return whatever row it's in.
So it's a Dynamic formula.
Now, Zach hang on. I never give two tips in one day.
They're going to start saying we want Zach to do the podcast.
That's good. Thanks Zach.
Thank you.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
