To filter duplicate values in Excel, add a helper column with a COUNTIF formula, then turn on the filter and show only rows marked Duplicate. This lets you review every matching row without deleting data. The steps below are for Microsoft Excel for Microsoft 365 on Windows; the formula and menu approach also works in current Excel for Mac and Excel for the web.
Excel does not have a single filter command that automatically displays duplicates. A helper column identifies them, and Excel’s regular filter hides the rows you do not want to see. This is different from Remove Duplicates, which deletes repeated records.
What You’ll Need
- A worksheet with a header row and the values you want to check in one column. For example, names could be in column A.
- A blank column beside your data for the duplicate labels.
- A clear idea of what counts as a duplicate: a repeated value in one column, or a repeated complete row. The steps here check one column.
If your data has no headers, add one before applying a filter. Headers help Excel identify the range correctly and give the filter dropdowns meaningful labels.
Step-by-Step
- Check the data and choose the field. Decide which column defines a match. If you want to find repeated email addresses, check the email column rather than a name column. Make sure records occupy consistent rows and that the first row contains headers. If you need to identify duplicate complete records, see the note in Tips; checking just one field will not establish that every value in a row is repeated.
- Add a helper-column header. In the first empty column beside the data, type a clear heading such as Duplicate status. For this example, assume the values to check are in A2:A100, the header is in A1, and the helper heading is in B1. Adjust the range to fit your actual list. Avoid including unrelated cells below the data.
- Enter the formula in the first data row of the helper column. With the values to check in A2:A100, enter this in B2. It leaves blank source cells blank and labels a value Duplicate if it appears more than once in the checked range; otherwise, it labels it Unique.
=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"))
For example, if A2:A7 contains North, South, North, West, a blank cell, and West, the formula marks both North rows and both West rows Duplicate. South is Unique, and the blank row stays blank. The dollar signs keep the checked range fixed when you copy the formula. - Fill the formula down. Select B2, then drag the small square at the bottom-right corner of the cell down to the last row of your data. You can also copy B2, select the remaining helper cells, and paste. Check the bottom few rows to confirm the formula reached the end and refers to the intended source column. If your data is an Excel Table, entering the formula in the first helper cell will usually fill the calculated column automatically.
- Turn on Excel’s filter. Click a cell inside the data range, then choose Data > Filter. Excel adds dropdown arrows to the header row. On Windows, Ctrl+Shift+L toggles filters on or off. In Excel for Mac, the equivalent shortcut is Command+Shift+F; the ribbon command is also available. Excel for the web uses Data > Filter.
- Show only the duplicate rows. Open the dropdown on the Duplicate status header. Clear Select All, select Duplicate, and click OK or Apply, depending on your Excel version. The visible rows are those whose values occur at least twice in the checked range. The other rows are hidden, not removed.
- Review the matching records. Look across each visible row, not just the repeated cell. Compare related details such as dates, IDs, or addresses before deciding whether records are truly redundant. A shared value can be legitimate—for example, two separate orders may belong to the same customer.
- Return to the full list when finished. Open the helper-column dropdown and choose Select All, or use Data > Clear to clear the active filter criteria. Clearing a filter shows hidden rows again; it does not erase the helper formula or the data. If you no longer need the helper column, remove it only after you have finished reviewing the results.
The formula above finds every occurrence of a repeated value, including its first appearance. If instead you want to flag only the second and later appearances, use a running count. With the values still in A2:A100, enter this alternative formula in B2 and fill it down: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Repeat after first","First or unique")). For North, South, North, this marks the first North as First or unique and the second North as Repeat after first. Filter for that second label to see only the additional copies. Use one formula approach or the other according to what you need to review.
Common Mistakes
- Choosing Remove Duplicates instead of Filter. Data > Remove Duplicates removes records from the selected range. It is useful when you deliberately want to keep one copy, but it is not a way to inspect duplicates while preserving every row. Keep a backup before using it.
- Checking the wrong column. A repeated city or customer name does not necessarily mean the whole record is duplicated. Select the field that defines a match, and inspect the other columns before deleting anything.
- Using the wrong formula range. If the formula checks A2:A100 but your data extends to row 140, values in later rows will not be considered when earlier rows are evaluated. Update the range to include all relevant records.
- Leaving gaps in the helper column. If the formula is not copied through every data row, some records will have no status and will disappear when you filter for Duplicate. Fill down to the final row and verify the bottom of the list.
- Expecting case-sensitive matching. COUNTIF is not case-sensitive, so Excel generally treats ABC and abc as the same value. It can also treat leading or trailing spaces as meaningful differences. Clean or standardize values first if those distinctions matter.
Tips
- Make the checked range easy to maintain. For a list that grows regularly, convert the data to a Table with Insert > Table and confirm that the header option is selected. Tables expand as you add records and can automatically carry the helper formula into new rows. Verify the formula’s references after adding data.
- Keep a copy before cleanup. Filtering is a reversible view, but deleting visible rows or using Remove Duplicates changes the data. Save a copy of the workbook before making permanent edits, especially if the source list is important.
- Use conditional formatting for a quick visual scan. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values can highlight repeated cells. This is helpful for spotting matches, but it only formats cells; it does not filter rows or tell you whether an entire record is duplicated.
- For duplicate complete rows, create a combined check. A match in one column is not enough if several fields must agree. You can make a helper key that joins the relevant fields, or check the individual fields together with a formula designed for your layout. Be careful with joined values: separators and formatting can affect whether two records produce the same key.
- Check unusual symbols and data consistency. COUNTIF treats the asterisk (*) and question mark (?) as wildcard characters in criteria. If those symbols occur as literal parts of your values, results may not match your expectation. Likewise, values stored inconsistently as text and numbers, or entries with hidden spaces, can complicate comparisons. Standardize the source before interpreting the results.
Troubleshooting
No duplicates appear, but you expect some. Confirm that the formula points to the full source range, is filled down, and refers to the column you intend to compare. Check for extra spaces, different spellings, or values stored differently. For example, an email address with a trailing space may not match the same address without one. You can inspect suspicious cells in the formula bar or clean a copy of the data before comparing it.
Too many rows are marked Duplicate. Check whether the selected field is supposed to repeat. Common fields such as status, city, or category often appear many times without indicating duplicate records. COUNTIF also ignores capitalization, so entries that differ only by letter case are treated as matches. If literal asterisks or question marks occur in values, remember that COUNTIF can interpret them as wildcards.
The filter arrow is missing. Click inside the data and select Data > Filter. If the range has blank rows or columns separating parts of the list, Excel may detect only part of the data; remove the gaps or select the full range before enabling the filter. Also check whether the worksheet is protected or whether a filter is already applied to a different range.
The filter hides rows you wanted to keep. Open the dropdown on the helper column and confirm that Duplicate is selected. If you used the alternative running-count formula, select Repeat after first instead. To restore all rows, clear the criteria or choose Select All in that dropdown. Hidden rows are still present in the worksheet.
New rows are not included. If you used a fixed range such as A2:A100, edit the formula range to include the new last row and fill the helper formula into the added rows. Converting the list to a Table can reduce this maintenance, but confirm that both the formula and filter include new entries.
Frequently Asked Questions
Does Excel have a built-in duplicate filter?
Excel has filters and duplicate-highlighting tools, but no single command that filters a list by duplicate status. A helper column with a formula such as COUNTIF, followed by a normal filter, provides that result.
Does filtering duplicates delete any data?
No. A filter hides rows that do not meet the selected criteria; clearing the filter brings them back. Remove Duplicates is a separate command that deletes repeated records from the selected data.
Can I show unique values instead?
Yes. With the helper formula in this guide, open the Duplicate status dropdown and select Unique rather than Duplicate. Blank source cells are left blank by the formula, so they will not be labeled Unique.
How do I check duplicates across multiple columns?
Decide which fields must match, then compare those fields together rather than relying on only one column. You can create a combined helper key or use a formula designed to compare multiple criteria. Inspect the full records before removing anything, particularly if values can contain the separator used in a combined key.
Will this work in Excel for Mac or Excel for the web?
The COUNTIF helper-column method and standard filter are available in current Excel for Mac and Excel for the web. Ribbon placement may vary slightly, but use Data > Filter. On Mac, Command+Shift+F is the filter shortcut; on Windows, use Ctrl+Shift+L.
Related
- How to Copy an Excel Spreadsheet Safely
- Undo in Excel: Reverse Your Last Action
- How to Change Caps to Lowercase in Excel Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
- Excel Sum a Row Based on a Column Header with SUMIFTo sum values across a row when their column headers match a label, use SUMIF: with headers in…
- Nested IF Statements in Excel: Examples, Limits, and TipsNested IF statements in Excel put one IF function inside another, letting a formula choose among several outcomes.…
- Count Excel Rows After a Filter: Formulas That WorkTo count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every…
- Excel Cell Color by Hex: Apply, Match, and DisplayYes. In Excel for Microsoft 365, you can apply a cell fill that corresponds to a hex color,…
- How to Calculate Row Totals in Excel with SUM FormulasTo calculate a row total in Excel, add a SUM formula in the cell where you want the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.