To find duplicates in Excel, select the cells you want to check and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values in that selection. For a count or a filterable list, use a COUNTIF formula instead. Check the range and what counts as a duplicate before removing anything; save a copy first if you plan to delete rows.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows. The main highlighting, formula, and removal methods are also available in recent Excel versions.
- A clear idea of what makes an entry a duplicate: the same value in one column, or a complete record with matching values across several columns.
- A saved copy of the workbook before deleting duplicates, especially if the data is important or shared.
- For the formula method, a spare column beside the data where you can display a duplicate flag or count.
Step-by-Step
- Decide what you are checking. A duplicate might mean the same email address appears more than once, or it might mean every field in two rows matches. These are different checks. If you select just the email column, Excel checks email values and does not consider names or other fields. If you select several columns and use the built-in rule, Excel checks repeated cell values in the selection; it does not identify whole rows as matching records. For duplicate records, use a method that compares the relevant fields together, or use Remove Duplicates with the appropriate columns selected.
- Prepare and select the data range. Click the first data cell and drag to the last cell in the column you want to inspect, such as A2:A100. Leave the header out of the selection so the column title is not treated as a value. If the data is an Excel table, click a cell in the relevant table column and select its data cells, or drag over the cells you want to check. Avoid including unrelated columns: selecting a block of columns makes the duplicate rule apply to the values in those columns, not to complete rows as a single unit.
- Highlight repeated values with Conditional Formatting. With the range selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, make sure Duplicate is selected, choose a formatting style, and click OK. Excel marks values that occur more than once within the selected cells. This is a quick visual check and does not change the underlying data. To remove or change the rule later, select the range and use Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the entire sheet.
- Check that the highlights mean what you expect. Review several marked cells against the original data. The rule is based on the selected range, so a value that appears once in the selection but elsewhere outside it may not be highlighted. Conversely, a repeated short value such as “CA” may be highlighted even if it is legitimate. Conditional Formatting is useful for spotting candidates, but it cannot determine whether two records refer to the same person or transaction. Confirm the context before editing.
- Add a formula to flag later occurrences. This option distinguishes the first instance from repeats, which is helpful when you want to keep the first entry. With values in A2:A100 and a blank helper column starting at B2, enter
=COUNTIF($A$2:A2,A2)>1in B2, then fill the formula down through B100. The first occurrence returns FALSE; each later occurrence of that value returns TRUE. For example, if A2:A7 contains North, South, North, East, South, West, the formula flags the second North and second South as TRUE. The dollar sign fixes the starting cell, while the ending cell expands as the formula is filled down. - Flag every value that appears more than once. If you want all members of a duplicate group flagged, rather than just later instances, use a fixed range. With values in A2:A100 and the formula entered in B2, enter
=COUNTIF($A$2:$A$100,A2)>1and fill it down to B100. A repeated value returns TRUE for each of its occurrences; a value appearing only once returns FALSE. To show the number of occurrences instead, use=COUNTIF($A$2:$A$100,A2)in B2 and fill down. For a sample list containing North twice and South twice, each North row and South row returns 2; East and West return 1. - Filter the helper column to review matches. Click a cell in your dataset and choose Data > Filter. Open the filter arrow in the helper-column header and select TRUE to display flagged rows. If you used the count formula, filter for numbers greater than 1. Compare the remaining columns to confirm which records should be kept. Clear the filter afterward using Data > Clear, or open the filter menu and choose Clear Filter. You can also sort the helper column to group flagged rows, but sorting only part of a dataset can misalign records, so select the full table or range before sorting.
- Remove duplicates only after checking the result. Save a copy before deletion. Select the complete dataset, including every column that belongs to each record, then choose Data > Remove Duplicates. In the dialog, indicate whether the selected data has headers and check the columns Excel should use to decide that rows are duplicates. If you check only Email, rows with the same email can be removed even if their names or other fields differ. If you check every column, Excel removes rows only when the selected values match across all those columns. Click OK and read Excel’s summary of how many duplicate values were removed and how many unique values remain. Remove Duplicates keeps the first instance it encounters and deletes later matches; it changes the data and is not the same as highlighting.
Common Mistakes
Checking the wrong range: The Conditional Formatting rule and COUNTIF formula only inspect the cells in their defined ranges. If you added new rows below the range, they will not be included unless you extend the selection or formula. Table-based data can make range management easier, but still check what is selected.
Confusing duplicate values with duplicate rows: Two rows can share an email address but contain different names, dates, or account details. A duplicate-value highlight does not establish that the whole record is a duplicate. Decide which columns define a matching record before using Remove Duplicates.
Deleting before reviewing: Remove Duplicates deletes rows immediately. It may keep a different row than you intend if the order is not right, and it cannot be reversed after the workbook is closed. Keep a backup and sort or review the data first if you need to control which version remains.
Overlooking inconsistent text: Extra spaces, spelling variations, and different number or date formats can make entries look alike while Excel stores them differently. For example, “Lee” and “Lee ” may not behave as expected in a comparison. Clean or standardize data before relying on a duplicate check.
Tips
Use Conditional Formatting for a fast visual scan, and use a helper formula when you need to filter, count, or document the result. You can keep the helper column temporarily and delete it after review, or retain it as an audit aid. Label the column clearly, such as “Duplicate?” or “Occurrences,” so another person can understand what the formula reports.
For a large sheet, convert the range to a table with Home > Format as Table. Confirm that the table has headers. Tables make it easier to filter the helper column and keep related fields together when sorting. When sorting, use the full table rather than a single column so names, dates, and amounts stay on the correct rows.
Excel’s COUNTIF comparison is not case-sensitive: “ABC” and “abc” are treated as the same criterion. Spaces and other differences can matter, however. If capitalization is meaningful to your task, do not assume COUNTIF provides a case-sensitive check. Also, an empty cell in a formula range can affect a count for blank values, so exclude blank rows or use a separate blank check when empty cells should not be treated as duplicates.
For an auditable review, keep the original data, add the helper formula, filter the flagged rows, and record the rule you used. This is safer than deleting rows first and trying to reconstruct why they disappeared. If you only need a clean output, copy the reviewed unique records to a new sheet rather than overwriting the source.
Troubleshooting
No cells are highlighted: Confirm the intended cells were selected and that the rule is set to Duplicate, not Unique. Check whether the repeated value is actually inside the selected range. Clear existing rules from the selection and apply the rule again if formatting was copied from elsewhere or overlapping rules make the result confusing.
Values that look identical are not flagged: Look for leading or trailing spaces, hidden characters, inconsistent punctuation, or number values stored as text. Compare the cells in the formula bar and test a small sample in a clean area. Standardize the data before repeating the check; for example, trim unwanted spaces in a separate cleanup column, inspect the cleaned values, and then run the duplicate check on those results.
Too many rows are flagged: Verify that the selected range contains only the field you meant to compare. A repeated city, status, or category may be normal. If you are checking records, choose the columns that identify a record rather than applying the one-column rule to the entire sheet. Review the data before removing anything.
The formula returns an error or the wrong result: Check that the first data cell in the formula matches the row where you entered it, and that the formula was filled down without shifting the fixed range. In the examples above, the values start in A2 and the formula starts in B2. If your regional Excel settings use semicolons as argument separators, replace commas with semicolons in formulas that contain multiple arguments; the COUNTIF examples shown here use commas.
The filter does not show the helper values: Make sure the filter includes the helper column and that the formula has been filled down. Recalculate the workbook if calculation is set to Manual by choosing Formulas > Calculate Now. Clear any existing filters, then open the helper column’s filter menu and select TRUE or the appropriate count. If the filter arrow is missing, click in the dataset and choose Data > Filter again.
Frequently Asked Questions
How do I find duplicates in one Excel column?
Select the data cells, excluding the header, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and click OK. For a filterable result, use the COUNTIF helper formula and filter for TRUE.
How do I find duplicate rows instead of duplicate values?
Decide which fields together define a duplicate record. Use Data > Remove Duplicates and select those columns to identify matching rows, but work on a copy because this removes later matches. For a non-destructive review, compare records using a helper method that combines or checks the relevant fields, then inspect matches before deleting.
Does Excel highlight the first occurrence too?
The Duplicate Values conditional-formatting rule highlights the repeated values in the selected range, including the first occurrence in a duplicate group. The formula =COUNTIF($A$2:A2,A2)>1 behaves differently: it flags later occurrences only.
Can I find duplicates without deleting them?
Yes. Conditional Formatting only changes how cells appear, and COUNTIF formulas only report a count or flag. Neither removes data. Use Remove Duplicates only when you are ready to delete rows, preferably after saving a separate copy.
Does Excel treat uppercase and lowercase as different?
COUNTIF and the built-in duplicate-value rule are not case-sensitive for ordinary text comparisons. For example, uppercase and lowercase versions of the same word are generally treated as duplicates. Check your data and the purpose of the comparison before relying on that behavior.
Do these steps work on Mac or Excel for the web?
The Conditional Formatting and Remove Duplicates features are available in current Excel for Mac and Excel for the web, though dialog layouts and ribbon placement can differ slightly. On Mac, use the Home and Data tabs to locate the equivalent commands. The instructions and ribbon paths here are written for Excel for Microsoft 365 on Windows.
Related
- Forecast Sheet in Excel 2007: What You Can Do Instead
- How to Enable Merge and Center in Excel: Quick Fixes
- Distinct Count in Excel PivotTables: How to Enable It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text to Columns: Split Pasted Data Into FieldsTo split pasted data in Excel, select the column containing the combined text, then choose Data > Text…
- Can You Create a Heat Map in Excel? A Practical GuideYes. In Excel for Microsoft 365 on Windows, select your numeric cells and choose Home > Conditional Formatting…
- Excel Adjusting Print Area: Fit, Set, and Clear PagesTo adjust a print area in Excel for Microsoft 365 on Windows, select the cells you want, then…
- How to Open SharePoint Files in Excel and Edit ThemTo open a SharePoint workbook in Excel, find it in the document library, select the file, then choose…
- Group Rows in Excel for Sorting Without Splitting DataTo keep related records together when sorting in Excel, select the entire data range (or convert it to…
- Autofit Pictures in Excel: Fit Images to CellsTo make a picture fit an Excel cell, use Picture Format > Place in Cell if that command…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.