Software

Use Excel's built-in features to simplify data entry

Entering data into a worksheet can be time-consuming, and mistakes often find their way in -- but with the right tools, both speed and accuracy can be improved. Here's a look at several Excel features that facilitate data entry.

Chances are, many of your users spend at least some of their time entering data into Excel worksheets. The easier you make the task, the more users will enter accurate data. You can create user forms and write VBA to help the process along. But before you do, review a few of the built-in features that ease the data entry burden. Excel's list, AutoComplete, and data validation features will reduce keystrokes and prevent errors.

Note: This article is also available as a PDF download.

Use lists to reduce keystrokes

One of the simplest ways to control data entry is to let Excel enter as much of the data as possible using the list feature. Lists reduce keystrokes and typos. Creating a list is simple enough, and you can work with an existing worksheet or create a new one. The only requirement is that each list (column) heading be unique. If there are no headers, Excel will create generic ones.

To demonstrate the process, we've imported the sample Access database, Northwind. Here are the steps for creating a list:

  1. Select any cell inside the worksheet for which you want to define a list.
  2. Choose List from the Data menu and then select Create List. Or press [Ctrl]+L. Excel will display the Create List dialog box and display the range for the worksheet, as shown in Figure A. (If the range isn't correct, check for a blank row in the worksheet. Excel's list feature can accommodate blank cells, but Excel interprets a blank row as the end of the active data.) If necessary, select the My List Has Headers option. (Usually, it's selected by default.)

Figure A

Excel intuitively selects the list range, which usually consists of multiple columns.

  1. Click OK, and Excel will create the list shown in Figure B by adding drop-down controls to each header cell. (The arrows are an AutoFilter feature.)

Figure B

The lists drop-down arrows let you quickly filter records by existing values.
To use the list to enter data, navigate to the bottom of the worksheet, where an asterisk character (*) serves as a placeholder for the new record row. That's where you'll enter the next record. As you enter data, Excel searches the existing list (column) items, looking for an item that uniquely matches the characters you enter. When it finds a match, the AutoComplete feature finishes the entry. For instance, Figure C shows what happens when you enter the characters Mo into the ProductName cell in the new record row. Excel completes the entry -- Mozzarella di Giovanni. To accept the completed item, press Enter; to reject it, just keep typing.

Figure C

Excel's AutoComplete feature can enter data for you.

When you reach the end of the new record, Excel is smart enough to know you've completed the record. When you press Enter, Excel selects the first cell in the newly inserted record row -- this is one of the few times Excel ignores the cursor movement setting (see the next section). If AutoComplete doesn't work, someone has probably disabled it. To check, choose Options from the Tools menu and click the Edit tab. The Enable AutoComplete For Cell Values option must be checked for AutoComplete to work.

Control cursor movement

When inserting data, pressing Enter moves the cursor down one cell by default. When entering new records, that behavior can be counterproductive. Most likely, users will want to complete each record by moving to the right. Fortunately, you can change the cursor's movement pattern. First, choose Options from the Tools menu. Then, click the Edit tab and do one of these things:

  • Choose Move Selection After Enter and then select a direction.
  • Clear the Move Selection After Enter check box to inhibit any movement when pressing Enter.

Restrict users to list items

Providing efficient data entry methods is great, but validating data is also important. You can control both by restricting data entry to a specific list, thereby limiting possible entries. First, you need a list like the one shown in Figure D. This list identifies all the possible categories for the worksheet -- each record will have a category value and it will be restricted to the items in this list. In other words, users will be allowed to enter only those items in this list.

Figure D

Enter only the items you want the list to store.

Be sure to create this list in an out-of-the-way spot. If you delete it or accidentally write over it, the restricted list will stop working. However, the list items must reside in the same sheet as the restricted list or you must assign a range name to the list items.

With the list in place (see steps 1 and 2 in the section "Use lists to reduce keystrokes"), select the list you want to restrict. In this case, that's column D, or cells D2:D80. With the list selected, you're ready to enable data validation as follows:

  1. Choose Validation from the Data menu to display the Data Validation dialog box.
  2. Click the Settings tab.
  3. Choose List from the Allow drop-down list.
  4. Use the point-and-click tool (to the right of the Source control) to select the original list. In this case, that's cells M2:M9, as shown in Figure E. Or enter a range name, including an equals sign. Click OK to return to the worksheet.

Figure E

Identify the list of items that determine the items the list will store.
Now, go to the new record row and click the category cell (column D). As you can see in Figure F, the list (which you can see in Figure D) is available for data entry. (If you imported the example data from Northwind, as we did, the original numeric values will generate an error once you enable data validation. Simply delete the imported values.)

Figure F

Use the drop-down list to enter a category for each record.
Not only is the list available to facilitate data entry, the feature rejects any entry that's not in the list. Excel displays the error shown in Figure G if you try to enter invalid data (an item not in the list).

Figure G

Excel rejects invalid entries.

This feature doesn't force users to use the drop-down list; users can manually enter an existing item via the keyboard if they prefer. If you want the drop-down list in a particular order, sort the original list (Figure D). You can do so before or after enabling validation.

Create a data entry form

Lists are great for reducing the amount of data users have to re-enter. But to enter different items, users might benefit from a data entry form. Start with a list. (Again, just repeat steps 1 and 2 from the "Use lists to reduce keystrokes" section.) Then, choose Form from the Data menu. Excel will construct a data entry form, like the one shown in Figure H.

Figure H

Many users find a data entry form easier to use than entering data directly into a worksheet.

Initially, the form displays the first record in the list. Click New to display a blank form, enter data, and press Enter. Excel will transfer the data from the form to the list (worksheet) and expand the list by one record. You can use a data entry form to enter new records, edit existing data, and delete existing records.

Unfortunately, Excel's automated data entry forms don't support AutoComplete the way lists do. However, your data entry form will inherit data validation settings. In the case of the example list, the data entry form's CategoryID field will reject invalid data, just as the worksheet does.

Easy data entry

Users who spend a lot of time entering new data will appreciate your efforts to increase their productivity and accuracy. Combine Excel's list, AutoComplete, and data validation settings to reduce keystrokes and restrict entries to protect the validity of your data. Or use a quick data entry form to automate a data entry task.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

23 comments
ge123
ge123

Hope someone can help!

I want to know if it is possible to generate a drop-down list in a data entry form. It is seems pointless to use a form for data entry if you then have to go back to the spreadsheet to use a drop-down list on two or three cells.

Can this be done?

dhays
dhays

Simply tab to the next cell to the right

graham
graham

Some good stuff. The biggest hassle with Excel (2002 anyway) is that it wont Autocomplete from a validation list - unless anyone knows how to make it do that.

robert
robert

Not applicable for Excel 2008 for Mac.

Navyman
Navyman

I guess I did things the hard way. I handle incomming UPS shipments to my company. What I did was set up a Excel Workbook with three worksheets. One was my input worksheet, #2 is my Facility worksheet, #3 is priority worksheet. I used VLookup to get the information and enter it into the cell. Example 1ZDWB0000152154565 Vlookup looks at the 1ZDWB000 and enters the facility in one cell then looks at the 01 after the 1ZDWB000 and enters that in another. No typing at all.

don.macrae
don.macrae

Good article. Many end users choose Excel for an entry-level, single table database miss the fact that data validation techniques really pay off, epecially for classification data and numeric or date data. One technique that should be added, accessible under Data / Validation, is a simple check that the value entered is a number (integer or decomal), with an numeric range check. Database purists may scoff at some of the limitations, but the familiar 80-20 rule applies ... The 20% of the data validation features in Excel, if used for Excel-based data collection worksheets, usaully delevoped by end users, could catch 80% of the problems at source and be corrected.

simon.freeman
simon.freeman

Using Excel 2000, and it doesn't seem to have this Create List function, at least not from Data menu or [Ctrl]-L key stroke.

jerang@
jerang@ Staff

Huh? This is interesting!

michelsenecal
michelsenecal

List is OK when you have less then 8 items but you can not change the font or size of the displayed text. To have a usable list you need to create a combo box that will replace the list popup when you click on a cell. VBA required. It would have been simpler if Excel had supported font size changes and a decent list length. No more than 15-20 lines as more is counter-productive. In a few cases a dependent list can help, but not if your choices are all at the same logical level.

juliang
juliang

Is is possibel to use an ACCESS form in EXCEL?

ssharkins
ssharkins

I don't know when MS added the List feature to Excel, but it's fairly new -- maybe 2002?

don.macrae
don.macrae

1. Excel 2000 (Office 2000) does lack many of the new features, and current users should consider a decision to move on in the near future. 2. While the Create List feature is not supported, the Data/Validation feature can reference a list of valid data entry values. This will help you buy some time and get some benefits of improved data validation until to choose to upgrade.

byu1980
byu1980

The version of Excel in Office XP (2002) also doesn't have the Create List function under the Data menu.

elongp
elongp

We're still on Excel XP. I was making a Intranet tip out of this, and was stumped at the start. There is no List command on the Data menu. Since many of your tips work in several versions, I didn't test this out before spending the time to create the tip. So... am I missing something easy? Thanks, E Powell Austin, TX

ssharkins
ssharkins

No, you can't use an Access form, but you do have access to what's called a "user form." They're very functional and flexible, but do require a bit of specializaed knowledge to implement, but easy to learn.

ebsfrmr
ebsfrmr

I have had trouble in the past using the Data Form feature, sometimes the data I enter into the form does not appear in the spreadsheet once I close it out. Not sure what I am doing wrong.

Andy P Roberts
Andy P Roberts

The LIst feature isn't in 2003 but the Data/Validation feature with reference to list option looks useful

ssharkins
ssharkins

The List feature was added to Excel 2003. Sorry!

ssharkins
ssharkins

Can you give me a specific example so I can reproduce it and study it?

ssharkins
ssharkins

I would, if I had them installed, but right now, I have Office 2003 and Office 2007 installed. Sorry I can't help on the older versions -- the good news is that other readers usually alert us, and I appreciate that.

elongp
elongp

I enjoy the tips and rely on the versions to see if it applies to what we use (Office XP/2002). It would be great if you could make sure to note what works in what older versions, like you do for Office 2007. Thanks, E Powell

ebsfrmr
ebsfrmr

Sorry for the delay in responding to your request for an example. It is quite frustrating to have to report I am not able to get one for you. Especially, since I have had this problem several times, in the past...what can I say. If it does occur again, I will certainly let you know. Thanks for your willingness to research this for me.