To show only duplicate values in Excel, select the cells you want to check, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then turn on Data > Filter and use the column arrow’s Filter by Color option to show the highlighted cells. This displays every occurrence of a repeated value, including its first appearance. If you need to find duplicate records based on several columns, use a helper formula instead; the steps below cover both situations.
What You’ll Need
- An Excel workbook with a header row and the data you want to check. Save a copy first if the workbook is important, especially before changing or deleting records.
- A clear decision about what counts as a duplicate: a repeated value in one column, or a repeated combination of values across multiple columns.
- Microsoft Excel for Microsoft 365 on Windows for the exact ribbon paths below. The core steps are also available in current Excel for Mac and Excel for the web, though the ribbon layout and some filter-by-color options can vary.
Step-by-Step
- Check the data layout. Keep one field per column, use a single header row, and avoid blank rows inside the list. For example, a customer list might have Customer ID in column A, name in B, and city in C. Decide which field or fields define a duplicate. If Customer ID must be unique, check that column rather than highlighting the entire worksheet range.
- Select the values to compare. For duplicates in one field, select only the data cells in that column, excluding the header. For example, select A2:A100 if IDs are in A2:A100. Selecting just the relevant column prevents Excel from treating an ID that happens to match a number in another column as a duplicate. If the list is an Excel table, select the cells in the table column rather than the whole sheet.
- Highlight repeated values. On the ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected. Choose a visible formatting option from the list, such as the default light red fill with dark red text, and click OK. Excel marks all matching values in the selected range, not just later copies.
- Turn on filtering for the list. Select a cell in the data list, including its header row, and choose Data > Filter. Excel adds a dropdown arrow to each header. If the arrows are already present because the range is a table or already filtered, do not add another filter; continue to the next step. Make sure the list has one header row so the arrows are attached to the correct fields.
- Filter the checked column by its highlight color. Open the dropdown arrow on the column you highlighted. Choose Filter by Color, then under Filter by Cell Color select the fill color used by the duplicate rule. Excel hides rows whose cells in that column do not have the chosen color. The visible rows now contain the duplicate values and their associated data. If you selected a text-color format instead of a fill, use the corresponding font-color option if it appears.
- Confirm what the results mean. The filter shows every occurrence of a value that appears more than once. For example, if A12 and A38 both contain ID 2047, both rows remain visible. This is useful when you want to review all records involved. It is different from keeping only the extra copies while excluding each value’s first occurrence.
- Clear the filter when finished. Open the same header dropdown and choose Clear Filter From [column name], or go to Data > Clear to clear filters across the list. Clearing the filter reveals the hidden rows; it does not remove records or erase the conditional formatting. To remove the highlighting itself, select the range and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
- For duplicates defined by multiple columns, add a helper column. A color rule across a whole rectangular selection compares values in that selection; it does not automatically decide whether an entire row is repeated. Add a header such as Duplicate pair in an empty column. With key values in A2:A100 and B2:B100, enter this in the helper cell on row 2, then fill it down alongside the data:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. The formula returns TRUE for each row whose A-and-B combination occurs more than once, and FALSE for a unique combination. For example, if the pair Lee / West appears in rows 5 and 21, both rows return TRUE. Filter the helper column to show TRUE. For three key fields, add the third range-and-criteria pair to COUNTIFS. If blank key rows should not count, use this version instead:=AND(COUNTA(A2:B2)>0,COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1). With the same two-column layout, it returns TRUE only for a repeated pair in a row that has at least one value in A or B.
Common Mistakes
- Selecting too many columns for a single-field check. A duplicate-values rule compares cells in the selected range, not just records with matching IDs. If you select several unrelated columns, matching text or numbers in different fields can be highlighted. Select only the column that defines a duplicate.
- Expecting the first occurrence to disappear. Excel’s duplicate highlighting identifies every member of a repeated group. If you want only later repeats, that requires a different test, such as a count of earlier occurrences, rather than the Duplicate Values rule.
- Using Remove Duplicates instead of Filter. Data > Remove Duplicates deletes repeated records from the selected data. It is not a way to display them temporarily. Use filtering to review results without deleting anything.
- Forgetting to include the helper formula in the filter range. If the helper column is outside the filtered list, its TRUE and FALSE values may not have dropdown arrows. Expand the filter to include the helper column or convert the full range, including its header, to a table.
- Assuming similar-looking values are identical. A trailing space, different punctuation, or a number stored as text can make entries behave differently. A display format may also make two different underlying numbers look the same.
Tips
- Use a stable key when possible. A customer ID, invoice number, or email address is usually a more dependable duplicate key than a name, which may legitimately belong to different people.
- Keep the helper formula inside the data list. Put its header beside the existing headers and fill the formula down to the last record. This makes it easier to filter, sort, and audit the results together.
- Use a table for data that grows. Select the list and choose Insert > Table, confirming that the table has headers. Tables provide filter arrows and expand when rows are added. Check that any helper formula fills into new rows as expected.
- Record the key you checked. Label the helper column clearly, such as Duplicate email or Duplicate name and city. A clear label helps prevent someone from mistaking a duplicate check for a deletion or a complete-row comparison.
- Review records before taking action. A duplicate can be a legitimate repeat purchase or a valid shared value. Filtering identifies matches; it does not establish that a record is erroneous. Compare the other fields before removing or merging anything.
Troubleshooting
Filter by Color is missing or there are no colors to choose from. Confirm that the duplicate rule is applied to the intended cells and that the chosen formatting includes a fill or font color. Reopen the column dropdown after applying the rule. If the workbook is open in Excel for the web and the color-filter option is unavailable in your current view, open it in the desktop Excel app and use the Windows steps. On Mac, use the Home and Data ribbon tabs; labels and menu placement can differ slightly by Excel version.
Expected matches are not highlighted. Check that the range includes all records and that the rule was not applied to just a few cells. Look for leading or trailing spaces and inconsistent spelling. If entries look identical but may have hidden spaces, compare them in a helper column or clean a copy of the data before checking. Also check whether a value is stored as text in one row and as a number in another; matching appearance alone does not guarantee identical stored values.
Too many values are highlighted. Recheck the selected range. A rule applied to columns A through C checks values across all those selected cells, so common words in unrelated fields may be marked. Reapply it to the intended key column only. For a record defined by two or more fields, remove or clear the broad rule and use the COUNTIFS helper-column approach instead.
The formula shows an error or gives unexpected TRUE results. Ensure the formula starts on the first data row and that its row references match that row. The dollar signs should remain on the range endpoints when you fill down, while A2 and B2 should change to A3 and B3 on the next row. Check that both COUNTIFS ranges cover the same number of rows. If the data has more than 100 rows, extend each range to include them; otherwise matches outside the formula’s range will be missed.
Rows seem to have vanished. Filtering hides rows temporarily. Use the dropdown’s clear-filter option or choose Data > Clear to show the full list again. If rows remain missing, check for other active filters in the other column arrows. A filter icon on a header indicates that a filter may still be restricting the list.
Frequently Asked Questions
Does Excel show every copy of a duplicate?
Yes. The Duplicate Values rule marks all cells in the selected range whose value occurs more than once, including the first occurrence. Filtering by that highlight color therefore shows all members of each repeated group.
Can I filter for duplicates without deleting anything?
Yes. Conditional formatting and Filter by Color only change what is highlighted or visible. They do not delete records. Avoid Data > Remove Duplicates if you only want to inspect or temporarily isolate the matches.
How do I find duplicate rows rather than repeated values in one column?
Choose the columns that define a record and use COUNTIFS in a helper column, with one criteria range for each key field. Filter the helper column to TRUE. Include every field that must match for two records to count as duplicates.
How can I show only the second and later occurrences?
The Duplicate Values rule includes the first occurrence, so it cannot isolate later copies by itself. You can create a helper formula that counts how many times the current value has appeared from the top of the list through the current row, then filter for counts greater than one. This approach depends on the order of the records, so keep a backup and avoid sorting between calculating and reviewing the results.
Will this work in Excel for Mac or Excel for the web?
Current Excel for Mac includes conditional formatting and filtering, though ribbon placement can vary. Excel for the web also supports common conditional formatting and filter tasks, but options can differ from the Windows desktop app. If Filter by Color is not available in your version, use a helper column that returns TRUE for duplicates and filter that column instead.
Related
- How to Enable Paste Special in Excel
- Link Quantities in Excel: A Step-by-Step Guide
- How to Enable Freeze Panes in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula List: Basic Functions, Uses, and ExamplesA basic Excel formula list includes SUM for totals, AVERAGE for means, COUNT and COUNTA for counting, MIN…
- Filter Search Shortcut in Excel: Find Values FastIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn column filters on or off. To search…
- Excel Formula: Check If a Value Is in a ListIf the value to check is in A2 and your list is in D2:D10, use =IF(COUNTIF($D$2:$D$10,A2)>0,"In list","Not in…
- Excel Formula to List All Sheet Names in a WorkbookExcel does not have a standard worksheet function that directly returns every tab name. In Excel for Microsoft…
- Excel SUBSTITUTE Double Quote: Formulas and ExamplesTo remove straight double quotation marks from a cell in Excel, use =SUBSTITUTE(A2,CHAR(34),"") when the text is in…
- Excel Workdays Between Dates: Formulas and ExamplesTo count workdays between two dates in Excel, use NETWORKDAYS. With the start date in A2 and the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.