To show duplicate values in Excel, select the column, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then filter that column by its highlight color. For a more reliable filter you can reuse or adapt, add a helper column with COUNTIF, turn on Data > Filter, and show rows marked TRUE.
What Excel means by duplicates
A duplicate is a value that appears more than once in the range you are checking. Excel’s built-in Duplicate Values rule highlights every occurrence, including the first one—not just the later copies. If Mina appears in rows 2 and 5, both cells are duplicates for this purpose.
Decide what counts as a duplicate before filtering. You might mean repeated email addresses in one column, or entire records with the same name and date. These are different checks: a repeated email can occur in otherwise different records, while a record-level duplicate requires matching values in multiple fields. Also decide whether blank cells should count. Several blank cells are repeated values, but they are rarely useful duplicates in a list of customer or product data.
Use conditional formatting for a quick visual filter
For a one-time check in Microsoft Excel for Microsoft 365 on Windows, the built-in rule is the fastest approach when the values to compare are all in one column:
- Select the data cells in the column, excluding the heading. For example, select A2:A100.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the dialog, leave Duplicate selected, choose a formatting style, and select OK.
- Select a cell in the data range and choose Data > Filter, or press Ctrl+Shift+L on Windows.
- Open the arrow in the column heading, choose Filter by Color, and select the fill color used by the rule.
The filtered list now shows cells Excel marked as duplicates. The rule’s range matters: selecting only the values in column A checks repetitions in A, whereas selecting a larger block can apply the rule to cells throughout that block. For a clean filter by color, apply the rule to the particular column you intend to filter. If the formatting color changes or the rule is removed, the color filter may no longer represent the duplicate check.
On Mac, the Conditional Formatting commands are also on the Home tab, and Data > Filter is available; use the ribbon menu if the Windows keyboard shortcut differs in your setup. In Excel for the web, the ribbon includes conditional formatting and filtering, though some dialogs and options can look different from the Windows desktop app.
Use a COUNTIF helper column for dependable filtering
A helper column makes the test explicit and is usually better when you need to keep the result, inspect it, or repeat the process after the data changes. Suppose names are in A2:A8 and you want to mark every name that appears more than once. Put a heading such as Duplicate? in B1. With names in A2:A8, enter this in B2 and fill it down through B8:
=AND(A2<>"",COUNTIF($A$2:$A$8,A2)>1)
With the example values Mina, Lee, Omar, Mina, Priya, Lee, and Sol in A2:A8, this returns TRUE for both Mina rows and both Lee rows, and FALSE for Omar, Priya, and Sol. The first part excludes empty cells; the COUNTIF part counts how often the current row’s value occurs in the full comparison range. A count greater than one means that value is repeated.
Next, choose Data > Filter, open the arrow in B1, clear Select All if needed, and select TRUE. Excel displays the rows flagged as duplicates. To include the headings and other fields in the filtered results, make sure the filter covers the entire table, not just columns A and B. If your data is an Excel table, adding a formula in the new table column typically fills it down automatically, and the table’s filter arrows make the TRUE/FALSE choice easy to repeat.
The dollar signs in $A$2:$A$8 lock the counting range as the formula is filled down; A2 changes to A3, A4, and so on. When your list grows, extend the range or use a table column reference so new rows are included. COUNTIF compares text without distinguishing uppercase from lowercase, so Mina and MINA are treated as a match. It can also interpret asterisks and question marks in criteria as wildcard characters, which can matter if those symbols occur literally in your data.
Check duplicate records across more than one column
Sometimes one field is not enough to identify a repeat. For instance, the same person may legitimately have two orders on different dates, while two rows with the same customer and order date may be an accidental duplicate. COUNTIFS checks multiple conditions together.
Assume customer names are in A2:A100 and order dates are in B2:B100, and a duplicate means the same name-date pair appears more than once. Put a heading in C1. In C2, use this formula and fill it down:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
For a pair that appears twice, the formula returns TRUE on both matching rows; unique pairs return FALSE. Filter column C to TRUE to display the complete duplicate records. This version treats two rows with the same customer and two blank date cells as a repeated pair. If blanks should not be checked, add a condition that excludes them, taking care not to reject blank fields that are meaningful in your data.
For a full-row comparison, include every field that defines record identity in the COUNTIFS test. Avoid combining fields by simply joining them into one text string unless you also handle separators carefully: values such as AB plus C and A plus BC can produce the same joined text. Multiple COUNTIFS criteria avoid that particular ambiguity.
Return matching rows with FILTER
If you want a separate, automatically updating results list rather than filtering the source table in place, the FILTER function can return rows whose key appears more than once. FILTER is available in Microsoft 365 and Excel 2021 or later. Suppose the records are in A2:C8 and the values that define duplicates are in A2:A8. Enter this formula in an empty area:
=FILTER(A2:C8,COUNTIF(A2:A8,A2:A8)>1,"No duplicates")
Using the earlier sample names in A2:A8, the result includes all rows for Mina and Lee, with the other columns from A:C carried along; if no names repeat, it displays No duplicates. The result spills into nearby empty cells, so leave room for it. This creates a displayed copy of matching rows, not an AutoFilter on the original data. If you edit the source, the result recalculates, but it does not replace the source records.
Common problems and how to avoid them
- Only one occurrence appears. The built-in Duplicate Values rule normally identifies all occurrences, but a helper formula may have been written to test for a count greater than one only in a different way. Check the formula’s range and confirm that it is filled down.
- Blank rows are flagged. COUNTIF can count blanks. The AND condition in the single-column example excludes empty cells. If cells contain spaces, they are not truly empty; clean them or check for whitespace separately.
- Values that look alike do not match. Extra spaces, different punctuation, and numbers stored as text can make entries differ. For example, a trailing space in an email address is a different character. Consider cleaning a copy of the data with trimming or consistent formatting before drawing conclusions.
- Unexpected matches appear. COUNTIF is case-insensitive, and wildcard characters in text can affect its criteria. Review the affected values; if uppercase and lowercase must count as different, use a case-sensitive approach rather than relying on the standard duplicate rule.
- Some rows disappear from the filter. Check that the filter range includes every column and row, has a single header row, and contains no accidental blank row splitting the list. Clear existing filters before diagnosing the result.
Before deleting anything, inspect the filtered rows and make a copy of the workbook or source data. Filtering only hides rows; it does not determine which copy is correct. In an orders list, for example, two apparently identical lines might represent separate transactions, so confirm the relevant ID, date, or other business rule first.
Which duplicate method should you choose?
Use conditional formatting and Filter by Color when you need a quick visual scan of repeated values in one column. Choose a COUNTIF helper column when the result must be easy to filter, audit, or refresh, or when you need to exclude blanks. Use COUNTIFS when a duplicate is defined by a combination of fields. Use FILTER when you want a separate live output and have a version of Excel that supports dynamic arrays.
These methods answer different practical needs. Color formatting is convenient but depends on the formatting rule and the range to which it applies. A helper column makes the logic visible beside each record and works with ordinary table filters. FILTER produces a separate list but does not hide rows in the source. None of the methods decides whether a duplicate is an error; that depends on what each row represents.
Frequently Asked Questions
Can Excel filter duplicates without deleting them?
Yes. Apply the Duplicate Values conditional-formatting rule and filter by its color, or use a helper column that returns TRUE for repeated values and filter that column to TRUE. Both approaches leave the source rows in place.
Does Excel show only the second and later copies?
The built-in Duplicate Values rule marks every occurrence, including the first. If you need to identify only later occurrences in their current order, use a running-count formula instead of the standard duplicate check; the result then depends on row order.
How do I find duplicate rows based on two columns?
Use COUNTIFS with both columns as criteria ranges, as in the customer-and-date example above. Filter the helper column to TRUE. Add every field that should define an identical record, and decide how blank fields should be treated.
Is Remove Duplicates the same as filtering duplicates?
No. Data > Remove Duplicates deletes repeated entries from the selected range and keeps one occurrence, so it changes the data. Filtering or highlighting only helps you locate matching rows. Make a copy before using Remove Duplicates if you may need the original records.
Why does the formula mark names with different capitalization as duplicates?
COUNTIF is not case-sensitive, so Mina and MINA match. If capitalization carries meaning in your data, use a case-sensitive formula or normalize the values first; the built-in Duplicate Values rule is also not a case-sensitive test.
Related
- Excel Data Validation List Character Limit Explained
- Excel Data Validation List: Build a Dynamic Source
- Excel Data Validation List Based on a Cell: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Aggregate Functions in UPDATE: What SQL AllowsUsually, no: SQL does not allow an aggregate such as AVG() or SUM() directly as a row-level value…
- How to Find Repeated Names in Excel with COUNTIF FormulaTo find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it…
- Date Minus Days Formula in Excel: Examples and TipsTo subtract days from a date in Excel, use a minus sign: if A2 contains a date and…
- How to Use the Search Function in Excel: Find Data FastTo find something in an Excel worksheet, press Ctrl+F on Windows (Command+F on Mac), type the text or…
- Excel Formula for Next Month: Dates, Month-End, and MoreTo move a date in cell A1 forward by one month, enter =EDATE(A1,1). Excel returns the corresponding date…
- Databricks SQL Functions: Types, Examples, and UsageDatabricks SQL functions are built-in or user-defined operations you call from SQL to transform values, summarize rows, work…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.