Look Who it Was Random video

Excel - Highlighting School Athletes with Conditional Formatting in Excel & Excel - Episode 517

568 views · Published 1 September 2009 · 3:16 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Highlighting School Athletes with Conditional Formatting in Excel & Excel.

Welcome back to the MrExcel podcast, where we answer your Excel questions and provide helpful tips and tricks. In this episode, we have a question from Rod in Cincinnati about using conditional formatting to highlight specific data in a large dataset. Rod is trying to highlight all of the athletes from his kids' high school who made it to the regional track meet, but he's having trouble figuring out how to do it. Let's take a listen to Rod's question and see if we can help him out.

Rod, thanks for your question. Unfortunately, this can be a bit tricky to do in Excel 2003, but I want to show you how easy it is in Excel 2007. Microsoft realized that this was a common issue and made it much simpler to accomplish. All you have to do is use the "Text that Contains" option under Conditional Formatting and type in the name of your school. But I understand that you may be using Excel 2003, so let's take a look at how to do it in that version.

In Excel 2003, we'll need to use a formula to evaluate whether the cell contains the school name or not. We'll use the "Formula Is" option under Conditional Formatting and write a formula that references the top left cell and the range. In this case, we'll use =NOT(ISERROR(FIND("Lake",A1))) to highlight all cells that contain the word "Lake". You can then choose a color to highlight these cells and make them stand out. While this may be a bit more complicated, it is still possible to achieve the desired result in Excel 2003.

Before we wrap up, I want to take a moment for a shameless plug. Since you mentioned track and field, I want to share a project that I'm involved in where students write books. One of the books that came out of this program is called "Harriers" and it's a story about a high school cross country team. We currently have a promotion going on to get this book out to all the kids finishing track season or looking ahead to cross country season in the fall. You can check it out at www.harriers.cc and support a great program and some talented young authors.

Thanks for tuning in to this episode of the MrExcel podcast. We hope this helped you with your conditional formatting question, and we'll see you next time for more Excel tips and tricks. Don't forget to leave us a voicemail or send us an email with your Excel questions, and we'll do our best to answer them in future episodes. Thanks for watching!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Question from Rod
(00:26) Conditional formatting for school athletes
(01:02) Solution in Excel 2007
(01:30) Solution in Excel 2003
(02:01) Formatting the cells
(02:21) Blatant plug for "Harriers" book
(02:56) Clicking Like really helps the algorithm

This video answers these common search terms:
Cell Value Is
Conditional formatting
Excel 2003
Excel 2007
FIND function
Formula Is
Harriers book promotion
Highlight Cells Rules
ISERROR function
MrExcel podcast
Text that Contains
Track and field


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!

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152594/

More from this channel