Hide Duplicate Rows in Excel with a Filter Step by Step

Formulas & functions · Updated November 20, 2026

To hide exact duplicate rows in Excel without deleting them, select the full data range, then choose Data > Sort & Filter > Advanced, select Filter the list, in-place, and check Unique records only. If you want to define duplicates by just one or two columns instead of matching entire rows, add a helper column, filter it to show first occurrences, and leave the original rows in the sheet.

What Excel Means by a Duplicate

Excel’s Advanced Filter considers a record a duplicate when all the values in the selected list range match another record. For example, if a row contains an order number, customer, and date, another row is a duplicate for this filter only when the values in all three selected fields match. Two rows with the same customer but different dates are not exact duplicates.

This distinction matters because people often use “duplicate” to mean repeated values in a particular column. A list might have several transactions for the same customer, and the goal may be to display only the first transaction per customer. Advanced Filter cannot express that rule by itself: use a helper column for a chosen key, such as customer ID, or use a formula-based output if you need a separate list.

Filtering hides rows from view; it does not remove them from the worksheet. That makes filtering useful for reviewing a clean-looking list while keeping all records available. Hidden rows can still affect other calculations, so a filter should not be treated as deletion or as proof that the underlying data has been cleaned.

Hide Exact Duplicate Rows with Advanced Filter

In Excel for Microsoft 365 on Windows, use these steps to filter an existing list in place:

  1. Make sure the data has one header row, with a different heading above each column. Remove completely blank rows or columns from the middle of the list.
  2. Click a cell in the data, or select the entire range including its headers. Check that the selection covers every column that defines a full record.
  3. Open Data > Sort & Filter > Advanced.
  4. In the Advanced Filter dialog, choose Filter the list, in-place. Confirm that the List range contains the full table and its headers.
  5. Check Unique records only, then select OK.

Excel hides repeated records and leaves one occurrence of each distinct record visible. To see the hidden rows again, use Data > Sort & Filter > Clear, or clear the filter from the relevant column if the interface offers that option. Clearing the filter restores the view; it does not alter the source values.

You can also use Advanced Filter to copy the unique records to another location instead of filtering the source list. In the dialog, choose Copy to another location, specify a destination cell, and check Unique records only. This is handy for producing a separate snapshot, but the copied results do not automatically update when the source data changes. Keep the destination clear of existing content so the output has room to expand.

Use a Helper Column for Duplicates by a Key

For duplicates defined by a key such as an ID, a helper column gives you control over which rows remain visible. Suppose IDs are in A2:A100, with a heading in A1. Add a heading such as Keep first in C1. With the ID in A2, enter this formula in C2 and fill it down the data:

With IDs in A2:A100 and the formula being entered on the row containing A2, use =AND(A2<>"",COUNTIF($A$2:A2,A2)=1). It returns TRUE for the first nonblank occurrence of an ID and FALSE for later occurrences. For example, if the IDs are 101, 102, 101, 103, the results are TRUE, TRUE, FALSE, TRUE.

Turn on filters by selecting a cell in the range and choosing Data > Filter. Open the dropdown in the helper-column heading and select only TRUE. The later occurrences are hidden, while the first row for each ID remains visible. The formula checks from the first data row down to the current row, so it treats the first occurrence in the current row order as the one to keep.

If a duplicate means that two fields together match, the formula can check both. With IDs in A2:A100 and locations in B2:B100, and the same first data row, use =AND(COUNTA(A2:B2)>0,COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1) in the helper column and fill it down. This marks the first occurrence of each ID-and-location combination. The COUNTA condition prevents a completely blank row from being marked as a record; a partly filled row is still evaluated as a combination of its cell values.

When applying a helper-column filter, keep the helper column inside the same table or contiguous range as the records. Filtering just the helper values without the full data range risks showing a set of flags that no longer lines up with the rows you intended to review.

Choose the Method That Matches Your Goal

  • Hide identical full rows temporarily: Use Advanced Filter with Unique records only. It compares every selected field.
  • Keep one row per ID, email, or other key: Use a helper formula and filter for TRUE. Decide which occurrence to keep by sorting the data first if necessary.
  • Make a separate, refreshable list: In Excel for Microsoft 365 or Excel 2021, the UNIQUE function can return distinct values or rows in a separate area. It produces results rather than hiding source rows.
  • Permanently remove repeated records: Use Data > Data Tools > Remove Duplicates only if deleting the extra records is intended. Make a copy first, because this command changes the data.

These options answer different needs. Advanced Filter is a convenient view of unique full records, a helper column applies your own definition of a duplicate while retaining the source, and Remove Duplicates edits the source list. A dynamically generated unique list is useful for reports, but it is not a filter on the original table.

Keep the Filter Reliable as Data Changes

A filter works only on the range it covers. If new records are added below the original range, they may not be included in the current filter or Advanced Filter list range. In Microsoft 365 desktop Excel, formatting the data as a table with Home > Format as Table helps the range expand as rows are added. Check that the header option is selected if the first row contains field names.

Helper formulas should also be filled into new rows. When a range is an Excel table, a formula entered in a calculated column is generally propagated through that column, which reduces the chance that new records lack a duplicate flag. After adding data, check the filter range and the helper formulas rather than assuming the previous view includes every new row.

Sorting can change which row is considered the first occurrence by a helper formula. If you want to retain the newest record per ID, sort by date from newest to oldest before evaluating the formula; if you want the oldest, sort oldest to newest. The filter itself does not decide which record is best to keep—it follows the order and rule you provide.

Common Problems and Their Fixes

  • Duplicates still appear: The rows may differ in a field you included, such as a date, space, or status. Check all selected columns and inspect the values. For an ID-based definition, switch to a helper formula that checks only the ID.
  • Distinct customers disappear even though their names match: Advanced Filter compares the entire selected record, but a helper formula can focus on the intended key. Use a stable ID where available rather than names that may be spelled inconsistently.
  • New rows are not filtered: Expand the list range or use a table that grows with added records, then reapply or update the filter.
  • Blank rows get unexpected flags: Use a formula that explicitly ignores empty keys or wholly blank records. The examples above exclude a blank ID in the single-key case and a completely blank A:B row in the two-field case.
  • The filter button is missing: Click inside the list and choose Data > Filter. A clean header row and a contiguous data range make it easier for Excel to identify the list correctly.

Excel’s duplicate checks are based on cell values for the selected range, not on whether two cells merely look alike on screen. For example, a date displayed as “Jan 5” may include a year that is not obvious from the format. If two entries appear identical but behave differently, inspect their underlying values and number formats before changing or deleting records.

Excel for Mac and Excel for the Web

The steps above use Microsoft 365 for Windows. Excel for Mac also has filtering and Advanced Filter features, but ribbon grouping and dialog layout can differ by release; look on the Data tab for Advanced and confirm the dialog options before applying an in-place filter. If a command is not visible, check that the workbook is open in the desktop app and that the ribbon is expanded.

Excel for the web supports ordinary filters, so a helper column can be used to show TRUE rows there. The desktop Advanced Filter workflow may not be available in the web interface. If you need its in-place Unique records only option, open the workbook in desktop Excel. Web and desktop versions can also differ in which functions are available, so use a helper formula supported by the version in which the workbook must operate.

Frequently Asked Questions

Can I hide duplicates without deleting them?
Yes. Use Advanced Filter with Filter the list, in-place and Unique records only for identical full rows, or filter a helper column to TRUE for duplicates defined by a particular key. Both approaches hide records rather than deleting them.

Does Advanced Filter find duplicate values in just one column?
Not when the list range includes multiple columns: its unique-record setting compares complete records across the selected range. To keep one record per value in a particular column, use a helper formula based on that column or create a separate unique list.

How do I show all the rows again?
Clear the filter with Data > Sort & Filter > Clear, or open the filtered column’s dropdown and choose the option to clear its filter. The rows reappear because filtering changes visibility, not the stored records.

Can Excel automatically hide new duplicates as I type?
A normal filter is not a permanent rule that automatically maintains a unique-only view in every situation. A table and helper formula make it easier to include new rows, but you may still need to confirm the filter range and selected values. For a separate automatically updating unique list, use UNIQUE in a compatible Excel version.

Will Remove Duplicates keep the first record?
Remove Duplicates removes repeated entries from the selected range and keeps one occurrence, but it is a data-changing command, not a hide filter. Review the selected columns carefully and preserve a copy first if the source records need to remain available.

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.