Highlight and Count Duplicates in Row - Excel Tutorial

In this video, we handle duplicates in a row in Excel. We will cover how to highlight duplicates and count duplicates in rows in all versions of Excel.
The video shows the duplicate rule of Conditional Formatting. It is helpful, but not up to the task we need a solution for.
So, a formula is created using COUNTIFS to highlight duplicates and is then applied to a Conditional Formatting rule.
Then two Excel formulas are shown to count duplicates in row. One for Excel 365 and another for all Excel versions.
This video describes the SUMPRODUCT solution to count duplicate values - • Count the Unique Entri...
The timings of the video.
00:00 - Duplicate values with Conditional Formatting
01:42 - Duplicate values rule is not good enough
02:15 - Formula to identify duplicate names
04: 39 - Highlight duplicates in a row
05:53 - Count duplicates in row (Excel 365)
08:13 - Count duplicates in row (All Excel versions)
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / alanmurray-computergaga
Instagram ► / computergaga1
Twitter ► / computergaga1
#computergaga #exceltips #exceltutorial

[@akramalazrak327]

I find this feature of excel is more flexible in my work and it helps me to do job properly. Thanks from deep of my heart.

[@Computergaga]

You're welcome, Akram 👍

[@garethwoodall577]

Great explanations Alan!

[@Computergaga]

Thank you Gareth.

[@WasabiSoySauce]

Very helpful! Thank you!

[@Computergaga]

You're welcome!

[@drjosiahv]

Thanks a lot. A very useful formula which I could not get even from Microsoft help

[@Computergaga]

You're welcome!

[@nadermounir8228]

Thank u Alan. this is a great video 📹 especially the old school solution. Thank u again :)

[@Computergaga]

You are very welcome Nader. Thank you for your comments.

[@JCEurovisionFan1996]

I subscribed to your channel, Alan. Thanks!

[@Computergaga]

Thanks for the sub! 😊

[@AhmedSC]

Simply YOU ARE A LEGEND 😮

[@Computergaga]

👍😊 Thank you

[@leilas2119]

excellent thank you so much!

[@Computergaga]

You're very welcome!

[@jeffwoodcock6702]

On the "No of Dups" col, I reckon that the following form, should also work: =COUNTA(UNIQUE(B3:E2, TRUE) - COUNTA(UNIQUE(B3:E2, TRUE, TRUE).

[@AliRaza-fd3xz]

How to find duplicates in excel on iPad

[@koraron]

I love u... Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, ... and a million more times. Dude i can thx u enough... life saver video xD

[@Computergaga]

You are so welcome!

[@LiaqatAli-qk8yp]

Wonderful

[@Computergaga]

Thank you very much!

[@teoxengineer]

Alan so great explanation about counting of dublicate values with different aproaches. However, I think it will be better to use F9 key in order to evaluate formula result in Formula because I haven’t understood sumproduct 1/counta function. If you are able to demostrate formula evaluation by using F9 key, we can get the results which are coming from formula. 🙋🏼‍♂️ I am going to try getting last result myself in excel ☺️ Good topic thank you again

[@Computergaga]

Thank you Emre. I have a link in the video description where I do break down that formula with F9 😊

[@adilalsabahi7251]

Thanks Alan very clear explanation, I just subscribed to your channel. One more question please, how about if want to count the duplicate in the whole sheet? lets assume, we have 3 companies and I want to know the employees registered in more than one company using their Identity Number?

[@Computergaga]

You're welcome! Thanks. You could use the COUNTIFS function for this. If the ID number was in column A, you could use =COUNTIFS($A$2:$A$50,A2). This will count their occurrences. This is a brief example that can be adapted to needs. I have a video on this channel somewhere.

[@JackieChung-eo3il]

Thank you Alan. My question is if in Wk3 there were 3 Gitas and 1 Adam, then the No of Dups will be 2 (not 1)...how could I fix it? Thanks.

[@Computergaga]

You're welcome, Jackie. The formulas return 2 if you enter Gita three times.

[@thtgrldiana6388]

Hi Alan! This was great! thank you! My data is in rows with all the data points in columns Trans#; name, Amount, Item, Action. if a person buys something, then returns it, it is the same trans#. I would like to count my duplicate Trans #'s , and hide the duplicates for Trans#'s hide them in such a way that: only one row for the Trans # is visible and I can see there are X-number of duplicates but those rows are simply not visible. (sort of like treating the dublicated values as secondary rows). Is this possible?

[@Computergaga]

You could use a COUNTIFS function in a new column to count the occurrences of the Trans#. For example, the formula =COUNTIFS($A$2:$A2,A2) works on the idea that the trans# is in range A2 down. You can then filter by the column to exclude the rows that contain a 1.

[@thtgrldiana6388]

@@Computergaga Thank you and true... I also need to see those unique trans# where only one action is executed. I'm trying Pivot tables options.. but still not getting quite what I'm hoping for. 🙂

[@Computergaga]

@@thtgrldiana6388 sure. You can use PivotTables and filter by the values in the column with COUNTIFS. Filter for 1 or 2. If you're on 365, you can also use the FILTER function.

[@swashofc]

Thank you for the video, I think this is the exact solution I am searching for. However, at 3:49, it does not allow me to select the first cell of the row. I have tried both clicking and typing the name or just typing the formula and pasting it in the conditional formatting rule, but I am getting the error box that there is something wrong with the formula. Here is what I tried for a row with 10 columns: =COUNTIFS($O2:$X2,O2)>1 To reiterate, it does not allow me to select the O2 cell without getting an error, and typing the formula out results in an error, too. Any idea what could be causing the issue? I don't see how what I have tried is different than in the video.

[@swashofc]

In case someone is as silly as I am, this error had to do with my version of Excel accepting semi-colon, not comma for the separation of the criteria inside the parentheses. So the following worked perfectly: =COUNTIFS($O2:$X2;O2)>1

[@JoseAntonioMorato]

Dear Alan; If one wants to list only the repeated ones: =UNIQUE(HSTACK(UNIQUE(B2:E2,1,0),UNIQUE(B2:E2,1,1)),1,1) 🤗

[@ezelkarlkl1284]

Hi , when I write to B2-C2-D2-E2 "Sally" formula result is 3. Is this normal ? or should be 1 ?

[@Computergaga]

Hi Ezel, I don't understand what you're doing. You can't use the formula B2-C2-D2-E2 on text values.

[@ezelkarlkl1284]

@@Computergaga sorry , problem iş my english :). I will try explain later.

[@Computergaga]

No worries.