(Note: Filters have changed a lot since this page was written. It’s scheduled for a complete makeover.)
When 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…
- Use the Advanced filter command, which is the easiest method to use, but must be refreshed manually.
- Use array formulas, which are difficult to set up and which calculate slowly, but refresh automatically.
- Use non-array formulas, which also are difficult to set up but which calculate more quickly and which refresh automatically.
- 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.
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.
After you do so, Excel displays the Advanced Filter dialog. Choose Copy to another location and checkUnique 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.
When 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:
