Creating Validation Lists / Drop down lists

In this post I will take you through creating validation lists easily without too much effort and without VBA.
For those who are wondering what is VBA it is code that Excel or Microsoft Applications let you write so that you can automate your processes.

? When would you use a Validation list / drop down list?

- If you are creating a document that many users may need to add information to and you'd like to restrict the data they input into a certain cell
- If you're working on a range of cells where only a limited amount of data needs to be entered repetitively

A few examples are, if you need only Yes / No answers or if you need a customized list of items.

In a sheet enter a list of items that you would want to see on your validation list.

In the snapshot below I've used Items1 to 12 for my list. An easy way for you to create this list is to type Item1, Item2 and Item3 in 3 consecutive cells in you worksheet and select the 3 cells move your cursor to the lower edge of the 3 cells till you see a "+" sign and then simply drag it for the number of cells that you would need for your list.
Excel automatically increments the number for the number of cells that you've dragged.

Select the Cell on which you would like to apply this validation and then click on the Data tab > DataValidation and select DataValidation from the menu.

You will see a settings tab within which you can select List from within the Validation Criteria. 

In the Source you can enter a range of cells in this case it is for cells B7 to B18.

You have the option of adding an Input Message to inform the use what kind of data these cells will accept. In this case we've added a Title of "Select Items" and the Input Message of "Select Items from pre selected list.". You can add your own message title or message. This will show up when the cells with the validation list is selected (see Image above with the Input message).

In case the user enters an wrong value which your validation list will not accept you can add a Error Alert message in the Error Alert tab. You can change your Icon style in your error alert by choosing one in the style drop down. Excel give you a preview of what the Error Icon will look like. In this example below we've used a Stop which is a white cross enclosed in a red circle.

The error alert is displayed in the image below.

Finally the validation list is complete(see the Image below).

There are other options for a validation list other than picking it up from a range of cells as you can see in the image below.

You can even add a formula for this example below the list allows the user to enter any number from 1900 to 2000 and also lets the user add "NA" into the cell B36.

=OR(AND(B36>= 1900,B36<=2000),B36="NA")

Using this you can easily create a list which users cannot tamper with and will only accept values which you require.

Hope this makes Validation lists a bit more simpler. If you have any questions please leave me a comment and I will be happy to get back to you.