Can You Filter Out Duplicates in Excel? Methods That Work

Formulas & functions · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can identify duplicates with conditional formatting, filter them with a formula, extract unique values with UNIQUE, or permanently delete repeated entries with Data > Remove Duplicates. Choose based on whether you want to inspect, hide, copy, or erase duplicates; deleting them changes the source data.

What Excel means by a duplicate

A duplicate is a value or record that matches another according to the columns you choose to compare. If a list contains the names Ana, Ben, Ana, Excel can identify the repeated name. In a customer table, however, two rows may share a name but have different email addresses. Whether those rows count as duplicates depends on your definition, not just on Excel.

For a single column, you might compare product codes. For a whole row, you might compare every field. You can also define a record by only some columns: for example, treat rows as duplicates when both the customer ID and order date match, even if a notes column differs. Decide which fields make a record unique before removing anything.

Excel’s common duplicate tools generally compare cell values rather than appearance. A red fill, bold type, or a different number format does not by itself make a value distinct. Extra spaces can matter: ABC and ABC followed by a space may look alike but are different text. Text matching in common Excel duplicate operations is not case-sensitive, so ABC and abc are generally treated as the same value.

Choose a method before changing the list

The right option depends on what you need to do with the results. Highlighting is useful for review, a formula can label or filter rows, and UNIQUE can create a separate list that updates as source data changes. Remove Duplicates is convenient when you have made a backup and want to clean the original range.

  • Find and inspect: Highlight repeated values or add a helper column that counts them.
  • Show only certain rows: Use a helper column and Excel’s filter controls.
  • Create a distinct list: Use UNIQUE in Microsoft 365 or Excel 2021 and later.
  • Clean the source: Use Remove Duplicates after checking the selected columns and retaining a copy.

These approaches do not all produce the same result. A highlighted duplicate remains in the data, while Remove Duplicates deletes rows. A dynamic unique list is a separate output and does not clean up the source range.

Remove duplicates from a range or table

To delete repeated records in Excel for Microsoft 365 on Windows, click a cell in the data, then choose Data > Remove Duplicates. If the data has headers, confirm that My data has headers is selected. In the column list, select the fields Excel should use to decide whether two rows are duplicates, then choose OK. Excel reports how many duplicate values were removed and how many unique values remain.

  1. Save a copy of the workbook or duplicate the worksheet before cleaning important data.
  2. Click inside the range or table. If Excel does not select the intended data, select the full range yourself.
  3. Open Data > Remove Duplicates and verify the header setting.
  4. Select every column that defines a duplicate. Select all columns to remove only identical records across the entire row.
  5. Review Excel’s summary and inspect the remaining records.

Excel keeps the first occurrence it encounters and removes later matching rows. Therefore, if you want to keep the newest entry, sort the records so the newest appears first before removing duplicates. Check that the sort includes the whole table; sorting just one column can misalign records.

If you select only the email column, Excel can remove an entire row when its email repeats, even if other fields differ. That may be appropriate for a mailing list, but not if separate transactions for one person must be retained. The operation is not a reversible filter: after removing rows, Ctrl+Z may undo it immediately, but once you continue editing or close the file, relying on Undo is risky.

Highlight duplicates without deleting them

To mark repeated cells for review, select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a formatting style and confirm. Excel applies formatting to the selected range; it does not remove or hide any rows. This is often the safest first step when you need to understand what the duplicates are.

The built-in rule is convenient for a single list or selected range, but it does not offer the same flexible definition as choosing several columns in Remove Duplicates. To check whether a complete row is repeated across multiple fields, use a helper formula or compare the full record with the removal dialog. For a large table, conditional formatting may also make the sheet harder to read if the selection is too broad.

To show only duplicate cells after highlighting, use a helper column instead. Conditional formatting changes appearance, not the value, so a normal filter cannot reliably filter by the color unless you choose a color filter and the rule has applied that fill. A formula label is clearer when you want a repeatable, auditable result.

Use a formula to label or filter repeated values

For a list in A2:A100, enter this formula in B2 and fill it down to label every value that occurs more than once:

=COUNTIF($A$2:$A$100,A2)>1

The result is TRUE for every occurrence of a repeated value, including its first appearance, and FALSE for a value appearing once. Turn on filters with Data > Filter and filter column B to TRUE to display the duplicate entries. The formula does not delete anything, and because its range is fixed at A2:A100, update that range if the list grows beyond row 100.

If you want to mark only the second and later appearances, with values in A2:A100 and the formula entered in B2, use:

=COUNTIF($A$2:A2,A2)>1

For example, if A2:A5 contains Pine, Oak, Pine, Elm, the formula returns FALSE, FALSE, TRUE, FALSE as it is filled down. The first Pine is not flagged; the second is. This running count is helpful when you want to preserve the first record and identify only later copies.

When a duplicate is defined by more than one field, use a count across the corresponding columns. For example, with IDs in A2:A100 and dates in B2:B100, enter this in C2 and fill down:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1

This returns TRUE when the same ID-and-date combination occurs more than once. It ignores differences in other columns, such as notes in column D. COUNTIF and COUNTIFS are not case-sensitive, and cells that look blank may need checking if formulas or spaces are present.

Create a separate unique list with UNIQUE

If you want a distinct list while leaving the original data untouched, UNIQUE is often the simplest choice. With source values in A2:A100, enter this in an empty cell outside the source range:

=UNIQUE(A2:A100)

Excel returns the distinct values as a spilling array in the cells below the formula. In Microsoft 365 and Excel 2021 or later, the results update when the source range changes. Leave the cells where the results will spill empty; otherwise Excel may show a #SPILL! error. The formula produces a new list, not a filtered view of the original rows.

To return distinct complete records, with related fields in columns A through C and data from rows 2 through 100, use:

=UNIQUE(A2:C100)

Here Excel compares entire rows and returns one copy of each distinct row. If two rows share a product name but differ in price, they remain separate. UNIQUE is case-insensitive for text comparisons, and it does not automatically trim spaces or standardize inconsistent spellings. It is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

Common problems and ways to avoid them

A duplicate report can be surprising when visually identical entries are not actually equal, or when Excel compares fewer fields than you intended. Before deleting records, investigate the data and decide whether to standardize it. These checks often explain unexpected results:

  • Leading or trailing spaces: Clean inconsistent text before comparing it. TRIM can remove ordinary extra spaces, but cleaning a working copy first lets you check the changes.
  • Numbers stored as text: A value stored as text may behave differently from a numeric value in other calculations. Check the cell format and convert deliberately rather than assuming the entries match.
  • Dates with hidden times: Two cells may display the same date while containing different time values. Inspect the underlying values if one date seems to be missed.
  • Wrong columns selected: Selecting only a name can combine different people with the same name; selecting every column can preserve records that differ only in an irrelevant note.
  • Headers mistaken for data: Confirm the header option in Remove Duplicates so Excel does not treat column labels as ordinary records.

For valuable records, work on a copy, then compare the number of rows before and after and inspect a sample of the deleted or retained entries. If you need an audit trail, keep the original sheet rather than treating deletion as a permanent record of what changed.

Alternatives for recurring cleanup

For a one-time review, conditional formatting or Remove Duplicates is usually enough. For a report that needs to refresh, a UNIQUE formula can keep a separate distinct list current. A helper column is useful when you need to filter, explain, or review repeated records without altering the source.

Excel also has an Advanced Filter command for extracting unique records. In desktop Excel for Windows, select the data and use Data > Advanced in the Sort & Filter group. Choose Copy to another location if you want a separate result and select Unique records only. This is useful for a one-time extract, but unlike a UNIQUE formula, the copied output does not update automatically when the source changes.

For repeated imports or larger cleanup workflows, Power Query can transform data and remove duplicates as part of a refreshable query. The specific result depends on which columns you select as the duplicate key, so check the query steps and output before relying on it. Power Query is more setup than a quick worksheet operation, but it can be preferable when the same cleaning steps must be run again on updated source files.

Frequently Asked Questions

Can Excel remove duplicates but keep the first entry?
Yes. Data > Remove Duplicates keeps the first matching record it encounters and removes later ones. Sort the complete table first if you want a particular record, such as the newest one, to appear first.

Can I find duplicates based on two columns?
Yes. In Remove Duplicates, select both columns as the comparison fields. To flag matching combinations without deleting rows, use COUNTIFS with those two fields as criteria.

Does filtering duplicates delete the original data?
No. Applying a filter or using a helper formula only changes what is displayed or labeled. Remove Duplicates is the option that deletes matching rows from the selected data.

Why does Excel miss values that look identical?
Extra spaces, different underlying date or time values, and values stored in different formats can make entries unequal even when they look alike. Inspect the actual data and standardize a copy before removing records.

Can I use UNIQUE in older Excel versions?
UNIQUE is available in Microsoft 365 and Excel 2021 or later. In older versions, use Remove Duplicates, Advanced Filter, or a helper column with COUNTIF to identify repeated values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.