Sometimes you really want to control what people type into Excel, especially if you need to do some analysis on the data at a later date, so all the input are uniform and easy to analyse.
There are a few of ways to do this.
1- Provide a custom list – this is great if you have a long list of options for the user to select it is easier to maintain.
2- Type the list in the validation area source box.
Let’s go through the two options.
Firstly setting up a Custom List.
The list can be set up on the sheet that will contain the drop down lists, or on a different sheet. In the examples we will go work though we will store them on another worksheet called Custom Lists. Let’s get started….
1. Type the list of items you want in a single column or row- in my example its a column it doesn’t matter which you choose, but they have to be in a single block, ie A1,A2,A3 and not A2, A4, A6.
2. Next, name the List Range, do this by selecting/highlighting the cells in the list. Click on the Name box to the left of the formula bar and type a one word name for the list in my example I have called it Widgets. Then press the enter key which assigns the Name to your range of cells you highlighted.
3. Thirdly, apply your Data Validation. Select the cells you want to apply the data validation. From the Data menu select Validation. From the Allow drop down list select List.
4. In the Source box, this is where you reference the named range, so type an equal sign and then the list name, the click Ok.
5. Go ahead and try it, look great doesn’t it?.
Ok, so you might only have a few options that you want the user to select, like Yes, No or Maybe.
You can set up a Delimited List in this instance instead of referring to a list of all items on the worksheet.
1. Select the cells you want to apply the data validation. From the Data menu select Validation. From the Allow drop down list select List.
2. In source type your options, separated by a comma.
3. Give it a go.
This method of Data Validation is case sensitive, so if a user types MAYBE instead of Maybe then they will get an error.
If you want to allow users to enter alternative entries in the list then you need turn off the Defaulted Error Alert, but I find this defeats the object to trying to capture squeaky clean data!!
Other Data Validation Posts
1.Restrict The Number Of Characters A User Can Enter Into A Cell