To check for duplicate values in Excel, select the cells you want to inspect, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated entries in the selected range. For a reusable check, enter a COUNTIF formula beside your data. These steps are for Microsoft Excel for Microsoft 365 on Windows; the same features are available in current Mac versions, though some dialog details may look different.
What You’ll Need
- An Excel workbook containing the list or table you want to check.
- A clear idea of what counts as a duplicate: a repeated value in one column, or a repeated combination of values across several columns.
- For any removal operation, a saved copy of the workbook or a duplicate of the original data. Highlighting and formulas are safer for an initial review.
Step-by-Step
1. Decide what you mean by a duplicate
Start by defining the comparison. If a customer ID should appear only once, check the ID column. If two records count as duplicates only when both the customer name and email match, compare those two columns together. This decision matters: checking a single column can flag the same surname on two different people, while checking a whole row may miss records that differ only in a note or date.
Also decide whether you want to identify every copy of a repeated value or only the second and later appearances. Excel’s Duplicate Values rule highlights all matching copies, including the first one. A formula can distinguish the later copies when you need to keep the first entry and review repeats.
2. Highlight duplicate values in one column
Click the first data cell and select the range you want to check. For example, if the heading is in A1 and the values run from A2 through A100, select A2:A100 so the heading is not included. Then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the dialog, leave Duplicate selected, choose a formatting style, and click OK. Excel applies a conditional-formatting rule to the selected cells. Repeated values should now stand out visually. The colors do not change the underlying data, and you can change or remove the rule later through Home > Conditional Formatting > Manage Rules.
The rule compares values within the selected range. If you select several columns at once, Excel looks for repeated values in that selection; it does not treat each entire row as a unique record. To check whether whole records repeat, use the multi-column method in step 5 instead.
3. Add a formula to flag repeated values
A formula is useful when you want a TRUE/FALSE result that can be filtered, counted, or used in another calculation. With the values to check in A2:A100 and an empty helper column B, enter this in B2:
=COUNTIF($A$2:$A$100,A2)>1
Fill the formula down through B100. TRUE means the value in that row occurs more than once in A2:A100; FALSE means it appears once. For example, if A2 contains Maple and Maple appears in A17 as well, both corresponding formula results are TRUE. The dollar signs keep the search range fixed as you copy the formula, while A2 adjusts for each row.
To return the number of times each value occurs instead of TRUE or FALSE, use the same layout—values in A2:A100 and the formula in B2—and enter:
=COUNTIF($A$2:$A$100,A2)
For a value that appears three times, the result is 3 on each of its rows. Filter the helper column for values greater than 1 to see only duplicates. COUNTIF is not case-sensitive, so Excel treats Maple and MAPLE as the same value for this check.
4. Flag only the second and later occurrences
If you want to keep the first occurrence and mark only repeats that come after it, use a growing range. With the list in A2:A100 and the helper formula entered in B2, use:
=COUNTIF($A$2:A2,A2)>1
Copy it down the helper column. The first time a value appears, the count so far is 1 and the formula returns FALSE. Its next appearance returns TRUE, as do any later appearances. For example, if Maple is in A2, A17, and A35, only the results on rows 17 and 35 are TRUE. This approach is helpful when reviewing imported lists because it identifies which later rows might be removed without marking the original entry.
5. Check for duplicate records across columns
When a record is defined by two fields, such as a name in column A and an email address in column B, compare both fields together. With names in A2:A100, email addresses in B2:B100, and an empty helper column C, enter this formula in C2:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
Fill it down to C100. TRUE means the combination of the name and email on that row occurs more than once. Two people with the same name but different email addresses will not be marked as duplicates by this test. To define a record using three fields, add another criteria range and matching cell reference to COUNTIFS, keeping the ranges the same size.
For exact duplicate rows across a set of columns, include every column that should match in the COUNTIFS test. If a row contains a unique transaction ID, including that ID means otherwise identical-looking rows will not count as duplicates. Leave out fields that should not affect the definition, such as a temporary import note, if they are not part of what makes a record unique.
6. Remove duplicates only after reviewing them
If you have confirmed that repeated rows should be deleted, first save a copy of the workbook or copy the original data to another sheet. Select a cell in the data, then choose Data > Remove Duplicates. In the dialog, confirm whether My data has headers is selected. Check the columns Excel should use to decide whether a row is a duplicate, then click OK.
Excel removes duplicate rows based on the selected columns and keeps the first occurrence it finds. If you select only an email column, for instance, rows with the same email can be removed even when other fields differ. Select every field that must match before removing records if you intend to delete only fully matching records. Read Excel’s results message to see how many duplicates were removed and how many unique values remain.
Removing duplicates changes the data, unlike conditional formatting or helper formulas. You may be able to use Undo immediately afterward, but do not rely on that as your backup: later actions can make recovery inconvenient. Keep the saved original until you have checked that the remaining rows are correct.
7. Check the rule or formula as your list changes
If you add more values after setting up a check, confirm that the conditional-formatting range or formula search range includes the new rows. A formula ending at row 100 will not inspect row 101 unless you extend it. For conditional formatting, use Home > Conditional Formatting > Manage Rules and check the Applies to range. For a formula, copy the helper formula into the new rows and adjust its fixed search range if necessary.
In Excel for Mac, use the Home and Data tabs for the corresponding Conditional Formatting and Remove Duplicates commands; the labels are generally the same, although the dialogs may be arranged differently. Excel for the web also has conditional formatting and Remove Duplicates options, but the layout can vary. If a command is not visible, check the relevant ribbon tab or use Excel’s command search.
Common Mistakes
- Including the header: Select the data cells, not the column heading, when applying a rule or writing a formula. If you include a heading with a repeated word in the data, it can be flagged too.
- Checking the wrong fields: A repeated last name does not necessarily mean two people are the same. Choose an identifier or a deliberate combination of columns before deleting anything.
- Assuming a highlight means a full row is duplicated: The Duplicate Values rule looks for repeated values in the selected cells. It does not compare every field in each row as a record.
- Ignoring spaces and inconsistent entries: A trailing space can make text look identical while causing an exact comparison to treat it differently. Conversely, COUNTIF ignores letter case, so case alone does not distinguish entries.
- Removing rows before checking the selection: Remove Duplicates keeps the first matching row and deletes later ones. If the checked columns are too broad or too narrow, Excel may keep or remove records you did not intend.
- Forgetting blanks: Blank cells can be counted as repeated values. If empty cells should not be treated as meaningful duplicates, review the highlighted blanks or exclude them from the range.
Tips
- Use conditional formatting for a quick visual scan, and use a helper formula when you need to sort, filter, count, or document the results.
- Sort or filter the helper column to group matching entries together. Keep the original row order in a backup if that order has meaning.
- For large lists, convert the range to a table with Insert > Table. Tables make headings and filters easier to manage, but still verify that your formulas and conditional-formatting rules cover newly added rows.
- Check for leading or trailing spaces if entries look the same but are not matching. You can clean a copy of the text with Excel’s TRIM function, then compare the cleaned values before replacing any original data.
- Use a stable identifier, such as an account number, when possible. Names and addresses can vary in spelling or formatting, so a match on those fields alone may need human review.
- If formulas show an error after pasting, check whether your Excel regional settings use semicolons instead of commas between arguments. The examples here use comma separators.
Troubleshooting
No cells are highlighted: Confirm that the selected range includes the repeated values and that Duplicate Values, rather than Unique, is selected in the dialog. Check the rule’s Applies to range in Manage Rules. Also check whether entries contain extra spaces, different punctuation, or numbers stored as text. Visually similar values may not be identical to Excel.
The formula returns FALSE for an apparent duplicate: Make sure the formula’s fixed range includes both copies and that the reference in the final argument points to the current row. If using the two-column test, both fields must match. Check for hidden spaces or inconsistent data types; the formula is comparing the stored values, not just how they appear on screen.
Every row is marked TRUE: Check that the formula range is anchored correctly. In the all-copies example, the lookup range should remain $A$2:$A$100 as you fill down. If you accidentally used a whole column or referenced the same cell for every row, results may not reflect the intended list. Also check whether the data genuinely contains repeated blanks.
Remove Duplicates deletes too many or too few rows: Undo immediately if possible, then restore the saved copy if needed. Reopen the command and review the checked columns. Selecting fewer columns makes Excel treat more rows as duplicates; selecting more columns requires a closer match. Test the settings on a copied range before applying them to important data.
The formula displays an error or will not calculate: Confirm the function is spelled COUNTIF or COUNTIFS, the parentheses are balanced, and the cell references are valid. Excel installations using a different regional list separator may require semicolons in place of commas. If the formula appears as text, change the cell format to General, then re-enter it.
Frequently Asked Questions
Can Excel highlight duplicates automatically?
Yes. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The formatting updates when values in the rule’s range change, though you may need to extend the range when adding new rows.
Does Excel highlight the first occurrence too?
Yes. Duplicate Values highlights every copy of a repeated value, including its first appearance. Use the growing-range COUNTIF formula in step 4 if you want to flag only later occurrences.
How do I check duplicates in two columns?
Use COUNTIFS to compare both fields on each row, as in step 5. This checks the combination: a match in one column by itself is not enough for the row to be flagged.
Can I find duplicates without deleting them?
Yes. Conditional formatting and helper formulas only identify matching values; they do not remove the data. Review the results first, then decide whether any rows should be deleted.
Is Remove Duplicates case-sensitive?
No. Excel’s duplicate checks and COUNTIF/COUNTIFS comparisons are generally not case-sensitive. For example, maple and MAPLE are treated as matches. If capitalization carries meaning in your data, use a separate case-sensitive method before removing anything.
Why does Excel miss values that look identical?
There may be extra spaces, different punctuation, or a difference between a number and text that displays the same way. Check the underlying cell contents and clean a copy of the data before changing the original list.
Related
- How to Center Selected Print on a Page in Microsoft Excel
- How to Center an Image in an Excel Cell and Keep It Aligned
- Center in Excel Without Merging: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis and Business Modeling ExplainedExcel data analysis turns organized information into summaries, patterns, and decisions; business modeling uses formulas and assumptions to…
- Excel Conditional Formatting RGB Colors ExplainedYes. In Excel for Microsoft 365 on Windows, you can use a custom RGB color in a conditional…
- Compressing Pictures in PowerPoint: Settings and StepsTo compress pictures in PowerPoint for Microsoft 365 on Windows, select a picture, go to Picture Format >…
- Importing CSV Into Excel: Steps, Settings, and FixesTo import a CSV into Excel for Microsoft 365 on Windows, select Data > From Text/CSV, choose the…
- How to Remove a Section Break in Word Without Losing TextTo remove a section break in Word, show formatting marks, locate the line labeled Section Break, then place…
- Python Pandas Excel to Dictionary: Practical GuideTo convert an Excel worksheet to Python dictionaries with pandas, read it into a DataFrame with pd.read_excel(), then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.