Rod calls in a question today; how to use conditional formatting to highlight all the track athletes from a particular school. This requires the conditional format to look for the school name within a cell. This is very easy in Excel 2007 but very difficult in Excel 2003. Episode 517 discusses the functions required to make it work.
This blog is the video podcast companion to the book, Learn Excel from MrExcel and Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel and Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel podcast. I'm Bill Jelen.
Today We have a question sent in by Rod.
If you have a question just call us leave us question as a voice mail or drop me an email whichever works. Let's take a listen to rods question.
Hey Bill, its Rob from Cincinnati.
I downloaded some information for my kids high school that shows the regional track meet coming up.
And I'm trying to do a conditional formatting to highlight all of the athletes from our school that made the regionals.
So I want to do a conditional formatting that looks for our school name, but I don't know how to do that.
I can, I know, you could do like a find and it finds it, but that returns a value.
I'm just trying to get a true or false if it's included in the text, thanks, bye.
Well, hey Rod. That was a great question. I have to tell you unfortunately, this is hard to do in excel 2003.
I just want to entice you a little bit.
I want to show you how easy it is in excel 2007.
Microsoft really realized that this was a painful process.
If we use Conditional Formatting and then go to Highlight Cells Rules.
There's now an option that says "Text that Contains" Very easy. Just type the name of your school and you've solved the problem. Now I realize you must be in excel 2003.
So let's go back and take a look at the problem in excel 2003.
Let's go to "Format" "Conditional Formatting" Now first thing we have to do is change the first drop down from "Cell Value Is" to "Formula Is" We can now write a formula that evaluates to true or false and this formula has to be referencing the top left cell and the range so in this case is A1.
So =NOT(ISERROR(FIND("Lake",A1))) to make it all work.
We'll just choose some sort of a color here.
So maybe we want to highlight those in blue.
Click OK Click OK and you'll see that all of the cells that contain in this case Lake show up in dark blue.
So Rod great question very hard to do in excel 2003.
But certainly possible easier in excel 2007. I'm going to take a second here for a blatant plug.
Rod since you talked about track and field, one of the projects that I'm involved in is to have students write books.
Couple of Summers ago, we had a couple of cousins write a book in this program.
The name of the book is "Harriers" It's a story about a high school cross country team.
Right now we have a brand new promotion going on trying to get this book out for all the kids just finishing track season or for those looking ahead to cross country season in the fall.
To check it out go to www.harriers.cc Great program couple of great kids and the book is really doing very very well at Amazon. Great book for all of your track and cross-country athletes.
Hey thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today We have a question sent in by Rod.
If you have a question just call us leave us question as a voice mail or drop me an email whichever works. Let's take a listen to rods question.
Hey Bill, its Rob from Cincinnati.
I downloaded some information for my kids high school that shows the regional track meet coming up.
And I'm trying to do a conditional formatting to highlight all of the athletes from our school that made the regionals.
So I want to do a conditional formatting that looks for our school name, but I don't know how to do that.
I can, I know, you could do like a find and it finds it, but that returns a value.
I'm just trying to get a true or false if it's included in the text, thanks, bye.
Well, hey Rod. That was a great question. I have to tell you unfortunately, this is hard to do in excel 2003.
I just want to entice you a little bit.
I want to show you how easy it is in excel 2007.
Microsoft really realized that this was a painful process.
If we use Conditional Formatting and then go to Highlight Cells Rules.
There's now an option that says "Text that Contains" Very easy. Just type the name of your school and you've solved the problem. Now I realize you must be in excel 2003.
So let's go back and take a look at the problem in excel 2003.
Let's go to "Format" "Conditional Formatting" Now first thing we have to do is change the first drop down from "Cell Value Is" to "Formula Is" We can now write a formula that evaluates to true or false and this formula has to be referencing the top left cell and the range so in this case is A1.
So =NOT(ISERROR(FIND("Lake",A1))) to make it all work.
We'll just choose some sort of a color here.
So maybe we want to highlight those in blue.
Click OK Click OK and you'll see that all of the cells that contain in this case Lake show up in dark blue.
So Rod great question very hard to do in excel 2003.
But certainly possible easier in excel 2007. I'm going to take a second here for a blatant plug.
Rod since you talked about track and field, one of the projects that I'm involved in is to have students write books.
Couple of Summers ago, we had a couple of cousins write a book in this program.
The name of the book is "Harriers" It's a story about a high school cross country team.
Right now we have a brand new promotion going on trying to get this book out for all the kids just finishing track season or for those looking ahead to cross country season in the fall.
To check it out go to www.harriers.cc Great program couple of great kids and the book is really doing very very well at Amazon. Great book for all of your track and cross-country athletes.
Hey thanks for stopping by. We'll see you next time for another netcast from MrExcel.
