Function Problems

Return Each Repeated Name Only Once in Excel

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Return each name once, either as a compact list or in its original row position.

Return Each Repeated Name Only Once in Excel
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.
Before you start

Identify the range containing the names and choose an empty column for the results. The examples below assume names are in A2:A100.

Solution 1Recommended

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.

1
Select an empty cell

Click an empty cell such as B2, and make sure the cells below it are clear for the results.

2
Enter the formula

Enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) and press Enter. Adjust A2:A100 to match your name range.

3
Review the spilled list

Check the list starting in B2. Each nonblank name appears once, in the order of its first occurrence.

Create a Compact List of Unique Names in Excel
If there are no names: FILTER returns a calculation error when the range has no nonblank names. You can use =IFERROR(UNIQUE(FILTER(A2:A100,A2:A100<>"")),"") to return a blank instead.
WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and choose File > Open to select the workbook containing your names.
  2. 2. Select an output cell: Click B2, assuming the names begin in A2 and column B is available for results.
  3. 3. Enter the formula: Type =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")) in B2 and press Enter.
  4. 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.
Identify first occurrences without removing source rowsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceGet started with the free version of WPS Office
microsoft office alternative - wps office

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.