Contextures

Contextures News 20181127

Do you use this Excel Data Entry Trick?

November 27, 2018

Quick data entry trick, Advent calendar, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Quick Pick

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

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.

Select an item in the drop down list

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

Advent Calendar

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.

Advent Calendar

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.

=CHAR(D3)&IFERROR(CHAR(E3),"")

Then, the calendar shows the number or picture, depending on the current date. This formula is in cell B3 -- the Show column.

=IF(CurrMth<>AdvMth,[@Num],IF(CurrDay<A3,[@Num],[@Pic]))

CHAR formulas for Advent Calendar

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

For fancier versions with macros, see the 2009 Advent Calendar, or the 2010 Advent Calendar.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Garden Cleanup

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!

weekly photo

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.

Debra Dalgleish
dsdalg@ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: December 1, 2018 2:31 PM