Software

Two built-in features for reducing data entry in Excel

Reduce data entry by training users to use Excel's AutoComplete and pick list features.
Excel comes with a variety of tools that reduce data entry tasks. Most of you probably know about the AutoComplete feature, but do your users? This feature starts working as soon as you enter data that closely matches a previous entry. In this case, previous entry means an existing value in a contiguous single-column range. In addition, closely means uniquely repeated characters. For example, if you enter the character H in cell A7 (see below), AutoComplete will do nothing, as there's more than one existing value that begins with H. If you enter HR6, AutoComplete kicks in and suggests HR6-12 because there's only one value that matches your input. Once AutoComplete makes a suggestion, you can press [Enter] to accept it or continue typing another value. (To disable AutoComplete, choose Options from the Tools menu, uncheck the Enable AutoComplete For Cell Values option, and click OK.) That feature's great, but there's more. When entering data in a contiguous single-column range, Excel can automatically create a pick list of unique values. All your users have to do is display the pick list by pressing [Alt]+Down Arrow or right-clicking the cell and choosing Pick From Drop-down List. Simply select a value from the resulting pick list. This feature is even more efficient than AutoComplete, as there's no need to type even a single character. Neither feature will keep a user from entering an invalid data, but reducing data input is the first step to reducing errors. In addition, your users will appreciate the respite! What built-in features do you teach your users in hopes of reducing data entry and thereby reducing errors?

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.

Editor's Picks