Highlight Cells that Match with Conditional Formatting
HTML-код
- Опубликовано: 21 авг 2024
- Say you have a table of records and you wanted to show where there were matches to a value. You could use the find function to do this but what if you wanted to have it show more? Like have it highlighted in a color? There is a way to do this and it's with the Conditional Formatting feature in Excel. You input a value somewhere and Excel will search for it in your table and highlight the rows that have that value. I'll show you a way to do this with as using a case sensitive (matching upper case or lowercase) and case in-sensitive match. See the video to learn how.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
![[TF2] The Russian Bear Trap](http://i.ytimg.com/vi/YFNSwz2_rOA/mqdefault.jpg)
For more videos that cover lookup concepts see the playlist at ruclips.net/p/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l
...
Thank you! Exactly what I was looking for.
Glad you liked!
Yeah it's an amazing video for match. I also came through one more video on "off to office". Must watch video actually. It has actually trained me with all possible situations of matching highlight comparing.
Thanks Doug! This is the 2nd time you've really helped me
Glad to hear it! Glad it helped you!
Hey cool.... USVI in the House!!! Got excited when I saw that. Let me finish the video.
Hi Juelis M Hodge, thanks for the comment!
THANK YOU SO MUCH! I had to generate a report with this functionality and works as expected.
Glad it helped!
Simple but very usefull... thank you Doug
Thanks Paulo Yvens!
Hey U.S Virgin Islands! That's where I'm from! Thanks for the help
Hi Bianka Telemaque, glad you liked it, thanks for commenting!
Thanks bro.
I am Indian
You’re welcome!
What if I wanted to highlight from a list and not just two items? Let's say I have 100 items in List A, and I want List B (containing 30 items) to be highlighted in List A?
Try this video ruclips.net/video/YLzm9MkDgr4/видео.html
Hi! This was really helpful. I already did what you taught in the video but is there a way where the highlighted cell will not be replaced once you change then details on the Enter SKU? That would be really helpful! Thank you
not that i know of...a workaround could be to have a helper column that incorporate the last value it changed from and let the conditional formatting check both cells
I only want to highlight duplicate cells in FILTERED rows, only in selected rows. Instead it’s highlighted everything because it’s checking all the table. Of course all will be duplicate if you check the whole table. I need to see unique numbers in my filtered results
Miss Viking, that is very interesting...thanks for letting me know.
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it, please?
Sorry, don’t understand the comment/ question as this is what the conditional formatting will do and even given multiple conditions will take it in linear priority (first to last). Another option is to manually do it with find replace advanced like this >> ruclips.net/video/5N6uwP8atzA/видео.html
Yeah it's an amazing video for match. I also came through one more video on "off to office". Must watch video actually. It has actually trained me with all possible situations of matching highlight comparing....
Thanks sir
Welcome!
Can we change the formatting (e.g. color) of some arbitrarily selected cells of a table in Word using a selection of combo box or dropdown list content control so that only the cells of the selected selection are formatted (active)... one color enough...
How can I get all highlighted cell display together in top of the table!?! Please let me know if you can.
Thanks in advance
Use Vlookup
Sorry, don’t understand the comment/ question... should be able to do this with the video steps
U mean u want that set of highlighted data to be extracted. Either use simple filter or vlookup to extract.
Yeah it's an amazing video for match. I also came through one more video on "off to office". Must watch video actually. It has actually trained me with all possible situations of matching highlight comparing
thank you... 😁
Welcome 😊
Thank-you
You're Welcome!
This is really helpful and thank you so much. Btw how do I make connection between these two data, means, if I select colour in format for all data and the same colour is showing in "QEWR65UYE" that u used in this example?
the conditional formatting would happen in the order set up so if you have multiple conditions, the one you want to be done 1st should be at the top
Yeah it's an amazing video for match. I also came through one more video on "off to office". Must watch video actually. It has actually trained me with all possible situations of matching highlight comparing.
Good day,
I must admit it is a gift to stumble on this article, but however my challenges was not addressed. I have a work sheet with just a single column. I need a formula that will highlight the row above the cell if the cell contains a specific text. For example if A2 contains "great" A1 should be highlighted. Thank you so much while i await your response.
Hi Jerry Friday, thanks for the comment! Could use it in conjunction with the OFFSET function. This video covers a little bit on how the OFFSET function is used. ruclips.net/video/OQbOlgdxT8o/видео.html
Do u want that cell to be highlights instead of entire row of match?. U can use proper referencing in lookup cell. Like if u write $a1 whole row will be highlighted and if $a$1 the specific cell only. I hope I made u clear.
Yeah it's an amazing video for match. I also came through one more video on "off to office". Must watch video actually. It has actually trained me with all possible situations of matching highlight comparing...
keep it up!
Hi gavaho, thanks for the comment!
how, if 3 column (a5=contain text "right";b5.f5=contain text "o"), the row will changed color to green and text to white
I need to highlight cell having two matching strings. AND condition. How do I do it?
maybe try using the AND function in the formula for conditional formatting? AND function explained at ruclips.net/video/aFcLFYjxa7I/видео.html
but that is cannot used in merge cells
link download for all tutorial
Hi Ubaidillah Muhammad, sorry don't have files to download :-(
Can I conditional format data I haven't yet entered
Hi George Banin, thanks for the comment! Yes, it just won't apply, yet.
@@DougHExcel can you help me with that?
Hi Doug, your video ruclips.net/video/wclmIcTsbtg/видео.html
is there away to display the scores flipped? so, F2 = F2, F3 = G2, F4 = H2, i.e. the names across the top and the scores display downwards? i been trying to flip the codes but keep getting errors thank you,
yes, the long way😐 would be to adjust the function (but i haven't looked into that). The short way 😜 would be to copy/paste as values and then copy/paste transpose ruclips.net/video/cjD6YT7-LlQ/видео.html
Thank you for quick reply, i think the trans-link would work but it would be heck of alot of work, need to do this 200+ times for each mini table i need i think i was hoping i could formula it out and only change the Ref cell it was searching for (Mike Etc), i will give it a try. thank you
THIS DOES NOT WORK!
I am trying to format a formula where a cell is highlighted if it matches the value based on another cell. For some DUMB REASON, when I enter the formula I am using into a blank column, it returns FALSE, which it's NOT FALSE! The formula that I am using for the condt'l format is: "=$E3=$A$2" and when I press Enter, IT DOESN'T FORMAT IT CUZ THE LOGICAL RESULT IS FALSE WHEN IT SHOULDN'T BE!
What am I doing wrong??
Check blank cell for trailing space or line breaks
@@DougHExcel I got it figured out, but thank you for replying back!
Video title is misleading: should be "highlight row" not "highlight cell"
Hi Doug James, thanks for the feedback!