Our Christmas Card and Email Manager uses some of Excel’s power to keep track of your Christmas messages, both sending and receiving. It’s available to download and use, for a limited time.
Microsoft has a Christmas Card template for Excel but, to be honest, it’s pretty lame. Just a simple table which assumes cards only (no emails) and without even a hint of Excel cleverness.
We’ve made a better list which acknowledges the existence of email Christmas greetings. It’s still simple to use while being a demonstration of a very useful part of modern Excel.
Say hello to the Christmas Card and Email Manager
There’s a single master list plus automatically filtered lists (in the sheet tabs) to easily keep track of
- whether you’re sending Cards or Email,
- which have been sent
- messages left to send,
- who has sent you a message
This is a simple demonstration of Dynamic Arrays and the Filter() command. The filtering can still be done with the familiar pull-down menus in the main list headers but Filter() can make many pre-set filtered lists that update automatically.
Any version of Excel from Excel 2007 onwards can open the workbook and use the first tab ‘Christmas Master List’.
However the other tabs use Dynamic Arrays and the Filter() function so you’ll need:
- Excel 365 for Windows/Mac
- Excel 2021 Windows/Mac
- Excel for iPad or iPhone
- Excel for Android
- Excel on the web
The Excel workbook can be shared with other people via OneDrive etc – handy for a couple or family collaborating on this festive chore.
Christmas Master List
This tab has the full list of people and is the only tab that you enter or change information.
The other tabs, as we’ll see later on, have different ‘read only’ versions of this master list.
Cards and Emails sent
We could have added an ‘Email Address’ column but didn’t want to add another complication when there’s a simpler way.
The ‘Address’ column is now called ‘Address or email’. Add either a postal address or email. We’ll filter the list to see just the emailed contacts or only postal.
We’ve kept the separate ‘Address’ and ‘City, St, Zip, Country’ columns but you might not need them both. Instead of copying full postal addresses (a time-wasting duplication), just type in a city or suburb. The full address is, presumably, in your contacts list and doesn’t need copying into Excel.
Enter the date that the card or email is sent.
This column is used to make the filtered lists Sent or NOT Sent.
Cards and Emails received
It’s important to remember who sent you a Christmas message.
The column Received? lets you choose whether a card or email came in.
Different views of your Christmas card list
The other tabs have different versions or filtering of the master list.
- Sent – all messages that have been sent – card or email
- NOT Sent – the messages yet to be done – card or email
- Received – who has sent you a Christmas greeting
- Cards – everyone who is listed to get a card (sent or not)
- Email – everyone to get an email (sent or not)
- Address Needed – blank address cell, that needs filling in.
All these lists are done using the Filter() function which shows only the parts of a table or array that match the test.
The formula is in cell A2 of each sheet. Here’s Sent tab example:
FullList – is table name for the master list.
NOT(ISBLANK(FullList[Card Sent/When])) – is the filter. It checks for non-blank cells in the column “Card Sent/When”.
The filters for the other tabs are:
The reverse of the ‘Sent’ test, dropping the NOT, in other words find blank cells.
Another simple not IsBlank() test, on the Received? column.
This is a little trickier because we need a way to distinguish email addresses from others.
IF(ISNUMBER(SEARCH("@",FullList[Address or email])),FALSE(),TRUE())
One way looking for an @ symbol in the Address cell. Do that by SEARCH() the address text for “@”, if that returns a number then there’s an @. ISNUMBER() tells Excel if the Search() result is a number or not.
If it is a number (i.e there is an @) return False() – meaning don’t show in the list. Otherwise True() or do show in the list.
The email only tab is the same search as Cards – we just swap the False() and True() parameters. It’s now “ , True(), False() “ instead of vice-versa.
IF(ISNUMBER(SEARCH("@",FullList[Address or email])),TRUE(),FALSE())
There are always a few people on an Xmas card list that you don’t have a full address for.
A late addition (you won’t see this in the above images) is a tab showing anyone without an address.
Leave the Address cell empty and those people will show up in this list.
The filter is:
ISBLANK(FullList[Address or email])
Jobs to be done
On right of the Master list tab is a summary of the message tasks yet to be done.
- Addresses needed
- Yet to be sent
The count is done with two Countblank() formulas over a single column of the master list, for example:
=COUNTBLANK(FullList[Address or email])
Do your own upgrades!
Feel free to adapt and expand the workbook to your own needs.
We could have added more features but must balance ease-of-use for all comers against demonstrating new Excel options. In other words, if we add too much to the workbook, we get complaints that it’s “too complicated”.
We’d love to hear suggestions for improvements to this workbook. Or send us your enhancements via the Feedback page.
If there’s enough interest, we’ll add more to the workbook for Christmas next year!
Download the Christmas Card and Email Manager
For a short time, the whole Christmas Card and Email Manager is available to all from this link.
We’ve checked and rechecked the download for viruses and other nasties. However, you should never take anyone’s word for that and check for yourself. Windows Defender will automatically check for you. Leave the seat down. Check your seat for belongings. Call your mother.