For business users of Microsoft Excel Free guides and templates

Interactive Reporting

How to Use Advanced Filters to Create Unique List of Items from an Excel Table

The Advanced Filter command provides a quick way to generate a unique list of items from a Table.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

(Download the files here.)

Simple Excel listWhen we work with data in an Excel Table, we often would like to set up a dropdown list box that allows us to select an item from one of its columns for use in formulas.

Here, for example, we might want to set up a list box for the  Product column.

Here, the task is easy, because the Product column has only three items. But in a real application, the Table could have tens of thousands of rows, and the number of unique products could be in the same.

Excel supports at least four ways to generate a list of unique items from a Table…

  1. Use the Advanced filter command, which is the easiest method to use, but must be refreshed manually.
  2. Use array formulas, which are difficult to set up and which calculate slowly, but refresh automatically.
  3. Use non-array formulas, which also are difficult to set up but which calculate more quickly and which refresh automatically.
  4. Use a PivotTable, which takes some work to set up and must be refreshed manually.

In this article, I’ll explain the first of these techniques: using the Advanced filter command.

Set Up the Advanced Filter Worksheet

As shown below, set up either an Excel Table or a normal table from which you want to generate a list of unique items. But make sure your table has column titles, as shown in cells B1 and C1 of both figures.

Setup for advanced filter

Then copy the title of the column you want to summarize and paste it twice to nearby cells, as shown above.

Use Advanced Filter to Generate Your Unique List

Start by selecting any cell within your table, cell B4, for example. Then choose Data, Sort & Filter, Avanced.

 When you do so, Excel will display a dialog about column labels. Assuming your columns have labels, choose OK.

Advanced filter dialogAfter you do so, Excel displays the Advanced Filter dialog. Choose Copy to another location and check Unique records only.

For the Criteria range, select the two-cell range that begins with the copy of the first column title in the previous figure and ends with an empty cell. So in the pair of figures above, you'd choose the range E1:E2.

For the Copy-to range, select cell F1 (which contains the text Product), as shown in the pair of figures above. At this point, your dialog should look like this example.

When you choose OK, Excel will generate a unique list of items from the specified column.

Warning…If your list is 100,000 rows or more, this step could take some time for Excel to complete. So you might want to refill your coffee cup at this point.

Advanced filterWhen the list is complete, as shown here in column F, select it and sort it, using the command Data, Sort & Filter, Sort.

Your sorted, unique list is now ready to use in your report workbook. So copy it to the range where you’ll use the list in your reports.

Downloads and Further Reading

You’ll find related content here:

And also, I created one download file for all three articles. You can download the files at this link.


Tags: #advanced filter, #unique list, #tables, #Excel Table

 

Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel

How to Set Up a Data-Validation List Box in Excel

Free Excel Dashboards


Charley's SwipeFile charts