How To Identify Duplicates & Triplicates Values
Excel Video : How To Identify Duplicates & Triplicates Values
Dirty data is a problem we all face. Whether you are a Power BI person, Excel person or just about anyone else, I am sure you have an on-going battle with some dirty data.
I wish we have access to a data cleaner so that we can spray and clean the nuisance.
But we got the next best thing... Excel.
Excel has 5 amazing features to help you CLEAN your data real good. These are,
Flash Fill: Pattern based data extraction and cleanup engine
Text to Columns: Split data by delimiters or widths
Remove Duplicates: Chop duplicate, redundant, replicate, look-alike data
Formulas: Split data, extract portions or convert from one format to another
and Power Query: This one is biggie. Automate data collection, clean-up and transformation process!
HOW DO I REMOVE DUPLICATE VALUES?
This video teaches you how to find duplicates (or triplicates) in Excel.
Go here to remove duplicates.
1. Select the range A1:C10.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Highlight Cells Rules, Duplicate Values.
4. Select a formatting style and click OK.
Note: select Unique from the first drop-down list to highlight the unique names.
As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Mike), quadruplicates (if we have any), etc.
Execute the following steps to highlight triplicates only.
5. First, clear the previous conditional formatting rule.
6. Select the range A1:C10.
7. On the Home tab, in the Styles group, click Conditional Formatting.
8. Click New Rule.
9. Select 'Use a formula to determine which cells to format'.
10. Enter the formula =COUNTIF($A$1:$C$10,A1)=3
11. Select a formatting style and click OK.
Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1.
If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell
Is the video helpful?
Drop your comment
Kindly follow and subscribe to my YouTube for more interesting videos on Excel
Watch video How To Identify Duplicates & Triplicates Values online without registration, duration hours minute second in high quality. This video was added by user Akerele Oluwasogo (Mr Excel) 01 January 1970, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,218 once and liked it 14 people.