To check for duplicate values in Excel, select the cells you want to inspect and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated entries in that selection. For a reusable duplicate label, enter a COUNTIF formula in a helper column instead. Before you start, decide whether you want to find repeated individual values, repeated combinations across columns, or entire duplicate rows; those checks are different.
The steps below use Microsoft Excel for Microsoft 365 on Windows. The same Conditional Formatting option is available in current Excel for Mac and Excel for the web, although the ribbon layout or dialog appearance may vary slightly. Make a copy of important data before sorting or removing anything.
What You’ll Need
- An Excel workbook containing the values to check.
- A clear idea of which cells or columns define a duplicate. For example, a repeated email address is a single-column check, while a repeated combination of first name and last name is a multi-column check.
- Optional: an empty helper column if you want Excel to label each row rather than only highlight it.
Step-by-Step
- Choose the values to compare. Click and drag to select the relevant cells, excluding a heading such as “Email” or “Product ID.” For a single list in column A with a heading in A1 and values in A2:A100, select A2:A100. If the list may grow, you can select a larger range, but avoid including unrelated columns. Conditional Formatting checks duplicates within the selected range; it does not automatically compare your selection with every other list in the workbook.
- Highlight duplicate values. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format from the dropdown, and select OK. Excel applies the format to each occurrence of a repeated value in the selected cells, including the first occurrence. If you select Unique instead, it highlights values that appear only once in that range.
- Review the highlighted cells. Compare the marked entries with nearby rows and any identifying details in other columns. A highlight tells you that a value appears more than once in the selected range; it does not tell you whether the entries are mistakes. Two orders can legitimately share a date or status, for instance. Confirm what the column represents before making a change.
- Use a formula when you want a label or count. Suppose the values are in A2:A7, with Maple in A2 and A4, Cedar in A3 and A6, Birch in A5, and Elm in A7. In B2, enter
=IF(A2="","",IF(COUNTIF($A$2:$A$7,A2)>1,"Duplicate","Unique")), then fill the formula down through B7. The formula returns Duplicate for Maple and Cedar, and Unique for Birch and Elm. The blank check keeps an empty cell from being labeled as a duplicate. The dollar signs keep the checked range fixed as you copy the formula down, while A2 changes to the value on each row. - Show how many times each value appears. With the same values in A2:A7, enter
=COUNTIF($A$2:$A$7,A2)in B2 and fill down to B7. The result for Maple in B2 is 2; Cedar also returns 2, while Birch and Elm each return 1. A count greater than 1 marks a repeated value. If your range is different, replace A2:A7 in the formula with the full list range and adjust the current-row reference, such as A2, to match the first data row. - Check duplicate combinations across columns. A repeated value in one column may not mean an entire record is duplicated. To test whether the combination of values in columns A and B repeats, assume the records occupy A2:B7 and both columns are part of the key. Enter
=COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,B2)>1in C2 and fill down. The result is TRUE for each row whose A-and-B combination occurs more than once, and FALSE for a combination that appears once. For example, two rows with the same customer but different order numbers are not duplicates under this two-column test. - Filter or sort to inspect results. If you used a helper column, select your data and choose Data > Filter. Use the filter arrow in the helper column to show Duplicate, or TRUE for the combination formula. You can also sort by the helper column to bring flagged rows together. Keep each record’s columns together when sorting: select the whole table or use a table filter, rather than sorting a single column by itself.
- Remove duplicates only after checking. If you have confirmed that some records should be deleted, first save a copy. Select the full table, then choose Data > Remove Duplicates. In the dialog, select the columns that determine whether a row counts as a duplicate, and confirm. Excel keeps the first occurrence and removes later matching rows; this action changes the data and is not the same as highlighting duplicates. If you only need to identify repeated values, stop after reviewing the highlighted cells or formula results.
Common Mistakes
Selecting the wrong range: If you include a heading, Excel can treat it as a value. If you select only part of a list, duplicates outside that selection will not be considered. Check the range in the Name Box or select the full data area before applying a rule.
Assuming every highlight is a bad record: A repeated status, city, or product category may be expected. Decide which field or set of fields should uniquely identify a record. A duplicated email address may matter in a customer list, while a duplicated country usually does not.
Checking a single column when the whole row matters: Duplicate Values evaluates cells in the selected range, not whether every field in two records matches. Use a COUNTIFS helper formula for a chosen combination, or select the relevant columns and configure a check that reflects the record key. Do not treat two rows as identical just because one of their values repeats.
Removing duplicates too early: Remove Duplicates deletes later matching rows and keeps the first one. If the selected columns are wrong, useful records can be lost. Review the criteria and keep a backup before using the command.
Overlooking spaces or inconsistent entry: “Northwind” and “Northwind ” may look the same but contain different characters. Extra spaces, alternate spellings, and different formats can prevent values from matching as expected. Clean or standardize data only when you understand what the change will affect.
Tips
Use Conditional Formatting for a quick visual scan and a helper formula when you need to filter, count, or document the results. You can keep the helper column in the workbook as an audit trail, or remove it after the review. When the source list changes, formula results recalculate; a conditional-formatting rule normally continues to cover only its configured range, so check its scope when you add rows.
Excel’s COUNTIF and COUNTIFS comparisons are not case-sensitive. For example, “AB12” and “ab12” are counted as the same value by COUNTIF. If capitalization is meaningful in your data, use a case-sensitive approach rather than relying on the Duplicate Values rule or COUNTIF alone.
For a quick duplicate check, you do not need to sort the original data. Highlighting and helper formulas preserve the row order, which can make it easier to compare results with an external source. If you do sort, sort the entire table so names, IDs, dates, and other fields remain attached to their original records.
On Windows, there is no need to use a keyboard shortcut for the duplicate-highlighting command; follow the ribbon path so you can verify the selected range and chosen format. In Excel for Mac, use the Home tab and Conditional Formatting menu; the dialog controls may be arranged differently. Excel for the web also provides Conditional Formatting, but some advanced rule-editing options can differ from the desktop app.
Troubleshooting
Nothing is highlighted: Confirm that the correct cells are selected and that at least one value really occurs twice in that range. Check for leading or trailing spaces, inconsistent punctuation, and values stored differently, such as a number in one cell and text in another. Also make sure the rule is set to Duplicate, not Unique.
Blank cells are highlighted or labeled: Blank cells can be treated as repeated entries. In the helper formula above, the IF check prevents blank cells from receiving a label. If you use a different formula, add a blank check suited to your data, or exclude empty cells from the selected range.
The formula shows an error: Verify that the formula starts with an equals sign, the range references include the correct worksheet cells, and quotation marks are straight double quotes. In Excel for Microsoft 365 on Windows, formulas generally use commas between arguments. Some regional settings use semicolons instead; if Excel rejects a valid formula with commas, use the separator required by your installation.
New rows are not being checked: The conditional-formatting rule or formula may stop at the original last row. Extend the rule’s applied range or copy the helper formula into the new rows. Converting a well-formed data range to an Excel table can help formulas and filters expand as you add records.
The results seem to count different-looking text as the same: COUNTIF is case-insensitive, and its criteria treat the asterisk and question mark as wildcard characters in text. If your values contain those characters literally, or capitalization must distinguish records, use a more specific comparison method and test it on a small sample before acting on the results.
Frequently Asked Questions
Does Excel highlight both copies of a duplicate?
Yes. The Duplicate Values rule highlights every occurrence in the selected range, including the first value and each later repeat. It does not mark only the extra copies.
Can I check duplicates in more than one column?
Yes, but decide whether you mean repeated values in any selected cell or repeated combinations across columns. To test a pair of columns as one key, use a COUNTIFS formula with one condition for each column, as shown in the steps.
Can I find duplicates without deleting them?
Yes. Conditional Formatting and helper formulas only identify or label matches. Data > Remove Duplicates is a separate command that deletes matching rows, so you can avoid it if you only need to inspect the data.
Are duplicate checks case-sensitive?
The built-in Duplicate Values rule and COUNTIF are not case-sensitive. They treat text that differs only by letter case as a match. Use a case-sensitive comparison if upper- and lowercase letters represent different values in your data.
Why does Excel miss values that look identical?
Cells may contain hidden spaces, different punctuation, or different underlying types, such as text versus numbers. Inspect the cells, standardize the data carefully, and repeat the check. A displayed number format can also make different stored values look alike.
Does this work in Excel for Mac or the web?
Yes. Current Excel for Mac and Excel for the web include Conditional Formatting and duplicate-value highlighting. Ribbon placement and some rule-editing details can differ, so use the Home tab’s Conditional Formatting options and check the applied range.
Related
- How to Calculate Average Hours in Excel
- How to Calculate an Average in Microsoft Excel Quickly
- Change Zoom in Excel Permanently: Save Your View
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Circular Reference Workarounds That Actually WorkAn Excel circular reference workaround is usually to change the formula so it no longer refers to its…
- Google Sheets SPLIT Formula: How to Separate Text CleanlyIn Google Sheets, use =SPLIT(A2, ",") to divide the text in A2 at each comma, with the pieces…
- Excel Freeze Panes: Why the Option Is UnavailableIf Excel will not let you freeze panes, first press Esc to exit cell-editing mode, then click a…
- Center a Number in Excel: Steps, Options, and FixesTo center a number in Excel for Microsoft 365 on Windows, select the cell or cells, then choose…
- Conditional Formatting Based on a Checkbox in ExcelYes. In Excel for Microsoft 365, conditional formatting can respond to a checked or unchecked box because a…
- Disable Protect Workbook in Excel: A Step-by-Step GuideTo disable workbook-structure protection in Excel for Microsoft 365 on Windows, open the workbook, choose Review > Protect…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.