Doing things for others should make you happy. And designing spreadsheets has been known to make me happy. So creating a Christmas gift-buying spreadsheet template allowed me to achieve a briefly blissful state of flow.

Why spreadsheet Christmas?

Lots of other people have tried to create Christmas list templates and unfortunately none of their efforts really worked for me.

Jason Fitzpatrick’s LifeHacker article on creating a simple gifting template gives at least one good reason – namely, making sure you don’t buy the same person the same presents year on year.

For me, it’s about tracking how much I’m spending, making sure I’m spending a fair amount, and making sure I’m not forgetting anybody. I also like to use the spreadsheet as a scratchpad for ideas and price comparison.

How does it work?

You can copy my viewable-to-anybody Christmas list template and try it out for yourself.

Unfortunately, there’s been an announcement that Google Drive’s template gallery will close in early 2017. So publishing my sheet to the web and providing the link here is the only way I know of to make it public.

The sheet consists of four tabs:

  • Person Summary
  • Shopping List
  • Status schema
  • Priority schema

Only the top two sheets need completing unless you want to customise the other elements.

To use the sheet, you actually need to begin with the Person Summary.

Add people you want to buy for

On the Person Summary sheet, you list who it is you want, or might want, to buy Christmas presents for.

Name who you’re gifting

Use the person column to name a person you want to buy for.

You can add a name by typing directly in the column.

Decide their importance

Decide how important it is that you buy them a Christmas present by filling out the “Priority” column.

It's possible to use the built-in drop-down menu to select the priority you want to give the person.

The schema I use for this column is the MoSCoW schema – a choice between “Must!”, “Should.”, “Could…” and “Would/Won’t”. This plain English way of prioritising helps me think about the people and presents more clearly, rather than something more abstract like “High”, “Medium”, or “Low”.

However, you can change this by going to the Priority schema tab and putting in your own values if you want. The drop-down menu should change to reflect your choices.

There are people I feel I must buy for – mostly family. And then there are people I feel I should buy for, because they have bought me a present in the past or for other reasons. I also have a “Could…” option for people it might be nice to buy for if I can spare a bit more money and I’m feeling generous – friends I haven’t seen in a while for example and for whom I have seen the perfect gift.

I also allow a “Would/Won’t” option for those I’ve decided, usually by mutually agreement, that I won’t be buying for so I can tick them off my list.

Decide what they’re worth

The “Allowance” column is there for setting a budget for each person.

Any number typed in should be formatted as currency.

The total allowance for everyone on your list – which should be the amount you’re willing to spend overall – will appear at the top of the column.

Keeping tabs

You can check you haven’t exceeded your total by coming back to this tab and looking across to the “Spent” column. I’ve used conditional formatting so that exceeding the budget will show up in red. Otherwise, it will show green, even if you’ve spent nothing. (You might be passing on a gift you’ve received yourself, for example – mission still accomplished.)

The total amount spent appears in the header at the top.

But in order for this to work you need to add the items you’re buying and their prices to the “Shopping List” tab.

Shopping around

The “Shopping List” sheet is designed to facilitate thinking about what you might buy as well as what you’ve bought.

Present ideas

Every year, I try and think of lots of ideas for what I can get people Christmas. Even if somebody has requested something of me directly or put something specific on wishlists, I like to think about what I can get them that would be specifically from me.

That’s why the first column in my spreadsheet is “Status”. It doesn’t just include “Bought” and “Wrapped” – although they’re the two statuses I most want to get to for each person.

You can select "Considering" or any other status from the drop-down menu in the Status column.

It also includes values like “Considering” for when I’ve got some ideas but am not quite or for when I know I want to get a person something but I’m shopping around for the best price or deal.

On top of that there’s “Requested”, for presents I know that person will want, perhaps because they’ve asked me or because I’ve studied their wishlist. I’ve even included a status for “Abandoned” for an idea that seemed a good one but just wasn’t practical – that way the idea stays noted for future years.

I change the status depending on what I’ve bought, wrapped or given up on.

Who is it for?

You should be able to select the name of anybody on your people list by using the drop-down in the "For" column.

I use the “For” column to select who from my person list it is for. That’s why it’s good to start by noting down all the people you want to buy for.

“It’s the thought that counts…”

In addition to noting down ideas for presents I also note down the price, where I can get it from and even why I’m getting it.

You can enter price and retailer in the "At" and "For columns".

The “At” and “From” columns handle my desire to get a good deal and also make sure, on the other tab, that I don’t exceed my allowance in each case (although I invariably do by a bit).

The but and because

The "Because" and "But" columns enable you to outline any reasoning behind the choice.

The “Because” and “But” columns are for laying out positives and negatives about any ideas I have. I write in the “Because” column why I think the present idea is a good one (for example, this is a great book that I really enjoyed) and the “But” column I explain to myself the risks of buying it (for example, “this person probably doesn’t have much time to read”).

For each gift I’ve telling myself a mini-story about why I might buy it – or why I’ve bought it.

The "Surprise" column allows for an extra note about whether or not the recipient will be expecting the gift you've got them.

I also have a little column to note whether or not the present will be a surprise.

No time like the present…

I’ve been using this template for the last three years, only tweaking it slightly each year.

It occured to me that I should take a short break from my late Christmas shopping splurge this afternoon to write this post and share my process for Christmas shopping. This is probably the nearest thing to a festive post that I can manage on a blog focused on development and writing!

I’m sure there are more improvements I can make. If you can think of any and have a Twitter account, feel free to let me know about your own list-making or present-buying process or what you would do to make my spreadsheet better.

Now I’d better get back to the presents. Fewer than two weeks left!