Function Problems

Excel: Remove All Duplicate Values, Including the First Instance

Phi Hung VoPhi Hung Vo Nov 12, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select an empty result cell

Click the first cell where the remaining values should appear, such as J2.

2
Enter the single-occurrence formula

In the formula bar, enter =FILTER(H2:H13,COUNTIF(H2:H13,H2:H13)=1,"No unique values") and press Enter.

3
Review the spilled results

Excel spills the values into the cells below J2. Only values appearing exactly once in H2:H13 are returned.

Use FILTER with COUNTIF to keep only values that appear once
Result: If every value is duplicated, the formula displays "No unique values" instead of returning a blank result.

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. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, and open the workbook containing the list.
  2. 2. Select a blank output cell: Click an empty cell outside the source list, such as J2, where the filtered results can spill.
  3. 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. 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.
Remove every occurrence of duplicated values instead of keeping one copy.Use FILTER and COUNTIF to generate a refreshed result list automatically.Work in a lightweight spreadsheet interface with familiar formula and formatting tools.Open, edit, and save Microsoft Excel XLSX files with broad format compatibility.
microsoft office alternative - wps office

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.