To check for duplicate entries in Excel, select the cells you want to check and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated values without deleting them. For more control, use a COUNTIF formula to flag duplicates, or check several columns together when a duplicate means the same combination of details.
What You’ll Need
- A workbook open in Microsoft Excel for Microsoft 365 on Windows.
- A clearly identified range or column to check. If your data has a header, plan to select the data rows without including the header.
- Optional: a copy of the workbook if you intend to remove duplicates or make other changes to the original data.
You do not need an add-in for the methods below. The built-in highlighting option is quickest for a visual check; a formula is useful when you want a count, a reusable flag, or a rule that excludes blank cells.
Step-by-Step
-
Decide what counts as a duplicate. A repeated email address in one column is different from a repeated customer record across several columns. Decide whether you are checking one field, such as an order number, or a combination, such as customer name and date. Also decide whether blank cells should count. This matters because a list can contain repeated names legitimately, while an ID that should be unique may signal an error whenever it appears more than once.
-
Prepare and select the values. If the column has a heading in row 1 and data from row 2 through row 100, select A2:A100 rather than including the heading. Replace the example range with your actual last row. If the entries are part of a structured Excel table, select the relevant data cells in the table column. Keep nearby columns out of the selection unless you intend to check those columns too.
Before checking, consider whether inconsistent spaces or formatting could disguise entries that look alike. For example, a name with a trailing space may not behave like the same name without one. Avoid changing the source data until you have decided how those inconsistencies should be handled.
-
Highlight duplicates with Excel’s built-in rule. With the cells selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave the choice set to Duplicate, select a format from the list, and click OK. Excel applies a visual format to values it identifies as repeated within the selected range. The rule marks every occurrence of a repeated value, not just the later entries.
To change or remove the highlighting, select the range and use Home > Conditional Formatting > Manage Rules. Choose the relevant rule to edit it, or delete it if you no longer need it. Conditional formatting changes how cells look; it does not alter or delete their contents.
-
Use a formula when you need a more specific visual check. Select the data range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With values in A2:A100, enter the following formula in the rule:
Assumed layout: the values to check are in A2:A100, and the selected range for the rule is also A2:A100. For example, if Mira appears in A2 and A5, the formula evaluates to TRUE for both cells because each value occurs at least twice.
=COUNTIF($A$2:$A$100,A2)>1Click Format, choose a fill or font style, and confirm the dialogs. The dollar signs keep the range being counted fixed, while A2 adjusts for each cell in the selected range. If your data begins on a different row, change both the range and the relative cell reference to match the first selected cell.
If blank cells should not be highlighted, use a rule that checks for a nonblank value as well as a repeated value.
Assumed layout: the values to check and the selected rule range are A2:A100. If A2 is blank, the rule returns FALSE; if A2 contains a value that occurs again in the range, it returns TRUE.
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)In this formula, the two quotation marks represent an empty string. This version is helpful for lists with unused rows within the selected range.
-
Add a helper column to see duplicate counts. A helper column makes it easier to filter or review results. If the list is in column A, add a heading such as Duplicate count in B1. Enter the formula below in B2, then fill it down through the last data row. You can drag the fill handle at the lower-right corner of B2, or copy the formula and paste it into the remaining cells.
Assumed layout: entries are in A2:A8 and the formula is entered in B2 and filled down. If the sample list is Mira, Sol, Mira, Noah, Sol, Ava, Mira, the result for Mira is 3, the result for Sol is 2, and the result for Noah or Ava is 1.
=COUNTIF($A$2:$A$8,A2)Change A8 to the actual last row in your list. The count includes the current row, so a result of 1 means that value appears once and a result greater than 1 means it is repeated. You can filter the helper column for values greater than 1 to review only repeated entries. In Excel, use the filter controls on the heading row; if filters are not on, choose Data > Filter.
-
Check duplicates defined by two columns. Sometimes a value is not unique by itself. For instance, two customers may share a name, but the same name and account number together should identify a repeated record. Add a helper column and count matching combinations with COUNTIFS.
Assumed layout: customer names are in A2:A100, account numbers are in B2:B100, and the formula is entered in C2 and filled down. If the pair Mira and 4107 appears in rows 2 and 9, both of those rows return TRUE; a pair appearing only once returns FALSE.
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1For this example, the formula checks whether both the name and account number match on another row. Add another range-and-criteria pair if the duplicate definition depends on more fields. Keep all the ranges the same size so the formula compares corresponding rows correctly.
-
Review results before changing the data. Sort or filter the highlighted or flagged entries and compare the full records. Confirm that the repeated values are genuinely errors rather than valid repeats, such as multiple line items for one customer. If you decide to delete repeats, first save a copy. Select the relevant data and use Data > Remove Duplicates, then choose the columns Excel should use to identify duplicates. This command removes rows, so it is different from highlighting and should be used only after checking what will be retained.
Common Mistakes
Selecting the wrong range: If you include the heading, Excel may evaluate it as a value. If you select only part of the list, entries outside that selection will not be compared. Check the range shown in the Name Box or the highlighted selection before applying a rule.
Checking the wrong definition of duplicate: A repeated surname may not indicate a repeated record. Conversely, checking only an email column may miss records that should be considered duplicates based on both email and account type. Decide which fields make a record unique before you use the result to clean data.
Deleting instead of identifying: Conditional formatting and COUNTIF help you find repeats. Remove Duplicates changes the data by deleting repeated rows. Do not use it as a first step if you still need to examine which records are repeated or choose which version to keep.
Assuming a visual match is an exact match: Extra spaces, inconsistent text, and values stored in different formats can affect comparisons. Also, COUNTIF is not case-sensitive, so entries such as IDa and IDA are treated as the same for this purpose. If capitalization matters, use a case-sensitive method rather than relying on the standard duplicate rule or COUNTIF.
Tips
Keep an untouched copy of important source data and do your review on a duplicate sheet or workbook. This gives you a way to recover if you remove the wrong row or normalize values too aggressively.
Use highlighting for a quick visual scan, a helper count when you need to sort or filter, and a multi-column formula when duplicates depend on a combination of fields. These methods answer slightly different questions, so choose the one that matches the task.
For large or frequently updated lists, turn the data into an Excel table with Insert > Table. Tables make it easier to work with headings and filters. If your range grows, check that any conditional formatting rule or formula-based range includes the new rows; a rule applied only to A2:A100 will not automatically cover data added below row 100.
If values may contain accidental leading or trailing spaces, inspect and clean those values deliberately before deciding that two records differ. Do this on a copy or in a separate helper column first, because cleaning can change legitimate source text. Check the cleaned results before using them to remove rows.
Troubleshooting
No cells are highlighted: Confirm that the selected range includes the data, and that the built-in dialog is set to Duplicate, not Unique. Check whether entries that appear identical contain spaces or other differences. For a formula rule, make sure the first relative reference matches the top-left cell of the selected range.
Too many cells are highlighted: Check whether the selected range includes unrelated columns or blank rows. If blanks should be ignored, use the nonblank formula rule above. Remember that COUNTIF does not distinguish capitalization. Also check whether your duplicate definition needs more than one column.
The helper formula shows the same count on every row: Make sure the criteria reference is relative (A2 in the example) while the full range is fixed ($A$2:$A$8). If you copied the formula, check that the row reference changes as it moves down. Update the fixed range if your list has more rows than the formula includes.
The formula appears as text or gives an error: Confirm that the cell is not formatted as Text, and that the entry begins with an equals sign. If Excel displays the formula characters instead of a result, change the cell format to General and re-enter the formula. Use commas between arguments as shown; regional settings can require different separators, even though the formulas here use standard Excel comma syntax.
New entries are not checked: Extend the selected range, the conditional formatting rule’s Applies to range, or the formula’s counted range. For a regularly growing list, consider using a table and verify that the rule covers newly added rows.
Frequently Asked Questions
Does Excel highlight every copy or only the second occurrence?
The built-in Duplicate Values rule highlights every occurrence of a repeated value, including the first one. A COUNTIF helper count also shows the total number of matches on each row. If you need to identify only later occurrences, you need a different formula rule that counts from the first row through the current row.
Can I find duplicates in more than one column?
Yes. Use COUNTIFS in a helper column to check whether two or more fields match on another row. Choose the fields that together define a repeated record, and make sure each compared range covers the same rows.
Does Excel treat uppercase and lowercase as different?
Not with the built-in duplicate highlighting rule or COUNTIF. Those checks treat text that differs only by letter case as a match. If case is important, use a case-sensitive comparison method and verify its results before changing the data.
Can I remove duplicates after checking them?
Yes, but removal changes the worksheet. Save a copy first, then choose Data > Remove Duplicates and select the columns that define a duplicate. Review the selected columns carefully because Excel removes entire duplicate rows based on that choice.
Will the duplicate check update when I add new rows?
Only if the formatting rule or formula range includes those rows. A fixed range such as A2:A100 does not necessarily include entries below row 100. Extend the range or use a table-based workflow, then check that the rule applies to the added data.
Related
- How to Change Cell Color in Excel
- Excel Drop-Down Colors: Change the Cell, Not the List
- How to Change Border Thickness in Excel for Clearer Sheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Background Image in Excel for a WorksheetIn Excel for Microsoft 365 on Windows, open the worksheet and select Page Layout > Background, choose an…
- Excel Row Always on Top? Keep It Visible While ScrollingYes—if you mean keeping a heading row visible while you scroll, choose View > Freeze Panes > Freeze…
- Excel Get Data From Table or Range: A Practical GuideTo get data from a worksheet table or cell range into Power Query in Excel for Microsoft 365…
- Forecast in Excel: How to Build a Reliable PredictionTo build a forecast in Excel, organize past results by date, select the timeline and values, then use…
- Add an Automatic Reply to a Shared Mailbox in OutlookTo add an automatic reply to a shared mailbox, open that mailbox in Outlook on the web, then…
- Google Sheets IF Not Empty: Formulas and Examples GuideTo make Google Sheets do something when a cell is not empty, use an IF test such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.