• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Tables as Source for Data Validation Lists

You are here: Home / Excel / Excel Tables as Source for Data Validation Lists
Excel Tables as Source for Data Validation Lists
April 30, 2013 by Mynda Treacy

I’ve set up a table with some team names that I want to use in a Data Validation list.

The reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the list.

My table’s name is Table1, as you can see here in the name box:

Excel Data Validation source Table

Now, if you’ve ever tried to reference an Excel Table as your Data Validation lists source like this:

Excel Data Validation source Table

Then you probably got the error:

β€œThe formula you typed contains and error”.

Here are three ways to fix this:

Method 1: Use the INDIRECT function with the tables structured references like this:

Excel Data Validation source Table

Method 2: Give your Table another name in the name manager. In this example my table is in cells A2:A7 and is called Table1. In the name manager I’ve set up a new named range called Team_Table and referenced it to the Team column (and only column in this case) of Table1:

Excel Data Validation source Table

Now I can set up my Data Validation list like this:

Excel Data Validation source Table

No need to use INDIRECT, and it will still dynamically update as new data is added or removed from the table just like the first example.

Method 3: Simply reference the cells in the table:

Data Validation source table cells

Then when you add items to the bottom of the Table your Data Validation List source automatically updates*, almost like magic. No named range, no Structured references:

Data Validation source table cells

It's a shame you can't just use the Table Structured Reference as your Data Validation list source. I suspect this is a bug/oversight. Unfortunately I'm not aware of any plans to fix it and it's still like this in Excel 2016!

*Note: this requires the Table to be on the same sheet as the data validation list. If not, use method 2.

Excel Tables as Source for Data Validation Lists

More Data Validation Posts

select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Searchable Drop Down List in Excel

Searchable Drop Down List in Excel

This searchable drop down list in Excel includes an option for "All" and ignores duplicates. No VBA and no formulas. You won’t believe how easy it is.
selecting multiple items in data validation list

Populating Multiple Cells from Single Data Validation (Drop Down) List

Using a little VBA we can use a single data validation list to select multiple items and populate multiple cells
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Excel Custom Data Validation

Excel Custom Data Validation to Limit Entries

Excel Custom Data Validation enables you to limit the value or number of entries in a range of cells.
Excel Combo Box KO’s Data Validation

Excel Combo Box KO’s Data Validation

Use an Excel Combo Box as an alternative to Data Validation Lists
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Data Validation With Dependent Lists

Excel Data Validation With Dependent Lists

excel drop down lists

Excel Drop Down Lists

Excel Drop Down Lists or Data Validation Lists as they're officially known, are a great tool speeding up data entry and ensuring data is entered correctly.

More Excel Tables Posts

excel tables

Save Time With Excel Tables

Excel Tables are a must have skill for all Excel users and their Structured References are a much simpler way to dynamically name ranges.

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category: ExcelTag: Data Validation, excel tables
Previous Post:Excel Step ChartsExcel Step Charts
Next Post:Excel BETWEEN FormulaExcel BETWEEN Formula

Reader Interactions

Comments

  1. Claire Squibb

    April 27, 2022 at 2:15 am

    Really useful article that explains the problem I had today. I was teaching Intermediate Excel and got my learners to make a DV list based on a table to allow for later expansion. All went well until I added the extra item – and the DV list didn’t expand. I expect it was because, on this occasion, I had decided to put the list on a new sheet. How irritating! So I had to add the extra range name step, which just pushed it over the edge of complexity for the group.

    Isn’t this behaviour irritating – it’s time DV got to hear about Tables!

    Reply
    • Mynda Treacy

      April 27, 2022 at 9:28 am

      Absolutely agree, Claire! We MVPs have been asking for this for years.

      Reply
  2. DJones

    April 8, 2022 at 7:44 pm

    How did you figure out that INDIRECT would work for structured references in Data Validation?
    (Rhetorical). Thanks a bunch!

    Reply
    • Mynda Treacy

      April 9, 2022 at 10:29 am

      πŸ™‚ glad it was helpful!

      Reply
  3. Chris Wright

    March 30, 2022 at 1:39 am

    Mynda
    That was a big help. I used method 1 and it worked like magic
    Chris UK

    Reply
    • Mynda Treacy

      March 30, 2022 at 12:20 pm

      Great to hear, Chris!

      Reply
  4. Joseph Payne

    January 4, 2022 at 8:12 am

    Turned each of my reference lists into a table (these will be expanded often). In the data validation input box, could find no way to identify the table as the source. I can select the entire column at the top of the reference list, but the dropdown list then also shows the column heading. How do I correct this?

    Reply
    • Mynda Treacy

      January 4, 2022 at 9:42 am

      Hi Joseph,

      If the data validation list is on the same sheet as the table, you can simply reference the rows (not the header) and the list will automatically grow with the table even though it doesn’t use the Table’s structured references. Try it and you will see.

      If your data validation list is on a different sheet to the table, first define a name for the table rows, then use that name in your data validation list.

      Mynda

      Reply
  5. Jafar

    August 8, 2021 at 5:53 pm

    Well done! Thanks very much. Work well

    Reply
    • Mynda Treacy

      August 9, 2021 at 10:13 am

      Glad it was helpful, Jafar!

      Reply
  6. Caroline Michael Kopilyo

    July 2, 2021 at 10:19 am

    Very useful information

    Reply
    • Mynda Treacy

      July 2, 2021 at 10:42 am

      Great to hear, Caroline!

      Reply
  7. Dominik

    September 17, 2019 at 1:11 pm

    Thanks for the advice. Some comments which you may want to add to the article are below:

    Method 1 has the disadvantage that renaming the table that is referred to will not result in updating the INDIRECT reference. As such, the referred Table should get its final name before making the references.

    I noticed that Method 2 unfortunately doesn’t work when the Cell to be validated is itself in another table. I see no apparent reason why that is the case, but I couldn’t get it to work.

    Reply
    • Mynda Treacy

      September 17, 2019 at 10:13 pm

      Hi Dominik,

      Regarding method 1, you’re correct, no table names dynamically update this way.

      Regarding method 2, I tested it and it worked for me.

      Mynda

      Reply
  8. Chris

    August 15, 2019 at 7:09 pm

    These work when adding data to the bottom of the list. How do you get data validation to work when you add a column as I can’t seem to get it to work. In my example I am expanding the table to the right by adding additional months, with data populated below the month name.

    I want the data validation to offer me the month names.

    Reply
    • Philip Treacy

      August 16, 2019 at 3:37 pm

      Hi Chris,

      You have to refer to table headers, like: =Table1[#Headers]

      To refer to a month, try:

      =Offset(Table1[Column1],0,MATCH(“June”,Table1[#Headers],0)-1)

      Reply
  9. Simon

    June 10, 2019 at 12:27 am

    Thank you so much! A long-standing ‘issue’ with Excel that has puzzled me. No matter… a bit of extra typing never hurt anyone and the INDIRECT solution works a treat.

    Reply
  10. Arnie

    March 25, 2019 at 9:07 am

    Thank you Mynda, this was really helpful.

    I was puzzled that the column names of an area formated as a Table (i.e. the columns names in the Header) couldn’t be used at all, but your trick nΒ°2 worked fine.

    I’ll definitely have a look at the other interesting information you are sharing

    Arnie

    Reply
    • Mynda Treacy

      March 25, 2019 at 9:15 am

      Glad I could help, Arnie πŸ™‚

      Reply
  11. Rich Malloy

    November 11, 2018 at 3:21 pm

    Mynda,

    Nice page on Data Validation Lists from Excel Tables. Thank you for posting it.

    I have noticed an obscure problem with the third method you posted, i.e., using explicit cell references to refer to the list. If the Excel Table and the Data Validation drop-down menu are on the same worksheet, the menu will indeed grow dynamically as the table grows.

    But, if the Excel Table is on a different sheet, then the Data Validation List will not grow. Because I often like to put lookup tables on separate sheets, I usually have to use your Method 2, where I define a name for the Lookup Column in the table.

    Reply
    • Mynda Treacy

      November 11, 2018 at 6:20 pm

      Hi Rich,

      Yes, you’re right. The data validation list referencing a table will only dynamically grow if they are on the same sheet. I’ll add a note about that. The workaround is as you described; give the table a named range and then use that in your data validation list.

      Thanks for the reminder.

      Mynda

      Reply
  12. Julian

    April 25, 2018 at 8:15 pm

    Really helpful!
    Thank you.

    Reply
  13. filterbank

    January 24, 2018 at 7:41 pm

    thanks for the tips… was also going crazy over this point…

    Reply
    • Mynda Treacy

      January 24, 2018 at 8:28 pm

      Glad it was useful.

      Reply
  14. Pradeep G

    November 4, 2017 at 11:26 pm

    Thanks a lot. Came across these tips after going nuts for an hour.

    Reply
    • Mynda Treacy

      November 5, 2017 at 5:31 pm

      Glad we could help, Pradeep.

      Reply
  15. Scott

    September 22, 2017 at 4:12 am

    Thank you for the tip!!! I searched all over the internet about this very problem. You were the only site I could find with the solution!!!

    Thank you so very much!

    Scott

    Reply
    • Mynda Treacy

      September 22, 2017 at 9:37 am

      Glad I could help, Scott πŸ™‚

      Reply
  16. Stephen

    May 14, 2017 at 3:30 pm

    Thanks very much for sharing this info – it was driving me nuts why couldn’t get it to work.

    Reply
    • Stephen

      May 14, 2017 at 3:39 pm

      As Mynda noted, you can drop the column reference from the formula – so that in your example:

      =indirect(“table1[team]”)

      can be shortened to:

      =indirect(“table1”)

      Obviously not something you’d want to do if your “lookup” table has more than one column though!

      Reply
  17. Alexi M.

    February 3, 2017 at 6:16 am

    Great info. I got method’s 1 & 2 to work right away. I struggled with method 3, naming the data only range of the table. Adding new rows didn’t appear in the lookup. I ran across this slight variation on method 3 that worked for me:
    – Create the lookup list (including a header)
    – BEFORE formatting as a table, name the explicit data only range A2:A4 (leaving A1, the heading, out)
    – THEN format the list as a table
    – NOW adding & removing rows are reflected in the lookup (because the explicitly named range gets updated with the changes)

    Reply
    • Mynda Treacy

      February 3, 2017 at 8:46 am

      Hi Alexi,

      I wonder if you included the Table header when you tried method 3. It works for me everytime when I don’t include the table header.

      Mynda

      Reply
  18. singaravelu s

    November 4, 2016 at 2:18 pm

    Can you mark the article with Method 1, Method 2, Method 3, etc. instead of simple numeric values. I really mistook it for Step 1, Step 2, Step 3, etc.

    Reply
    • Mynda Treacy

      November 4, 2016 at 2:25 pm

      Great idea. Done πŸ™‚

      Reply
  19. Guillermo G.

    September 25, 2016 at 2:46 am

    Thanks!

    Reply
  20. Lynda

    June 9, 2016 at 2:00 am

    Sweet!

    Reply
    • Philip Treacy

      June 9, 2016 at 8:17 am

      You’re welcome πŸ™‚

      Reply
  21. Robert

    March 22, 2016 at 12:48 am

    Hi,

    I have a table on one sheet and it’s connected to another sheet as a data validation pull-down menu. I used the “indirect” option. The table is a list of alphabetized names (last,first). When I add a new name to the table and re-sort it by last name (to keep things alphabetized), any data validation cell that was based on the original alphabetized table is changed by the new alphabetical order. How do I get it so that and updates to the table won’t change what was previously selected on the other sheet so that we can continue to use this spreadsheet as we add and delete new people from the team?

    Reply
    • Catalin Bombea

      March 22, 2016 at 2:30 am

      Hi Robert,
      You have to prepare an example file with your data structure, it’s hard to visualize what you did. Please open a new ticket on our Help Desk, I will help you find a solution for this.
      Cheers,
      Catalin

      Reply
  22. Steve

    October 2, 2015 at 2:10 am

    I’m still using Excel 2010; using the table name in the formula works well if there is only one column in the table, but when I have multiple columns in my table and there are entries added to only one column it works for that column, but subsequent additions to other columns are not recognized. Do you have a workaround for this?

    Reply
    • Mynda Treacy

      October 2, 2015 at 10:21 am

      Hi Steve,

      If your Table has more than one column then you can reference the specific column by using a reference like this:

      =Table1[column_name]

      The column name is surrounded by square brackets.

      I hope that helps.

      Mynda

      Reply
  23. evillama

    July 25, 2015 at 11:38 pm

    Thanks for the tip. Definitely helped me out.

    Is there a well defined “reason” for not being able to use a table name directly in the Source field for Data Validation? I get how INDIRECT ends up working, but still unsure why this has to be done…

    ENV

    Reply
    • Catalin Bombea

      July 26, 2015 at 3:08 am

      Hi,
      You don’t have to use INDIRECT or a defined name for your data validation list. When you create the validation list, instead of typing the table name and column name, try to use the mouse to point to that table column. You will notice that excel will insert the range reference, like: A1:A12, instead of the column name. Now, if you add new rows to your table, you will be surprised to see that the new rows do show up in your validation list, even if you did not used INDIRECT, or a defined name to refer to your table column!

        Excel will update the range that refers to a table!


      If you want to use the validation list in other sheets than the source table sheet, the only thing you should be aware of is that this works only in excel versions from 2010 up… Excel 2007 and lower versions are not accepting references to other sheets.
      Same thing happens in Conditional Formatting rules, you cannot use table names there, Excel will convert them to the equivalent range, but

        it will update the reference when the table size increases!


      Using names is the best choice though, this is what I recommend, because sometimes Excel fails to update table references, especially in tables or pivot tables conditional formattings.
      Cheers,
      Catalin

      Reply
  24. Allen

    June 4, 2015 at 7:42 am

    Brilliant! I must have last search for a solution in 2012, before you published this πŸ™‚
    This will simplify so many of my workbooks. Thanks. You just got another subscriber.

    Cheers,

    Mitch

    Reply
    • Mynda Treacy

      June 4, 2015 at 9:56 am

      Awesome! Glad we could help πŸ™‚

      Reply
  25. Paul Spiteri

    August 11, 2013 at 9:54 pm

    Very helpful, congrats on explaining quickly/easily
    thanks

    Reply
    • Mynda Treacy

      August 12, 2013 at 1:20 pm

      Thanks, Paul πŸ™‚ Glad I could help.

      Reply
  26. Sorin

    June 7, 2013 at 10:31 pm

    Hi Mynda,

    It is very interested and useful. Thank you.

    Best regards,
    Sorin.

    Reply
    • Mynda Treacy

      June 8, 2013 at 8:50 am

      Thanks, Sorin πŸ™‚

      Reply
  27. Justin

    June 7, 2013 at 3:16 pm

    double naming each of your tables sure makes the name manager busy when you use a lot of tables, not to mention when choosing them from the popup list when you’re writing formulas.

    Have you run into performance problems with indirect because it’s volatile or something? What the advantage of method 2 over method 1? I always just used method 1 with indirect.

    Reply
    • Carlo Estopia

      June 7, 2013 at 7:03 pm

      Hi Justin,

      As far as question number 1, I haven’t run into performance problems yet regarding indirect yet.
      Regarding question number 2, I think method 2 is a lot organized and quicker for Excel to process.

      Cheers,

      CarloE

      Reply
  28. Cyril Seguenot

    May 8, 2013 at 5:46 pm

    Hi Mynda,
    Thank you very much for this trick. I often use tables and this is a question I am often asked. Now, I have a relevant response πŸ™‚

    Reply
    • Mynda Treacy

      May 8, 2013 at 7:55 pm

      Cheers, Cyril πŸ™‚

      Reply
  29. Teddy Fragopoulos

    May 7, 2013 at 9:36 pm

    Does this require that the table resides on the same tab as the validation area? This has been an undesirable restriction in using an array.

    Reply
    • Mynda Treacy

      May 7, 2013 at 10:10 pm

      Hi Teddy,

      No, you can put your table on any worksheet and use the INDIRECT function to reference it in a data validation list.

      Kind regards,

      Mynda.

      Reply
  30. Brenda

    May 3, 2013 at 7:45 am

    Great resource. Can’t wait to try it! Thanks for doing simple and clear writing.

    Reply
    • Mynda Treacy

      May 3, 2013 at 7:55 am

      Cheers, Brenda πŸ™‚

      Reply
  31. Bob Watson

    May 2, 2013 at 5:06 am

    Tables are a great, though underutilized, feature of Excel; and Data Validation is an essential part of building robust and reliable spreadsheets. Using the two features together like this is a nice combination.

    Because we like this article, we’ve added it to our collection of useful and interesting spreadsheet-related articles from the web at http://www.i-nth.com/resources/connexion

    Cheers,

    Bob.

    Reply
    • Mynda Treacy

      May 2, 2013 at 6:45 pm

      Cheers, Bob. Thanks for sharing our tutorial.

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.