Saying good bye to 2010 and welcoming in 2011, Mike "Excel Is Fun" Girvin and Bill "MrExcel" Jelen present you with "Red Over 140". In todays Dueling Excel Podcast [Episode #1316] Mike and Bill use a variety of methods to track values that exceed a predetermined threshold. Maybe you'll also need information in cells adjacent to the exceeded value - they'll show you how to get that too!
Transcript of the video:
Hey, welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel, Mike Girvin from Excel Is Fun will be joining us.
Hey, Mike this is either the last podcast of 2010 or depending if people aren't there on Friday, they catch us on Monday the first one of 2011.
So, Happy New Year either way.
Question today is we have some data here and we want to highlight any cells where the blood pressure goes over this hurdle of 140.
So, we want to have something that looks like this and change it into this, lots of different ways to do this.
I'm just going to do really, really easy way, I'm going to choose the range where they are entering that data.
Normally, I'd go to Home and then in the Styles group, Conditional Formatting, but that's going to run off my screen.
So, I taken this Conditional Formatting, I right click and said to add to quick access toolbar.
So now, it's right up there, okay.
So, we're going to go to Highlight Cells Rules, everything Greater Than and I'm going to say Greater Than 1 39.9999 I'm and that will force it to be 140.
I don't like Light Red Fill with Dark Red Text, I'm going to do a Custom Format and there what we're going to do, want to do the fill of red the font of white.
click OK, click OK.
All right! So, there you have anything over 130 I have such as a 140 will handle that, will just try something here 144, it's over 99 under and it doesn't get formats, there we go.
Mike, let's see what you have.
Mike: Hey, thanks MrExcel.
Hey, Happy New Year to you and to everyone out there watching.
Hey, blood pressures 140, so the doctor says we have to watch.
So, we want to add some Conditional Formatting, and what I like about MrExcel, I like the fact that we just do Highlight Cell Rules, Greater Than, boom there's a built-in feature right there Greater Than.
I'm going to go down to More Rules, this was Greater Than, I'm going to do a greater than or equal to.
Now, instead of clicking More Rules I'm going to click Escape here and I'm going to use the keyboard shortcut to open up Conditional Formatting, this works in all versions.
I'm going to highlight all the way down the keyboard shortcut is Alt+O+D, Alt+O+D and then I'm going to say New Rule and the one I want is Format only cells that contain, the nice thing about this is here's the Cell Value and I have a drop down that has lots of different options what do I want greater than or equal to, not only that, but I love when I see this collapsed, I love when I see this collapse dialog box because it means i can link this in essence true/false formula to something in the cell.
So, I'm going to click on this right there there and now, it's link there.
So, I can change the cell and the Conditional Formatting will change, I'm going to say Font, red fiil, I mean Font white, fill red, click OK, click OK, and boom I'm going to do that same test 144, very good, 99 very good and now, if I change this 130, let's say, the doctor says, we need to check lower than that boom, it's all done.
How about this what if we didn't want just the number, but the date to which means in essence we want the whole row highlighted based on a value in this cell here.
Let's go down here and the key to doing and by the way I don't think I know, I don't know how to do a built-in feature and Conditional Formatting to highlight a whole row.
So, I'm going to do a true/false formula and the trick to understanding true/false formulas is, hey, Conditional Formatting is just true, I want to format it or false I don't want to format it.
So, I'm going to highlight this whole range here and when you're doing true/false formulas, you got to be highlight the whole range and be aware of where the active cell is.
We have to build the true/false formula from the point of view of that cell.
All right, highlighted active cell right there, I'm going to Alt+O+D, New Rule on down at the bottom use formula, click right here, there's that collapse button, I'm simply going to say what...
Well, for this row right here even though the active cell is right there, that cell and this cell for this rows needs to be looking there.
So, I'm gonna say boom that right there, there's two dollar signs I want it locked going this way, but when I copy down I need to move to the next blood pressure value.
So, I'm going to hit the F4 once and twice a dollar sign in front of B, says the column is locked, but not the row, so when I move it down those dancing ants will move down to the next one greater than or equal to and I'm going to click on that there locked in all directions that will work, I'll click Formatting, Fill red, Font white, click OK, click OK, click OK.
Oh! Just as we suspected it looks like it's going to work 144, 99 and I can go ahead and change this also.
All right, I'll throw it back to MrExcel.
Bill: Hey! Mike, okay now, maybe think of a couple of things when I watched your method of doing that, one in Excel 2007 in the beta, there was a way to highlight the whole row in the table, but they took it out I guess I wasn't working.
So, I love your method using formulas you know, also there's a really, really old school way of doing this.
Let me see if I can remember, I'm going to go into Format Cells that's Control+1, I'm going to go into Custom Format and the code that I'm going to type here in square brackets.
I'm going to say greater than or equal to 140, close the square bracket, if that's true, then I want red, close the square bracket and then the format code zero semicolon.
So, in other words if it's not greater than 140, then we just go back with a regular old format of black or white, click OK and what that does is anything greater than equal to 140, I will change it to red the colors you can use their I think are the big seven red, blue, cyan, black, green, maybe yellow. I forget white.
So, you know, not, not that many different alternatives but if you're just looking to change the color of the cells red custom number formatting a great way to go.
Hey! All right, well, I want to thank everyone for stopping by.
See you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, Mike Girvin from Excel Is Fun will be joining us.
Hey, Mike this is either the last podcast of 2010 or depending if people aren't there on Friday, they catch us on Monday the first one of 2011.
So, Happy New Year either way.
Question today is we have some data here and we want to highlight any cells where the blood pressure goes over this hurdle of 140.
So, we want to have something that looks like this and change it into this, lots of different ways to do this.
I'm just going to do really, really easy way, I'm going to choose the range where they are entering that data.
Normally, I'd go to Home and then in the Styles group, Conditional Formatting, but that's going to run off my screen.
So, I taken this Conditional Formatting, I right click and said to add to quick access toolbar.
So now, it's right up there, okay.
So, we're going to go to Highlight Cells Rules, everything Greater Than and I'm going to say Greater Than 1 39.9999 I'm and that will force it to be 140.
I don't like Light Red Fill with Dark Red Text, I'm going to do a Custom Format and there what we're going to do, want to do the fill of red the font of white.
click OK, click OK.
All right! So, there you have anything over 130 I have such as a 140 will handle that, will just try something here 144, it's over 99 under and it doesn't get formats, there we go.
Mike, let's see what you have.
Mike: Hey, thanks MrExcel.
Hey, Happy New Year to you and to everyone out there watching.
Hey, blood pressures 140, so the doctor says we have to watch.
So, we want to add some Conditional Formatting, and what I like about MrExcel, I like the fact that we just do Highlight Cell Rules, Greater Than, boom there's a built-in feature right there Greater Than.
I'm going to go down to More Rules, this was Greater Than, I'm going to do a greater than or equal to.
Now, instead of clicking More Rules I'm going to click Escape here and I'm going to use the keyboard shortcut to open up Conditional Formatting, this works in all versions.
I'm going to highlight all the way down the keyboard shortcut is Alt+O+D, Alt+O+D and then I'm going to say New Rule and the one I want is Format only cells that contain, the nice thing about this is here's the Cell Value and I have a drop down that has lots of different options what do I want greater than or equal to, not only that, but I love when I see this collapsed, I love when I see this collapse dialog box because it means i can link this in essence true/false formula to something in the cell.
So, I'm going to click on this right there there and now, it's link there.
So, I can change the cell and the Conditional Formatting will change, I'm going to say Font, red fiil, I mean Font white, fill red, click OK, click OK, and boom I'm going to do that same test 144, very good, 99 very good and now, if I change this 130, let's say, the doctor says, we need to check lower than that boom, it's all done.
How about this what if we didn't want just the number, but the date to which means in essence we want the whole row highlighted based on a value in this cell here.
Let's go down here and the key to doing and by the way I don't think I know, I don't know how to do a built-in feature and Conditional Formatting to highlight a whole row.
So, I'm going to do a true/false formula and the trick to understanding true/false formulas is, hey, Conditional Formatting is just true, I want to format it or false I don't want to format it.
So, I'm going to highlight this whole range here and when you're doing true/false formulas, you got to be highlight the whole range and be aware of where the active cell is.
We have to build the true/false formula from the point of view of that cell.
All right, highlighted active cell right there, I'm going to Alt+O+D, New Rule on down at the bottom use formula, click right here, there's that collapse button, I'm simply going to say what...
Well, for this row right here even though the active cell is right there, that cell and this cell for this rows needs to be looking there.
So, I'm gonna say boom that right there, there's two dollar signs I want it locked going this way, but when I copy down I need to move to the next blood pressure value.
So, I'm going to hit the F4 once and twice a dollar sign in front of B, says the column is locked, but not the row, so when I move it down those dancing ants will move down to the next one greater than or equal to and I'm going to click on that there locked in all directions that will work, I'll click Formatting, Fill red, Font white, click OK, click OK, click OK.
Oh! Just as we suspected it looks like it's going to work 144, 99 and I can go ahead and change this also.
All right, I'll throw it back to MrExcel.
Bill: Hey! Mike, okay now, maybe think of a couple of things when I watched your method of doing that, one in Excel 2007 in the beta, there was a way to highlight the whole row in the table, but they took it out I guess I wasn't working.
So, I love your method using formulas you know, also there's a really, really old school way of doing this.
Let me see if I can remember, I'm going to go into Format Cells that's Control+1, I'm going to go into Custom Format and the code that I'm going to type here in square brackets.
I'm going to say greater than or equal to 140, close the square bracket, if that's true, then I want red, close the square bracket and then the format code zero semicolon.
So, in other words if it's not greater than 140, then we just go back with a regular old format of black or white, click OK and what that does is anything greater than equal to 140, I will change it to red the colors you can use their I think are the big seven red, blue, cyan, black, green, maybe yellow. I forget white.
So, you know, not, not that many different alternatives but if you're just looking to change the color of the cells red custom number formatting a great way to go.
Hey! All right, well, I want to thank everyone for stopping by.
See you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
