How to / Counting

How to remove duplicates in Excel with a formula

Use =UNIQUE(A2:A7) in an empty column. It returns the list with every repeat removed and leaves the original as it is.

Each city once

ABC
1City
2LisbonLisbon
3PortoPorto
4LisbonFaro
5Faro
6Porto
7Lisbon

In C2: =UNIQUE(A2:A7) → Lisbon; Porto; Faro

How it works

  1. UNIQUE keeps the first of each value and drops the rest.
  2. The result spills down from the cell with the formula, as many rows as it needs.
  3. It is live: add a new city to the list and it appears in the result.

Variations

  1. Without duplicates, and sorted

    ABC
    1City
    2LisbonFaro
    3PortoLisbon
    4LisbonPorto
    5Faro
    6Porto
    7Lisbon

    In C2: =SORT(UNIQUE(A2:A7)) → Faro; Lisbon; Porto

  2. Only the values that have no duplicate

    ABC
    1City
    2LisbonFaro
    3Porto
    4Lisbon
    5Faro
    6Porto
    7Lisbon

    In C2: =UNIQUE(A2:A7, FALSE, TRUE) → Faro

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related