Excel Lists for Email

This topic came up again in one of my Excel classes this week and, considering how incredibly useful it can be to so many of us, I thought I would share it with you in this post.

Whether they are employees, clients, or vendors, Lists of Names of people are often kept in Excel. 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 and 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?

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 so easy you’ll laugh. Here is what you do…

Let’s assume your table starts in cell A1. Using the magic of Concatenation, (there’s a word for yah!), put the following formula in C1:

=B1&", "&A1

Note: Be sure to use the quotation marks and insert a space after the comma.

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.

Bamm! Your Outlook Mailing List is Ready to Go!

INDEX + MATCH = INFORMATION

There are many ways to look up information in tables and databases. Excel provides us with functions such as VLOOKUP, DSUM, DGET, SUMPRODUCT, and several others. There are pluses and minuses when working with any of these tools, but in certain circumstances one or more of them can be an excellent solution. It’s all about returning Information in the way you want it.

Flexibility is always a plus, of course. When it comes to Versatility, the combination of using INDEX and MATCH can be hard to beat! This is especially true when comparing these powerful tools with the decidedly more limited VLOOKUP and HLOOKUP functions.

 For instance, using this combination alleviates the need to stay locked into the first column for a reference point. As I discussed in this blog in years past, a simple example goes a long way in illustrating this piece of Excel Magic:

 INDEX and MATCH can serve as your able-bodied servants. Let’s take a look at a simple example using the Illustration above.

 For instance, MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

 =MATCH("Tampa", $A$2:$A$8,0) 

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula: 

=INDEX($A$2:$A$6,4) 

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

 =INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0)) 

Using the INDEX and MATCH functions together enables the crafty Excel Guru to extract information from a table or database of nearly any size or dimension. It gives you the ability to sidestep some of the shortcomings of other techniques, and lets you Have it Your Way!

Indirectly to the Point

Using Named Ranges in your work in Excel is a highly recommended Best Practice. A problem may arise when trying to refer a Named Range, however. The solution is mastering the easy-to-use Indirect Function! 

If you read this blog occasionally, you know that I am big fan of Interactive Reports. Using the Indirect Function, along with drop-down boxes created with Validation, is another very cool way of making reports of this type.

 As I discussed in this blog several years ago, there are many uses for the Indirect Function. Used in its simplest form it simply take the Name in the cell it is referencing and returns the Named Range that it refers to.

 Try Creating a Small Spreadsheet Similar to the Graphic Above, and Try the Following: 

 1) Create your Named Ranges by selecting each of your ranges and typing the new Name in the Name Box on the upper-left corner of your worksheet. In the above example graphic, Name each of the monthly rows (excluding the cells with the month name) for the Units shown. For instance, for August, select C12:F12 and name it August (clever name, eh?).

 2) Then go to Data Validation in Tools and choose Allow List (choose the list of items that you want to appear in the drop-down box). For example, in the above graphic, the cell C2 with “April” in it contains the interactive drop-down created using the list of months.

 3) Now for the Good Stuff! In cell D2 insert the formula“=SUM(INDIRECT(C2))”. The Indirect Function reads the name of the month you choose with the drop-down box in C2 and the formula sums up the total for the units.

 Using the Indirect Function, along with Validation, is an easy way to make a truly powerful interactive report. How Cool is That!

Spreadsheets: The Big Three

Spreadsheet applications have been around for quite a long time and, as is typical of technical evolution, the strong have survived while the weak fell away into obscurity. Currently, the Big Three are Apple Numbers, Google Sheets and, of course, Microsoft Excel.

 As is the case in most categories, it is no surprise that Excel is the King (or Queen if you prefer) when it comes to Functions. In addition to other tools, Functions, arguably, Make Spreadsheets Go. It is therefore interesting to take a quick look at the major applications in this regard.

 Apple Numbers 
There is no doubt that Apple’s Numbers is pretty to look at and does a great job on charts and graphics. The software also claims to make using functions and formulas less complicated through the use of an “intuitive” browser. Approximately 255 functions are available as is built-in help and the ubiquitous tooltips. Newly added functions include DATEVALUE, NETWORKDAYS, YEARFRAC, and several more.

Google Sheets 
Google Sheets is a free online spreadsheet application that lets you simultaneously work with other people. As with all of the major spreadsheet apps, it has substantially evolved over time and currently has approximately 315 functions to choose from. You can even Chat in real time with others who are editing the functions in your spreadsheet, and do most of the work that professionals require in this arena.

Microsoft Excel 
It is no surprise that Excel leads the industry with over 400 functions. It is doubtful that anyone in the foreseeable future will surpass the sheer depth of functionality that Excel enjoys. For instance it is particularly notable for many professionals that no other spreadsheet software includes anything like Pivot Tables. Although there is specialized number-crunching software, Microsoft continues to do an admirable job in updating and refining its crowning application.

Which one to use? Well, that depends on who you work with and what your needs are. When in doubt, however, you can’t go wrong with Good Ol’ Excel…