Using Excel with Collection Development

excellibrarians1Holly and I are in the process of writing a new edition of Making a Collection Count. As we go through the chapters and start really re-thinking the content and updating this book, I really wanted to make the idea of using statistics more palatable to those folks who avoid math at all costs. I used to be the queen of math avoidance. As I got older, went to business school and started working in libraries, I realized that I am actually more mathematically inclined or maybe more comfortable with with data, than my fellow library people.

Aside from budgets, there are lots of ways numerical literacy helps in managing a collection. What I am talking about is really thinking in terms of expressing library functions and trends with hard data. Modern technology makes this much easier than back in the 1970s when I was trying to pass algebra. Calculators and my personal favorite, Excel, can make analysis of library collections an absolute dream.

So how do you get over your aversion to analysis in library science? Use a small example of something you love and work it to death. Start with a small piece of a shelf list of a particular collection. Practice with excel in organizing this spreadsheet in various configurations.

Below is a screen shot of what I am talking about. This is a list of 19 records of Juv Fiction “A” in my collection. (I use Sirsi Dynix Symphony through our consortium. I downloaded a shelf list from Director’s Station which can provide all sorts of downloadable data sets.) I only have a handful of records, but really with Excel, size doesn’t matter! Practice with a tiny set to work out kinks and figure out Excel functions. When you get more confident, use a bigger data set.

First, you should maybe try some basic analysis to learn what your collection can tell you. Try calculating an average “age” (publication age) of this sample. Use the @average function over the range of dates. Sort your data set by number of circulations. Sort your data by date last checked out. Just a simple couple of Excel functions will begin to give you a start on some great collection analysis.

sample excel shelf list

(Click for larger image)

Of course a couple of hundred words and a screen shot is hardly a breakthrough, but often when I am talking to people they are paralyzed about starting somewhere. Think of this a first baby step into collection analysis.
Not sure where to get a data set? Talk to anyone in charge of your ILS and ask if you can have a report or shelf list downloaded. Jump in and start poking around a shelf list.



Originally published at on December 3, 2012.