Making heat maps with spreadsheets

http://commons.wikimedia.org/wiki/File:Geothermal_heat_map_US.pngSpreadsheets have a lot of neat ways to graph things, but there’s one common type of graph that you won’t find on the built-in list: heat maps.

Most people are used to the idea of heat maps, if not the name. One example is the colored maps you see on weather reports, where the redder an area is, the hotter it is. Although these same visualizations are used to depict things other than heat, such as humidity, precipitation, crime rates, and so on, the type of visualization is called a heat map.

Even though it’s not a built-in function, it’s pretty easy to get modern versions of Excel to make one for you, and possible to other spreadsheet programs do so as well, although somewhat more tediously and therefore less precisely. All you have to do is take advantage of conditional formatting. Continue reading Making heat maps with spreadsheets

Tracking greeting cards

By Serjmooradian at en.wikipedia [Public domain], from Wikimedia Commons Another useful little holiday tool I’ve developed is a card tracker. If you’re of the school of manners that says you shouldn’t send the same greeting card to the same person two years in a row, or to two people who are likely to visit each others’ houses, then you’re faced with the question of what to do with any leftovers. You can throw them away, you can try to give them away, you can try to find some other crafty re-use for them, or you can try to track who you sent each one to with some sort of inventory. Continue reading Tracking greeting cards

Holiday gift lists

By Bart Kelsey (SuperTuxKart) [CC-BY-SA-3.0 (http://creativecommons.org/licenses/by-sa/3.0)], via Wikimedia Commons

Time to get back to the basics after that long foray into complex formulas, and there’s not much more basic than inventories. And, given the time of year, I thought it might be good to discuss gift lists.

While I’m pretty good at organizing things, especially non-physical things like information, I’m pretty bad at remembering things. I can’t even remember everything I’ve got sitting in a draw to give to people this year, much less what I gave people last year. And the year before that? Hah.

So I try to keep an inventory of what I’ve given to various people, at various points. That way I don’t wind up looking at books in a series and thinking “Did I give him 7 and 8 last year, or was it 8 and 9?” or at t-shirts thinking “Did I already give her one with this design? I remember thinking she’d like it…”

In my opinion, for a gift inventory you just need one header row, and one or two header columns. The row has the years, the first column has the person, and the second (optional) column has the holiday type. For example:

Person Event 2009 2010 2011 2012 2013
Mom Xmas
Dad Xmas
Brother Xmas
Sister-in-law Xmas
Nephew Xmas
Nephew Bday

In this example all the adult members of the family get Christmas gifts, but the nephew (still a child) gets both Christmas and birthday gifts.

There are several advantages to doing this in a spreadsheet instead of a a word processor. First, when you’re looking for a particular person, you only have to look through the names, not the lists of gifts. Second, when there get to be too many years of gifts, you can freeze columns. And third, it makes it a whole lot easier to see if, perhaps, you gave that same give to their sibling or someone else they’re likely to know, in a previous year.