Hi Excellers, welcome back to another #Excel tip. Today I want to show you how to set up a data drop-down list that is automatically updated when we add new items or options to the list. Dropdown or data validation lists are really useful when you want to control data entry into your Excel workbook. By giving the user limited options, you control the quality of the data that the user enters.
You can follow these simple steps to make your drop-down lists dynamic, so they update automatically when you add new items to our source data. Here is my example. I have a list of activities that a user can select as part of the data entry process.
Step 1. Create A Data Table
- Enter your options into Excel.
- Convert the list to a data table. Select your data then Hit Ctrl+T. Confirm if your data table has header and hit Ok.
Step 2. Create A Named Range.
- Formula Tab | Name Manager | New.
- Name your range of cells. In this example it is rngProducts.
- In the Refers to field select the range you want to include (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 type the named of the created Named Range. In this case rngProducts
- Tick Apply these changes to all other cells with the same settings
Now it is the time the test our solution. Let’s add another product into 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 it’s automatically updated.
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 first Wednesday of the month.