Excel: Remove All Duplicate Values, Including the First Instance
Question details
The user needs to remove every occurrence of any duplicated value from an Excel list, including the first occurrence, so only values that appear exactly once remain.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A list contains repeated values, and the user wants to create a result containing only values with a count of one.
- Observed behavior
- Standard duplicate-removal tools may keep one instance of a repeated value, while the required result must exclude all instances of duplicated values.
Confirm the source range, such as H2:H13, and decide where the filtered results should spill. Check that the destination cells are empty so the dynamic array formula can return all unique-occurrence values.
Use FILTER with COUNTIF to keep only values that appear once
Use this solution in Excel 365 or another version that supports dynamic array formulas when you want a separate result list without altering the original data.
COUNTIF counts how many times each value appears in the selected range. FILTER then returns only the values whose count equals 1, excluding every occurrence of duplicated values.
Click the first cell where the remaining values should appear, such as J2.
In the formula bar, enter =FILTER(H2:H13,COUNTIF(H2:H13,H2:H13)=1,"No unique values") and press Enter.
Excel spills the values into the cells below J2. Only values appearing exactly once in H2:H13 are returned.

Identify single-occurrence values with conditional formatting
Use this option in legacy Excel or when you need to highlight values that occur exactly once before filtering or deleting rows.
Keep Only Non-Duplicated Values in WPS Spreadsheets
WPS Spreadsheets can create a clean list containing only values that occur once. Enter a FILTER and COUNTIF formula in a blank cell, review the spilled results, and save the workbook in the familiar Excel format when needed.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, and open the workbook containing the list.
- 2. Select a blank output cell: Click an empty cell outside the source list, such as J2, where the filtered results can spill.
- 3. Enter the filtering formula: Type =FILTER(H2:H13,COUNTIF(H2:H13,H2:H13)=1,"No unique values") in the formula bar and press Enter.
- 4. Check and save the results: Review the values returned below J2, then use Menu > Save As to save the workbook as an XLSX file if required.

Frequently Asked Questions
How do I remove all duplicate values instead of keeping one copy in Excel?
In Excel 365, enter =FILTER(H2:H13,COUNTIF(H2:H13,H2:H13)=1,"No unique values") in an empty cell. The formula returns only values that occur once and excludes every repeated value.
What should I do if Excel displays a spill error?
Make sure the cells below and beside the formula cell are empty, because FILTER returns a dynamic array. Remove any existing content blocking the spill range and recalculate the formula.
Can I use this method in older versions of Excel?
The FILTER function is available in newer Excel versions such as Microsoft 365. In legacy Excel, use a conditional formatting rule with =COUNTIF($H$2:$H$13,H2)=1 to identify values that occur exactly once, then filter or copy the highlighted cells.
How can I show a message when no values occur only once?
Add a third argument to FILTER, as in =FILTER(H2:H13,COUNTIF(H2:H13,H2:H13)=1,"No unique values"). Excel displays the specified message when every value is duplicated.
Will the formula update when the source list changes?
It updates when values in the referenced range change, but the range H2:H13 must include any new rows. For a growing list, convert the source range to an Excel Table and use the appropriate structured references.




