How To Use Autocomplete With Data From A Different Column In Excel?
For work I enter names in different columns. Sometimes the names are the same. It would save me a lot of time if Excel were to recognize that I was typing a name I have previously typed and suggest a finished string of text that I might want.
That feature is known as Autocomplete. If you are entering strings of characters (names, keywords, phrases, etc.) in every cell going down a column, then if you start to type the same string again, Excel will suggest to you something you've already entered in another cell.
This feature only works within a single column and all the the cells must be touching. That means that you cannot get Autocomplete suggestions based on text entered in another column, and you can't skip a row or leave a row blank on your way down a column, or else the Autocomplete stops working.
This is a really cool feature, but I can't make use of it because I want to have Autocomplete suggestions based on the text I've entered in EVERY other cell in the worksheet. I don't care if I entered the text in column A or column D, I want to see a suggestion if I previously entered that string of characters before.
I googled this issue to see if there was a setting I could tweak or a technique I could use to make Excel do what I wanted it to do. I read several forum posts, and it seems like the answer is that it can't be done. Excel strictly limits Autocomplete to the current column.
The Solution I Came Up With
I enter so many names, and they are repeat names so often that I had to find a way to make Autocomplete work for me. The only thing I could come up with was to enter all of my data in a single continuous column. I can enter all of what I would normally enter in column A, then when I would normally move to column B data, I just continue on in column A.
I can make a cell in column A that is titled "Column B", or whatever title makes sense for what I'm doing, and highlight that cell a different color so that it is obvious where the next column's data starts. Then I just continue on with column B data in column A.
Because all of the cells are in the same column and there are no blank cells, Autocomplete continues to work. Then when I am all done entering the data, I can just copy and paste my columns into their proper spots in the worksheet.
This workaround isn't perfect, but it can really save a lot of time if the alternative is typing out the same strings of characters over and over.
And if anyone that works for Microsoft on the Excel program reads this, it would be great if you could just make a setting which would allow Autocomplete to look for options in every cell in the worksheet/workbook and not just the current column.
Do you know a better way to do this than I've suggested here? Let me know about it in the comments!