Outlook and Excel

The topic of Concatenation came up in one of my live Excel classes this week and, considering how cool and useful it can be, I am addressing it in this week’s post.

Whether they are employees, clients, or vendors, Lists of Names of people are often kept in Excel. The format may vary, of course, but often the First Name and Last Name are contained in separate fields (which is how it should be…).  There are times, of course, when you may want to send an email via Outlook to a group of folks that you have in one of these Excel databases.

Outlook, as well as some other email systems, requires that the names be entered in a Last Name/Comma/First Name format. So what do you do if that is Not the way your Excel list is arranged?  Here is where Concatenation becomes useful!

Let’s say you have a list of employees with the First Names in column A and Last Names in column B. Combining them into an Email-Friendly column of names in a “Last Name, First Name” format is (as you will see) incredibly easy.

Let’s assume your table starts in cell A1. Using the convenience of Concatenation by putting the following formula in C1:

=B1&", "&A1

Notice: Be sure to use the quotation marks and insert a space after the comma.  This provides the syntax that Outlook will immediately recognize.  This formula combines the contents of B1 (last name) with a comma, space, and contents of A1 (first name). Drag the formula down to fill Column C, and then copy and paste it into Outlook (any version from 2007 on…).

Outlook will very quickly recognize anyone in your address book and those to whom you’ve sent emails previous.  Happy Halloween All!

F3 – The Magic Key Revisited

This morning I was privileged to do another Advanced Excel Class with one of the finest group of Excel scholar-practitioners that I have had the pleasure of teaching.  Our topic was the somewhat challenging one of Boolean functions.  As is the case with many complex formulas, Boolean functions involve the insertion of a great many Named Ranges.

If you are an experienced user of Excel, you undoubtedly know that Naming Ranges can save you a lot of time and make your formulas more intuitive to any user of your workbooks. Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

Still, building multi-part formulas can be rather labor-intensive. Here is where the Magic Key of F3 can help alleviate some work for you.

Although “Magic Key” might overstating it a bit, the following simple example shows what it can do for you:

Let’s say that you have named several ranges in your workbook. When creating a formula, (in this example, we will find the average of a field named, “Sales”), do the following:

1. Type “ =Average( "
2. The hit the F3 key and
3. Using the Arrow Pad on your keyboard, choose your Named Range from the dropdown
4. Hit the Enter key and, Ala Kazam, the range is inserted into your formula!

This shortcut will save you little bits of time that will Add Up to many hours of work. (And that, Ladies and Gentlemen is a little bit of “Magic” …).

Retro Look: Easter Eggs

You may, (as I do), find it just a bit curious that Easter Eggs has been the most frequently searched topic on this blog.  During the 7 years of the blog’s existence, Easter Eggs have been searched for 26,240 times!  This represents nearly 15% of all searches on Excel Enthusiasts.

So, what does this mean?  Well, I have a theory, but first let’s review what we’re talking about in this regard:

Virtual Easter Eggs are hidden surprises, games, or messages that have been built into various software creations by clever developers who have a sense of humor (apparently frowned upon at Microsoft these days).   In years gone by, users who were “In-the-know” could feel smug knowing how to reach this cryptic, and often entertaining, secret content. 

So, where did this unusual terms come from?  “Easter Eggs” is attributed to one of the founding fathers of computer games, Warren Robinett. While working for Atari in the late 1970s, Mr. Robinett created a hidden screen which read, “Created by Warren Robinett”.  As with many talented people working for large companies in the past (think of the Disney empire…) it was not uncommon for designers to be given little credit for their work.  That being the case, Robinett probably felt his small ploy was justified.  I’m sure we all agree.

If you have been a longtime-user of Excel, you may remember that Excel 97 had an ambitious Flight Simulator hidden within the application (quite amazing for the time!). Using a simple combination of keyboard commands brought you to this remarkable simulator game.

Although a good deal more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled the classic Spy Hunter game.  If you are interested in the old classics, you can still find several downloads for this retro favorite.

Excel 2003 included an Office Quiz featuring the Crabby Office Lady (I am betting you remember her…).  If you still have a copy of this version, you can access this egg by typing in “Tortured Soul” (really…) in the search box. 

About the only Surprise (it doesn’t even warrant being called an Easter Egg…) you’ll find in Excel these days is the DATEDIF function.  This curiously undocumented tool calculates the difference in whole days, months or years between two dates.  It’s nice to know about.

So, why do so many Excel users continue to search for Easter Eggs?  My theory is that several of us are nostalgic for the more innocent time of the past when you could find a little hidden Fun in our office work applications.  A little Fun is, after all, always a good thing…