Data Validation in Excel is very useful tool. You can setup simple value format validation so only numbers or only valid dates can be entered in a given cell or you can provide list of static values that can be used in a cell or you can even use a Formula that can implement almost any arbitrary logic to validate that a given value is allowed for a given cell. The “List Data Validation” is one of the most useful options in this category. It will both check that the value in a cell is one of the values in the list of allowed values and will change the user interaction with the cell to use a drop down list of possible valid values.
Let see the following example: The goal is to add a data valuation that will support drop down for the top 10 cities in terms of population in California.
The list of possible values is as follows:
- Los Angeles city
- San Diego city
- San Jose city
- San Francisco city
- Fresno city
- Sacramento city
- Long Beach city
- Oakland city
- Bakersfield city
- Anaheim city
To achieve this goal, first select the Data menu, then the Data Validation icon and then Data Validation sub menu. Like in the following screen:
In the dialog screen that will be shown, In the “Allow” field select the List option, and in the “Source” field just type the list of static values with comma separating between values. Like in the following screenshot:
Finally, the end result should be a cell that has a dropdown list of the top 10 biggest cities in California, just like in the screen below:
If you want the list of values to be just a little bit more dynamic, you can make the list to use a list of values in one of the other cells in the worksheet. Let’s do that. We start by creating a small table of the possible values, like this:
Now we can go back to the cell with the Data Validation and change it to point to this list of values:
Notice that the “Source” field is more dynamic now. It is a range of values in the excel worksheet. In this case the range is static. But it can also be more dynamic…
That dynamic option is exactly what is needed to achieve our next goal.
The use case is called “Depended Value”. Consider the case of two cells that has dependency between them, such as in the case of State/City. In that case, when a State is selected (using a static list) we would like to change the available options on the City column to represent only the list of Cities in that State. More on this one on the next entry.
For more information on the Data Validation in Excel follow the next list:
Another location with more specific examples for drop down lists: