Data entry is one of the things for which Excel is widely used. Making a data entry form that prohibits inaccurate data entry from users is straightforward in Excel, through the use of data validation. Data validation can restrict the kind of data that goes into a specific cell, which is useful if you want to limit a cell text only or provide drop-down lists that are an exact match to a lookup table used elsewhere in Excel.
- Skill level:
- Moderately Easy
Other People Are Reading
Select the cell or range of cells in which you want to restrict data entry.
Go to the Data tab in Excel 2007 and select "Data Validation." (In Excel 2003 or earlier, click on the "Data" menu, and select "Validation"). A dialogue box will appear with three tabs: Input, Input Message and Error Message.
Select the Input tab if it isn't up already. Allowed Validation will show "Any Value." Clicking the drop-down list will show the options you can restrict the field to. The most useful kinds of restrictions include whole numbers (for quantities of goods sold or purchased), dates and times (when something was done) and text length (to make entering names easier). Select the input type that best suits your needs. The list type validation is covered in the next section.
Select the logical operators (greater than, less than, in between) that suit the data entry form you're building.
Click on the Input Message tab and enter the message you want displayed when the cell is selected. This can help your end users figure out the form more quickly.
Click in the Error Message tab and enter the message you want displayed when the data is entered incorrectly. Click the "OK" button on the lower-right edge of the dialogue box to finish.
Restricting the Type of Data Entered
Create a range of acceptable data values in another set of cells in Excel; for example, a list of branch locations.
Select the cell you want the drop-down list to be in.
Go to the Data tab in Excel 2007 and select "Data Validation." For earlier versions of Excel, click on the "Data" menu and select "Validation." This will bring up the Data Validation dialogue box.
Select "List" from the drop-down menu. The field for Source will become available.
Click on the "Source" box. Then select the range of cells you created. Click the "OK" button at the lower edge of the dialogue box to finish.
Using a Drop-Down List on a Data Entry Form
Tips and warnings
- You can copy and paste validation between cells by selecting the cell with the validation you want and pressing "Ctrl"+"C." Then select the range you want the validation on, right-click and select "Paste Special," "Validation."
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for