{"id":1992,"date":"2016-03-29T14:09:34","date_gmt":"2016-03-29T12:09:34","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1992"},"modified":"2024-05-22T21:28:09","modified_gmt":"2024-05-22T19:28:09","slug":"dynamic-data-list-using-excel-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/dynamic-data-list-using-excel-tables\/","title":{"rendered":"Dynamic Data List using Excel Tables"},"content":{"rendered":"

Excel Tables have many great features to them and one of them is their ability to create a dynamic drop down list.<\/p>\n

A dynamic drop down list<\/a> expands as the Excel Table expands\u00a0when\u00a0new\u00a0data gets added to it.<\/p>\n

This is great when you want to have users select from a predefined text or value list rather than\u00a0having them manually enter data, which can lead to mistakes.<\/p>\n

Here is how to create a dynamic data validation list using Excel Tables:
\nSTEP 1:<\/strong><\/span>\u00a0\u00a0Convert your list in\u00a0to an Excel Table (Ctrl+T)<\/strong><\/p>\n

\"Dynamic<\/p>\n

STEP 2:<\/strong><\/span> Select your Table column<\/strong> by hovering over the Excel Table and left clicking when the arrow pointer shows<\/p>\n

\"Dynamic<\/p>\n

STEP 3:<\/strong> <\/span>In the ribbon menu, go to Formulas<\/a> > Define Name ><\/em> enter a custom name<\/strong> with no spaces<\/strong> (we will put this name in step 5<\/strong>) and press OK<\/strong><\/p>\n

\"Dynamic<\/p>\n

\"Dynamic<\/a><\/p>\n

STEP 4:<\/strong><\/span> Click on the other Excel Table column that you want to enter the dynamic list into and go to the ribbon and choose Data > Data Validation > List<\/em><\/strong><\/p>\n

\"Dynamic<\/p>\n

STEP 5:<\/strong><\/span> In the Source box enter the name you created in Step 3<\/strong> and press OK or<\/span><\/strong> click in Source<\/em> box,\u00a0press F3 and select the named range<\/strong> from there (thanks to Michael<\/em> from www.excelpogodzinach.pl for introducing this tip to us!)<\/p>\n

\"Dynamic<\/p>\n

STEP 6:<\/strong><\/span> If you want to add extra data into your Excel Table list<\/strong>, hover with your mouse in the bottom right hand corner and when you see a double arrow, drag down.<\/p>\n

\"Dynamic<\/p>\n

STEP 7:<\/strong><\/span> Enter a new entry and this will automatically be updated in your drop down list<\/strong><\/p>\n

\"Dynamic
\n