Did you know that Excel thinks it knows what you want to type?
If you have ever typed text into cells in a column, then you may have come across Excel’s version of “predictive text”. Try it for yourself; type your name in an empty cell on a new sheet. Now in the cell below start typing your name again. Excel helpfully tries to complete the text by matching what you have typed in the rows above and suggesting the word or phrase. A couple of simple rules to follow:
1) Place your input cell directly under the text rows.
2) Excel will not try to auto-complete until it finds a match. Hence if “David” was in the example below, “De” would need to be typed to get a match.
This function can be especially useful if you are typing in data from a hand written list with similar but random items. Remembering point 2 above try to set the item name as a differing first and, if necessary second letters. For example if your list contained “Electric Fan”, “Electric Heater”, and “Electric Desk Lamp” it would be necessary to type in “Electric “ before any auto-complete kicks in. Better to rename the items as “Fan”, “Heater” , and “Desk Lamp”. Type “Desk Lamp” in your list and the next time you need to type it in just type “D” and enter. This function does not distinguish between upper and lower case, so “d” would work in the above example. Also works with hidden cells.
Recently had to create a list of 300 items from a hand written sheet with around 20 variations. I adjusted all the variation names to begin with unique characters, placed the 20 items in the cells above the main list and hid the rows. By typing “a” for Apples, “b” for Bananas, etc my list was auto -completed very quickly and consistent in spelling and appearance.
Another way to quickly enter text into a cell is on the Right Click menu.
From this menu choose “Pick From Drop Down List” and click on the required item. This type of drop down list is automatically created from what has already been entered, so no setting up required. See example above.
Like all of Excel’s automatic functions sometimes they can be really annoying! So as with most you have an option to turn them off. Uncheck the “Enable auto-complete for cell values” box found in Excel Options, Advanced, in both Excel 2010 and 2007 to lose this function.
EOXL Quick Tip: If you consistently mis-spell a word checkout the AutoCorrect function in Excel Options, Proofing. Auto Correct Options. Add your version of the mis-spelt word and it’s correction to the list already there. Also available in the other Office Programs.
Peice of cake really.