Method 1: Data → Remove Duplicates (Fastest)

  1. 1

    Select the data range

    Click any cell within your data table. Excel will auto-detect the range, or you can manually select the exact cells you want to check.

  2. 2

    Data tab → Remove Duplicates

    Click the Data tab in the ribbon. Click Remove Duplicates in the Data Tools group.

  3. 3

    Choose which columns to check

    A dialog box shows all columns. Tick the columns that define a “duplicate” — usually all columns if you want exact row duplicates. If a row is a duplicate only when Column A AND Column B match, tick only those two. Click OK.

  4. 4

    Review the result

    Excel shows a message: “X duplicate values found and removed; Y unique values remain.” The first occurrence of each duplicate is kept; subsequent duplicates are deleted. The action cannot be undone after you dismiss the dialog (Ctrl+Z works immediately after).

Method 2: Highlight Duplicates First (to Review Before Deleting)

  1. 5

    Select column → Conditional Formatting → Highlight Duplicate Values

    Select the column or range. Home tab → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Duplicates are highlighted in colour so you can review them before deciding to delete. Then filter by colour (Data → Filter → Filter by Colour) to select and delete all highlighted rows.

Method 3: COUNTIF Formula (Find Without Deleting)

In a new column next to your data, enter: =COUNTIF($A$2:$A$100,A2)>1. This returns TRUE for duplicate values and FALSE for unique values. Filter on TRUE to see all duplicates before removing them.

Always work on a copyBefore removing duplicates, press Ctrl+Z immediately if the result was not what you expected. Better yet, work on a copy of the file or duplicate the sheet (right-click sheet tab → Move or Copy → tick Create a copy) before running Remove Duplicates.

Frequently Asked Questions

Common causes: extra spaces in cells (TRIM the data first), different capitalisation (“SMITH” vs “Smith” — Excel’s Remove Duplicates is case-insensitive but check for trailing spaces), numbers stored as text vs numbers (format all cells consistently), or the wrong columns are ticked in the dialog. Use the EXACT() function to compare cells character-by-character to identify subtle differences.
Yes — in Google Sheets: select your data range → Data menu → Data cleanup → Remove duplicates. Same concept with a similar dialog. Alternatively, select a range and use the UNIQUE() function to extract only unique values into a new location without modifying the original data.