How do you autofill repeated words in Excel?

If the first few letters you type match another cell in the same column Excel will complete the text for you automatically.

AutoComplete is the automatic filling in of your text when you start typing and is switched on by default
Every time you start typing in a cell, all the entries in the same column of the current region are scanned and as each character is typed, any possible matches are automatically highlighted.
When you repeat list entries, Excel intuitively suggests entries based on the first few characters you type. You can either accept the suggestion or continue overtyping
You can either continue typing in order to overwrite or press Enter to accept the suggestion.
This only matches exact cell entries. This does not work when inserting or editing formulas.
AutoComplete , although you can switch it off.
Remember that AutoCorrect and AutoComplete are very different.
AutoCorrect happens automatically and there is nothing you can do to stop it, other than removing the entry from the list.
AutoComplete however requires you to press the Tab key to accept the suggestion.

Using AutoComplete

Make sure you can edit directly in cells. Check your (Tools > Options)(Edit tab, Edit directly in cell).
If you are unable to edit directly in cells then the entry will auto complete in the formula bar.
You can accept the suggestion by pressing either the (Enter) or (Tab) keys.
It is possible to also accept the suggestion by pressing any of the arrow keys or by selecting another cell.
You can remove the highlighted suggestion by pressing the (Delete) key.
Autocomplete is only displayed after enough characters have been entered in order to determine which value matches.

To ignore just keep typing. AutoComplete only matches complete cell entries and not individual words.
An alternative to using AutoComplete is to use the Pick from List drop-down list.
This can be displayed by press (Alt + Down Arrow) or using the Cell shortcut menu.

Pick from a List

An alternative way to enter values that have been entered before without re-typing the value is to use the Pick from List feature.
A quicker way to select an existing entry is to right mouse click and select "pick from List" on the shortcut menu. This will display a drop-down of all the possible values ?
Pick from list available when you right mouse click to display the Cell shortcut menu. The list contains all entries that have been previously keyed in.
Pick from list - If a column contains several words all starting with the same characters, it may be easier to select an entry from a drop-down list of choices.
This is available from the Cell shortcut menu. A shortcut menu is activated by pressing the Right mouse button as opposed to the Left.
Select the cell directly below and press the right mouse button. Select "Pick From List".

A quick way to display the Pick from List drop-down box is to press (Alt + Down Arrow).
This will display a drop-down list of the unique entries in that column from the data directly above.

The list will be automatically sorted into alphabetical order.
Just select the value you would like to enter.

Important

AutoComplete will only match on exact cell entries and not on individual words within a cell.
This feature is on by default although you can switch if off by changing your (Tools > Options)(Edit tab, Enable AutoComplete for Cell Values).
AutoComplete does not work on numbers or when editing any formulas.
If the text that AutoComplete displays is not correct, then just continue typing.

Using Excel 2013. I have a spreadsheet that I copy and reuse with new, imported data which contains between 50,000 and 200,000 rows of time-evolution data. I perform simple calculations on the data, such as multiply by 1000 or divide by 60. In an adjacent column, I enter the pertinent calculation, select the cell, and double-click on the auto-fill square. The auto-fill will correctly fill all 200k cells in the column. Other data, same spreadsheet a few columns over has a different calculation performed on it. In this column, the auto-fill procedure will only work to about 50k of the 72k values and I have to manually drag the auto-fill square down to complete the procedure. Scrolling down to the bottom of the incomplete column, selecting a cell, and double-clicking on the auto-fill square does nothing. I have to manually scroll down to complete the action.

Strange thing is that if I start with a new, empty spreadsheet and import the same data, auto-fill works fine. But, if I save it, delete the data and enter new data, the auto--fill is broken again. I don't want to do this because the graph formatting is very complex and time-consuming and I don't want to "reinvent the wheel" with every new set of data.

Any thoughts? Thanks!

Dan says:
May 6, 2016 at 10:52 am

Hi Stephanie,

Having worked through a number of your samples here I've picked up a few tips that I wasn't aware of, however I was wondering if you could help me with a problem I'm running into. I'm not sure if it is related to this topic (I may have used the wrong search criteria to find a solution - if I did I still learned something new :-) and will try to find it elsewhere).
I have a spreadsheet with a number of formulas in it, most of which are date related and are spread across a number of columns (which are not beside each other). I was wondering if there was a way to have these formula automatically appear on a new line each time a new record is added or is this always going to be a manual process.

For example
COLUMN L =IF(ISBLANK(K3),"N/A",+K3+70)
COLUMN M =IF(ISBLANK(K3),"N/A",L3-TODAY())
COLUMN N =IF(ISBLANK(K3),"N/A",+L3+42)
COLUMN U =IF(ISBLANK(T3),"N/A",EDATE(S3,T3))
COLUMN V =IF(U3="N/A","N/A",U3+42)
COLUMN Y =IF(ISBLANK(X3),"N/A",X3+42)
COLUMN AA =IF(ISBLANK(Z3),"N/A",Z3+42)

Any help on this issue would be greatly appreciated

Regards,
Dan

Sara says:
April 9, 2015 at 3:15 pm

Hi,
I'm having an autofill error that I'm hoping you can help me with.

I have a very long nested IF statement formula that I am trying to apply to every row in my spreadsheet (hence, the autofill).

My spreadsheet is for salary analysis. Basically, I am saying this:
if Grade=3 AND New Salary is less than or equal to 35149, then Salary Range=Min
if Grade=3 AND New Salary is between 35150 and 40554, then Salary Range=25%
if Grade=3 AND New Salary is between 40555 and 51363, then Salary Range=Mid
if Grade=3 AND New Salary is equal to or greater than 51364, then Salary Range=Max

(The grade level and the salary amount being the logical test, and the Salary Range being the true value. If the first statement is false, then it will evaluate the second, and so on.)

There are 8 different possible grades, with four salary range options for each grade, so a 32-statement formula. The grade possibilities are 3, 4, 5, 6, 7, A, B, C.

When I enter the formula and drag it through my spreadsheet, it works just fine through all the numbered grades (3-7). When the formula hits the lettered-grades (A-C), it suddenly stops working. However, if I copy/paste the formula again on the first lettered-grade row, and drag it the rest of the way, it works just fine. So to me, it seems the autofill capability is having a hard time getting the transition from numbered-grades in the logical test statement, to lettered-grades in the logical test statement. Again, when I drag the formula through the grades separately, it works just fine. So the formula is correct... Hoping you guys can help?

How do I autofill the same words in Excel?

How do you repeat multiple words in Excel?

How do I automatically duplicate text in Excel?