Mail Merge

There are times when many Excel gurus will need to send an email to a list of recipients.  For instance, you may wish to send a Word document to everyone in your unit, your department, or even your entire company.  Now, I’m not talking about sending Spam, mind you, just legitimate communication that needs to be sent to a comparatively large number of email recipients.

You can, of course, accomplish this by manually entering each of the receiver’s address, but who wants to do that?!?

Another way is use to create a custom Contact Group using the innate abilities of Outlook.  This may be an acceptable course of action, especially if you have a fairly limited and stable number of addressees.

The fact is, of course, that lists of this sort are often kept in good old Excel.  They are regularly stored and updated in worksheets that are maintained in individual units and/or corporate human resource departments.

To make use of this information, save your Excel workbook to My Data Sources on your hard drive, open your Word doc and do the following:

1.    Click on the Mailings tab

2.    From the Start Mail Merge group, choose Start Mail Merge/E-Mail Messages

3.    Click on Select Recipients and choose Use an Existing List

4.    Finally, double-click on the Excel workbook housing your mailing list and finish

That’s really all there is to it.  It may, of course, take a few brief minutes of experimentation to become comfortable with this technique,.  I can assure you, however, you will be hooked once you use this procedure even once.  Mail Merge – Give it a try!

Logging TIme of Data Entry

Data Entry is a fact of life in any information system.  This is true whether the data is being entered straight into Excel or is being imported from another system which was the recipient of the data entry.

When the data was entered is often a worthy piece of information in itself.  If the data is being entered directly into Excel, there are several (some better than others…) ways of accomplishing this.  In our examples below, let’s assume we have the data being entered in Column C and we want the time it was entered in Column D:

1.    First of all, you could enter it manually by selecting the adjacent cell in Column D and pressing: Ctrl+Shift+;  This is a shortcut key for entering the current time.

2.    A better way to do this may, of course, be to use a Formula to enter the time. The =NOW() function not only records the current time, but the date as well.

3.    The problem with the approach described in #2 above is that this simple formula will Recalculate each time the worksheet is reopened or otherwise refreshed.  This is also the case (unfortunately) if you use a more sophisticated pre-filled formula such as =IF(C1="","",NOW()), since this will also update.

4.    If, therefore, you wish to take this to the next level, you may wish to try out some VBA code similar to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C1:C500")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

VBA is certainly not everyone’s cup of tea, but it is worth taking a look at occasionally.  Who know, you actually may find that you have hidden talents as a Code Expert!

Multi-Tiered Dropdowns Revisited

One of the most popular ongoing topics on my LinkedIn group, Excel Enthusiasts, (catchy name, eh?), is the surprisingly easy-to-create Multi-Tiered Dropdown configuration for more sophisticated research into your data.  Wow, that’s a mouthful, but hang on, it’s Really Cool!

The popularity of the topic is not surprising, since looking up information with the use of only one parameter is insufficient in many cases.  Take for instance that you have a large database with 65 different cities and 14 states. If you want to use the City Name in an Interactive DropDown List to pull up reports based on that city, you are faced with at least a Couple of Possible Glitches:

1. You will be presented with a Long List of City Names in your dropdown (65 in this case) 
2. Many city names Occur in More Than One State (e.g. Dallas, TX & Dallas, GA or Glendale, CA & Glendale, AZ)

There are, of course, a great number of other scenarios where a single parameter is simply lacking in lookup power.  So let’s build a Dropdown List of States that generates a Second Dropdown List of Cities for that state only. This way you can use the parameters of Both the State and the City to assemble your information!

Here is How It is Done:

 

Let’s assume you have a Horizontal Database containing your States and Cities in E2:N12, and your interactive cells set up in B2:C3 as per the illustration above. Note: The list of States would be in the first column of the database, E2:E12.

 

1. Select the Table, E2:N12

     a. Go to Formulas / Defined Names and choose Create from Selection

     b. Assure that the check box with Left Column only is checked and click OK

 

2. Select B3 and Create a Dropdown Box by Using Validation

     a. Under “Allow” choose List and select $E$2:$E$12

 

3. Select C3 and Create a Dropdown Box Using Validation

     a. Under “Allow” choose List and insert the formula =INDIRECT($B$3)

     b. Click OK (Click Yes if you get an error alert…)

 

4. Now to Make It Look a Bit More Professional…

     a. Select, F2:N12 (Note: Do Not include Column E)

     b. Go to the Home tab and select Find & Select from the right-hand side of the ribbon

     c. Select Go to Special and choose Blanks and click OK

     d. Right-click the selected area and Delete / Shift Cells Left

 

And that’s all there is to it! This is a very straightforward technique that results in Multi-Tiered DropDown Lists.  Incredibly useful when setting up Sophisticated Interactive Reports.

 

Interactivity in Reports is, as you may know, a theme that I address with regularity.  The reason is that they are what (whether they know it or not) your users Really, Really Want!

Add a Background (or not…)

When some Excel users learn that they can insert a Background Image into their Excel worksheets, they suddenly consider themselves the Picassos of the Excel World and believe that images belong everywhere.  This is, of course, certainly not advisable, and may raise the eyebrows of any executives that may see their handiwork. 

There are instances, however, when Backgrounds are appropriate or even desirable.  Take for instance an important Company Logo.  If inserted in the background in a very light-colored, non-interfering manner, the logo may serve as a tasteful Watermark.  This can be quite effective if you use a special company graphic that adds just a hint of a background without obscuring the readability of the data being presented in the worksheet.

 
How to Add the Background Image

Adding an image to a worksheet could not be easier (just try to resist doing it to all of your work…).  Simply do the following:

1. On the Toolbar, click on the Page Layout tab.
2. Go to the Page Setup group on the ribbon and click Background
3. Browse to the file in your own network or on the web, and double-click it
4. Voila!  Your image has been inserted!

 So, What if You Don’t Like the Background?

To remove an image, simply follow the same instructions listed above, but at Step 2 click “Delete Background”.

A Few Added Suggestions:

• Take Care in Choosing Your Picture:  Adding images can significantly increase the file size of your workbook if you choose a large image, so make sure it is comparatively small.
• Recommended Image Type:  For best results you should use standard image types such as JPG, PNG, or GIF.
• Format to Improve Appearance:  Use an image that provides good contrast with your Excel content, and try hiding cell gridlines and applying solid color shading to cells that contain data. It can be very effective.

Adding Backgrounds.  When used with caution, they can add a bit of pizazz to your worksheets.  Try it out the next time you are posting scores for your Company Bowling League.  Cheers!