List Management Applications in Excel

Like our confused feelings for Tom Cruise, our love-hate relationship with Excel rages on.

We’ve railed against Excel’s ugly charts, inactive user community, and plans for the Excel 2007 user interface. Now here I am doing an ankle-snapping reverse to share some cool stuff we’ve done recently to create quick, easy-to-use applications for list management. First, we created an interface for a searchable database of people, like an alumni directory or yellow pages. More recently, we put together a tool to keep track of lists of people - like a classroom attendance log or mailing list manager. It’s all possible in Excel.

First a disclaimer: this isn’t a claim that Excel has proven to be an ideal tool. We ran into all sorts of limitations when we asked Excel to act like a database, work across multiple lists, and input new data. To a certain degree, MS Access may have been a better option.

That said, there are situations where Excel’s interface features (e.g. check boxes, drop-down lists), user familiarity, data manipulation, and VBA fit the bill. For instance, you may find yourself...

  • Wanting to give users a tool they are familiar with
  • Needing a solution that can be thrown together in weeks, not months
  • Facing users who don’t always have Internet access
  • Independent of a central database
  • Wanting to easily report and analyze your data

Here’s a taste of the two applications we put together recently:

Member Directory

* Search using the first letters of last name

* Filter search by various attributes

* Find members within a specified distance

* Map results in Yahoo! Maps

Directory Excel Tool

(Here, your selected list shows up in a Yahoo! map)

Directory Map

Attendance Tracker

* Search for individuals by first letters of last name

* Add one person at a time to the master list

* Create filtered lists and select which people you want to add to your master list

* Save, open, delete lists

* Export lists to a separate data file

* Report on attendance participation by event, participant, date, etc.

Attendance Tracker Excel Tool