Colour match each duplicate with Excel's conditional formatting

[@JonPrevost]

This should be a default feature in Excel. I feel like all these work arounds are great but it's a LOT of work for what would seem like a very common desired feature. Did the Excel dev team just retire back in the early 2000's? Maybe they should release a new version to compete with Google Sheets and simply call it Transcend, or Outdo. Microsoft Transcend sounds pretty cool. Needs cooler features for engineers like the one you just showed in this video. Thank you.

[@learnspreadsheets]

Lol Microsoft transcend! Not in the slightest, excel released 40 new features last year alone, & already a bunch this year. I’m a huge fan of both Google Sheets & excel, particularly the new stuff. I’ve met multiple times with the Excel dev team, they’re doing what they can to focus on the features which they see as important (which yes aren’t always the ones which I or others see as the most useful), but they are making amazing strides for sure! Check out all my videos with the « new » tag on my channel to see more!

[@att6098]

Talk way too fast. Can't hear the words.

[@learnspreadsheets]

Sorry about that, I am making an effort to slow down in newer videos but for this one you can try changing the playback speed to see if it helps

[@michaelseitz4647]

Damn Bro, you must have snorted like 4 lines before this video. Great content though.

[@learnspreadsheets]

Hahah this is possibly the best comment ever!

[@joshb5197]

I understand countifs, but I don't understand the logic for the word "player" and those 4 words and how it is calculating it. For example, why does it show "4"?

[@learnspreadsheets]

Good question! We use countifs as a hack here to make it check how many instances there are & sequence them accordingly & apply colour coding to the sequence

[@webelongtothelord4597]

Yes we didn’t catch anything This video is not how to do This video is look how fast i am

[@learnspreadsheets]

Thanks for the feedback, sorry it’s too fast, I’ve slowed down in videos since

[@ethanwiebe6010]

In an Excel sheet of considerable size, the colour difference becomes very minimal and hard to see. Is there any workaround or other technique that indicates different groups of text duplicates that you have found, or is this one of the pitfalls of duplicates?

[@learnspreadsheets]

Hi good question, if you sort from low to high then select eg the first 50 cells & change to 3 colour colour scales, then the next group & do the same, & so on. I’ve used this approach before which isn’t ideal but works ok

[@JoseAntonioMorato]

For the COUNTIFS formula, there is no need to lock the criteria range cells. Just include, in criterion1, the same range: =COUNTIFS(L5:L14,">="&L5:L14) or =COUNTIFS(L5:L14,"

[@learnspreadsheets]

That's a cool tip, but its a dynamic array so I wanted to show the solution without it being version specific (even though for my stuff I love the new features usually)!

[@armydillo9792]

Talk slower please 😢

[@learnspreadsheets]

Sorry about that, it might be helpful to change the speed of the video & make it slower

[@oliviadevoe993]

Talk too fast 🤦🏾

[@learnspreadsheets]

Sorry I’ll try to slow down in future. You can slow down the video in settings if that helps

[@omnipop4936]

@@learnspreadsheets 0.75 speed seems to be the sweet spot - sounds like the original speed it was recorded at. Maybe your video editor sped something up while the clip was being prepared? Great content though.

[@srinivasanviswanathan6534]

This only seems to work with a few unique duplicate groups... when we add hundreds of dups and you use color scale the difference between the groups is barely noticeable... is that correct? Is there any way to have distinct colors for all the different groups that go across a variety of colors?

[@learnspreadsheets]

Good point, it’s a hack & so it’s not perfect sorry, wish there was a built in better way

[@snacach]

can you do the same in google sheets?

[@learnspreadsheets]

Good question. Yes but slightly different, firstly conditional formatting is the under “format” menu & there is a tab for colour scales, secondly highlighting unique values (which is used in the method) isn’t doable though the UI but rather you need a custom formula to do that using COUNTIFS, if you look up conditionally format duplicate cells in google sheets you can find it

[@cuteness_]

Hi David, thank for this video. I just want to ask if there's a way to highlight the duplicates in alternative colors (just two colors) so that I won't do it manually after I use the countif formula?

[@learnspreadsheets]

Hi, no I don’t think there is without extra helper columns

[@K-Kratos]

Thank you

[@learnspreadsheets]

Glad you like it!

[@bettiniw]

It worked! How can color not the neighbor cell, but the cell in which I inserted the data?

[@learnspreadsheets]

Im glad it worked. You can do colour scales on numbers only, so for text you need to either make lots of manual rules or colour the adjacent cells. ALternatively you can recolour duoplicates and all will be the same colour

[@HB-kh8vc]

hi! I watched this video and found it super helpful, but when I tried it myself, I got #VALUE! results in a handful of cells. when I checked afterwards, those 4 cells were the longest by far out of the comparator strings (200+ characters). Is there an upper limit to string length for this method of comparison (the countifs formula)?

[@learnspreadsheets]

Hi I looked it up and there is a 255 character limit. This link has a workaround though: superuser.com/questions/1429978/countifs-when-criteria-has-more-than-255-characters

[@HB-kh8vc]

thank you!!@@learnspreadsheets

[@lovefoolish2019]

Thank you so much for this!!! I was trying to find a way to color code matching texts and this tutorial worked perfectly. I really wanted the text itself to be colored however and I found that if you paste the match cells into Word and then back to Excel, you can copy the newly pasted cells and paste special -> format onto the cells with text and the colors will be applied directly to the text.

[@learnspreadsheets]

Nice! Glad it helped you solve your issue!

[@NikhilKulkarni-u5m]

how to match if we have two columns A and B and add matching color to unique text

[@learnspreadsheets]

If you pre select both columns & follow the same steps it should work

[@davebalino]

This is simply amazing

[@learnspreadsheets]

Thanks Dave! Glad you like it. PS great choice for your name 😆

[@WilsonALO]

Great video.

[@learnspreadsheets]

Thanks for the feedback!

[@dhananjayparihar4283]

Just amazing 💥

[@learnspreadsheets]

Cool trick eh?

[@M.HanifK]

Great

[@learnspreadsheets]

Glad you like this nifty trick 😉

[@M.HanifK]

@@learnspreadsheets I have searched this many times on KZbin but all i got is macro code. Appreciate your trick.

[@wayneedmondson1065]

Good trick! Thanks!!

[@learnspreadsheets]

Glad you like it! Pretty simple but cool 😃