Excel Extract a Unique List

As with most things in Excel, there are many ways to extract a list of unique or distinct values from a column of data. We’ll look at the options, and the pros and cons of each.

Download the Workbook

Excel Formula to Extract a List of Unique Values from a Column

We can use an array formula to extract a list of unique values from a column.

The example below uses INDEX, MATCH and COUNTIF to generate the list. It’s then wrapped in the IFERROR function so that when the end of the list is reached the formula simply returns a blank, instead of an error.

Here is the formula so you can copy it to your own workbook:

=IFERROR(INDEX($B$6:$B$10,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0)),"")

Notes:

1. The formula must start at least one row below row 1
2. The source data cannot contain any empty cells.
3. The range in COUNTIF starts 1 row above the cell containing the formula. i.e. in this case D5 - if your formula starts in a different row then you need to adjust this reference accordingly.
4. This is an array formula, so it must be entered with CTRL+SHIFT+ENTER. That’s how the curly braces are inserted that you can see in the formula bar.

What’s that? You want a sorted list. Ok, you can use this formula:

Here is the formula so you can copy it to your own workbook:

=IFERROR(INDEX($M$6:$M$10, MATCH(SMALL(IF(COUNTIF($O$5:O5, $M$6:$M$10)=0, COUNTIF($M$6:$M$10, "<"&$M$6:$M$10), ""), 1), COUNTIF($M$6:$M$10, "<"&$M$6:$M$10), 0)),"")

Notes:

1. The formula must start at least one row below row 1
2. The source data cannot contain any empty cells.
3. The range in the first COUNTIF starts 1 row above the cell containing the formula. i.e. in this case D5 - if your formula starts in a different row then you need to adjust this reference accordingly.
4. This is an array formula so it must be entered with CTRL+SHIFT+ENTER

Now normally I’d translate those formulas into English, but I’m not going to waste your time or mine because I don't recommend this approach. There are less complicated and more robust ways to extract a list of unique values, namely Power Query or PivotTables, that I cover below.

Pros: Automatically updates when new data is added to the range….assuming the range being referenced in the formula is an Excel Table or dynamic named range so that it too expands to include the new data. The example above doesn’t reference dynamically expanding ranges.

Cons: These are complex formulas that are easily broken if an unsuspecting user edits the cell and doesn’t enter it with CTRL+SHIFT+ENTER. Array formulas can slow workbooks down, especially if the range being referenced is large, or there are lots of these formulas.

Remove Duplicate Values

A quick and easy way to get a list of unique values is to take a copy of the column of data for which you want to get the list, then Data tab > Remove Duplicates

And I’m left with a list of unique values:

Pros: Quick and easy to use.

Cons: If your data gets updated then you need to copy the source column and run the Remove Duplicates process again.

Extract Unique Values with Advanced Filter

Advanced Filter can extract a list of unique items from a column or columns. First select the data, then Data tab > Advanced:

In the Advanced filter dialog box (image above) choose to copy the list to another location (4 & 5), and check the box for ‘Unique records only’. And voila, we now have two lists, the original, and the list of unique values in column E:

Pros: Easy to use.

Cons: No link is maintained between the original data and the filtered data. If the original data gets updated then the Advanced Filter must be run again.

Extract a Unique List with PivotTables

A PivotTable is an excellent way to quickly extract a list of unique items which can then be used to feed Data Validation lists etc.

Simply place the column you want the list for into the Rows area:

Tip: Right-click the Grand Total cell > Remove Grand Total:

Pros: Quick and easy to do. It automatically sorts the list in alphabetical order and it retains a connection to the source data so it’s easy to refresh/update. Unlike formulas, PivotTables are not easily broken.

Cons: Requires clicking on the Refresh button to update the PivotTable, or you can write some VBA code to automatically refresh it.

Tip: Set the PivotTable Options to refresh upon opening of the file:

Tip: Use a dynamic named range to reference the list in the PivotTable and use it in other formulas, or data validation lists.

Power Query Extract Unique Values from Column

Power Query (available in Excel 2010 onwards), has a Remove Duplicates tool, which essentially leaves you with a list of unique values.

Format your data in an Excel Table then load the data into Power Query:

Excel 2010 & 2013: Power Query tab > From Table:

Excel 2016: Data tab > Get & Transform group: From Table:

This will load a copy of the data into Power Query and launch the Power Query Editor window.

In the Power Query Editor simply select the column you want it to extract a unique list from > right-click > Remove Other Columns (assuming there is more than one column in your table).

Then Home tab (1) > Remove Rows (2) > Remove Duplicates (3):

Tip: before closing and loading, click the filter button for the column and sort the data.

Click Close & Load (4) > to Table. Now you have two tables; your original table and your Power Query table containing the unique list:

Pros: The great thing about using Power Query is if your source data gets updated you can Refresh the query and it will remove duplicates again. No need to open the query editor. The original data remains intact.

Tip: You can link a Data Validation list to a Power Query table and it will automatically pick up new data. No need to create a dynamic named range like you have to with PivotTables.

Cons: Requires a few more steps than the PivotTables. Power Query is not available in Excel 2007.

Highlight Unique Values with Conditional Formatting

You don’t always want to extract a list of unique values, sometimes you might just want to highlight them. Conditional Formatting can quickly highlight duplicates in a column. Simply select the column of cells containing the suspected duplicates > Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values:

Tip: You can change the format by clicking the drop down for ‘Values with’ (see image above).

Once the formatting is applied you can use filters (Data tab > Filters), based on the cell fill color or font color to display or hide the duplicate values:

Pros: Great for visually highlighting unique values in a column. You can use filters to hide or focus on unique values or duplicate values.

Cons: Duplicates do not get highlighted at all, so you can’t use the formatting to display a unique or distinct list. This technique doesn’t highlight the row and only identifies unique values in a single column.

Highlight Unique Rows with a Conditional Formatting Formula

Let’s say you want to highlight rows that contain unique values across a row. For example, rows 7, 8 and 10 have the unique Dates and ID’s:

For this we need to apply the conditional formatting using a formula:

Click here for an in depth understanding of how to write formulas for conditional formatting.

Pros: highlights the whole row and takes into consideration more than one column. Filters can be used to hide the duplicates, or the unique values from view.

Cons: The formula can be difficult to remember. Duplicates do not get highlighted at all, so you can’t use the formatting to display a unique or distinct list.

So, there you have 6 ways to identify or extract a list of unique values. Depending on my needs I like to use Power Query or PivotTables to extract a distinct list, or Conditional Formatting to visually indicate unique records.

Related Lessons

 

Tutorials:

• Conditional Formatting
• Conditional Formatting with Formulas
• Advanced Filters
• Advanced Filter Unique Records
• Excel Tables
• PivotTables

Courses:

• Advanced Excel
• Power Query

Please Share

If you liked this please click the buttons below to share.