Do you use this Excel Data Entry Trick?
November 27, 2018
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Do you use this quick trick for data entry? It works best in a table where you already have quite a few entries. For example, every day I enter weather info in a worksheet, with the high temperature, and a short description.
Instead of typing out the description, I type the first letter or two in the cell, then right-click on it (while still in the cell). In the pop-up list, click on Pick From Drop-down List
A list appears, with all the items previously entered in that column, in alphabetical order. It selects the first item that starts with the letters that you typed -- a real time-saver!
Scroll up or down, if necessary, to find the item that you want, and click on it to put it into the cell.
You can even use this trick in cells that have a Data Validation (DV) drop down list. It's handy if there's a long list of items, and you want something near the end of the list.
NOTE: To do this with the keyboard, tap the Menu key, then tap k, and use the arrow keys to go up or down in the list.
UPDATE: Or even quicker, press Alt and the Down arrow (Thanks UniMord!). In a cell with a DV drop down, that shortcut will open the DV list, unless you've started typing. In other cells, it will always open the "Quick Pick" list.
There are more Data Entry tips on my Contextures website
In past years, I've shared Excel Advent calendars with fancy pictures, that require macros to show each day's "treat". This year's Advent calendar is simple, with no macros -- just formulas and conditional formatting. It's always full to see what Excel can do with just the basic features.
The main page uses the Wingdings 2 -- the only font I found that has numbers and pictures.
On another sheet, the CHAR Function pulls the number and picture options for each square, based on the character numbers. This formula is in cell C3, to create the one or two digit number.
Then, the calendar shows the number or picture, depending on the current date. This formula is in cell B3 -- the Show column.
To see how it works, click here to download the Advent Calendar sample file. Even if you don't need an Advent calendar, you might find some useful techniques for your other Excel projects
Here are a few Excel articles that you might find useful or interesting.
Mr. Excel - Congratulations to Bill Jelen, who celebrated the 20th Anniversary of his Mr. Excel website. Bill's first article was about grouping pivot table dates, and now he wonders why he didn't suggest a different solution. Never look back at the work you did 20 years ago! (Level - All)
Excel Skills - How would you rate your Excel skills? Are you an expert? Read this interesting discussion on Quora, of what an Excel expert is. (Level - Int)
After avoiding the job for weeks, I finally cleaned out the garden, and raked up the leaves in the back yard. We only have a catalpa tree back there, so I'm not sure where all the other leaves came from! It was a cloudy day, but not too cold, and I wanted to get the job done before more snow arrives this week.
Speaking of snow, this week I learned that actor Boris Karloff, who is the voice of Mr. Grinch, got his acting start here in Canada, before he went on to star in Frankenstein. If you spend more than a month in Canada, we will take credit for all your success!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181127ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: December 1, 2018 2:31 PM