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.

Understanding AutoComplete

by Allen Wyatt
(last updated July 21, 2012)

22

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Paragraph Order

Want a quick way to rearrange entire paragraphs of your document? You can easily do it by using the technique described here.

Discover More

Searching for Multi-Byte Hex Codes

Need to find a character for which you only know the hex code? There are a few ways you can search for the information, as ...

Discover More

Setting a Default for Shifting when Inserting

When you insert cells into a worksheet, Excel needs to know which direction it should shift the displaced cells. If you ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Making AutoComplete Work for an Entire Column

AutoComplete is a great feature for quickly adding data to a worksheet. If you are confused by why some things are picked up ...

Discover More

Using AutoComplete with Disjointed Lists

AutoComplete can help you to more quickly enter information in a worksheet. How it works, behind the scenes, can affect how ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 0?

2016-12-26 05:46:23

sridhar

If you would like to know some more special keys on excel or word note pad, etc. Click on this link
https://youtu.be/wEPBQS3SZ_E


2016-12-23 11:45:43

Adnan

Here is a VBA solution for a autocomplete list in excel.
https://mishotechs.wordpress.com/2016/10/03/pick-from-list-in-excel/


2016-09-22 00:27:54

shivam singhal

Hi,

I hv not find out these tools {option}
pl let me know.


2016-04-12 09:36:00

Louis Farrell

@Pete
Press Alt and Down Arrow to get a drop-down list of all AutoCorrect entries


2016-04-04 05:39:09

Barry

@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"


2016-04-03 17:16:10

Pete

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?


2016-01-10 14:14:04

Avinash

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 ?


2015-03-15 23:24:26

Gary Kitchen

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?


2015-01-13 02:51:52

Nick the Greek

A simplistic way around the empty cell problem is what I use: match the cell colour to the text colour and type a letter


2014-08-05 14:35:21

Gerry B

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.


2014-06-07 13:50:52

Steve

Why does it stop at empty cells? With that behavior it is of no use.


2013-11-27 10:00:16

milan

how can i use it on number


2013-02-15 20:54:44

Lovetta D.

Can I disable auto complete in just one workbook?


2012-07-25 13:01:18

Steve H

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


2012-07-25 03:46:17

kevin tansley

Aaaah, now that is good.
Big thanks, Steve.


2012-07-24 12:56:48

Steve H

Kevin/Jaker, another solution is to press the backspace key. Only the remaining highlighted text is deleted. No need for a double space.


2012-05-25 05:18:36

siva rama krishna

how to make auto fill for some words by typing the first letter in every work sheet


2012-04-17 05:04:02

kevin tansley

Thanks for the workaround Jaker R. That's probably the easiest solution. Annoying that you can't not accept the suggestion, though.


2012-04-16 15:51:55

Jaker R

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.


2012-04-16 06:44:34

Paul Vaglio

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.


2012-04-16 03:35:01

kevin tansley

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?


2012-04-15 07:47:26

gewurtz cornel

if in row n+1 we type trl+d excel copies the text from row n to row n+1


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share