Excel - Type a Name and All Matching Cells Change to Certain Color - Episode 2078

Описание: Microsoft Excel Tutorial: Type a name and have all the matching cells change to a certain color.

Welcome to another episode of the MrExcel Podcast, where we solve your Excel problems and share valuable tips and tricks. In today's episode, we will be addressing a question sent in by one of our viewers on YouTube. They asked if there was a way to automatically highlight cells with a specific name, making it easier to spot and identify them. Well, the answer is yes, and I will show you how to do it in this video.

But before we dive into the solution, I want to take a moment to address a common issue that many of us have faced - a stuck Caps Lock key. If you're tired of screaming at your keyboard, there are plenty of videos on YouTube that can help you fix this problem. So, make sure to check those out before you send in a comment about it.

Now, let's get back to the main topic. In order to highlight cells with a specific name, we first need to type that name in a designated cell. In this case, we will use cell E1. Then, we will select all the cells where we want the matches to be highlighted. Next, we will go to the Home tab, click on Conditional Formatting, and select New Rule. From there, we will choose the option to format only cells that contain specific text, and in the first drop-down, we will select "Containing". In the second drop-down, we will choose the cell where we typed the name, making sure to use an absolute reference by adding the $ sign. Finally, we can choose a fill color and font to make the highlighted cells stand out. And just like that, we have successfully highlighted all cells with the specified name.

But what if we only want to highlight cells with an exact match? Well, that's easy too. We just need to go back to the Conditional Formatting menu, manage the rule, and edit it to say that the cell value must be equal to the cell where we typed the name. This way, only cells with the exact name will be highlighted.

And there you have it - a simple and effective way to highlight matching cells in Excel. If you want to learn more tips and tricks like this, make sure to check out my book, "Power Excel with MrExcel, the 2017 Edition", where I share 617 Excel mysteries solved. Thank you for watching, and don't forget to subscribe to our channel for more helpful Excel content. See you next time for another episode of the MrExcel Podcast.

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-...

#excel
#microsoft
#microsoftexcel
#exceltips
#exceltricks
#excelhacks
#evergreen
#excelformatting
#conditionalformat

This video answers these common search terms:
how to highlight matching cells in excel
how to highlight matches
excel highlight cells with specific name
color code cells based on name in excel
conditional formatting for name in excel
highlight matching cells in excel with color
excel color code cells by name
mark cells with specific text in excel
highlight text in cells using excel's conditional formatting

Table of Contents:
(00:00) Highlighting matching cells
(00:19) Solution to the problem
(00:29) Tips for fixing a stuck Caps Lock key
(00:40) Steps to highlight matching cells
(01:31) Testing the function and adjusting for exact matches
(02:27) Buy the book
(02:46) Topics covered in this episode
(03:00) Clicking Like really helps the algorithm

Figure out where you will type the name
Select all the cells that potentially contain a match
Home, Conditional Formatting, New Rule, Highlight Cells that Contain, Specific Text, Contains, $E$1, Click OK, OK
If you need to have it be an exact match, change Specific Text to Equals to

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