every school counselor should know how to mail merge

12
APRIL 2018
Anthony M. Wagner
Excel Yourself
Picture it: Your principal comes into your office and asks you to draft a letter. It’s a letter to parents, and it has to go to about 20 of them.

She also asks you to print, label, and mail them.

Do you dread the idea of having to send those letters? The letter itself is simple enough, right?

But that mailing… it will take forever to address all of those letters, make labels, and get everything ready for sending.

You might be wondering if there’s a better way. There has to be a better way.

Follow along with our free documents!

If you’d like to follow along with this post, download our free mail merge documents now!

Contents

Use these links to jump to any section

  1. What’s a mail merge?
    1. Let’s look at a quick example
  2. A sample letter
  3. How to get the data
    1. Exporting from student management systems
    2. Manual entry into Excel
    3. Overall…
  4. Formatting the data
  5. Linking the data to Word
  6. Using merge fields
    1. Previewing the results
  7. Printing your merged document
  8. What’s the bottom line? <– CLICK HERE IF YOU’RE IN A RUSH!

Shortcut!

If you already have your data in Excel and have your letter ready, feel free to jump to sections 6, 7, and 8!

What’s a mail merge?

A mail merge is “the automatic addition of names and addresses from a database to letters and envelopes in order to facilitate sending mail to many addresses.” Thanks for the definition, Google!

In other words, it’s a quick way to write one letter, and have the computer customize each one, and generate your labels/envelopes for you.

That would definitely be a better way than doing each one manually!

Let’s look at a quick example:

Let’s take that letter from the top of this post: It’s a letter to the students at your school who are in danger of not graduating due to low grades. There are about 20 kids in this group, so you have to send 20 individualized letters.

Instead of generating a generic form letter, loaded with a bunch of “To whom it may concern”s and “Your child”s, you decide you want to personalize the letter.

You also don’t want to have to create a separate letter for each student (because of the mailing address at the top, you’d usually have to create a separate page for each student), and a mailing label for all of them too.

That’s where mail merge comes in.

A sample letter

Below is a screenshot of a sample letter I’ve written in Word. Read it over, paying attention to the blocks highlighted in yellow.

It’s our goal to have Word (and Excel) fill in those yellow blocks with the right information for each parent / student.

Based on the letter, we’re going to need the following data:

  • Parent’s full name (PARENT-FULL)
  • Parent’s last name (PARENT-LAST)
  • Family’s address (ADDRESS-1, ADDRESS-2)
  • Parent’s pronoun (PRONOUN)
  • Child’s full name (CHILD-FULL)
  • Child’s first name (CHILD-LAST)

These few pieces of data will be kept in an Excel spreadsheet that we’ll link to Word.

If you’ve ever seen / used mail merge, you might know that Word has a built-in tool for storing this data. So, why am I saying we’ll need to use Excel instead?

It’s because I find that the data is much easier to use when it’s in Excel. Word’s tool is a bit clunky!

How to get the data

Each school district is different, and the ways schools store data varies.

The best advice I can give here is to talk to your principal, pupil accountant, and/or IT person about getting the data you need.

Exporting from student management systems

Many schools have a student management system (SMS), a piece of software where student data is kept, in place to handle this. For instance, in the Detroit area, many schools use either PowerSchool or MISTAR for managing this information. And typically, data like this can be exported from that software into an Excel spreadsheet.

If you have access to a tool like this, great! You might be able to export the data yourself.

Otherwise, reach out to a colleague for help!

Manual entry into Excel

If you don’t have an SMS, or you don’t have access, you might have to type out the data yourself. I know that’s a bummer.

You might also be wondering: If I have to type it all out myself, why even bother with a mail merge? I can just type it into the letters myself. 

That is true. However, if you keep the data in a spreadsheet, you can reuse it in the future without having to type it again!

Overall…

…most schools, even underfunded ones (i.e., nearly all of them), have student management systems. If your school takes attendance digitally, chances are very good you can have the data exported!

Again, if you’re not sure, talk to your principal, pupil accountant, and/or IT person if you need help.

Formatting the data

Your data might not be 100% ready to use after you export it. Or, if you’re building from scratch, you might be wondering where to start.

Here are a few general tips you should follow:

  1. First, be sure your Excel document uses headers!
    1. Your headers must be in the top row
    2. Your headers should be descriptive (e.g., don’t use “Column_1”; use “Full_Name”)
    3. Your headers must be unique (i.e., don’t have two headings with the same name)
  2. Be sure you have all of the data you need. From my example above, we need:
    1. Parent’s name
    2. Parent’s pronoun
    3. Student’s name
    4. Address
  3. For both parents and children, it’s best to have their first and last names in separate columns. This is because we need to use the parent’s last name by itself, and the student’s first name by itself.
    1. If your names are in a single column (e.g., Last, First or First Last), you’ll need to split those names up. No worries though! There’s a quick way to do it. Watch my video on this technique here: https://excelyourself.org/2017/11/split-student-names-in-excel/
  4. The same applies to the address, too. We need the street name/number in one column, and the rest in a second. The link in step 2a will help with this!
  5. For the pronoun, your student management system might not have this. In my case, I just typed out the Ms. or Mr. myself. If you don’t want to be bothered, you could simply omit this part and have your salutation include the parent’s first name instead (i.e., Dear John, instead of Dear Mr. Smith).

 

Follow along with our free documents!

If you’d like to follow along with this post, download our free mail merge documents now!

 

Linking the data to Word

Now is when it starts to get real. We’ve got our data and our letter ready to go. Let’s bring the two together!

  1. Be sure you save your Excel file somewhere you can find it
  2. In Word, click the Mailings tab in the Ribbon. Then click Select Recipients > Use an Existing List…

          Note! You may be wondering why we didn’t use the Start Mail Merge option. With the method I use, we can bypass all of Word’s wizards and get right into merging our data.

  3. In the dialog box that opens, locate your Excel spreadsheet, select it, and click Open.

  4. In this dialog, Word is asking which worksheet your data is on. If your spreadsheet has more than one tab, select the one with your data. Most likely though, your spreadsheet will only have one, and it’ll be the only option there. Click that, and select OK.

         Note! There’s an option in this dialog box that says First row of data contains column headers, and it’s usually checked by default. If you followed my advice in the formatting section above, your data will have headers, so you can leave this box checked.

The dialog boxes will close, and it will appear as though nothing has happened. Don’t panic! Your data did link!

Now we’ve got to start putting it where we need it.

Using merge fields

Again, my method is a bit different from what’s typical. Instead of using the address block / greeting line options, I opt to insert the fields themselves. This allows greater control.

First, let’s take a look at the Insert Merge Field button in the Mailings tab of the ribbon.

You’ll see that the headers we used for the columns in our database appear here.

Now, all that’s left to do is begin inserting the merge fields in the right spots.

From the example above, we’ll overwrite all of the yellow boxes.

  1. Place your cursor where you want the merge field to go (in my example, I will highlight the yellow box and delete it first)
  2. Click Insert Merge Field
  3. Select the appropriate field

         For example, I’ll replace the yellow PARENT-FULL block with the PARENT-FULL merge field

  4. Repeat steps 1-3 for all fields needed

As you insert fields, you’ll notice they appear like this: <<PARENTFULL>>.  This is the stunt double for your actual data.

But, when we print the letter, Word will replace the << >> merge fields with the right information.

Would it be great if Word would allow us to see what our letter actually looks like before printing it, though?

Previewing the results

Once all of our merge fields are in place, let’s preview what each letter will look like.

This is what the letter looks like with all of the fields (I’ve bolded them):

Now, still on the Mailings tab, click the Preview Results button. You’ll notice that all of the merge fields are replaced by the data from the spreadsheet!

Our letter looks great!

And, you’re able to look at each result individually. Simply click the right and left arrows to page through each result.

Watch it

Here’s a quick clip which shows how to preview your results:

Printing your merged document

The last step is to get the letter to print each record (i.e., a letter for each person in the data set).

Caution! You cannot use the typical print functions to do this. If you just need one page from your merged document, normal print options (the print button, CTRL+P, etc.) are fine. But, to print a all of the records, you need to follow these steps:

  1. Still in the Mailings tab, click Finish & Merge > Print Documents

  2. In the dialog that opens, you’re given the option to print all records, or to be more specific.
    1. To get the number for the From and To fields, use the left and right arrows mentioned in the Previewing section above to page through the records. Note the number in the box between the arrows: This is the number of the record, and the number you’ll need.
  3. The normal print dialog will then open, and you can customize the settings like normal.
  4. After clicking OK, all of the records in your merge will spool to the printer and will print!

What’s the bottom line?

In a rush? Here are the main points from this post:

  1. Use Word’s Mail Merge feature to quickly generate unique documents for numerous recipients
  2. Get all of your data into an Excel spreadsheet
    1. Export it from your school’s student management system. Or, have a colleague help you if you’re not sure how.
  3. Clean up your data
    1. Ensure you have unique, descriptive headings
    2. Split data using the smallest chunks possible
  4. Link your data to Word using Mailings > Select Recipients
  5. Insert merge fields in the appropriate spots in your letter
  6. Preview the results using Mailings > Preview Results
  7. Print your documents using Mailings > Finish & Merge > Merge to Printer

 

Create your own letter with our free documents!

Use our free mail merge documents as a guide to create your own letter!

What’s next?

Here are three of our latest posts we think you’ll like:

FREE! The Ultimate Excel Cheat Sheet for School Counselors

Our one-page guide covers 8 simple Excel hacks all school counselors can start using right now. Download it for free!

One moment, please!