At work earlier this week, I was putting together an Excel spreadsheet and wanted to include a dropdown of values in some of the columns. I knew this could be achieved using Data Validation, but I wanted something that was going to be easy to maintain as the number of entries grew and as the list of values evolved.
A combination of Data Validation and Name Ranges seemed like the best way to go and I decided to throw together a guide.
1) Table structure
The first thing to do is put together the structure of your table. In this example there is a table for storing addresses. We will be adding a drop down to the State field.
Next, you need to create somewhere to store the values to include in the dropdown. In my workbook I created a separate sheet for this so that it doesn’t get in the way of the main table.
2) Create a Named Range for the dropdown values
The next step is to name the range of cells where the dropdown values are stored. First select the cells, then enter the name for the region in the top left corner.
3) Set up data validation
Now, return to the sheet where you want to include the dropdown. Click on the first cell to include validation, and from the Data tab click Data Validation.
In the Allow field select List. Then, in the Source field enter an equals sign, followed by the name of your cell range – for example =STATE_VALUES
Data validation is now set up on the cell you selected.
You can extend the validation to further cells by click and dragging the handle on the cell.
4) Updating the values in the drop down
Modifying the values in your dropdown is as simple as changing the values in the named range. To extend the list, you will need to update your named range.
You may have noticed the list of Australian states in this example did not include the territories. We’ll add these to the list now.
First, go to your list of dropdown values and type in the additional values. Next, from the Formulas tab click on Name Manager.
In the Refers to field, update the range. You can do this by typing in a new range, or using the Select range button to the right of the Refers to field. Once complete, click Close then click Yes if prompted to save changes.
Returning to the cells with validation, the new values should now appear in the dropdown.
If you have any questions, feel free to post in the comments section.
