Excel – Using Data Validation Lists to Create Dropdown Lists within Cells
Data validation list in Excel cell
Using data validation is extremely simple and you don’t even have to use VBA to do it. I’m using Excel 2010, but the process is the same in 2007. I’ll show you how to set up the data validation and two ways to populate the list, dynamically and hard-coded.
With Hard-coded list values:
- Go to Data > Data Validation > Data Validation and the Data Validation dialog box should appear.
The Data Validation Icon
- In the “Allow:” list choose the “List” option. The Ignore Blanks check box allows the field to be empty if an option has not yet been chosen. Keep the “in-cell dropdown” option checked so that the dropdown list will appear within the cell.
- In the “Source:” field, enter in the list values, separated by commas.
Enter comma separated values to appear in the list
- If desired, you can customize a message displayed to the user when the cell is selected and/or customize the error message displayed to the user when a value other than those in the lits is entered in the other two tabs of the Data Validation dialog box.
- Click OK and you’re good to go! You can now copy this cell anywhere else and it will keep the same data validation rules.
With Dynamic values:
- Go to Data > Data Validation > Data Validation and the Data Validation dialog box should appear.
- In the “Allow:” list choose the “List” option. The Ignore Blanks check box allows the field to be empty if an option has not yet been chosen. Keep the “in-cell dropdown” option checked so that the dropdown list will appear within the cell.
- Instead of typing a hard-coded list in the “Source:” field, select on your worksheet the cells that you would like to use in the list. A trick I learned, is that any empty cells selected will not show up in the list. This is handy so that you can select empty cells and later add data to them so that the new data is added to the list (Dynamic!). Also, it is important to note that the references are absolute references with the dollar signs (like $E$2:$E$24) and the worksheet is specified (=Other! since the worksheet is named “Other”). That’s all there is to it!
Select the cells to populate the list
- If desired, you can customize a message displayed to the user when the cell is selected and/or customize the error message displayed to the user when a value other than those in the lits is entered in the other two tabs of the Data Validation dialog box.
- Click OK and you’re good to go! You can now copy this cell anywhere else and it will keep the same data validation rules.
Categories: Technology, VBA Excel Programming
