Software

Five tips for avoiding data entry errors in Excel

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 values

Using 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).

Figure A

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 list

Validating 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.

Figure B

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 AutoComplete

Excel'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.

Figure C

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.

Figure D

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.

Figure E

The list in C1 updates automatically after you convert the input range into a table.

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.

7 comments
xngelo
xngelo

Thanks for sharing these tips. I have been going bonkers searching for a keyboard shortcut for muting my microphone. It's a big plus if you are multitasking while on a PC call.

sparent
sparent

Does anyone know of a way to have cells with a selected list value changed automatically when you change the value in the list range? In other words, if I have already picked "Maybe" from the drop-down list for a bunch of cells and then I go change "Maybe" to "Unknown" in the drop-down list range, can I have the "Maybe" cells change automatically to "Unknown?"

dogknees
dogknees

That you can put your lookup lists on another sheet as long as you name the range and refer to the name in the Data Validation dialog.

ssharkins
ssharkins

I asked around. From John Bartow: Get a Logitech keyboard, which has a toggle key for this. From Stuart McLachlan: Download nircmd (it's only about 100KB) from http://www.nirsoft.net/utils/nircmd.html and extract the .exe to somewhere convenient. Create a one line batch/cmd file containing: nircmd.exe mutesysvolume 2 microphone Create a shortcut to the file Assign a Hotkey to the shrotcut.. From Steve Erbach: Download AutoHotkey (http://www.autohotkey.com/download/) and create a keyboard macro to open the volume control, tab to the microphone volume mute checkbox, and check/uncheck it. Then you could assign the macro to, say, the Window-M key, M for mute. Hope you find something useful here.

bazstad
bazstad

Could you not just use the find and replace feature to change all of your Maybes to Unknown?

sparent
sparent

That is certainly an approach I have used. It's a bit blunt for the task at hand. You need to make sure that the F&R is does exactly what you need and no more. I'm sure everyone has horror stories of F&R gone overboard...

Editor's Picks