Excel Filter: Hide Duplicate Values Without Deleting Rows

Formulas & functions · Updated November 19, 2026

Yes. To show each distinct value once without deleting data, use Data > Advanced and select Unique records only, or enter Excel’s UNIQUE function in a separate area. The ordinary Filter menu does not have a switch that hides repeated occurrences while keeping the first one.

What “don’t show duplicates” can mean

People use “duplicates” to describe two different things in a spreadsheet. You might want to see a list of distinct names or products, with each value appearing once. Or you might want to identify records that are repeated, then hide or review those rows. The right method depends on which result you need and whether you want to keep the original data visible.

For example, if a column contains Kiwi, Pear, Kiwi, and Plum, a distinct list is Kiwi, Pear, Plum. It removes the repeated appearance of Kiwi from the result, but it does not delete either Kiwi from the source. By contrast, a filter for Kiwi shows both rows that contain Kiwi. And Remove Duplicates deletes repeated entries from the selected range, which is a different, more permanent action.

It also matters whether you are comparing one column or entire rows. Two rows with the same customer name but different order numbers are duplicates by name, but not identical records. Decide what counts as a duplicate before choosing a tool.

Why the standard Filter menu still shows repeated values

Excel’s regular AutoFilter is designed to show or hide rows according to values or conditions. Open the drop-down on a column heading and select a value, and Excel displays every row matching that value. If “Kiwi” appears five times, filtering for Kiwi shows five rows—not one representative Kiwi row.

The checkboxes in that drop-down can make the feature seem like it should remove duplicates: the list of choices normally shows each distinct value once. But that list is a set of filter criteria, not a deduplicated version of the worksheet. Selecting Pear keeps all rows whose cell is Pear.

Use the normal filter when you want to narrow a table by a category, date, number, or text condition. For example, it can show all orders for one customer or all records from a selected month. If you need a separate list of distinct entries, use Advanced Filter or a formula instead.

Use Advanced Filter to show unique records

Advanced Filter is the built-in option for filtering a list to unique records without deleting the source rows. It can filter the original list in place, or copy the unique results somewhere else. To use it in Excel for Microsoft 365 on Windows:

  1. Make sure the list has a heading in its first row, such as Product, and avoid blank rows inside the data.
  2. Select a cell in the list. If necessary, select the full range, including its heading.
  3. Choose Data > Sort & Filter > Advanced.
  4. In the Advanced Filter dialog, choose Filter the list, in-place to hide repeated records in the original list, or Copy to another location to create a separate result.
  5. Check Unique records only, confirm the list range and, if copying, specify the destination. Leave Criteria range blank when you are not applying an additional condition, then choose OK.

Advanced Filter compares the complete records in the list range. If your range includes Customer, Order ID, and Date, two rows are considered duplicates only when all those fields match. If you want unique customer names regardless of order, apply Advanced Filter to the customer column alone, including its heading. This distinction prevents a common surprise: choosing “unique records” for a whole table will not collapse rows that share a name but have different details.

When filtering in place, the other rows are hidden rather than erased. To show them again, clear the filter using Data > Sort & Filter > Clear. Advanced Filter is available in desktop Excel; commands and dialog presentation can differ by platform and release. Excel for the web may not expose the same Advanced Filter dialog, so use a formula there if the command is unavailable.

Make a separate unique list with UNIQUE

If you have Microsoft 365 or Excel 2021 or later, UNIQUE is often the simplest way to produce a live list. It returns distinct entries from a range and updates when the source data changes. Put the formula in an empty cell outside the source range, leaving space for the results to spill downward.

With product names in A2:A8, where the entries are Kiwi, Pear, Kiwi, Plum, Pear, Lime, and Grape, enter =UNIQUE(A2:A8) in an empty cell. The result is Kiwi, Pear, Plum, Lime, and Grape, in the order each value first appears. The source list remains unchanged.

To return only entries that occur exactly once in the source, use the function’s third argument. With the same data in A2:A8, enter =UNIQUE(A2:A8,,TRUE). The result is Plum, Lime, and Grape, because Kiwi and Pear each occur twice. This is not the same as returning one copy of every distinct value: the normal formula includes Kiwi and Pear once, while the exactly-once option excludes them.

UNIQUE is a dynamic-array function. It spills results into nearby cells, so the destination area must be clear; otherwise Excel can show a #SPILL! error. Do not type into the middle of a spilled result. On a Mac, the function works in supported Microsoft 365 and Excel 2021-or-later versions as well; the formula is the same. In older Excel versions without UNIQUE, use Advanced Filter or a helper column.

Use a helper column to filter the original rows

A helper formula is useful when you want to keep the original table and apply an ordinary AutoFilter to a flag such as First occurrence or Appears once. This also makes the rule visible to colleagues. Add a heading for the helper column, enter the formula in its first data row, fill it down, and filter that helper column.

With values in A2:A8, enter =COUNTIF($A$2:A2,A2) in B2 and fill down. The results for the example list are 1, 1, 2, 1, 2, 1, and 1. Filtering column B for 1 keeps the first appearance of each value and hides later repeats. It does not identify values that occur exactly once: Kiwi’s first row also has a 1, even though Kiwi appears again.

To keep only values that occur once in the entire range, use a full-range count. With the same values in A2:A8, enter =COUNTIF($A$2:$A$8,A2)=1 in B2 and fill down. The result is TRUE for Plum, Lime, and Grape, and FALSE for Kiwi and Pear. Filter column B for TRUE. If your list grows, update the formula range or use an Excel Table so the data and helper formula can expand together.

These examples compare values in a single column. For duplicate complete rows, a helper formula needs to compare all relevant fields, or you can use Advanced Filter on the full record range. A count based on just one field can flag matching names even when the rest of their records differ.

Choose the method that fits your goal

  • Want a quick, separate list that updates? Use UNIQUE in Microsoft 365 or Excel 2021 or later.
  • Want to hide repeated records in the existing list? Use Advanced Filter with Filter the list, in-place and Unique records only.
  • Want a separate, static copy of distinct records? Use Advanced Filter with Copy to another location.
  • Want to filter by first appearance or occurrence count? Add a COUNTIF helper column, then filter its results.
  • Want to permanently remove repeated rows? Use Remove Duplicates only after preserving a backup or confirming that deletion is intended.

Remove Duplicates is found under Data > Data Tools > Remove Duplicates in desktop Excel. It changes the selected data rather than merely hiding rows, and it keeps one occurrence according to the columns you select. If you select only Product, Excel can remove rows with repeated products even if other columns contain different information. Make a copy first when those other details may matter.

Common reasons the result looks wrong

The rows look the same but are not exact matches. A trailing space, a different punctuation mark, or another field that differs can affect whether Excel treats entries as duplicates. For example, “Pear” and “Pear ” may appear identical on screen but contain different text. Clean or standardize the source if those differences should not count.

The filter compares more columns than expected. Advanced Filter’s unique-record check applies to the list range. If you select an entire table, differences in any included field mean the rows are distinct. Select just the field you want to deduplicate, or use a formula that compares the intended fields.

The formula result does not fit in its destination. UNIQUE needs empty cells for its spill range. Clear obstructing cells or move the formula to an open area. Also check whether the formula was entered inside an Excel Table: dynamic-array results should be placed outside the table rather than in a table column.

Case or whitespace matters to your data. Excel’s standard duplicate-removal and counting tools generally do not distinguish uppercase from lowercase text for these comparisons, so “kiwi” and “Kiwi” are usually treated as the same value. Extra spaces are different characters, however. If case-sensitive uniqueness is essential, a separate, more specialized formula or cleanup process may be required.

The list contains a heading or blank cells. Include the heading when setting up Advanced Filter and verify that Excel has recognized it as a header. Decide how blanks should be handled before interpreting a unique list; a blank can be treated as a distinct entry rather than being ignored. If blanks are irrelevant, remove or exclude them deliberately.

Frequently Asked Questions

Can I hide duplicates without deleting them?
Yes. Use Advanced Filter with Filter the list, in-place and Unique records only, or use a helper column and filter it. Both approaches preserve the underlying records; Remove Duplicates does not.

How do I show only the first occurrence of each value?
With values in A2:A20, use a running COUNTIF helper such as =COUNTIF($A$2:A2,A2), fill it down, and filter for 1. This keeps the first instance of each value, even when that value appears again later.

How do I show values that appear only once?
In Microsoft 365 or Excel 2021 or later, use =UNIQUE(A2:A20,,TRUE) in a clear area. Alternatively, count each value across the full range with a COUNTIF helper and filter for a count of 1.

Does the regular Excel Filter remove duplicate values?
No. AutoFilter hides or shows rows according to criteria. Selecting one value displays every row that contains it; it does not reduce matching rows to one occurrence.

Does Advanced Filter compare the whole row?
Yes. It compares records across the selected list range. To get unique values from just one field, apply it to that column rather than to the full table. To get distinct full records, select all the fields that define a record.

Can I do this in Excel for the web?
UNIQUE is available where supported by the web version and account, and a helper-column formula can also work. If you cannot find the desktop Advanced Filter command, use one of those methods or open the workbook in desktop Excel.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.