Remove duplicates in Excel with UNIQUE, then FILTER and SORT

UNIQUE removes duplicates with a formula: give it a column and it lists each value once, and keeps the list up to date as the data changes. FILTER and SORT work the same way. The sheet above is the lesson; this page explains it, with an example worked out by the same formula engine.

Two ways to remove duplicates

The Remove Duplicates button (Data tab) deletes the repeated rows from the data itself. It is quick, but it changes the data itself, and it has to be done again whenever new rows arrive.

UNIQUE leaves the data alone and writes a clean list somewhere else. Add a row and the list updates. That is what this lesson practises.

UNIQUE, FILTER and SORT need Excel 2021 or later, or Microsoft 365. Older versions show #NAME?.

A worked example

This sheet comes from the lesson, set in the Coffee bar: sales, with who made each one and at which branch.

UNIQUE, and how answers spill

=UNIQUE(array, [by_col], [exactly_once])

In E2, =UNIQUE(A2:A9) gives each name once, in the order they first appear. One formula, but the answer fills as many cells as it needs, down from E2. That is called spilling. Only E2 holds the formula; the cells under it show the rest of the answer.

If something is in the way, the formula shows #SPILL! instead. Clear the cells below it and the answer appears. With TRUE as the third argument, UNIQUE lists only the values that appear exactly once.

To count the different names, count what UNIQUE gives back: =COUNTA(UNIQUE(A2:A9)) gives 3, from a list of more rows than that.

FILTER

=FILTER(array, include, [if_empty])

FILTER keeps the rows where the test is TRUE. The test is a comparison over a whole column: =FILTER(A2:A9,B2:B9="Campus") keeps the names on rows where column B matches. The third argument is what to show when no row passes; without it you get #CALC!. For two conditions at once, multiply the tests: (B2:B9="North")*(C2:C9>100) means both.

SORT

=SORT(array, [sort_index], [sort_order], [by_col])

SORT returns the list in order, A to Z by default. Feed it the UNIQUE list and one formula gives a clean, sorted list that keeps itself up to date: =SORT(UNIQUE(A2:A9)). For Z to A, give -1 as the sort order. To see the top of that list: =INDEX(SORT(UNIQUE(A2:A9),1,-1),1) gives Sven, the first name of that list.

The lesson, step by step

1. 1 Remove the duplicates

   In E2: =UNIQUE(A2:A9) gives Priya Ines Sven, spilled down from E2.

2. 2 Count the people

   In C11: =COUNTA(UNIQUE(A2:A9)) gives 3.

3. 3 Keep only Campus

   In F2: =FILTER(A2:A9,B2:B9="Campus") gives Ines Priya Sven, spilled down from F2.

4. 4 Sort it

   In G2: =SORT(UNIQUE(A2:A9)) gives Ines Priya Sven, spilled down from G2.

In real Excel

A spilled range can be referred to as a whole with a # after its first cell: =COUNTA(E2#) counts everything UNIQUE spilled from E2, however long it grows. UNIQUE and FILTER ignore capitals when they compare text. Spilling doesn't work inside a formatted table (Insert, Table), so put these formulas next to it, not in it.

Keep going

1. 10
   Next up is Month end: month-end reportThe final sheet: a real report asked in plain words, no formulas shown.
2. 8
   Text: text functionsJoin first and last names, clean up stray spaces and capitals, split an email.
3. 6
   Conditions: SUMIF and COUNTIFTotals and counts that only include the rows you ask for.