Three ways to make a unique list in Excel, the hard way and the new way.

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


Making a list of unique or not duplicated names from a longer Excel list with duplicates is possible in three ways that update automatically. One is headspinningly hard and one has some minor quibbles.  The best option is a lot easier but not yet available to everyone.

We’re talking here about making a list of unique (non-duplicate) values which update automatically if the source list changes.  There is a ‘unique’ option but it’s a ‘one off’ action that doesn’t refresh.

Why bother?

A surprising question about making a unique list is why it’s even necessary. After all, the question goes, “We know the list of departments, regions etc”, just type them in and move on.

There are two main reasons why it’s better to produce unique lists from the original data.

Expand or Contract automatically

If the list of stores, regions etc change an automatic list of unique values will update automatically.  See the bottom rows of the examples below.

Let’s say one of the seven dwarves has gone walkabout, the new list of values removes ‘Doc’ automatically (left).   Or maybe there’s a new (and very popular) dwarf added without changing a single formula (right).

three ways to make a unique list in excel the hard way and the new way microsoft office 30508 - Three ways to make a unique list in Excel, the hard way and the new way.

Error Checking

Automatic unique lists also quickly show data errors in the source list, like spelling mistakes:

three ways to make a unique list in excel the hard way and the new way microsoft office 30509 - Three ways to make a unique list in Excel, the hard way and the new way.

A manually typed list (looking for a hardcoded ‘Bashful’) would ignore the misspelled item, making the totals and summaries incorrect.  The auto generated unique list always shows all unique names, including spelling mistakes.

IFERROR with Index, Match and Countif

There’s a complex array formula that was made in the dim, dark past of Excel and passed down from developer to developer.  An Excel meme, if you will.

It’s complicated but widely supported across Excel releases and past versions.  You should not have to worry about compatibility as long as Excel supports array formulas (which have been around for many years)

=IFERROR(INDEX(<range to search>, MATCH(0,COUNTIF(<absolute cell reference of cell at top of current column>:<current cell less one row>, <range to search – same as earlier in formula), 0)),"")

Here’s the array formula in action making a list of all the dwarves in the list at left.

three ways to make a unique list in excel the hard way and the new way microsoft office 30510 - Three ways to make a unique list in Excel, the hard way and the new way.

Far easier to copy and paste one of these formula (one with a table reference) then change the parameters to suit.

=IFERROR(INDEX($B$2:$B$14, MATCH(0,COUNTIF($E$1:E1, $B$2:$B$14), 0)),"")

=IFERROR(INDEX(ShortPeople[Name], MATCH(0,COUNTIF($E$1:E1, ShortPeople[Name]), 0)),"")

ARRAY FORMULA!  This is an array formula, meaning the results spill into other cells.  Keep clear the cells below the main cell with the formula.

The new Dynamic Arrays are NOT needed but you must end typing the formula by pressing Ctrl + Shift + Enter instead of just Enter.  Ctrl + Shift + Enter tells Excel to make an array formula.

Using PowerQuery

A sneaky way to get a unique list of values is with PowerQuery.  Normally PowerQuery is used to get data from outside sources before massaging it ready for an Excel worksheet.

PowerQuery can accept an existing Excel table as a data source. Then use PowerQuery’s options to make a unique list and drop the result back into the worksheet as another table.

This works quite well but isn’t available across all current Excel releases.  It’s especially useful if you’re importing data from outside sources or using PowerQuery to do other things with the same data anyway.  PowerQuery has other tricks available like automatic sorting, ignoring blanks and even replacing values.

The downside is recalculation. Unlike other Unique options, this PowerQuery trick does NOT update automatically, not even if you press F9.  PowerQuery works on its own updating schedule. It’s something we explain and workaround in Real Time Excel (because it’s very important for stock price updating).

First, click in a cell of the source table you want to use.  Then Data | Get Data | From Other Sources | From Table/Range.

three ways to make a unique list in excel the hard way and the new way microsoft office 30511 - Three ways to make a unique list in Excel, the hard way and the new way.

Because the cursor is in a table already, Excel assumes that’s the table you want to import and does it right away.

Removing duplicates couldn’t be easier.  Select the column you want to de-duplicate then PowerQuery Home | Remove Rows | Remove Duplicates.

three ways to make a unique list in excel the hard way and the new way microsoft office 30512 - Three ways to make a unique list in Excel, the hard way and the new way.

Then select the other columns and choose Remove Columns.

three ways to make a unique list in excel the hard way and the new way microsoft office 30513 - Three ways to make a unique list in Excel, the hard way and the new way.

Now you have a simple list of unique names.  Close and Load inserts a new table into Excel.  Feel free to move that table to a better location, like next to the source table.

three ways to make a unique list in excel the hard way and the new way microsoft office 30514 - Three ways to make a unique list in Excel, the hard way and the new way.

Another issue with the PowerQuery method is this incorrect warning when opening the workbook.

three ways to make a unique list in excel the hard way and the new way microsoft office 30515 - Three ways to make a unique list in Excel, the hard way and the new way.

Security Warning: External Data Connections have been disabled.

Of course, there aren’t any external connections.  The only connection is internal (i.e from a table in the current workbook).

Click ‘Enable Content’ then the prompt to make the workbook a Trusted Document.  That will stop those prompts in future.

The new and better way Unique()

Here’s the super-simple way to make a unique list of names.  It’s so easy you’ll wonder why it hasn’t been in Excel for years.

The new Dynamic Arrays feature includes a simple, direct version of the IFERROR… etc. formula mentioned above. UNIQUE() does all the work and some more.

Dynamic Arrays are coming to Excel 365 across almost all platforms. If you don’t have Dynamic Arrays already, you soon will.

All that’s needed is a source list like the name of a table column. Mostly, the two optional parameters aren’t needed.

=UNIQUE(ShortMen[Name])

three ways to make a unique list in excel the hard way and the new way microsoft office 30516 - Three ways to make a unique list in Excel, the hard way and the new way.

Simple as that.  The results will expand or contract automatically and recalculate any time the source list changes.

The sub-totals column F uses SumIF()

The full function is:

Unique(<range>, <by column>, <occurs once>)

Range

the source list of names/values.  The documentation talks about an ‘array’, in this contest any range or table reference is enough/

by_column

[optional] how to sort the list.  The default is FALSE for row; by column = TRUE.

Clarification:  the FALSE default means looking down a column which is usual (i.e the range has the same Column reference but different rows B2:B23) .  TRUE means a horizontal array working across (the range has different letters  B2:F2).

occurs_once

[optional] the default is FALSE – all unique values, TRUE = values that occur once.

FALSE gives a list of all the values in the list, no matter how many times each name/value appears (left).

TRUE shows only names that appear once (right), any name that appears more than once is ignored.

Comparing the occurs_once FALSE (left – showing all names) with   TRUE (right – showing only names that appear once)

Comparing the occurs_once FALSE (left – showing all names) with   TRUE (right – showing only names that appear once)

Do you have Dynamic Arrays in Excel 365?


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.