Here are the most practical ways to identify duplicates in Excel, from quickest-click options to more flexible formulas.

Use built‑in “Duplicate Values” highlight

This is the fastest way when you just want to visually spot duplicates in a column.

  1. Select the range or column you want to check (for example A2:A500).
  1. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
  1. Make sure “Duplicate” is selected in the dropdown, choose a format color, and click OK.

Excel will shade all duplicate entries in that range, leaving unique values uncolored.

Use “Remove Duplicates” (with optional backup)

If you want to actually clean the data, not just see the duplicates:

  1. Select your data range (include headers if you have them).
  1. Go to Data → Remove Duplicates.
  1. In the dialog, tick only the columns that must match to be treated as duplicates.
  1. Click OK; Excel will delete duplicate rows and keep the first occurrence, then show a summary of how many were removed.

Tip: Copy your data to another sheet first, so you can compare before/after or undo later if needed.

Flag duplicates with a formula (COUNTIF)

Formulas are best if you want a live “Duplicate / Unique” flag that updates when data changes.

Basic single‑column duplicate check

Assume your values are in column A, starting in A2:

  1. In B2, enter:
    =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")
  1. Copy the formula down column B.

Any value that appears more than once in A2:A100 will show “Duplicate” next to it.

TRUE/FALSE style check

If you just need a quick logical result:

  • In B2: =COUNTIF($A$2:$A$100,A2)>1

This returns TRUE for duplicates and FALSE for unique values, which you can then filter on.

Find duplicate rows across several columns

Sometimes a duplicate is only real if several fields match, like Order ID + Date + Item. Suppose:

  • Order ID in A
  • Date in B
  • Item in C
  • Data from row 2 to 100

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

Copy down; any row where all three columns match another row will be labeled “Duplicate row”.

You can then filter column D to show only duplicate rows.

Case‑sensitive or more advanced options

Excel’s standard duplicate tools are not case‑sensitive, so “Apple” and “apple” are treated as the same.

For more advanced scenarios:

  • Use functions like EXACT inside an array style formula to treat “Apple” and “apple” differently.
  • Use Power Query (Data → Get & Transform) to load your data, normalize case if needed, and remove or mark duplicates using its built‑in “Remove Duplicates” on selected columns.
  • Use pivot tables to see each value and how many times it occurs, which makes duplicates stand out.

Simple HTML table: key methods in Excel

html

<table>
  <thead>
    <tr>
      <th>Goal</th>
      <th>Method</th>
      <th>Pros</th>
      <th>Cons</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Quickly see duplicates in a column</td>
      <td>Conditional Formatting → Duplicate Values</td>
      <td>Very fast, no formulas, great for visual scan</td>
      <td>Does not give counts; not ideal for export or logic</td>
    </tr>
    <tr>
      <td>Delete duplicate rows</td>
      <td>Data → Remove Duplicates</td>
      <td>One click cleanup, built in to Excel</td>
      <td>Permanently removes data; must choose columns carefully</td>
    </tr>
    <tr>
      <td>Label each entry as duplicate/unique</td>
      <td>COUNTIF formula (e.g., =IF(COUNTIF(range,cell)&gt;1,"Duplicate","Unique"))</td>
      <td>Dynamic, filterable, easy to audit</td>
      <td>Requires formulas; large ranges can be slower</td>
    </tr>
    <tr>
      <td>Check duplicates across multiple columns</td>
      <td>COUNTIFS formula on several columns</td>
      <td>Captures truly duplicate rows, not just one field</td>
      <td>Formula more complex to set up</td>
    </tr>
    <tr>
      <td>Case‑sensitive or complex logic</td>
      <td>EXACT, Power Query, or pivot tables</td>
      <td>Very flexible; supports custom rules</td>
      <td>More advanced features with a learning curve</td>
    </tr>
  </tbody>
</table>

Tiny TL;DR

  • To see duplicates fast: use Conditional Formatting → Duplicate Values on your range.
  • To remove duplicates: use Data → Remove Duplicates (after saving a backup).
  • To flag duplicates dynamically: use a COUNTIF or COUNTIFS formula in a helper column and filter on “Duplicate”.

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