How to Create a Data Entry Form for an Excel Spreadsheet

Restricting the Type of Data Entered
1. Select the cell or range of cells in which you want to restrict data entry.
2. 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 dialog box will appear with three tabs: Input, Input Message and Error Message.
3. 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.
4. Select the logical operators (greater than, less than, in between) that suit the data entry form you're building.
5. 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.
6. 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 dialog box to finish.
Using a Drop-Down List on a Data Entry Form
7. Create a range of acceptable data values in another set of cells in Excel; for example, a list of branch locations.
8. Select the cell you want the drop-down list to be in.
9. 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 dialog box.
10. Select 'List' from the drop-down menu. The field for Source will become available.
11. Click on the 'Source' box. Then select the range of cells you created. Click the 'OK' button at the lower edge of the dialog box to finish.