SOLVED

Auto suggesting text in column

New Contributor

On a worksheet I have typed a word in a cell, when I type the same word in the cell directly below, Excel autosuggests the same word for you. When typing this word 10 empty cells lower, there is no suggestion and I have to type the same word completely. 

I tried to see if Options could help me, but nothing found there.

Who can help to turn the autosuggestion on?

6 Replies

@Tasar298 

As you have found, autocomplete stops working as soon as there is an empty cell above the active cell.

As far as I know, you'd have to enter something in the empty cells, even if only a space.

@Hans Vogelaar 

Hi Hans,

Thank you for your reply.

In another Excel sheet, I am keeping a record of the milage I drive for my work.

I visit ships and when I enter the ship's name for the second time, no matter how far up the first entry is, it puts the complete name down and it has plenty really empty cells (no formula, conditional format or text) and cells with different names between these two entries.

I have tested it with a name in row 5 and when I type that same name in row 135 after two letters input, it autocompletes it without a problem.

The further down I go, the more letters I need to get the autocomplete to fill in the rest.

after 225 lines, I need 4 letters, after 300 lines, it needs 5 letters.

Now I am using an Excel sheet (not set-up by myself) and it does not work anymore.

The cell right below, one letter activates the autocomplete. Two cells below and it does not work anymore. Also when I open a new workbook, it is as you explained: leave one empty cell and it stops.

Where is that setting (if it exists) that turns auto complete on or off?

To help me in this case, I have shortened the name I have to repeat to one letter and when ready, replace it in the selected column with CRTL+F routine to the proper name.

 

best response confirmed by Tasar298 (New Contributor)
Solution

@Tasar298 

More accurately, AutoComplete works within a contiguous range where at least one cell in each row is filled:

S1191.png

Hans, many thanks!
It is all clear to me now why it does not work on my active sheet.

@Tasar298 You may also find Alt+Arrow Key Down useful to get a list of choices.

You can use Arrow Keys to select a choice and Enter Key to enter you choice.

bosinander_0-1646462350950.png

When you have Excels choices of functions though, it is Tab Key to choose and continue working with the selected function before you finally enter it.

bosinander_1-1646462652905.png

 

@bosinander 

Thank you for this one.

I will try it out soon.