Dear Spreadsheet Whisperer: Po-em-G!

Dear Spreadsheet Whisperer,

I am a professor in the beginning phases of my next research project, which will include cataloging all of Julio Cortazar’s poems and sorting them into different categories. In some cases, the same poem appears in multiple volumes or anthologies. I need a spreadsheet system that will work for both me and my research assistant. Can you help me?

Signed, Axolotl Questions

---

Dear Axolotl,

I am currently sitting in a beautiful lakeside cabin in Northern Minnesota. Around me, there are birds chirping, wild flowers, pine trees, boulders and kitschy cabin decorations. I’m the only one awake (somehow still on East Coast time??) and there’s really no better setting for writing about spreadsheets and poetry. Thanks for being so proactive and making sure your data are alright! If everyone put as much thought and intention into their data coding and data collection efforts before a project began, well, I think we’d save a lot of time and frustration!

I had a lot of thoughts about how to help you organize your poems! Some of the questions came down the data concept of “one to many.” If every poem appeared in one (and only one) volume, then we wouldn’t be having this discussion! You would just have Poem + Volume and that’s it. However, things quickly got more complicated so we had to make some choices. I’m going to describe the three options I came up with, and why we chose the solution we did.

Option 1: “Drop Down” and give me 20!

This was my first idea. I could build a “dropdown menu” style interface in Excel so that when you are in the “title” column, you could pick from a list. This is really helpful because you can easily “normalize” the data, ensuring that every book is spelled exactly the same way, making it easier to sort and filter later. Downside? This doesn’t allow you to pick more than one.

Here’s a thorough tutorial on how to make dropdown menus in Excel. The technical term for this feature is “Data Validation.”

Option 2: “Column Me Maybe”

In this scenario, we don’t need any special Excel features or automation! We can just make a separate column for each volume or anthology. Then, as you add Poems (each poem gets it’s own row!), you can just “check off” if the poem appears in that volume. This is nice, simple, user friendly method, and it’s very easy to see “just poems in Volume X” or “just poems in Volume Y.” The downside of this method is that you could need a potential LOT of columns, which would take up a lot of space and cause for some tedious scrolling. Fortunately, you have a finite number of volumes that you’re tracking, so this isn’t a problem for you! Also, you’re not tracking OTHER data about the volumes in this sheet. If you wanted to have data like “Volume Name, Date, Number of Poems, Themes, Critical Acclaim, etc etc etc” regarding EACH volume, this system would quickly fall apart! Fortunately for us, you don’t! You are tracking your data about volumes a different way, and there aren’t that many volumes anyway.

Option 3: “Lookup” at the stars!

Last night at our cabin-side bonfire, we did some fabulous stargazing. You kept reminding everyone to “lookup!” Little did you know, lookup is a central concept in database management!

In option 2, I started to talk about what to do if you have a “many-to-many” relationship with complex attributes. For example, you want to link a Poem (title, year, theme, form, literary devices, etc) with a Volume (publication information, critical reception, years in circulation, etc etc). This is fundamentally the “use case” for a relational database. The best way to do that would be to have a separate list of Poems and a separate list of Volumes and then use a “lookup” to link them together. You can’t typically do this in Excel (or at least… I don’t know how) but you can do it easily, online, and for free with a platform called Airtable. We didn’t end up picking this option, but I like recommending it because it’s a great way to build a mini, modular, scale-able database system.

So, what did we decide? My recommendation is to go with Option 2 with a side of Option 1. Option 2 will give you the ease, flexibility, and easy sorting that you need, plus you don’t have that many volumes that you’re tracking so it will stay manageable. I would also encourage you to use some features from Option 1… we discussed having a dropdown menu for Themes, so that you don’t have quite as much “descriptive/narrative” data that you’ll have to re-sort into codes or categories later. Of course this project (like all research projects and all data projects in my experience!) will inevitably be iterative. Don’t be afraid to change your mind (or come back and ask for help!) if your data collection methods aren’t working for you. I can’t wait to see what you come up with! I’m rooting for you! And I hope you “axolotl” questions along the way 🙂

PS – read Cortazar’s famous short story about the Axolotl here!

PS — alternate pseudonym: Corta-czar!