You can find duplicates in Excel using built‑in tools or formulas, depending on whether you want to just highlight them or truly clean data.

Quick Scoop: How to find duplicates in Excel

1. Easiest: Highlight duplicates with Conditional Formatting

Use this if you want to visually spot duplicates without changing any data.

  1. Select the range (for example, a single column like A:A, or a whole table).
  2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
  1. Make sure “Duplicate” is selected in the dropdown.
  2. Choose a format (e.g., light red fill) and click OK.

Now every duplicate value in that selected range is highlighted, while the first occurrence is usually highlighted as well (Excel treats all repeated values as duplicates).

2. Find and remove duplicates (clean your data)

Use this when you want to delete extra copies and keep only one instance of each value.

  1. Click any cell inside your data range.
  2. Go to Data → Remove Duplicates.
  1. In the dialog:
    • Check only the columns you want to use to define a “duplicate” (for example, just “Email” or a combination of “First Name”, “Last Name”, and “Email”).
  1. Click OK and confirm.

Excel will remove duplicate rows based on those columns and tell you how many duplicates were removed and how many unique values remain.

Tip: Always make a backup copy of your sheet (or work on a copy) before removing duplicates so you can undo if something goes wrong.

3. Use a formula to flag duplicates (flexible + transparent)

If you want more control (for example, only flag second and later occurrences, or use the result in filters/pivot tables), use a formula column.

a) Mark duplicates in a single column

Suppose your values are in column A, starting at A2. In B2, enter:

=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")

Then fill the formula down.

  • “Duplicate” appears where the value occurs more than once in A2:A100.
  • “Unique” appears where the value occurs only once.

To mark only duplicates and leave uniques blank:

=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","")

Now you can filter column B to show only “Duplicate”.

b) Mark duplicates including first occurrence vs later ones

If you want to see 1st, 2nd, 3rd occurrence counts for each value:

In B2:

=COUNTIF($A$2:A2,A2)

Fill down.

  • 1 = first time that value appears.
  • 2, 3, … = later duplicate occurrences.

Filter B to show values > 1 if you only want duplicates.

4. Find duplicate rows across multiple columns

Sometimes two rows are “duplicates” only when several columns all match (e.g., same Order ID, Date, and Product).

If your data is in:

  • A2:A8 → Order ID
  • B2:B8 → Date
  • C2:C8 → Product

In D2, use:

=IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2)>1,"Duplicaterow","")

Fill down.

This marks rows where all three columns are identical to at least one other row as “Duplicate row”.

5. Quickly filtering or copying duplicates

Once duplicates are flagged (via Conditional Formatting or formulas), you can:

  • Use AutoFilter on the helper column to:
    • Show only “Duplicate”.
    • Show only occurrences greater than 1 if you used the COUNTIF occurrence formula.
  • Copy visible rows to another sheet to work only with duplicates, or delete them in bulk.

6. Example mini‑story: Cleaning a mailing list

Imagine you have 7,000 email addresses from different campaigns, and some people signed up multiple times.

  • First, you highlight duplicates in the Email column with Conditional Formatting to see how bad the duplication is.
  • Then you add a helper column with the COUNTIF occurrence formula to number the occurrences.
  • Finally, you filter to show only rows where the occurrence is greater than 1 and decide whether to delete those extra entries or archive them.

In a few minutes, you’ve gone from a messy, bloated list to a clean, deduplicated database ready for reliable reporting and campaigns.

7. HTML table: Key methods to find duplicates

Below is an HTML table comparing the main approaches:

html

<table>
  <thead>
    <tr>
      <th>Method</th>
      <th>Best for</th>
      <th>Changes data?</th>
      <th>Skill level</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Conditional Formatting</td>
      <td>Quickly highlighting duplicate values in a column or range[web:8]</td>
      <td>No</td>
      <td>Beginner</td>
    </tr>
    <tr>
      <td>Remove Duplicates (Data tab)</td>
      <td>Deleting duplicate rows based on one or more columns[web:9]</td>
      <td>Yes (removes rows)</td>
      <td>Beginner–Intermediate</td>
    </tr>
    <tr>
      <td>COUNTIF formula</td>
      <td>Flagging duplicates, counting occurrences, flexible filtering[web:3]</td>
      <td>No (unless you delete based on results)</td>
      <td>Intermediate</td>
    </tr>
    <tr>
      <td>COUNTIFS for rows</td>
      <td>Finding duplicate rows across multiple columns[web:3]</td>
      <td>No (unless you delete based on results)</td>
      <td>Intermediate</td>
    </tr>
  </tbody>
</table>

8. Bottom note

Information gathered from public forums or data available on the internet and portrayed here.