{"id":2825,"date":"2026-11-02T14:30:13","date_gmt":"2026-11-02T03:30:13","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=2825"},"modified":"2026-11-04T09:09:29","modified_gmt":"2026-11-03T22:09:29","slug":"how-to-remove-duplicates-in-excel","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-remove-duplicates-in-excel","title":{"rendered":"How to Remove Duplicates in Excel (4 Easy Ways)"},"content":{"rendered":"\n

To remove duplicates in Excel, click anywhere in your data, go to the Data tab and click Remove Duplicates in the Data Tools group. Tick the columns Excel should compare (all columns removes only identical rows; one column removes repeats of that value), make sure My data has headers is ticked, and click OK. Excel deletes the duplicate rows, keeps the first occurrence of each, and tells you how many were removed.<\/strong><\/p>\n\n\n\n\n\n\n\n

\"How<\/figure>\n\n\n\n

Duplicate rows creep into every list: a customer entered twice, an export run two times, two lists pasted together. This guide covers four ways to deal with them, from the one-click Remove Duplicates button to formula methods that leave your original data untouched, plus what to check when Excel misses duplicates you can see.<\/p>\n\n\n\n

How do you remove duplicates in Excel with Remove Duplicates?<\/h2>\n\n\n\n
    \n
  1. Click any cell in your data (or select the exact range).<\/li>\n\n\n\n
  2. Go to Data > Data Tools > Remove Duplicates<\/strong>.<\/li>\n\n\n\n
  3. Tick My data has headers<\/strong> if your first row contains headings.<\/li>\n\n\n\n
  4. Choose the columns to compare:
    \u25e6 All columns ticked<\/strong> removes rows that are identical across every column.
    \u25e6 One column ticked<\/strong> (for example Email) removes every row whose email has already appeared, even if other columns differ.<\/li>\n\n\n\n
  5. Click OK<\/strong>. Excel reports how many duplicates were removed and how many unique values remain.<\/li>\n<\/ol>\n\n\n\n

    Remove Duplicates deletes rows permanently (press Ctrl + Z<\/strong> straight away to undo), so it is worth working on a copy of important data. If your data is in an Excel Table, the same button is on the Table Design<\/strong> tab.<\/p>\n\n\n\n

    How do you find and highlight duplicates without deleting them?<\/h2>\n\n\n\n

    Sometimes you want to review duplicates before removing anything:<\/p>\n\n\n\n

      \n
    1. Select the column or range to check.<\/li>\n\n\n\n
    2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values<\/strong>.<\/li>\n\n\n\n
    3. Choose a format and click OK<\/strong>.<\/li>\n<\/ol>\n\n\n\n

      Every repeated value is now highlighted. Use Data > Filter<\/strong> and Filter by Color<\/strong> to see only the duplicates.<\/p>\n\n\n\n

      To count duplicates instead, add a helper column with =COUNTIF($A$2:$A$500,A2)>1<\/code>. It returns TRUE<\/code> for every value that appears more than once.<\/p>\n\n\n\n

      How do you remove duplicates with a formula?<\/h2>\n\n\n\n

      In Excel for Microsoft 365 and Excel 2021 or later, the UNIQUE<\/strong> function returns a duplicate-free list in a new location and updates automatically when the source data changes:<\/p>\n\n\n\n