With certain forms or lists in Excel, it is helpful to have some control over what people can enter. For example, if you have a zip code column you could have Excel only accept entries that are at least 5 characters, but no more than 10. Or if you have a column for sales rep, you could make people choose from a predefined list of names, so later when you want to sort by sales rep, misspellings won’t cause it to sort incorrectly.
For the zip code example, lets say that the zip code is column E on your spreadsheet. First, you would select the whole column by clicking on the E column heading. Then go to the Data menu and choose Validation. (In Excel 2007, go to the Data tab of the ribbon and click the Data Validation button.) In the Allow drop down box, select “Text Length”. At Data, choose “between”. Enter 5 as the minimum and 10 as the maximum. If you want to, you can also enter an Input Message which is shown when a cell is selected, and/or an Error alert which is displayed when someone enters data that does not meet the settings you have chosen.
For the second example, you have to make the list of acceptable entries first. Since you don’t want it to be in the way, I would suggest putting it on a different tab. We’ll use column A of Sheet2 to hold a list of sales reps. Then, back on the main tab, select the sales rep column of the spreadsheet, and go to Data Validation. This time select List in the Allow drop down box. In Source, enter =Sheet2!$A:$A. Again, enter an input message or error alert if desired and click OK. Now when you click in the Sales Rep column, will see that you get a drop down list to choose from. If you type something else in manually, you will get an error and Excel will force you to change your entry.
