Create a dropdown list in Excel that will update automatically

If you need to do any sort of summaries or data analysis in Excel, one of the key pieces is having clean, consistent data. The first step in this is to create a dropdown list that users have to choose from. You do this by using data validation.

In this tutorial, using some data from my beloved Tour De France, I have used the Remove Duplicates feature to first of all create a list of unique countries, then formatted it as a table and then used this table as the basis of my dropdown. The beauty of using a table as your dropdown is if you add another entry, your dropdown list will update. You can use Data | Sort to have your list in alphabetical order.

But here are the steps:

1. Create your list(s) in a separate worksheet – this makes them much easier to manage.
2. In this exercise, I copied and pasted the existing list into a new sheet and did Remove Duplicates.
3. Name the range (essential if you are using Excel 2007 or earlier) that contains your list. Remember – no spaces in the name
4. Highlight the area in your spreadsheet where you want to add your list
5. Click on Data tab, then Data Validation, then Data Validation again.
6. From the dropdown list, under Allow, choose List
7. Click in the Source box, click F3 (this calls up the list of range names) and then click on the range name that you created for your list
8. You can also amend your input messages at this point as well. Note that when you use a list, the message displayed when someone clicks on the list is what you have typed into the Input Message box in the Input Message tab.
9. Hey presto, your list is ready. Users can only input what is on the list.

Automating your dropdown list even more – using Dynamic Array formulas.

When I completed this tutorial, I found myself wondering what it would be like if I used some of the new dynamic array formulas with this e.g. Unique and Sort to really automate this. So here goes. I used a different file for this one…

1. Click in another sheet
2. Click in Formulas | Lookup and Reference and choose Unique
3. Click on the range starting in F2 and press Ctrl and Shift and Arrow down to move to the end of the list and then again as data validation doesn’t expand with a table
4. Add the Sort function on it to ensure all entries are in alphabetical order
5. Note that you can only edit this dynamic array formula in the first cell – not in any other ones.
6. Click in another part of the spreadsheet and type in = and click on the first entry in this formula
7. Copy down and convert this list to a table by clicking on Insert | Table
8. Make sure you have the entire table selected and click in the Name Box to give it a name e.g. StoreLocation (note no spaces in range names)
9. Return to the Dynamic Dropdown list and click in F2
10. Press Ctrl and Shift and Arrow down to move to the end of the list and then again as data validation doesn’t expand with a table
11. Click on Data | Data Validation | Data Validation
12. From the dropdown choose List.
13. Click in the Source box and press F3 to call up the range name i.e. StoreLocation
14. Note that your list now reflects the table
15. Change one of the entries in this list in the Dynamic Dropdown list table e.g. F6 . In the video I changed it to Galway
16. Note how the entries in the dropdown list change to reflect that