Create An Excel Data Validation List Using A Table

Disclaimer: Creating a table is only available in Excel 2007 and newer.

In Excel, it's possible to create a dropdown list within a cell. This allows users to choose from a list rather than having to type any information directly into a cell, maybe incorrectly. The simplest way to do this using the Data Validation options is pointing the source of the list to some cells that having the information in them. So, if you want the list to be comprised of departments for your company, you can make the source point to some cells else where that have the departments listed. The downside to this way is that if the list is visible to the user and not protected, they can edit or delete the list, probably by accident, but it can happen. Also, if you want to add new departments to this list, you will have to edit the source of the list to include them.

Setting Up A Simple Data Validation List Without Using A Table

  1. Create a list of 5 departments in cells E1, E2, E3, E4, and E5
  2. Select cell A1
  3. On the Ribbon, select the Data tab
  4. In the Data Tools group, click on Data Validation
  5. On the Settings tab, from the Allow dropdown list, select List
  6. In the Source textbox, type =E1:E5
  7. Click OK
  8. Verify A1 contains a dropdown list to select Departments

Setting Up A Data Validation List Referencing A Table Column

First, we have to create the table with the departments in it. We will do this on a separate worksheet.

  1. Create a new worksheet by clicking on the Insert Worksheet button to the right of the very last worksheet tab at the bottom of the screen. (Shortcut Shift+F11)
  2. Rename this worksheet Administration by right clicking on the worksheet tab and selecting Rename
  3. In cell A1 of the new worksheet, type Name
  4. Type the rest of the departments below in cells A2, A3, A4, A5, and A6
  5. Select cell A1
  6. To convert this range to a table, we need to select the Insert tab on the Ribbon
  7. In the Table group, click on the Table button
  8. Double check to make sure the range =$A$1:$A$6 is in the textbox
  9. Check the My Table Has Headers checkbox
  10. Click OK



Next, we need to change the name of this table to something more descriptive.

  1. Click somewhere within the table you just created
  2. On the Ribbon, select the Table Tools Design tab
  3. In the Properties group, click inside the text box that has the default table name, usually Table1, or something similar.
  4. Type Departments in the textbox, replacing the default name
  5. Press Enter on the keyboard

Now that the table is setup with a descriptive name, you can reference the table using this name. You can also take it a step further and reference a specific column inside the table. The benefit of something like this, is that when you add new rows to the table, any formula using the table reference will update to include the new rows automatically.

To reference a specific table column you can use the following method.

TableName[ColumnName]

In our example, you would use the following.

Departments[Name]

Now we need to go back and setup our Data Validation using the table reference.

  1. Go back to the blank worksheet that we started with before making the Administration worksheet
  2. Select cell A1
  3. On the Ribbon, select the Data tab
  4. In the Data Tools group, click on Data Validation
  5. On the Settings tab, from the Allow dropdown list, select List
  6. In the Source textbox, type =INDIRECT("Departments[Name]")
  7. Click OK
  8. Verify A1 contains a dropdown list to select Departments

Microsoft doesn't allow direct reference to a table and its columns from the Data Validation. Because of this, we have to include the INDIRECT function as well. The image below shows you the arguments dialog box which gives a brief description of the function.

Excel Indirect Function Arguments

Now that this is setup, you can add new rows to the Departments table, and they will automatically show up in the Data Validation dropdown list.

Download Example

Excel Data Validation List Examples

Posted on: October 21, 2015

12 Responses

  1. Thanks very much for the hint, is simple to use and very very useful

  2. Hey Brandon!,

    I followed step by step and seem to be having an error occur. I typed =indirect(“Department[Name]”) in the source textbox, however i get a message that pops up “The source currently evaluates to an error. Do you want to continue?” Any suggestions on why this may be? Thanks so much for your time!

    • Double check the name of the table. Make sure it is named Department and not Departments so that it matches what you are trying to reference there. Or vice versa, change your source to Departments instead of Department. The syntax itself is correct, so the only thing I can think of is that the reference you are trying to use isn’t recognized.

  3. Thanks a ton for this. Do you know if there’s a way I can make multiple Tables the source of a Data Validation? i.e. Source Table 1 contains Active Users, Source Table 2 contains Inactive Users and 1 cell which has data validation with a combined list of Active and Inactive Users.

    • There’s not a simple way to accomplish this with functions as far as I know (if you do find a way please let me know! lol). If it’s possible for your setup, would you be able to combine them into one table with an extra column that marks each row as ‘Active’ or ‘Inactive’? The single column could then be collected for the validation list and you could use a filter function to grab only the active or inactive from the column for wherever else you want to use it. If not and you are familiar with VBA, in my opinion, coding it would probably be the easier way.

  4. Brandon

    Thanks – just what I was looking for. This INDIRECT is a great function. Appreciate the post.

    +Steve

  5. Thanks Brandon, that was really helpful. I searched for ages through Excel help to see if it were possible to refer to a table for data validation but found nothing. A quick check in Google and I found your site which answred my query perfectly.
    Thanks again, Brian

  6. What if I wanted to feed the data validation list from a dynamic table source? That is, if I added a new department in row 14 of this workbook, the data validation list will auto-update. Thank you!

    • Apologies for the delay! Using the table references as a source for the list will do this automatically. When you use a table’s column reference is grabs everything in that column at that point in time. If something was added or removed, it will reflect that next time it fetches the source, in this case, when the user clicks on the dropdown.

Leave a comment