Microsoft Excel - Speeding Data Entry With AutoComplete (Excel 97/2000/2001/2002)

You hardly have to do any data entry before you stumble across Excel's AutoComplete feature. As you enter text into a cell, Excel checks the other adjacent cells that precede it in the column to see if any existing entries match what you're entering. As soon as Excel detects a possible match, it fills the cell with the anticipated word. You can then exit the cell to accept the suggestion keep typing to remove the suggested word and finish your entry.

For example, open a new workbook and enter United States in cell A1.

  • Then, enter U in cell A2. As soon as you press U, Excel displays United States in the cell. Now, press the [Down Arrow] key to select cell A3 and enter United Kingdom.
  • Until you press the K key, Excel assumes that United States is the entry you want to make. As soon as you press K, Excel clears the suggested entry and you can finish typing kingdom.
  • Finally, select cell A4 and enter United States again. Excel can't differentiate between the prior entries until you press the S key, at which point it makes an assumption about your current entry.

In a case like this, waiting to get to the character that differentiates similar entries can be tiresome.

  • Fortunately, you can speed the process. To do so, press [Alt] and the [Down Arrow] key whey you begin your new entry.
  • For example, select A5 and press [Alt][Down Arrow]. Excel displays a dropdown list of the column's previous entries.
  • Use your mouse or keyboard's directional arrows to select an item and press [Enter] to insert it into the cell.
  • You can also display the item list by right-clicking on a cell ([control]-clicking on the Mac) and choosing Pick From List.

When working with a long list of column entries, enter the first few letters of the word you're looking for prior to displaying the dropdown list so you don't have to scroll through a lot of entries.

  • To demonstrate, enter Canada, France, Uganda, Ukraine, and Zambia beneath the entries in the current column.
  • Now, let's say that you want to enter United States again. In the column's next available cell enter Un and press [Alt][Down Arrow].
  • The dropdown list opens and the first entry matching the criteria, United Kingdom, is selected. Although you need to manually select the United States item, this technique is much faster than scrolling through the entire list or typing the name until Excel recognizes it.

Go back