Thursday, November 12, 2015

Organize Your Christmas Card List

It might be a little early, but due to a Groupon I got through a friend, I'm already thinking about my Christmas cards this year.

I haven't chosen them yet, but here is where I'll be ordering from and a sample of one of their cards. So cute!


I don't know about you, but for me, the absolute hardest part of sending cards every year is figuring out the list. Forget about choosing a design and picture and how to sum up your year into 1200 characters. Trying to decide who gets a card and who doesn't is impossible!

The first year we were married, I used our wedding invitation list and sent a card to pretty much everyone. That was over 80 cards! And I think we received about 5. Now, in fairness, my husband and I were newlyweds in a temporary apartment so I'm sure people just didn't have our address, but it taught me a lesson:

While you don't want people to feel left out, sending a card to your entire Facebook friends list is just silly.

With that in mind, I created this spreadsheet, and present it here as my first Excel tutorial. Yay!


You can access the full spreadsheet here:
Download Christmas Card List

Or, you can open it in a Google doc here:
Christmas Card List Blank

Get Started

As I said, I started with our wedding invitation list, since it already had all the names and addresses, and pasted them into a new Excel file. When you do this, make sure you keep at least the Name, Address, City, State, and Zip columns.

Here is where you need to enter all your addresses. Hopefully it's not too tedious!

Then I set up the years. I wanted to keep track of who we sent cards to and who we got them from (so I could send them a card too!). To help with this, I used frozen panes and filters. If you've never used them before, I recommend watching this video. If you just need a reminder, the explanation will probably be enough.



Freeze Panes

You can freeze your screen in a couple different ways: the top row, the first left column, or from any cell that you like. To do either of the first two, click "View" in the ribbon, then "Freeze Panes" and select your option. To do the last, click in the top left cell of the section you want to move. Then click "View" and "Freeze Panes." Check to make sure it's scrolling the way you want it to.

Filters

Those cool little arrows at the top of your spreadsheet allow you to select and sort your data. To set it up, first highlight the entire dataset including column titles. Then click Data and Filter. Voila! You can click Filter without highlighting first, but I find that Excel is sometimes so smart that it's stupid, if you know what I mean. When I don't select my data first, Excel isn't always the best at getting all my data, or has the wrong header row, or something.

A little tip: Filters are one of the things I often use with keyboard shortcuts. I hope to do more posts about shortcuts, but here's a quick introduction. Select the first cell in your dataset. Then hold down Ctrl and Shift and use the arrow keys to jump and highlight the whole section. Cool, huh? Now, hit Ctrl +Alt+T. Ta da! You now have filtered data. The other extremely helpful shortcut is Ctrl +Alt+C. This will clear all filters on all columns, so if you can't remember or find which column you filtered, this sets everything back.

Once you have all that in place, all you have to do is mark who you want to send to. I used a "1" as my marker, but you can easily use an "x," "yes," "send," or whatever makes sense to you.

Now, on to the fun part: Mail Merge!

Have you ever used Mail Merge? It's a super easy way print labels. Unfortunately, the steps are not intuitive and I have to reteach myself every year. So here is a video with me showing it and step by step instructions.


  1. Starting in Excel, use the arrows to filter your address list to only include those people you want a label for.
  2. Copy the list including the column names. Paste into a different tab in your spreadsheet. Make sure the entire list didn't copy. If you had it filtered, it shouldn't.
  3. Keep the selection highlighted, and give it a name. You do this by clicking in that top left box where it has a cell reference right now (probably A1). Type any name you want for the address list. I called it List2015 to make it easy.
  4. Save your file.
  5. Open Microsoft Word.
  6. From the Mailings tab on the ribbon, select Start Mail Merge and Labels.
  7. Look at your label package. It should say somewhere something like "Use Avery Template 5162." (That's the number I'm using.) Find that template in the list, double check the size, and click OK.
  8. Now to add your addresses. Click Select Recipients from the ribbon and choose Use an Existing List. Navigate to your Excel file and click Open.
  9. A new box should open with a list of all the named ranges in your file. Find the one you want and click OK.
  10. Alright, making progress. Now you just have to tell Word which fields to put where. Click Insert Merge Field and one at a time put in the correct fields. You can add spaces and commas just like normal. When you're done, the first "label" should look something like this, while the rest of the page says «Next Record»
  11. Almost done! Click Update Labels to change all those Next Records to your format, then click Finish & Merge. You can choose to Edit or just Print from there. (I personally like to click Edit and make sure it worked the way I expected.)

Hooray! You did it! Thank goodness for not typing (or worse, writing!) your addresses year after year. With this list and Mail Merge, you can just add or edit your list each year as you need to, and keep good track of who you send cards to and get them from!

Merry Christmas!

This is where I party

11 comments:

  1. Oooh, great tips, Megan. I need to get started on my Christmas cards. I'd love if you linked this up at our #ThisIsHowWeRoll Link Party next Thursday at 5am. We get over 350 links every week, so that's lots of other folks that will appreciate your Excel tips.

    ReplyDelete
    Replies
    1. Thanks, Susan. I appreciate you stopping by and will definitely link up on Thursday!

      Delete
  2. Wow! I need these tips! I always seem to wait till the last minute to get our cards in the mail. Thanks for sharing and for visiting my blog earlier.

    ReplyDelete
    Replies
    1. I know exactly what you mean. Sometimes I just hope people get a card before New Years. :) Thanks for visiting!

      Delete
  3. Thanks - I agree the list is the hardest part! I'm always amazed at how long it takes me to get New Years cards out the door!

    ReplyDelete
    Replies
    1. It is quite the process isn't it? I hope my list and tutorial helps. Good luck and thanks for visiting!

      Delete
  4. Great tips, and yes, the list is the hardest part! Thanks for linking up at Best of the Weekend!

    ReplyDelete
    Replies
    1. Hi Amy! Thanks for coming to see me and thanks for all the great recipes and tips on your blog!

      Delete
    2. Hi Amy! Thanks for coming to see me and thanks for all the great recipes and tips on your blog!

      Delete
  5. Great tips!! Thanks so much for sharing your tutorials. You've made me realize I definitely don't use excel to its full potential.

    ReplyDelete
    Replies
    1. Thanks, Emily. I think a lot of people don't realize how easy Excel can be for everyday tasks. I'll be posting lots more tutorials so stay tuned and let me know if there is anything in particular you'd like help with!

      Delete