How to visualize historical data

Question

I have inherited a horrible spreadsheet with 4 years of data about when people participated in a given activity, by month. Each person would likely participate once every 18 months or so, but obviously there are variations. It's currently laid out with a single cell for each person/month combination, so there are 48 columns representing the last 4 years and 150 rows representing the people. When the person participates the background color of the cell for the month they participate is colored black. That's the only representation.

Rather than update the spreadsheet for 2011, I would like to get a new representation. I'll likely have to manually migrate the existing data to the new system, but that's an unrelated problem. Assuming I have a list of dates for each person, how can I visualize the data so that I can quickly assess the following:

• Who hasn't participated in a long time
• How long has it been since Person X participated
• How often has Person X participated

Writing a custom application to handle this isn't an option since I'll have to hand it off to someone in the next 1-4 years and they likely won't have the skills to maintain it. Because of that I'd like to keep it in a spreadsheet that I can host on google docs, or at worst maintain in open office and email around to the "need to knows."

Answer 1

I would setup your spreadsheet like a database (as @Shinrai suggested) with "participated date", "person", and perhaps "type of activity" as columns.

person   date_participated  activity_type
Joe      2010-12-04         cow tipping
Jimmy    2010-12-04         surfing
John     2010-12-16         compost toilet maintenence
Joe      2011-07-01         cow tipping
John     2011-07-03         compost toilet maintenence 
Sam      2011-09-30         surfing

(John's a trooper) Now the rest you could do with pivot tables and charts. You could also use the limited database-esque functions in Excel or roll your own. You can make charts of individuals participation, tables with date last participated (or months since last participation), and counts of total numbers of participation.