Function Problems

Remove Duplicate Values and Find Unique Entries in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 871 views

Question details

Remove repeated values from an Excel list while keeping one copy of each value, or find values that occur exactly once.

Remove Duplicate Values and Find Unique Entries in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to clean a list or extract entries that have no duplicates.
Observed behavior
The desired result is either one copy of each distinct value or a separate list containing only values that appear once.
Before you start

Decide whether you want to keep one copy of every value or exclude all values that occur more than once. Check whether your list has a header and whether adjacent columns contain related data that should remain together.

Solution 1Recommended

Remove duplicates while keeping one copy of each value

Use Excel's Remove Duplicates command to clean the selected list in place.

1
Select the list

Select the cells in your list, including the header if there is one. If adjacent columns contain related data, select the full table to keep rows together.

2
Open Remove Duplicates

On the Data tab, click Remove Duplicates.

3
Choose the comparison column

In the dialog box, check My data has headers if applicable. Select the column or columns to compare, then click OK.

4
Review the result

Read the confirmation showing how many duplicates were removed and how many unique values remain, then click OK.

Remove duplicates while keeping one copy of each value
The selected data changes: Remove Duplicates retains the first occurrence and removes later matches. Use Ctrl+Z to undo the change if the result is not what you intended.
WPS Spreadsheets

Remove duplicate list entries in WPS Spreadsheets

Use WPS Spreadsheets to remove repeated entries from a list while keeping one copy of each value.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 2. Select the data: Select the list or full table containing the values you want to check.
  3. 3. Open the duplicate tool: On the Data tab, choose Duplicates, then Remove Duplicates.
  4. 4. Confirm the columns: Choose the columns to compare, confirm any header setting, and click OK to remove repeated entries.
Remove repeated list entries with a built-in duplicate toolOpen and edit Microsoft Excel XLS and XLSX filesWork with a familiar spreadsheet interfaceUse WPS Office for free
QA img-9

Frequently Asked Questions

Does Remove Duplicates delete every copy of a repeated value?

No. Data > Remove Duplicates keeps the first occurrence and removes later matches from the selected data.

How do I exclude values that appear more than once?

In a supported version of Excel, enter =UNIQUE(A2:A100,,TRUE) in an empty cell, changing the range to match your list.

Can I remove duplicates based on more than one column?

Yes. Select the full table, open Data > Remove Duplicates, and check the columns whose combined values should determine a duplicate.

Why does my UNIQUE formula show a spill error?

The formula needs empty cells for its results. Clear any entries blocking the output range below the formula cell.

Can I undo a Remove Duplicates operation?

Yes. Immediately after removing duplicates, press Ctrl+Z or select Undo to restore the removed entries.