Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Understanding AutoComplete

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Understanding AutoComplete.

Excel includes a handy time-saving feature called AutoComplete. This feature can save you time when you are entering lots of similar information in a column. You may already have noticed this feature before—when you start to type something in a cell, Excel tries to guess what you are typing and shows a "match" that you can accept simply by pressing Enter.

The "matches" that Excel uses in its "guess" is nothing but the contents of the cells in the column, above where you are making your entry. For instance, if you have information in cells A1 through A6 and you are entering a value in cell A7, Excel looks at what you are typing. If the first few characters uniquely match something in any of the six cells previously entered in the column, then Excel offers to AutoComplete A7 with the contents of the cell that matched.

Excel only tries to match your new entry with immediately adjacent cells above the one in which you are entering the information. It stops trying to match entries when a blank cell is reached. For instance, suppose you have information in cells A1 through A14 and A16 through A23. When you start typing an entry in cell A24, Excel only tries to match it with values in A16 through A23; the blank cell at A15 halts the comparisons.

In addition, Excel does not try to match with cells that contain only numbers, dates, or times. The cells must contain either text or a combination of text and numbers.

For some people, AutoComplete can be annoying rather than time-saving. If you want to turn off the AutoComplete feature, follow these steps:

  1. Choose Options from the Tools menu. Excel displays the Options dialog box.
  2. Make sure the Edit tab is displayed. (See Figure 1.)
  3. Figure 1. The Edit tab of the Options dialog box.

  4. Clear the check box named Enable AutoComplete for Cell Values.
  5. Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2110) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Understanding AutoComplete.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

shivam singhal    22 Sep 2016, 00:27
Hi,

I hv not find out these tools {option}
pl let me know.
Louis Farrell    12 Apr 2016, 09:36
@Pete
Press Alt and Down Arrow to get a drop-down list of all AutoCorrect entries
Barry    04 Apr 2016, 05:39
@Pete

The way the "Autocomplete" function is implemented in Excel (rightly or wrongly) it will only suggest a replacement when it gets a unique match with an existing entry in the range of cells it is matching.

So if you have some cells with "Publix" only in them, the one cell with "Publix meat refund" will result in two possible matches after entering "Pub" i.e. "Publix" only and "Publix meat refund"; Excel will only suggest the autocomplete once you type the first space after "Publix" which then gives it a unique match with "Publix meat refund". This defeats the purpose for you.

You could change the "Publix meat refund" entry to include say, a leading space, or some other character, but this may have other consequences e.g. when sorting. The other (and better) solution would be to use the auto correct so that, let's say, when you enter "Px " (note the trailing space) the autocorrect is set to change this to "Publix"
Pete    03 Apr 2016, 17:16
I'm curious why Excel will not autocomplete certain words. I use the word "Publix" often and it stubbornly refuses to autocomplete it. It is the only word in the spreadsheet that begins with "Pub". There is one line that has "Publix meat refund" - maybe that is confusing it?
Avinash    10 Jan 2016, 14:14
can Autocomplete option in excel sheet applied entire sheet, if I have long data in colomn A and I want to use anywhere on the sheet ?
Gary Kitchen    15 Mar 2015, 23:24
I use the Auto-Complete feature a lot. However, I haven't found a way to make it work across multiple Sheet tabs yet. Correct me if I'm wrong, by Sheet tabs I'm referring to the bottom left of an Excel 2000 document.

I have a 12 Sheet tabbed spreadsheet, each Sheet tab being used as a monthly checkbook register for a different month of the year. For each entry row I enter a descriptive category name for type of purchase, vendor etc. I want to keep these categories the same across the different Sheets for each month. Is there a way to make Excel 2000 "remember" what was on the previous Sheet pages?
Nick the Greek    13 Jan 2015, 02:51
A simplistic way around the empty cell problem is what I use: match the cell colour to the text colour and type a letter
Gerry B    05 Aug 2014, 14:35
I have noticed that Auto Complete seems to become ineffective as a worksheet grows. I have a worksheet that contains hundreds of company names, I don't want to enter the same company twice so Auto Complete was a way to warn me of a duplicate. Is there a limit to how far back the search for an auto complete match works, it feels like it is becoming hit and miss. I frequently sort the list to make sure it contains no blank rows and is in alphabetical order.
Steve    07 Jun 2014, 13:50
Why does it stop at empty cells? With that behavior it is of no use.
milan    27 Nov 2013, 10:00
how can i use it on number
Lovetta D.    15 Feb 2013, 20:54
Can I disable auto complete in just one workbook?
Steve H    25 Jul 2012, 13:01
Another thing that may or may not be helpful to you. A list of the unique text in adjacent cells is available by clicking right mouse >> Pick From Drop-down List...
kevin tansley    25 Jul 2012, 03:46
Aaaah, now that is good.
Big thanks, Steve.
Steve H    24 Jul 2012, 12:56
Kevin/Jaker, another solution is to press the backspace key. Only the remaining highlighted text is deleted. No need for a double space.
siva rama krishna    25 May 2012, 05:18
how to make auto fill for some words by typing the first letter in every work sheet
kevin tansley    17 Apr 2012, 05:04
Thanks for the workaround Jaker R. That's probably the easiest solution. Annoying that you can't not accept the suggestion, though.
Jaker R    16 Apr 2012, 15:51
Kevin I run into this all the time. I just enter two spaces after yes. Then if having two tailing spaces becomes a problem, select the sheet and do a find/replace of two spaces with the replace box empty.
Paul Vaglio    16 Apr 2012, 06:44
Based on some experimenting I was doing last week, I believe that autocomplete tries to match new entries/changes with immediately adjacent cells above AND BELOW the one in which you are working on.
kevin tansley    16 Apr 2012, 03:35
When you have autocomplete on, is there a way of NOT accepting the suggestion? EG, if it's suggesting 'Yes but only on Monday' and I just want to enter 'Yes' - can I stop it adding in the rest of the text?
gewurtz cornel    15 Apr 2012, 07:47
if in row n+1 we type trl+d excel copies the text from row n to row n+1
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.