To find duplicates quickly in Excel for Microsoft 365 on Windows, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and select a highlight style. For a reusable check you can filter, add a COUNTIF formula in a helper column. Before you start, decide whether you want to highlight every occurrence of a repeated value or identify only the later copies.
What You’ll Need
- An Excel workbook with the column of values you want to check.
- A header row, if present, so you can exclude it from the selected data range.
- Optional: an empty column beside your data if you want to mark and filter duplicates with a formula.
You do not need to sort the data or install an add-in. The built-in conditional formatting rule works for a quick visual scan; a formula is more useful when you want to filter, count, or review duplicate rows.
Step-by-Step
- Identify the range to check. Find the first and last cells containing values. For example, if column A has a heading in A1 and values from A2 to A100, the data range is A2:A100. Selecting only the data avoids treating a repeated heading as a duplicate. If there are blank cells, you can leave them in the range, but be aware that formulas may treat empty cells differently from the built-in highlighting rule.
- Highlight duplicates with the built-in rule. Select the data range, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave the first dropdown set to Duplicate, choose a format from the second dropdown, and click OK. Excel applies formatting to every cell whose value occurs more than once in the selected range, including the first occurrence and its later copies. The formatting changes how the cells look; it does not change or remove their contents.
- Check what the highlight means. Scan the selected range for the chosen fill or font color. If the same value appears three times, all three matching cells are highlighted, not just the second and third. That is useful when you want to see all the locations involved. If you need to mark only the repeated appearances after the first one, use the helper-column method below instead.
- Add a helper column for a filterable result. If your values are in A2:A100, add a heading such as Duplicate? in B1. In cell B2, enter this formula, then fill it down through B100. With values in A2:A100 and the formula in B2, it counts how often the value in A2 appears in the full range:
=COUNTIF($A$2:$A$100,A2)>1. A result of TRUE means that value appears at least twice; FALSE means it appears once. For instance, if “Maple” occurs in A4 and A9, the formula returns TRUE for both rows. - Exclude blank cells if needed. COUNTIF counts blank cells when the criterion refers to a blank cell, so a simple formula can mark empty rows as duplicates if there are multiple blanks. If you only want nonblank repeated values, use this version instead. With the same A2:A100 data and the formula in B2, fill it down the helper column:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). It returns FALSE for an empty cell and TRUE for a nonblank value occurring more than once. - Filter to review the matching rows. Select your data, including the helper heading, and choose Data > Filter. Open the dropdown in the Duplicate? heading and select TRUE. Excel displays the rows whose values are repeated, while the other rows are temporarily hidden. Review the full row, not only the duplicate cell, so you can tell whether two records are genuinely the same or simply share one value.
- Choose whether to keep or remove anything. Conditional formatting and formulas only identify matches; they do not delete data. If you intend to remove duplicates, first save a copy or use File > Save As to create a working copy. Then consider Data > Remove Duplicates, checking the appropriate column or columns in its dialog. If a repeated value occurs in otherwise different records, removing duplicates based only on that one column may discard information you need.
For a fast check, the conditional formatting rule is usually enough. For a repeatable process, the helper formula has an advantage: you can filter TRUE values, sort the results, or keep the check in the workbook as new rows are reviewed.
Common Mistakes
- Selecting the whole worksheet column without checking the header. A heading repeated elsewhere in the data can be highlighted, and a formula referencing the wrong start row may give misleading results. Select the actual data cells and make sure the formula starts on the same row as its first data value.
- Thinking the first match will stay unhighlighted. The Duplicate Values conditional formatting rule marks all occurrences. If you want only later appearances flagged, use a running count instead. With values in A2:A100 and a formula in B2, this version returns TRUE only from the second occurrence onward when filled down:
=COUNTIF($A$2:A2,A2)>1. - Removing records before checking their other fields. Two rows can have the same email address or product code but different dates, quantities, or notes. Confirm which columns define a duplicate before using Remove Duplicates; Excel removes entire rows based on the columns you select.
- Assuming visually similar text is identical. Extra spaces, punctuation, or different characters can make values that look alike compare differently. For example, “North” and “North ” may not be treated as the same value. Check suspicious cells in the formula bar and clean the source values before relying on the result.
Tips
- Use all relevant columns when checking records. If a duplicate means the same name and date together, checking just the name will flag legitimate repeated names. You can create a combined helper key from the relevant fields, or use Data > Remove Duplicates and select each field that defines a true duplicate.
- Keep the formula range consistent. The dollar signs in
$A$2:$A$100keep the full comparison range fixed as you fill the formula down; the final A2 reference changes to A3, A4, and so on. If you add rows later, extend the range or use an Excel table so the check can grow with the data. - Use a table for data that will expand. Select a cell in your data and choose Insert > Table, confirming whether the range has headers. Tables expand when you add rows, and you can apply the same conditional formatting or helper-column check to the new data. Verify the rule’s “Applies to” range if new rows do not receive the highlighting.
- Remember that matching is generally not case-sensitive. COUNTIF treats uppercase and lowercase text as equal for this check. Thus, “blue” and “Blue” count as the same value. If capitalization is meaningful in your data, ordinary duplicate highlighting and COUNTIF are not sufficient for a case-sensitive distinction.
- Keep a record of the original data. If you are cleaning a list, filter and inspect the matches first. Save a separate copy before deleting rows, especially when the workbook contains formulas, linked data, or records that cannot easily be restored.
Troubleshooting
Nothing is highlighted. Confirm that you selected the intended cells and chose Duplicate, not Unique, in the Duplicate Values dialog. Look for spaces or punctuation differences in values that appear identical. If the cells contain formulas, compare the displayed results as well as the underlying formulas; two formulas that look different can return the same value, while similar-looking results may contain different underlying text.
The formula marks empty rows TRUE. Use the version with the nonblank test shown above, and check that the formula has not been copied into rows outside the intended data range. If cells contain formulas that return an empty string, the nonblank test treats the displayed empty string as blank.
The formula gives FALSE for values that look repeated. Check for leading or trailing spaces, nonbreaking spaces, hyphens versus en dashes, and numbers stored as text. These differences can prevent two entries from matching as expected. Clean or standardize a copy of the data first; do not replace the original values until you know the changes are safe.
New entries are not highlighted. The rule may apply only to the range selected when it was created. Choose Home > Conditional Formatting > Manage Rules, select the relevant rule, and inspect the Applies to box. Extend it to cover the new rows, or use an Excel table so the formatted range can expand with added data.
The menu looks different. These ribbon paths describe Excel for Microsoft 365 on Windows. In Excel for Mac, the Duplicate Values command is also available under Home > Conditional Formatting > Highlight Cells Rules, though dialog appearance can vary by release. In Excel for the web, select the range and look under Home > Conditional Formatting for the duplicate-values rule; if a command is unavailable in your browser, open the workbook in the desktop app. On Mac, keyboard shortcuts can differ from Windows, but the ribbon paths above avoid relying on a shortcut.
Frequently Asked Questions
Does Excel highlight every copy or only the extra copies?
The built-in Duplicate Values rule highlights every occurrence of a value that appears more than once, including its first occurrence. To flag only later occurrences, use the running COUNTIF formula in the Common Mistakes section.
Can I find duplicates without changing the data?
Yes. Conditional formatting only changes cell appearance, and a helper formula adds a separate result you can filter. Neither method edits the original values. Avoid Remove Duplicates unless you intentionally want Excel to delete rows.
Can I check duplicates across two columns?
Yes, but first define what makes a match. If a record is a duplicate only when both its name and date match, check both fields together rather than highlighting repeated names alone. Data > Remove Duplicates lets you select multiple columns; for a formula-based check, create a consistent combined key from the fields.
Does Excel treat uppercase and lowercase as different?
No. The Duplicate Values rule and COUNTIF are not case-sensitive for ordinary text comparisons, so “ACME” and “Acme” are treated as a match. If case is important, use a case-sensitive approach rather than these standard checks.
How do I remove duplicates after finding them?
Make a backup, select the dataset, and choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate and confirm. Excel keeps the first instance it encounters and removes later matching rows, so check the sort order and selected columns before proceeding.
Related
- Export an Excel Sheet to an Image: Methods and Tips
- How to Export an Excel Sheet to Word: Practical Methods
- How to Export an Excel Tab to a New Workbook
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 Delete an Excel Workbook SafelyTo delete an Excel workbook in Microsoft 365 for Windows, save any changes, close the workbook, find its…
- Project Tracker Spreadsheet in Google Sheets: Setup GuideYes. You can make a project tracker spreadsheet in Google Sheets by creating a sheet with one row…
- Google Sheets Dropdown Chips: Setup and UseGoogle Sheets dropdown chips are colored, pill-shaped choices displayed inside cells; create one with Insert > Dropdown or…
- Excel Table With Headers on the Side: What Actually WorksNo—an Excel Table has a header row across the top; it cannot use a column on the left…
- BI Conditional Formatting in Power BI: Rules and UsesBI conditional formatting uses color, icons, data bars, or other visual cues to make important values in a…
- Excel Mac: How to Insert a Row Above or Below a TableTo insert a row in Excel for Mac, select the worksheet row header where the new row should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.