When there are many records in the table, there is often duplicate data, and we often only want to save one for duplicate records, so the excess needs to be deleted.
Chapters:
0:00 How to find duplicates in Excel?
0:25 How to remove duplicates in Excel?
0:49 How to remove duplicates with Advanced Filter in Excel?
1:02 How to find duplicates with Formula in Excel?
Description:
How to remove duplicates in Excel (find, mark, highlight duplicate rows with color or formula)
Note: The cc should be changed to greater than sign, the ee should be changed to less than sign in the formula.
There are two ways to remove deduplicates in Excel, remove them with Data Tools and Advanced Filter; It will leave blank rows at the end of the table with the former, while it will not will do with the latter. If you only want to see duplicates without having to remove, you can highlight the duplicates markers in color so that they can be seen at a glance. In addition, you can use formulas to count how many duplicates there are or filter out duplicates.
One. How to find duplicates in Excel? (Mark duplicates in Excel, Highlight duplicate rows in Excel)
1. Select column A, click "Conditional Formatting" in the "Home" tab, select "New Rule" in the pop-up menu, open the "New Formatting Rule" window, select "Format only unique or duplicate values", click "Format", open the "Format Cells" window, select the "Fill" tab, select a fill color, such as "Green", click "OK", return to the "New Formatting Rule" window, select "Duplicate" for "Format all, click "OK", All duplicates in the table are colored.
2. When coloring duplicates, columns that can be used as keywords are generally selected, that is, unique columns can be identified, such as the ".No" column selected in the above operation, so that the marked is two completely duplicate records.
Two. How to remove duplicates in Excel?
(1) Method 1: With "Remove Duplicates"
1. Duplicates have been marked earlier, now delete them. Check "NO." column, select the "Data" tab, click "Remove Duplicates" above "Data Tools" to open the "Remove Duplicates Warning" window.
2. Select "Expand the Selection", click "Remove Duplicates" to open the "Remove Duplicates" window, here you can select the columns you want to delete.
Note: Removing duplicates in this way leaves empty rows, and removing a few duplicates leaves a few empty rows.
3. Keep the default select all columns, click "OK", then the duplicates will be deleted, and a prompt window will pop up to indicate that the duplicates have been deleted, click "OK".
(2) Method 2: How to remove duplicates with Advanced Filter in Excel?
1. Click "Advanced" in the "Data" tab, open the "Advanced Filter" window, select the entire table, check "Unique records only", click "OK", then all duplicate records are deleted, and there are no blank rows behind the table.
2. If the table data is more difficult to select, we can directly enter Sheet6!$A$1:$F$12 in the "List range", where "Sheet6!" Can be omitted.
Three. How to find duplicates with Formula in Excel?
1. Copy the formula =COUNTIF($A$2:$A$12,A2) to cell G2, press "Enter", then count whether there are duplicate records of "WS-585", the result is 1 means that the table has only one record of "WS-585"; Move the pointer over the fill handle of cell and drag it down, the number of records in the corresponding row is counted in the cells passed, and the result of "WS-581 and NS-281" is 2, indicating that they both have two records, that is, there is duplicates.
2. Change the formula to =IF(=COUNTIF($A$2:$A$12,A2)cc1,"Duplicate",""), press "Enter" and drag down, then duplicates are marked with "Duplicate".
3. Formula description
A. =COUNTIF($A$2:$A$12,A2) means that count the number of A2 in A2:A12, that is, there are several "WS-585" in column A; where $A$2 means that the reference column in turn refers to the row.
B. =IF(COUNTIF($A$2:$A$32,A2)cc1,"Duplicate","") means that if the counted quantity is greater than 1, it displays "Duplicate", otherwise it displays empty (i.e. ""); Because the table has no duplicate records with a statistic result of 1, the statistic result with duplicate records is greater than or equal to 2.
- How to remove duplicates in Excel (find, mark, highlight duplicate rows with color or formula) ( Download)
- How to Find Duplicates in Excel & Highlight Duplicates If You Need To ( Download)
- How To Highlight Duplicates in excel | Conditional Formatting in Excel Color coding | #msexcel ( Download)
- Google Sheets - Highlight Duplicate Data in a Column or Row ( Download)
- Excel - Conditional Formatting find duplicates on two worksheets by Chris Menard ( Download)
- How to find duplicates in Google Sheets ( Download)
- Find Duplicate Entries in Excel ( Download)
- Highlight Duplicates in Excel in Same Column in a Different Colour ( Download)
- How to highlight duplicate rows across Multiple columns in Excel ( Download)
- How to highlight values that appear in two columns | Compare Two Columns in Excel for Matches ( Download)
- How To Highlight Duplicates In Excel | How To Find Duplicate Records In Excel | Simplilearn ( Download)
- How to find duplicates based on two columns in excel ( Download)
- How To: Filter Duplicate Values in Excel ( Download)
- Libreoffice | Calc - Remove duplicate entries ( Download)
- Compare Two Sheets for Duplicates with Conditional Formatting ( Download)
