Excel Tips - Best Way to Create Dropdowns that Dynamically Update

This video will show you an easy way to create dropdown lists in Excel that can update dynamically when new fields are added.

[@peterwaugh9416]

I was using Excel back in the late 80's & early 90's. I really wish KZbin was around then. I used to read through the manual, yes printed book, from back to front trying to work out how to get more out of it. I still use Excel to this day but for far less complicated tasks. Still find your content interesting.

[@Angel_Ripoff]

Love the simplicity and how clearly you describe & demonstrate the process, thanks so much!

[@vividtang7999]

Clearly describe easy to understand. Thanks.

[@CareerSolutionsforToday]

Thank you =)

[@acreguy3156]

Amazing!!!!! I never knew you could do this. I've had issues with manually adding new entries to drop down lists using Data Validation. I'm good to go now. That's a big "Thumbs up" and "Subscribed." Thank you!!

[@dtikvxcdgjbv7975]

Nice, easy to follow.

[@CareerSolutionsforToday]

Really appreciate it =)

[@bingoteeth2047]

I was expecting Deferred to be 4th in the dropdown. I take it that the sort needs to be done again?

[@CareerSolutionsforToday]

You could add the Sort function to auto update the list but this is only available in later excel versions. Otherwise just click sort again and it will alphabetize. Thanks for the comment have a great day!

[@mgangat]

Great tip

[@CareerSolutionsforToday]

Thank you!

[@neomadrasi]

When you add a new value to the table, will you need to alphabetically sorry it again? Or, will excel do the sorting intuitively? I'm Libre Office calc, you get a check box in the data validation tab to automatically display the dropdown value alphabetically

[@CareerSolutionsforToday]

You can use the SORT formula to auto-sort if you have a newer version of Excel or set up sort rules on the sheet. You can also just click sort again if you add a new value =)

[@mmaurice2120]

Great! Thank you

[@shailendrachaudhary2131]

could you also include the data file for practice it would be huge help

[@CareerSolutionsforToday]

Hey there, appreciate the comment. I am looking for ways to include practice files in the future. Thank you for the feedback.

[@arindambhattacharya7848]

Well I was expecting much more..this is quite simple

[@pattamaljayaram5501]

How to activate this in all cells of column B??

[@CareerSolutionsforToday]

let me know which version you are using for Excel as I may have a better option =) You can also simply make all of column B a table to solve for your question.

[@pattamaljayaram5501]

@@CareerSolutionsforToday thanks. Why clip board is not visible in Excel

[@seports8379]

Is there a way of increasing the font size of the dropdowns?

[@CareerSolutionsforToday]

Thank you for the question. In my research I don't believe so, or at least not without using VBA. Here is an article on the Microsoft website with some information: techcommunity.microsoft.com/t5/excel/font-size-in-a-drop-down-list/m-p/1472046 You can always zoom in to the Excel table to see the data more closely as well. I hope this helps and thanks for watching.

[@JanSnoek]

great tip. can u make a tutorial for multiple dependant dropdown lists?

[@CareerSolutionsforToday]

@@JanSnoek thanks for the question. I do have a video on that topic almost ready. I will let you know when it is uploaded.

[@CareerSolutionsforToday]

@@JanSnoek Hey there, let me know if this video helps. Any feedback is appreciated. Video link: kzbin.info/www/bejne/bnemgWuCrcyJpZI

[@acreguy3156]

Good question. Also the scroll bar in the drop down list doesn't work with the mouse roller. At least, not with my Logitech mouse. Anyway to fix that?

[@MrAnderson610]

😮😮😮😮

[@davidgruty]

what if you delete an item of the list? We get a blank. Do we have to resize the Table Format?

[@CareerSolutionsforToday]

You should be able to delete the extra row. Is that an option in your situation? There is a more advanced way to have it auto update but requires more work.

[@davidgruty]

@@CareerSolutionsforToday yes, i deleted it and resized the table (with the little blue dot on the bottom right corner). But the excel is for a friend, so she won't be able to do this "little tricks". How is the advanced way? is it very complicated?

[@CareerSolutionsforToday]

@@davidgruty which version of Excel are you using?

[@davidgruty]

365​@@CareerSolutionsforToday

[@CareerSolutionsforToday]

@@davidgruty Try this: Assume column A contains your dropdown list. Then in Column B enter the function: =FILTER(A1:A10,A1:A10"") [Note: You can make the range as big as you like] Your list should now appear and be filtering out blanks. Now in the cell where you want the dropdown go to Data Validation and click the cell with the formula in column B. This should be the first line of your updated dropdown list and should appear as =$B$1. Add a "#" after this so it should appear as =$B$1# Then test the dropdown and let me know =)

[@bsa18599]

With Microsoft 365 this tip is outdated, MS 365 allows searchable dropdown lists, to include more entries simply extend the range of the validation list to encompass blank cells, the blanks won't be shown in the DD list, but when filled in they will be included.

[@gausselim1474]

Your drop-down list didn't auto alphabetize.

[@CareerSolutionsforToday]

You can click sort again to alphabetize it or you can use the sort function to have it done automatically =) The key is that the dropdown list with auto update when new items are added. I hope this helps. Have a great week ahead =)