What I Wish I’d Known – Spreadsheets

Today’s “What I Wish I’d Known” tip comes from CoDA intern Elizabeth Minor, whose experience in archaeological databases has taught her a thing or two about making spreadsheet programs like Excel and Numbers do the work for you. Here’s Elizabeth, on the Concatenate function.

Every archaeologist comes across this problem—you have a beautiful table in Excel (or Numbers) packed with great data, but then you have to clean it up into the right format.  Maybe you’re importing it into a database, or as in my example today, maybe you just need to cram some information into a dissertation footnote.  A great tool for taking several fields and stitching them together is the Concatenate function.  You tell it what the format is and where to take the information from, and the program will stitch the text fields together for you. These screenshots will come from the iWork program Numbers, but you use exactly the same method in Excel.

So for example, I have a giant table with rows of individual bodies from graves at the ancient Nubian site of Kerma. As I do statistical analysis, I want to show generalized data about all the women in my data set. Each column holds the number of associated finds of certain main object types, but not everything fits neatly into those categories. I need a footnote with a list of the unique finds, with their provenance and correct citation.  Right now all that information is held in three different fields, the columns Tomb, Reference, and Notes on Finds.

First, I will use the Concatenate function to make a complete entry for each individual.  Using the Function Browser will give you a prompt that tells you how to input your information correctly. In this case, each string of text should be separated by a comma.

Fill in your text strings, paying special attention to the separating characters (spaces, commas, etc.) that you put in between quotes. The new field called “Footnote entry” pops up, formated correctly! In reality, you usually have to do a little trial and error before it looks just right.

This is the step that can be the most useful for cleaning up tables for importing into databases. Maybe you need to label a text field as “Inventory Notes: ….” or “Previous description: …”. Or maybe multiple fields have to be joined, like Height, Depth, and Length put into a single description. Make your spreadsheet program do it all for you!

Now for the big reveal, use Concatenate again to generate a whole block of text for your footnote. It’s so easy to do, it may take longer to read the footnote than to make it!



Leave a Reply

Your email address will not be published. Required fields are marked *