This quick data entry trick for Excel works best in a table where you already have quite a few entries. To see the trick, watch the short video below. There are written steps too, below the video.
Video: Quick Data Entry Trick
In this video, there are several rows of data in a named Excel table.
- First, see a data entry trick for columns that do NOT have a data validation drop down.
- Next, see a similar trick for columns that DO have a data validation drop down
Quick Pick from List
Here’s an example of how I use the quick pick trick. Every day I enter weather info in a worksheet, with the high temperature, and a short description of the weather conditions.
To quickly enter the description, I do this:
- Type the first letter or two in the cell
- Press Alt+the down arrow
That opens the Pick From List feature, which shows previously entered text items in that column, in alphabetical order.
Select an Item
When the list opens, it selects the first item that starts with the letters that you typed — that’s a time-saver!
- To select a different item, use the arrow keys to scroll up or down
- When the item that you want is selected, press the Enter key, to add it to the active cell.
Missing Items
The Pick From Drop-down List shows all the previously entered text items in the column.
However, it won’t show items that are numbers or dates. In the screen shot below, the dollar amount and the date are missing from the list.
Use the Mouse
If you’d rather use the mouse than the keyboard, right-click on a cell, and click “Pick from Drop-down List”.
Data Validation Cells
You can even use this Alt+Down Arrow trick in cells that have a Data Validation (DV) drop down list.
NOTE: For DV cells, you have to type a letter or two, or the Alt+ Down Arrow shortcut will open the full Data Validation list.
The trick can save you time if:
- there’s a long list of items in the DV drop down, AND
- the item you want has already been entered in the data entry column
___________________
Quick Data Entry Trick for Excel
___________________
___________________
That’s a handy little trick. Thanks Deb!
Thanks, JoAnn! Glad you like it.
I’d like to note that the Alt+Down trick has limitations, unfortunately.
I have a table (formatted as an Excel table), with about 49,000 rows with an attached data validation list of about 2000 items. Typically, if I begin entering something, Excel comes up with autofill suggestions from _recent_ items only. In other words, if in my list I have both “Milk” and “Milky Way”, but recently I’ve only been entering “Milky Way”, when I begin typing “Mi”, Excel will suggest “Milky Way”. In fact, if I haven’t entered an item in a long time, Excel won’t even try to suggest anything despite the item being “known”. The same happens with Alt+Down – the dropdown list won’t have “Milky Way”. My guess is Excel has a “short memory” for suggestions (maybe 1000 unique items, for example).