Excel Basic Data Validation & Drop Down Lists

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:

  1. Los Angeles city
  2. San Diego city
  3. San Jose city
  4. San Francisco city
  5. Fresno city
  6. Sacramento city
  7. Long Beach city
  8. Oakland city
  9. Bakersfield city
  10. 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:

Excel Basic Data Validation.1

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:

Excel Basic Data Validation.2

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:

Excel Basic Data Validation.3

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:

Excel Basic Data Validation.4

Now we can go back to the cell with the Data Validation and change it to point to this list of values:

Excel Basic Data Validation.5

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:

http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx

Another location with more specific examples for drop down lists:

http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx

 

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s