Unique Data Validation Drop-Down From Duplicate Table Data


In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values.


Before we get started on the mechanics, let’s take a look at what we are trying to achieve.

We have a data table that is exported from our accounting system. It contains the rep, the transaction date, and the amount, as shown below.


On another sheet, we want the user to be able to select a rep from a drop-down.


We want the drop-down list to contain a unique list of reps from the table.

The goals for our solution are that it is fast and easy to maintain over time, even when new reps may appear in the data table, that we don’t use VBA macros, that we can move the workbook to other folder locations in future periods, and that we don’t need to update any formulas in future periods.

Ready? Me too…let’s do this thing.

NOTE: An alternative to the technique presented below is to use Power Query instead of a PivotTable as discussed in this post: http://www.excel-university.com/use-power-query-to-create-a-drop-down-list-without-duplicates/


This technique involves creating a unique list of reps in a worksheet with a PivotTable. The PivotTable is an intermediate step that creates the rep list that we can use to feed the data validation drop-down list. In a way, the data flow for this technique can be visualized as follows:

  • Table > PivotTable > Named Reference > Data Validation

Let’s take these steps one at a time.


First, we store the source data in a Table. This is easily accomplished by selecting the ordinary range and then the Insert > Table Ribbon icon.


With the table in place, it is time to create the unique list of choices. We select any cell in the table and then the Insert > PivotTable icon. We build a simple PivotTable on a new sheet by placing the field that contains the drop-down choices into the Rows area and removing the field headers and grand totals, as shown below.


Any time you change the underlying table data, such as adding new rows in future periods, you’ll need to update the rep list by clicking the Refresh button (PivotTable Tools > Refresh, or, right-click and Refresh). If you want the list to be sorted as it is refreshed, you can update the sort order of the PivotTable by right-clicking a rep cell and selecting the desired Sort option. If you delete data from the table, and the deleted reps still appear in the PivotTable, head into the PivotTable Options dialog and change the Number of items to retain per field to None.

Now that we have generated a unique list of reps, it is time to put them into a named range that can be used with the data validation feature.

Named Reference

Now, we’ll set up a named range that can be used by the data validation feature to create the drop-down. There are many ways to create a dynamic named range to reference the PivotTable results, and I’ll provide one such solution. If you have any others, please share by posting a comment below.

The named reference needs to dynamically adjust as the number of PivotTable items change. We’ll set up a new name, dd_reps, that uses the following formula:


In summary, this formula creates a range reference that begins with cell C9 on the Lists worksheet (the first cell in the PivotTable) and continues through the cell returned by the INDEX function. The INDEX function dynamically returns the last data cell in column C by using the MATCH function.

To set up the new name, click the New name button in the Name Manager (Formulas > Name Manager) and then enter the name information as shown below.


The resulting name, dd_reps, contains a list of the PivotTable results, even if the number of reps changes over time.

Data Validation

Now, we are home free. All that remains is to set up the drop-down box with the data validation feature (Data > Data Validation).

We simply Allow a List equal to the name, dd_reps, as shown below.


The resulting drop-down contains a list of choices from the PivotTable, as shown below.


Once the workbook is set up, as new data is added to the table each period, you just need to Refresh the PivotTable and any new reps will flow through the PivotTable into the drop-down.

Since this is Excel, there are many ways to accomplish any task, so the method presented above is only one such solution. If you prefer a different solution, we’d love to hear it…please post a comment below and share!

Additional Resources

This article was written by jefflenning



    for validation i used the formula as list


    I agree I use C100 which means max 100 rows from C9

    1. jefflenning Post author

      Thanks for sharing this named reference alternative, which uses the OFFSET/COUNTA combination instead of the INDEX/MATCH combination…thanks!

  2. sean

    Very nice! Thanks!

  3. Gary

    Your solution was my first thought but with the users intended to operate this, they couldn’t be relied upon to update the Pivot.
    Thanks anyway.

    1. jefflenning Post author

      Ah…I see…if you figure out an approach that works for your workbook, please share by posting a comment…thanks!

  4. Felipe

    Found out a better solution so you don’t have to create a pivot table (if you can include new columns in your database – you can hide everything later):
    It will require 3 more columns
    Assuming RepID in cell A1; Date cell B1 and Amount cell C1:

    1) In column D you will have to exclude duplicated values using IF+IFERROR+VLOOKUP as below:
    *** Please note the relative and absolute references carefully
    Apply this formula for the whole table

    2) In column E you will sum only the values that are shown in column D:
    *** In this case the column header must to be empty or equal to zero
    Apply this formula for the whole table

    3) In column F you will exclude the duplicated numbers:
    Apply this formula for the whole table

    4) In another column or sheet (I’ve included in column H of the same sheet):
    Include sequential numbers starting by 1 (1, 2, 3, and so on in each line)
    You can include as many numbers as you want

    5) In column I (in my case) you will lookup the sequential numbers in your table:
    Apply this formula for the whole sequence of numbers you created in column H

    6) Edit the dd_reps using the new reference:

    7) Include the validation list with this new sequence

    Felipe Maier

    1. jefflenning Post author

      Felipe – Thanks for sharing your alternative!

  5. Brian

    I recently did something very similar to Filipe. My slight variation was that if it was the first occurrence (using a similar “if error” reference) and then returned the row(). This gave each first occurrence a unique identifying number. I then used rank() to turn that list of unique but spread numbers into a 1, 2, 3, 4, 5…. On a separate page I had a column of 1, 2, 3, 4, 5, 6, 7 and used it to look up the the row. I think they ways listed here are a bit more elegant, but it is another solution, particularly if you are going to want to make any other references to the list.

    I hope this is something excel adds natively in the future. I was recently using Google Sheets and realized that it automatically turns a validation from a duplicated list into an unduplicated drop down. I have to assume an unduplicated list is the goal more often than not.

    1. jefflenning Post author


Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.