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 November 22, 2017)


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


Controlling Field Shading

If you use fields in your documents, you may want to highlight them in some way so that you can find them easier. Word ...

Discover More

Finding Related Words

One part of the grammar tools provided with Word is a thesaurus that helps you find all sorts of word variations. One ...

Discover More

Using a Protected Worksheet

If you have a worksheet protected, it may not be immediately evident that it really is protected. This tip explains some ...

Discover More

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!

More ExcelTips (menu)

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

Discover More

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

Discover More

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

View most recent newsletter.


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. Maximum image size is 6Mpixels. 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 9 - 4?

2019-07-15 12:54:53


Autocomplete also suggests formulas when you're entering one. If you type the letter C, a dropdown pops down. 99.9% of the time, you have to hit escape, and resume entering your formula. It's devastatingly deproductive; ergo, Microsoft Office Interface Deproductivity Team, mission accomplished.

2018-12-27 11:59:03


Is it not possible for Excel to autocomplete phone numbers, ie: 555-123-1234 since it has no 'text'?

2017-11-13 23:04:27


I'm using this tool to create autocomplete combobox on each cells the cursor enters. It's definitely a life saver

2016-12-26 05:46:23


If you would like to know some more special keys on excel or word note pad, etc. Click on this link

2016-12-23 11:45:43


Here is a VBA solution for a autocomplete list in excel.

2016-09-22 00:27:54

shivam singhal


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

2016-04-12 09:36:00

Louis Farrell

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

2016-04-04 05:39:09



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


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


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


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

2013-11-27 10:00:16


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

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

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.