How To Check Duplicate Values In Excel

7 min read

Finding duplicate values in Excel is one of the most essential skills for anyone working with data. But whether you are cleaning a mailing list, auditing financial records, or preparing a dataset for analysis, identifying repeated entries ensures accuracy and prevents costly errors. Excel offers several built-in tools—ranging from visual highlighting to advanced formulas and Power Query—that allow you to detect, manage, and remove duplicates efficiently. This guide walks you through every major method, helping you choose the right approach for your specific workflow.

Why Checking for Duplicates Matters

Duplicate data skews analysis, inflates counts, and leads to incorrect business decisions. Sometimes a repeated value is legitimate—such as a customer placing two separate orders on the same day. Consider this: these scenarios create confusion and waste resources. Imagine sending a marketing email twice to the same customer or counting inventory twice during a stock take. Before removing any data, however, it is critical to review the duplicates. The goal is not always deletion; it is identification and verification.

Method 1: Conditional Formatting for Visual Detection

The fastest way to see duplicates without altering your data is Conditional Formatting. This method highlights cells that appear more than once, allowing you to scan the spreadsheet visually.

  1. Select the range of cells you want to check (e.g., A2:A100).
  2. Go to the Home tab on the ribbon.
  3. Click Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  4. In the dialog box, ensure "Duplicate" is selected in the first dropdown.
  5. Choose a formatting style (Light Red Fill with Dark Red Text is the default) and click OK.

Every cell with a value that exists elsewhere in the selected range will immediately change color. Because of that, this is ideal for small to medium datasets where you want to manually inspect each case before taking action. To remove the highlighting later, go to Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Highlighting Entire Rows Based on Duplicate Columns

Often, you need to highlight the whole row if a specific column (like an ID number) contains a duplicate. This requires a formula-based rule:

  1. Select your entire dataset (e.g., A2:D100).
  2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter this formula (assuming column A holds the key): =COUNTIF($A$2:$A$100, $A2)>1
  4. Click Format, choose a fill color, and press OK twice.

The $ signs lock the search range but allow the row reference (A2) to move down, checking every row against the fixed column range Not complicated — just consistent..

Method 2: The Remove Duplicates Tool (Built-in Data Cleaning)

When you are confident that duplicates are errors and want to delete them permanently, the Remove Duplicates feature is the standard solution. Always create a backup copy of your sheet before using this tool, as the action cannot be undone with a simple Ctrl+Z after saving.

  1. Click anywhere inside your data table.
  2. deal with to the Data tab and click Remove Duplicates in the Data Tools group.
  3. A dialog box lists all column headers. Check the columns that define a "duplicate."
    • Select All: Removes rows where every single column matches exactly.
    • Specific Columns: Removes rows where only the selected columns match (e.g., keep the row if the "Date" differs, but remove if "Email" matches).
  4. Click OK. Excel reports how many duplicate values were found and removed, and how many unique values remain.

This tool keeps the first occurrence of the data and deletes subsequent matches.

Method 3: Using Formulas for Dynamic Detection

Formulas are superior when you need a dynamic solution that updates automatically as data changes, or when you need to flag duplicates in a helper column for filtering Surprisingly effective..

The COUNTIF Function (Classic Approach)

The COUNTIF function counts how many times a specific value appears in a range.

Syntax: =COUNTIF(range, criteria)

To flag the first occurrence as "Unique" and subsequent ones as "Duplicate": In a new column next to your data (assuming data starts in A2), enter: =IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "Unique")

Notice the range $A$2:A2. The start is locked ($A$2), but the end (A2) is relative. As you drag this formula down, the range expands. The first time a value appears, the count is 1 ("Unique"). The second time, the count becomes 2 ("Duplicate") Worth knowing..

To flag all instances of a value that appears more than once (including the first): =IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique") Here, the range is fully locked. Every row containing a value that exists anywhere else in the list gets flagged Easy to understand, harder to ignore..

The COUNTIFS Function (Multi-Column Duplicates)

Real-world duplicates often span multiple columns (e.Also, g. On top of that, , same First Name AND Last Name AND Date of Birth). COUNTIFS handles this natively Small thing, real impact..

=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2, $C$2:$C$100, C2)>1, "Duplicate", "Unique")

This checks if the combination of columns A, B, and C appears more than once.

Modern Excel: UNIQUE and FILTER Functions (Excel 365/2021+)

If you have a modern version of Excel, dynamic array functions make this significantly easier.

To extract a list of unique values only: =UNIQUE(A2:A100) This spills a new list containing each value only once.

To extract only the values that have duplicates: =UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1)) This filters the range to show only items with a count > 1, then wraps it in UNIQUE to list each duplicate value just once. This is incredibly powerful for building dashboard reports.

Method 4: Power Query (Get & Transform) for Repeatable Workflows

For large datasets (100k+ rows) or recurring data cleaning tasks (weekly imports), Power Query is the professional standard. It records your steps so you can refresh the query next week with new data and the duplicates are removed automatically Which is the point..

  1. Select your data and press Ctrl + T to create an official Excel Table (highly recommended for Power Query).
  2. Go to Data > From Table/Range. The Power Query Editor opens.
  3. Select the column(s) you want to check for duplicates (hold Ctrl to select multiple).
  4. Right-click the selected column header(s) and choose Remove Duplicates.
    • Alternatively, use the Home tab > Remove Rows > Remove Duplicates.
  5. To keep a record of what was removed, you can instead use Group By (Transform tab) to count occurrences before removing.
  6. Click Close & Load to push the clean data back to a new worksheet in Excel.

Power Query does not modify your source data; it creates a clean output table linked to the source.

Method 5: Pivot Tables for Counting Occurrences

Pivot Tables are excellent for analyzing the frequency of duplicates rather than just deleting them.

  1. Insert a PivotTable (Insert > P

ivotTable) from your dataset. 2. Plus, drag the field you want to analyze into the Rows area. 3. Drag that same field into the Values area. That's why excel will typically default to a count of occurrences. 4. Sort the resulting count column from largest to smallest so the most repeated values appear at the top. 5. Apply a filter on the count column to show only values greater than 1, isolating the true duplicates But it adds up..

This approach leaves your source data untouched while giving you a clear audit trail of how frequently each item appears—perfect for reviewing duplicates before taking any destructive action.

Choosing the Right Method

  • Conditional Formatting / COUNTIF: Best for quick visual checks on smaller lists.
  • Remove Duplicates: Fastest when you just need clean data and don’t need to review what was deleted.
  • UNIQUE / FILTER: Ideal for Excel 365 users building dynamic reports or dashboards.
  • Power Query: The choice for large datasets or recurring monthly/weekly imports.
  • Pivot Tables: Best when you need to understand the scale and frequency of duplication before deciding what to do.

Conclusion

Excel offers multiple paths to handle duplicates, and the right one depends on your dataset size, how often you repeat the task, and whether you need to review the duplicates before removing them. For ongoing workflows, Power Query provides automation and auditability. And if you're working in Microsoft 365, dynamic array functions like UNIQUE and FILTER can extract insights in seconds. For one-off cleanup, the built-in Remove Duplicates feature is hard to beat. By matching the tool to the task, you can keep your data clean, accurate, and reliable without wasting time on manual checks.

Currently Live

Fresh Off the Press

Handpicked

Similar Stories

Thank you for reading about How To Check Duplicate Values In Excel. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home