Return Each Repeated Name Only Once in Excel
Question details
Return each name once, either as a compact list or in its original row position.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column contains repeated names, and the user wants to identify each name only once.
- Observed behavior
- The desired result is either a compact unique list or a column that leaves repeated occurrences blank.
Identify the range containing the names and choose an empty column for the results. The examples below assume names are in A2:A100.
Create a Compact List of Unique Names in Excel
Use UNIQUE and FILTER in Excel for Microsoft 365 or Excel 2021 to create a list without blank entries.
Click an empty cell such as B2, and make sure the cells below it are clear for the results.
Enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) and press Enter. Adjust A2:A100 to match your name range.
Check the list starting in B2. Each nonblank name appears once, in the order of its first occurrence.

Show Each Name Only in Its First Row
Use COUNTIF when you want to preserve the original row positions and leave repeated names blank.
Show Each Name Once in WPS Spreadsheets
Use a COUNTIF formula in WPS Spreadsheets to display each name at its first occurrence while keeping the original rows in place.
- 1. Open the workbook: Open WPS Spreadsheets and choose File > Open to select the workbook containing your names.
- 2. Select an output cell: Click B2, assuming the names begin in A2 and column B is available for results.
- 3. Enter the formula: Type =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")) in B2 and press Enter.
- 4. Fill the formula down: Select B2 and drag the fill handle down to the last row of names. Each name appears only at its first occurrence.

Frequently Asked Questions
Which formula keeps the original rows in place?
Enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")) in B2 and fill it down. Repeated names remain blank in the output column.
Why does my unique list show a spill error?
The formula needs empty cells below its output cell. Clear any entries blocking the spill range, then check the formula again.
Can I create a compact list without blank names?
In Excel for Microsoft 365 or Excel 2021, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell. Change the range to match your data.
Will these formulas delete duplicate names from the source column?
No. Both formulas display results in a separate column and leave the names in column A unchanged.




