Hi Excellers, welcome back to another #Excel tip. Did you know that you can automatically add new items to a drop-down list in Excel? It’s a great way to keep your list of options up to date without updating it manually. How tedious! In this blog post, we’ll show you how it works. Stay tuned! So, today I want to show you how to set up a data drop-down list that automatically updates when adding new items or options to the list. Drop-down or data validation lists are handy for controlling data entry into your Excel workbook. By giving the Excel user limited options. Essentially, you control the quality of the data that the user enters.
You can follow these simple steps to make your drop-down list dynamic in Excel. Subsequently, when you add new items to the source data for the Excel list, the drop-down list updates automatically. Here is my example of working through today. First, I have a list of activities that a user can select as part of the data entry process. Next, I will step through all of the techniques to get your drop-down list in Excel to update automatically.
Step 1. Create A Data Table
- Enter your options into Excel that form part of the drop-down list.
- Convert the list to a data table. Select your data, then Hit Ctrl+T.
- Next, confirm your data as a table, and it has a header.
- Finally, hit Ok.
Step 2. Create A Named Range.
- Formula Tab | Name Manager | New.
- Name your range of cells. In this example, it is rngProducts.
- Select the range of cells you want to include in the ‘Refers To’ field (your data table).
- Hit Ok and close Name Manager.
Step 3. Set The Range As A Data Source For Validation
- Select the cells to apply Conditional Formatting to using Data Tab | Data Tools | Data Validation.
- Settings | Allow| list.
- In Source, go ahead and type the name of the created Named Range. In this case rngProducts
- Tick Apply these changes to all other cells with the same settings.
Now is the time to test our solution. Let’s add another product to our data table and see it appear as a new option in our data validation list. I will add Top Hat to the list.
It works!. How cool is that?. I can now add new items to my list, and what is even more remarkable, it updates automatically.
hope this blog post helped provide you with some useful information on automatically adding items to an Excel drop-down list. If you have any questions, please comment below or contact us directly in the comments below. Thank you for reading, and we look forward to hearing from you soon!
Finally, do you have you an Excel question?. So, why not leave it in the comments below.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the every month.