Excel Formula Conditional Formatting Tips
Hey, welcome back to the Mr. Excel net cast, I’m Bill Jelen. Basic, we start out with massive amounts of data and so how we’re going to analyze this well, let’s—and see if we can solve this problem.
Hey welcome back to the Mr. Excel net cast. I'm Bill Jelen. This question sent in by William. William has a question and he says he wants to use conditional formatting to highlight rows based on a start dating column A and a duration in column B. And what I’m guessing is he’s looking for projects that are past due or something like that. So, before we even get into the conditional formatting, I want to just solve this in the spreadsheet. I’m going to put a formula up here “equal today” which gives us today’s data course. And then I want to see if it's a past due so to figure out when its due we would take “A2+B2” and that gives us the due date and then to see if that’s past due, I would use a formula saying is it less than today, today function that we used up there in “E1” and just coming down here. So, we have March 7 + 13 days certainly last of May 5th so that is past due so we get a nice little range of trues and falses are now.
I'm not going to automatically used that in the conditional format. I just wanted to get my head wrapped around exactly how this is going to work. Conditional formatting needs a little bit of dollar signs. So, we have a nice formula there “=$A2+$B2
No Links were listed yet. Go ahead and share!
Hey, welcome back to the Mr. Excel net cast, I’m Bill Jelen. Basic, we start out with massive amounts of data and so how we’re going to analyze this well, let’s—and see if we can solve this problem.
Hey welcome back to the Mr. Excel net cast. I'm Bill Jelen. This question sent in by William. William has a question and he says he wants to use conditional formatting to highlight rows based on a start dating column A and a duration in column B. And what I’m guessing is he’s looking for projects that are past due or something like that. So, before we even get into the conditional formatting, I want to just solve this in the spreadsheet. I’m going to put a formula up here “equal today” which gives us today’s data course. And then I want to see if it's a past due so to figure out when its due we would take “A2+B2” and that gives us the due date and then to see if that’s past due, I would use a formula saying is it less than today, today function that we used up there in “E1” and just coming down here. So, we have March 7 + 13 days certainly last of May 5th so that is past due so we get a nice little range of trues and falses are now.
I'm not going to automatically used that in the conditional format. I just wanted to get my head wrapped around exactly how this is going to work. Conditional formatting needs a little bit of dollar signs. So, we have a nice formula there “=$A2+$B2
Now, for those of you in Excel 2003, this is relatively different. Let’s take a quick look. We got a format, conditional formatting and it does not appear to offer the formula is but you have to open this dropdown and choose formula and then we paste the same formula in there that we did in Excel 2007. So, it’s still possible in the old Excel just a little bit more hidden. I want to thank William for sending in that question. I want to thank you for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Well, thanks for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services