Excel Unique Records Only Not Working? Fix the Filter

If Excel’s Unique records only option is not giving the result you expect, check the list range first: Advanced Filter identifies unique records by comparing complete rows across every selected column. In Excel for Microsoft 365 on Windows, select a cell in the list, go to Data > Sort & Filter > Advanced, confirm the list range and headers, then select Unique records only.

What “Unique records only” actually means

The checkbox belongs to Excel’s Advanced Filter. It returns one instance of each distinct record in the range; it does not mean “keep rows where the value in my chosen column appears once.” If you select columns A through C, Excel compares the values in A, B, and C together. Two rows count as duplicates only when the record across the selected columns matches.

For example, if a list contains “Maya, North, 12” twice, those are duplicate records. “Maya, North, 12” and “Maya, North, 15” are different records, even though the name and region match. This is the most common reason the result looks wrong: a reader expects uniqueness by customer name, but the selected range includes order date, amount, or another column that differs.

Advanced Filter can either hide repeated rows in the original list or copy the unique records elsewhere. Filtering in place does not delete the other rows; they are hidden by the filter. Copying to another location creates a separate result and leaves the source list intact.

Run Advanced Filter with the right settings

Make sure the list has a single header row, such as Customer, Region, and Orders. Avoid blank header cells.
Click a cell in the list, or select the full list including its headers. Check that the selection covers every column that should define a record.
Choose Data > Sort & Filter > Advanced.
In the dialog, verify List range. If you want to filter based on conditions, enter a valid Criteria range; otherwise leave it blank.
Choose Filter the list, in-place to hide duplicates in the source, or Copy to another location to create a separate output. For a copy, specify an empty destination outside the source list.
Select Unique records only, then choose OK.

If you filter in place, use Data > Clear in the Sort & Filter group to show the full list again. Clearing the filter reveals the original rows; it does not remove duplicates permanently.

Check the list range and column headers

A wrong or incomplete list range can make the option appear unreliable. For example, selecting only column A asks Excel to find unique values in A, not unique combinations across A, B, and C. Selecting an extra column has the opposite effect: a difference in that extra field makes otherwise similar records count as distinct.

Use one continuous rectangular range with one header row. Check the range displayed in the dialog instead of assuming Excel selected the whole dataset. Blank rows or columns can cause Excel to detect only part of a list when you start from a single cell. If that happens, select the complete range manually. Merged cells, repeated header rows within the data, and stray values below or beside the list can also make the detected area confusing.

Headers matter especially when using criteria or copying selected fields. Make each header distinct and keep it in the first row of the list. When copying, ensure there is clear space for the output so it cannot overwrite the source. If the copied output contains unexpected columns, recheck which fields the range includes and the column labels specified for the destination.

Understand why rows still look duplicated

Two rows may look identical on screen but contain different values. A date displayed as “Nov 2” might include a different year or time; a number may have extra decimal places hidden by its number format; and text may contain a leading or trailing space. Formatting controls appearance, not whether the underlying cell contents match.

To investigate, compare the cells in the suspect rows directly. Widen columns, increase displayed decimal places, or inspect the formula bar. You can also copy the suspect values to a blank area and use Home > Find & Select > Replace carefully to remove known unwanted spaces, or use Excel’s trimming and cleaning tools in a helper column. Do not mass-replace spaces if spaces are meaningful in the data.

Extra columns: A different value anywhere inside the selected range makes the full records distinct.
Hidden characters: Imported text can contain spaces or nonprinting characters that are hard to see.
Number formats: Values that display alike can have different underlying precision or date/time values.
Incomplete selection: Excel may be checking fewer rows or columns than intended.
In-place filtering: Repeated rows may still exist in the worksheet, simply hidden from view.

Excel’s duplicate tools generally do not treat capitalization alone as a reliable distinction between records. If uppercase and lowercase need to count as different, ordinary Advanced Filter is not a suitable case-sensitive test; the comparison logic needs to be designed explicitly rather than relying on how the cells look.

Use criteria without changing what counts as unique

Advanced Filter can combine a criteria range with the unique-record option. The criteria range must use a header that matches a column header in the list, with the condition beneath it. For example, a criteria range headed Region with North below it can limit the records to that region. Put the criteria outside the list so it does not become part of the data range.

Criteria decide which records qualify for the filter; the selected list columns decide what Excel compares when identifying distinct records. If the output is surprising, check these two things separately: whether the criteria include the rows you intended, and whether the list range includes only the fields that should define a record. Multiple criteria can have different meanings depending on whether they are placed on the same row or separate rows, so keep the criteria layout simple and verify it on a small copy of the data first.

Use the UNIQUE formula for a live result

For a result that updates when the source changes, the UNIQUE function is often easier than repeatedly opening Advanced Filter. It returns distinct values or rows in a spill range. It is available in Microsoft 365 and Excel 2021 or later; the result cells below and beside the formula must be clear so the array can spill.

With customer names in A2 and regions in B2, through row 6, and the records Maya/North, Luis/South, Maya/North, Maya/West, and Luis/South in that order, enter this in D2 to return distinct two-column records:

=UNIQUE(A2:B6)

The result is three rows: Maya/North, Luis/South, and Maya/West. The repeated Maya/North and Luis/South records appear once each. This is comparable to Advanced Filter on the two selected columns, but the formula result recalculates as the source changes.

With the same A2:B6 data, enter this in D2 if you want only records that appear exactly once:

=UNIQUE(A2:B6,FALSE,TRUE)

That result is Maya/West only, because it occurs once; Maya/North and Luis/South each occur twice. This distinction is important: Unique records only keeps one copy of each distinct record, whereas the third argument of UNIQUE set to TRUE returns records occurring exactly once. The formula’s second argument, FALSE, tells Excel to compare rows rather than columns.

Advanced Filter versus Remove Duplicates

These commands solve related but different problems. Advanced Filter can display unique rows in place or copy them to another location, leaving the original records available. Data > Remove Duplicates deletes duplicate rows from the selected data and keeps one instance. That command changes the source list, so make a copy or save a backup if you may need the removed rows.

In the Remove Duplicates dialog, the selected columns determine how Excel identifies duplicates. Selecting only Customer can remove rows that share a customer even when their other fields differ; selecting every field removes only records matching across all those fields. Read the column choices before confirming. Use Advanced Filter for a non-destructive distinct list, Remove Duplicates to clean the source itself, and UNIQUE when you want a formula-driven output.

Excel for Mac and Excel for the web

In Excel for Mac, Advanced Filter is available from the Data tab; the dialog and the same range and unique-record principles apply, though ribbon grouping can differ by release. Keyboard shortcuts also differ: Windows commonly uses Ctrl, while Mac uses Command for many general actions. The Advanced Filter command is easiest to locate from the Data tab rather than relying on a shortcut.

Excel for the web does not provide the same full Advanced Filter dialog as the Windows desktop app in every experience. If you cannot find the command, use a supported UNIQUE formula for a dynamic list, or open the workbook in desktop Excel. The Remove Duplicates command, when available in the web ribbon, removes data rather than simply producing a separate non-destructive filtered view.

A quick troubleshooting checklist

Did the list range include all rows and only the columns that define a record?
Does the first row contain clear, unique headers?
Are apparently identical values actually different because of spaces, times, precision, or imported characters?
Did you choose in-place filtering or copy to another location, and are you interpreting the result accordingly?
Do you want one copy of each distinct row, or only rows that occur exactly once?
Would a backup, formula result, or separate output sheet be safer than deleting duplicates?

Frequently Asked Questions

Why does Excel keep rows that have the same name?
Because Advanced Filter compares all columns in the list range. If another selected field differs, such as region, date, or amount, the rows are distinct records. Select only the fields that should define uniqueness, or use a suitable formula.

Does “Unique records only” delete duplicate rows?
No. Filtering in place hides rows, and copying to another location makes a separate result. Use Data > Remove Duplicates to delete repeated records from the selected source range; consider making a copy first.

How do I keep only values that appear once?
That is different from keeping one copy of each distinct value. In Microsoft 365 or Excel 2021 and later, UNIQUE with its exactly-once argument set to TRUE returns items that occur once. For records, compare the full range of relevant columns.

Why does Advanced Filter show duplicates that look identical?
Check for differences that are not obvious on screen, including extra spaces, hidden characters, different date or time values, and decimal precision. Also confirm that the dialog’s list range is correct and that the repeated rows are not merely hidden or shown in a separate output.

Can I make the unique list update automatically?
Yes. In Microsoft 365 or Excel 2021 and later, place a UNIQUE formula in an empty area. Its spilled result updates when the referenced data changes, provided the spill area is not blocked.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.