Add New Items To Excel Drop-down Lists Automatically In Seconds!

[@jaypogi7603]

by far, the best tutorial video for this topic. Straightforward and easy to follow.

[@ExcelCampus]

Thanks, Jay! 😀

[@khaleethequeenofcutekatz4265]

The best and clear tutorial so far thank you!

[@GamingRevenant]

This video is THE solution for everyone struggling with making organised and dynamic Data Validation lists. Many, MANY thanks!

[@francinethings3589]

Dude, you were all over the place! I see I have a lot to learn.

[@ryanpanes1457]

this is by far the neatest procedure ive seen. many thanks!

[@tejsingh5512]

Step to step instructions, nice and clear

[@geomatrix5452]

Excellent tutorial for auto expanding data validation source (list) Thank you!

[@ExcelCampus]

Glad you liked it! 😀

[@ZijZijnZijnZoons]

Exactly what I needed, thanks!

[@mariviclelis5500]

Thanks Jon it really helps me! Clear explanation easy to follow steps love it

[@andrewmcdermott9517]

Really helpful, thank you! Brilliant tutorial.

[@davehall9423]

- Very helpful and easy to follow

[@ashutosh676]

Very informative lession .......thanks a lot and keep it up

[@richards67240]

Excellent, keep up the great tutorials

[@angelagarine3295]

Thank you for making the use of Excel quicker and easier

[@GearheadExplorer85]

Many thanks for making this video! This will definitely be a time saver in my shop tool database.

[@MarkRoche]

Thanks so much! Really well explained and has helped me a lot.

[@vtetreault6621]

Great quick tutorial. Super easy to follow. Short and to the point.

[@rolandhunter]

AWESOME! Thank you so much!

[@goyal6342]

Thank you so much man, This is incredible. I love this...

[@grimblock]

Thank you clear and concise

[@matthewtschetter1953]

Outstanding Video

[@michelleguraieb7934]

Thank God I found this

[@wayneedmondson1065]

Hi Jon.. thanks for another helpful and informative video tutorial. Thumbs up!

[@ExcelCampus]

Thanks so much for your support Wayne! :-)

[@makeittrades5750]

Great tutorial, any tips as to why the list won't scroll up or down?

[@djomari254]

Wow. awesome tutorial

[@journeyjournalswithsimran]

Thanks, because of you i could learn something new today

[@ExcelCampus]

Glad to hear that! 😀

[@learn_with_amit4167]

You earn a subscriber😘

[@cleoehlers8669]

Very easy to follow, thanks so much!

[@Bee_Hive_10134]

I know this is an old video, and this is a long-shot looking for a reply. I really appreciate you explaining this as this is exactly what I needed. I am wondering, can I have excel automatically select items from my dropdown box based on text from another cell? I keep a database of patient testimonials and want to track symptoms. I've added the list of symptoms and created it as a table. Under column called "testimonial" is their written testimonial. Under column "symptoms" is my table/drop down list. I want to automate it so that when I enter their testimonial text, that any keywords that appear in the "symptoms" list will automatically be checked. Especially if they have more than one symptom listed. Is this possible? If/then? I'm not an expert at Excel. Thank you for your help.

[@gerrybaker5793]

fab instruction - I have created a whole load of pick lists on my current spreadsheets, which will stop others putting erroneous information my tracker!! THANKS!!

[@treajwortham7430]

Really great video well explained not to confusing

[@COOKINGBIRD]

Thank you so much 👍

[@marctemura2017]

Thank you for the video.👍😊⭐

[@agalgonzales8809]

Excellent video

[@ahmedimam4662]

Your videos are great! I'm looking for format the drop down list fonts, increase the contents 8 items max??

[@footyvids9052]

great video

[@SuperMazharuddin]

nice .... from this method can we edit the searchable drop-down list????

[@Bosbulls]

This was really informative. I also had a look at your video to automatically sort the named range. That is something that I was looking for for some time. I use the VLookup function to autofill cells. For example I have a table with customer names, telephone numbers and email addresses on a seperate "Data Sheet". On my job card sheet, I will then only enter the customer name and all of this other detail will then be automatically added with the VLookup function. Sometimes when I get to a new customer, their telephone number and email address is not yet in my list and I will get a #N/A result in these cells. How can I automatically update my Data Sheet and then after I've added the new customer name, telephone number and email address can I automatically sort it in alphabetical order from the customer name? I've already used your autosort macro, but I want it to run after I completed the complete row in the table and not immediately after I added the new customer name. Thanks in advance for your help.

[@abdulhamidalhaddadi2255]

Great lesson. May I ask you a question, after I making a huge list, how can I search and select an item in easy way? Thank you in advance.

[@udayam77]

Hi, what if I have two product columns in a table in which I frequently need to add items? Like say one column for vegetables and another for fruits. Thank you.

[@billleonard1435]

Subtle, as is usually the case. It makes sense though. I've been tripped up by having cell-references within some named ranged, within tables. That now sticks out as a dumb oversight on my part. Well done and many thanks.

[@myspacey01]

In the video at the 2:17 mins in I noticed when I followed the steps you end up with duplicates line items but with different names like you did. So after using name manager table 3 can you go and delete table 3 from the name manager so you do not have the duplicate information ( table 3 & rngProducts)

[@tobiasgartner1799]

Thanks for the helpful hint!

[@bikassingh7469]

Great tip

[@deepajayaram5226]

Hello Jon, the same process is followed, unfortunately in my system In data validation setting, the ICON "Apply these changes to all other cells with the same settings" is disabled. can you please give solution what needs to be corrected.

[@soniaaqua6129]

Hello. What if after you've created your drop down list you notice your data list (using your example, Products) contain duplicate items. Is there an easier way to remove duplicates from the drop down list you created? Thank you.

[@marienverx1852]

Any Idea how to get the value selected in the dropdown list updated, When you change the cell value were the list is made from? So for example jou make a column of some rows with animal types. Than you selct another cell, and greate here a dopdown list from those animals. You select the "COW" from the list and that cell has now value "COW". But now I change the original source cell to "Rabbit" insteat of Cow. In the dropdown list cell, the value keeps Cow, is not updated to Rabbt. How to solve this????

[@Akrowdya64]

Hi, I have updated a list of supplier/buyer assignments through the name manager function. The updates have taken place, but when I try scrolling through the drop down menu of suppliers it doesn't allow me to click on the bar and drag up and down through the options. It only lets use the arrows at the top and bottom which takes forever to navigate when I have over 300 options. Any ideas on how to correct this?

[@ceceharvey1797]

Hello John I really like watching your video. I have a spreadsheet that I am working on at work using a dropdown list and when I add a new item in the list, it reflects in the list but it is not working correctly on my main sheet to return a number. How can I get personal help from you on my document? Thanks

[@hakimkiebekov1972]

=INDIRECT("Table3[Products]") Is an alternative to a named range

[@abhishakya5263]

Sir...How to auto open drop-down box when cursor in that current cell.

[@frankconte2457]

I am trying to expand the size of the options in a drop down menu. Presently I can only see eight options (yes I can scroll down with with scroll bar- but I would like to see more) Is this determined by the size of the cell in which the drop down data validation lives? Any guidance appreciated.

[@Makku293]

How do you keep the selection arrow inside the cell, instead of out?. I don't like how it looks outside of it.

[@matthewtschetter1953]

Question: I will have hundreds of names within the list, is there a way to get a search bar to scroll through the list to find the one I want quickly? Thank you.

[@thomasreichhart5081]

Is this also possible vice versa? What I mean by that is if I add a new product into the inventory that is not in the products list (rngProducts), how can I make Excel automatically add this from my inventory to my products list?

[@souravbanik7398]

I love this video🎥

[@bobbimurray1999]

great video but i need to find out how to use define name when Name Manager isn't an option. i got as far as that and couldn't go any further.

[@talidami4268]

Hey Jon. Please tell me, can we use the Excel Tables in order to update data for Pivot Tables (I'm using now the OFFSET Formula)? Thank you for your time!

[@Rk-bh7ru]

How i can roll the drop down list with the help of mouse roller in excel. Please tell me.

[@kuruturuku9039]

how do i get the price to follow the description as well? like baseball is 3 dollars, how do i get the 3 dollars to appear as well when i select baseball?

[@mikeaikens7823]

Your videos are great! Thank you for what you do. Is it possible to add new products to the list on the "Products" tab by typing into the "Product" field on the "Inventory" tab? Thanks again!

[@reemadsouza3538]

Hey Jon, Excellent informative video tutorial, but when i deleted a row in between, when i came to drop-down lists there was a blank space, how can I get rid blank space - thank you

[@ChristineStryczynska]

Hi Jon, great video! Exactly what I was looking for except I can't seem to get it to work on mine. I have MS Excel 2016 so maybe something has changed? I can't seem to update the list. Managed to create the table and add the list but can't update it. Also, I have a question on drop lists. 5 years ago, I once did an Excel course and they had a drop-down list that showed previously typed names that appeared when you type the name. It wasn't a drop down list (I forgot the term for it). I wanted to make that list as I have a workbook that tracks the list of books I own. I love sorting by the author but due to a limitation in the formatting function, I use a max of 1000 rows per sheet. So I have several sheets all dedicated to different letters of the alphabet. I wanted to know of a way to make a list of the author names connect with all the sheets so that I can type the first few letters of the name and it will give me all the names that start with it. Your method works but I don't want to create a separate list, I want the list to update itself using all of the author columns data. Does that make sense?

[@David-oq9wn]

Is there a way to do this with excel online

[@satishtelang]

If aside product name any addition collum is there n it should reflect automatically then how to do

[@matthewwool7064]

This is exactly what I was looking for, except I can't get it to work. When go to define the name range, and I enter [ctrl]+[shft]+[down arrow] i get a static value at the bottom or list. This does not allow me to input new figures into my data validation. Any thought on what I'm doing wrong. Excel novice here.

[@vicvega8287]

How do I fix a filter that is not responding to the whole product? It is not reading the months past March.

[@evanlincy2634]

If we share the excel to another system , it wil work or not ?

[@suliman4life]

Can the newly added items alphabetically be arranged in the list automatically whenever I add them ?

[@ExcelCampus]

Hey Suliman, great question! That can be done with a macro. I'll create a video in the future with that solution. Thanks! :-)

[@Zomaar54]

Had the same question.😁

[@colinobri]

Had same question also!

[@alexj6529]

Great video! Can the source range be the same column as the data validation column? So theoretically all of column A in "inventory" sheet would have data validation, but if you typed out a new "product" rather than use the dropdown, it would add that new product to the dropdown?

[@midvok3918]

That's cool, but what if I rename an item in the range list? It seems the entered items in the data table won't update dynamically, which makes the whole table inconsistent:( So, is there a way to update the whole table after renaming items in the range list?

[@Anonymous-mz4fn]

Hi there can anyone help me... ? I have excel data and have applied filtered to it.. and i want to add information to each item i. the list of filtered data. Now As list is long and I am selecting each item one by one by scrolling down. Is there any way to skip to next item in the list instead of scrolling from mouse as my list is very big and i have to go very down to select next value

[@vijaysahal4556]

nice 👍👍👍👍👍

[@AhmedKaddouraSyria]

That's nice, thanks alot :)

[@davidwillis4545]

Jon, how can you make the drop down list always be alphabetized after adding new items to the drop down list. ?

[@billleonard1435]

It is just as quick to sort the table as step 2. I do this as a matter of course. A short VBA macro can be included, but there are just as many clicks to running a macro as there are to select and run the sort.

[@jeanyveslaval4293]

Thanks for useful video but if the list is more than one hundred then search as we type in google is more efficient if can provide next video on it

[@promoa1706]

Well done

[@juliet44144]

Can I use a Data validation list that will also populate a separate column, for example when I select the product name from the drop down I also want the cell beside it to populate with the associated product code number?

[@Matt.With.Huskies]

Lara Robins that’s usually done with a lookup formula such as Vlookup to a control table where it returns your product details.

[@nitinc2373]

Hello Jon, Greeting of the Day. My Name is Nitin,Thanks for your quick response.I am working as a Sales Manager,I have one problem regarding drop down list.Your video is very helpful for me.I create a Sales Invoice in Excel.I have 20 main Items.Each Item have a various different sizes and also different Rate. I could not manage dependent drop down list for my main 20 Items.Please help me out.Thanks.

[@victorzarate2507]

Nice tutorial, I will definitely be coming for more and message you for solutions if needed :)....Thanks for sharing!

[@anubhavsaxena11]

Hi Jon, can we do same automation in our excel pivot dashboards..??

[@anubhavsaxena11]

Hi Jon please reply ASAP

[@talidami4268]

I asked the same question. I'm using now the OFFSET formula. If you want, I can explain that one meanwhile.

[@kilua87xxx_kynangexecl73]

Thanks.

[@bridietimbers1747]

Thank you!

[@sahildarolya3189]

I want to make a Excel automated software can u plz help me out

[@ValmisFilm]

for me the problem is: if i have lets say 20 things, but 15 in the end are empty - then it still gives me them in the dropdown list and I have to scroll like mad to find things from the beginning of that list. How to make a dropdown, that only has NONE-EMPTY cells in the list?

[@karnabudhathoki5311]

Use offset function...in row height...use count function...That way you don't have empty..ones...that's because your named range isn't dynamic..Hope that helps.....

[@ChrisNewsome]

Hi Jon, Does this also work for the activex combo box drop down button? I built a file that utlizes that but I don't have a good way of updating a list of accounts if any changes are made. At the moment, I have a list array for the comboBox with static names written in VBA.

[@ExcelCampus]

Hey Chris, Great question! Starting in Excel 2013 you can't directly reference a named range to a Table in the ActiveX control. However, you can create a 2nd named range based on the first one, then use that in the ListFillRange property. Here is a link to an article by my friend Debra that explains how to do it. www.contextures.com/excelworksheetcomboboxes.html Scroll down to the section for Show a List in Excel 2013 I hope that helps.

[@sijidsaeed]

thanks from Pakistan

[@moesadr3342]

Awesome Jon, One question, what would you suggest if there is an id column associated with the drop down? i.e. Product_id which will be used in Power Query and Data Modeling Relations.

[@microsoft1665]

this also works without creating table. so whats the purpose of the table?

[@puneetvij8433]

Sir John, Thanks ! ; )

[@TechnologyRS]

Nice thanks

[@krishj8011]

thanks

[@md.saifulislamtuku9303]

Okay, thanks from Bangladesh

[@vijaypratap8895]

Sir good videos

[@andrewr.ludlow2722]

We see how to create a table but how do you delete one?

[@Alltalk2023]

How do i add colour automatic?

[@microsoft1665]

bro, show similar video but with colored cells. example, u chose something from the list, and u get colored cell. Conditional Formatting thing...

[@amanprincegupta]

how to merge two ranges in single dropdown list in excel

[@UjanShrestha]

How to set value for individual items.. like product-name = '8265'

[@CustomerService-gn8nr]

With All Ma Luve .... ♥