How to Find and Remove Duplicates in Excel

When an export contains the same rows twice

Combining monthly exports, copying a sheet, or importing the same file twice can leave repeated records in your table. A matching name alone does not make two records identical. This tool compares every column, so rows with different dates, amounts, or IDs stay separate. Case and spacing are compared exactly.

How to remove duplicates in Excel

1. Because Remove Duplicates permanently deletes data, copy the original range or table to another worksheet or workbook first.
2. Select the range, or click any cell in a table. On the Data tab, in the Data Tools group, select Remove Duplicates.
3. Under Columns, choose Select All to remove only rows that match in every column. Select fewer columns only if those fields alone define a duplicate for your task.
4. Select OK. Excel keeps the first occurrence, deletes the other copies and reports how many duplicate values were removed and how many unique values remain. If the result is not what you intended, select Undo or press Ctrl+Z.

Excel removes the whole row within the selected range when the selected columns match. Selecting only an ID column may remove records whose other fields differ. This online tool deliberately compares the whole row; it does not merge people, infer matching identities, or perform fuzzy matching.

Remove duplicates based on selected columns in Excel

In Data → Remove Duplicates, the columns you check together form the key. When those columns match, Excel deletes the whole row within your selection, even if other columns differ, and keeps the first occurrence.

To remove duplicates based on one column of a table, select the whole table and check only that column. If you remove duplicates from that column alone, only the selected cells change and values outside the selection do not move, so the column no longer lines up with the rest of each record.

Match on selected columns only when an ID or email truly identifies one record. The free TidyX tool does not match on selected columns: it removes a row only when every column matches.

Keep the most recent row for each ID

Excel keeps the first occurrence of each duplicate, so put the rows you want to keep first.

1. Copy the data to a new worksheet.
2. Sort by the date column from newest to oldest.
3. Select the range, choose Data → Remove Duplicates and check only the ID column.
4. Excel keeps the first row for each ID in the current order, which is now the newest one.

In Power Query, Remove Duplicates does not guarantee which row of a duplicate set it keeps, so follow Microsoft’s guidance on preserving sort order before relying on it. TidyX can keep the last of fully identical rows, but it cannot choose the newest row for each ID.

Find duplicates in Excel without deleting them

To highlight repeated values, select the cells and choose Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, then pick a format. The rule compares individual cell values, not whole rows: with several columns selected, a value is colored whenever it appears anywhere else in the selection.

To show one row per unique record without deleting anything, choose Data → Advanced and select Unique records only. You can filter the list in place or copy the result to another location.

The free tool above also lets you find duplicates before removing them: its preview lists every row it would remove, with the original row number, and nothing changes until you approve.

Flag duplicate rows with a COUNTIFS formula

Add a helper column next to your data. If columns A to C define a record, enter =COUNTIFS(A:A,A2,B:B,B2,C:C,C2) in row 2, fill it down and filter the helper column for values greater than 1. Every copy is flagged, including the first. For a single column, =COUNTIF(A:A,A2) works the same way.

These counts are looser than an exact match. COUNTIF criteria are not case sensitive, so APPLES and apples count as the same; the * and ? characters act as wildcards; and COUNTIFS treats an empty criteria cell as 0, so rows with blanks can be miscounted. Check the flagged rows before you delete any of them.

Remove duplicates with the UNIQUE function

In Excel for Microsoft 365, Excel 2024 and Excel 2021, =UNIQUE(A2:C100) returns each distinct row of the range once. The result spills into the neighboring cells as a new list, and your original data stays as it is.

With several columns, UNIQUE compares whole rows. To list only rows that occur exactly once, set the third argument to TRUE: =UNIQUE(A2:C100,,TRUE) drops every copy of a repeated row. If your Excel version has no UNIQUE function, use Remove Duplicates or the Advanced filter instead.

How to find duplicate rows in a CSV file before deleting them

Opening a CSV directly in Excel can change values such as IDs with leading zeros. This tool reads CSV fields as text and compares whole rows exactly, including case and spaces.

The preview lists the original row numbers of the rows that would be removed. Nothing is deleted until you approve; uncheck any row you want to keep, then download a cleaned CSV or XLSX.

What Excel compares and what TidyX compares

Excel compares what each cell displays. The same date formatted as 3/8/2006 in one cell and Mar 8, 2006 in another counts as two different values.

TidyX compares each cell’s stored type and value, so number and date display formats do not change the result. Case, spaces and leading zeros still matter, and the file never leaves your browser.

Review the rule before it runs

TidyX separates finding matches from removing them. You see the exact rule, original row numbers and removal count before running. Choose whether to keep the first or last match, and uncheck any candidate row you want to preserve. Changing a setting clears the previous approval. Execution uses the reviewed rule, not an AI guess.

After confirmation, download CSV or XLSX plus a PDF summary and JSON audit. The audit records the rule, removed source-row numbers, and file hashes. Your original file stays separate from the result.

Frequently asked questions