You can prevent self-inflicted data entry mistakes — and all the problems they create — with the help of a few easy-to-use Excel features.
You can spend a lot of time entering formulas and formatting ranges, but all your hard work is wasted if someone enters an inappropriate value. Usually, you can fix these types of errors — if you catch them. But the most practical thing you can do is to avoid them altogether. The following tips will help you eliminate data entry errors and protect the validity of your data.
1: Validate input valuesUsing Excel's Data Validation feature, you can eliminate inappropriate data. You specify the conditions a value must meet, and Excel rejects values that don't meet those conditions. Figure A shows a simple example of limiting input values to only decimal values between .01 and .99. To access this feature, click the Data tab and then click the Data Validation drop-down in the Data Tools group. The Allow option offers several validation options (which are self-explanatory).
Validate input values to limit errors.
You can fine-tune the process by displaying a helpful message to explain why Excel rejected the input value. This feature is indispensable when distributing a sheet to others.
2: Provide a listValidating data input values is one way to limit data entry mistakes, but it isn't foolproof. For instance, you can assign a rule that limits entries to a decimal value, but the user can still enter the wrong decimal value. When input values belong to a known set, create a list similar to the one in Figure B.
(Click the Data tab, click the Data Validation drop-down in the Data Tools group, and choose List from the Allow drop-down.) Users will select an item from the list, further narrowing their opportunities to introduce errors into the sheet. You can use a list to enter data into a single cell or a range.
The list in B1 is based on the labels in A6:A9; if you change a value at the sheet level, Excel will also update the list.
3: Use AutoCompleteExcel's AutoComplete feature matches previous entries to the current input. As soon as the input value matches an existing value (in a single-column contiguous range), Excel attempts to complete the current entry. Figure C shows an example. In this case, you must enter HR6 to match an existing value because there are two HR values. If the suggested value is the right one, press [Enter] to accept it. If it isn't, continue typing. AutoComplete reduces data entry and errors.
Excel's AutoComplete feature tries to complete input values.
You can disable this feature if you find it annoying. Click the File tab (or Office button) and choose Options. Click Advanced in the left pane, uncheck the Enable AutoComplete For Cell Values option in the Editing options, and click OK. In Excel 2003, choose Options from the Tools menu.
4: Use an on-the-fly list
AutoComplete is helpful, but you have to enter a few values to engage it. You can avoid data entry completely by making use of on-the-fly pick list — a combination of AutoComplete and a data validation list. The tool is practically effortless to use.When entering data in a contiguous, single-column range, press [Alt]+Down Arrow. Excel will display a pick list of unique values, based on the values in the previous rows, as you can see in Figure D. (You can also right-click and choose Pick From Drop-down List.) Choose a value and continue on your way. This route reduces data entry errors because there's no input value.
Note that the pick list is a text feature. Excel won't generate a pick drop-down list for a column of numeric values.
This quick pick list sidesteps data entry completely, unless you need to enter a new value.
5: Use a dynamic list
Adding or deleting values won't update the items in a validation list (#2). In Excel 2007 and 2010, convert the list item range into a table. To do so, select the single-column range, click the Insert tab, and click Table in the Tables group. Any validation list based on the data input range will now update as you update the input range. For instance, the list in C1 updates automatically as you enter new (and delete existing) values into the table at A1:A8.