Using Google Sheets to Organize and Manage Your Humanities Data
Workshop Series Topics
- Setting up your table
- Tool 1: Code tables
- Tool 2: Data Validation
- Tool 3: Conditional Formatting
- Tool 4: VLookup (coming soon!)
- Tool 5: Column Statistics & Filters (coming soon!)
- Tool 6: Pivot Tables (coming soon!)
If you’d like to download the full workshop presentation slides, you can find them here.
So you’ve managed to successfully create a Google Sheets document, which you’ve begun to populate with your data. Or you’re using the sample dataset to practice your new skills. Either way, whether your goal is to create maps out of your data or find shortcuts to speed up data collection in the next steps of your work, read ahead to learn why a code table can be a useful tool to help you save time and effort.
A code table—just a table in a separate tab—is essentially a storage space for frequently-used data (the “codes”) that recurs in your primary spreadsheet. It is a way to organize repeated information without disrupting your primary data. You can use this table and other functions to auto-populate your spreadsheet, especially when working with large amounts of data.
Let’s look at an example. Say your dataset contains ancient artifacts from around the world. In each row, where you have artifact names, you decide that you want to include at which excavation site the artifact was found, the city/country where the site is located, its geographic coordinates, and the local archaeological team’s name. Eventually, after listing thousands of artifacts, it might get tiring to have to keep typing out the site name, city, coordinates, etc. every time you add a new artifact.
| Artifact | Year | Site | City | Latitude | Longitude | Team | |
| 1 | Obj. 1 | 1000 | Site A | ? | ? | ? | ? |
This is where your code table comes in handy. In your code table, under columns titled “Site,” “City,” “Latitude,” “Longitude,” and “Team,” you can store all of this information and never have to type it again. Now, each time you enter a new artifact into your primary spreadsheet (above), you can simply write the site name (Site A) and employ a VLookup function to auto-populate the rest of the cells with your data from the code table (below). This would bring in the information from “City” to “Team” that corresponds with Site A.
Here’s what that might look like:
| Site | City | Latitude | Longitude | Team | |
| 1 | Site A | City X | 12.3456 | 65.4321 | Team AA |
Your code table will also be useful when enabling the data validation tool as it will create an easy reference point when selecting the possible entries for a cell. More on this in Part 3 (Tool 2) of this workshop!
To create this sort of reference table, you don’t have to have finished entering all of your data into your primary spreadsheet; in fact, you can complete this step before even starting! As Part 2 of my 7-part Google Sheets for Humanities Data workshop, this lesson comes early on in the process so that you can use code tables to facilitate the rest of your data organization steps.
Tool 1: Code Tables
Let’s get started!
The image above is an example of what a code table can look like, based on my sample dataset (tab 2 in the sample spreadsheet). This table contains NYU residential hall names and their respective cities and geospatial data. Because my primary spreadsheet tracks all of the artworks fictionally displayed in these halls, the data in the code table will be used in every row.
All I have to do after typing a new artwork’s name into my spreadsheet is choose the residential hall where it is displayed and use a simple VLookup formula, then the rest of the information in columns B–G will appear! As such, storing it in a reference table saves will save me from having to retype this data dozens of times.
The examples provided here encompass a very small minority of possible uses for a code table. If your research looks at publishing houses, for instance, your reference table may contain author names, years, birthdate/birthplace data, etc., so that when you enter book titles into your primary sheet, you can simply pull recurring author information from your secondary table.
Keeping it in this tab enables you to simply employ a VLookup function (explained in Part 5 (Tool 4) of this workshop) to fill all of this data automatically into your main sheet. I recommend organizing your columns in the same order on both sheets as this will allow you to fill cells in consecutively when you use the VLookup function.
Hooray! You’ve now made not one, but two spreadsheets! Stay tuned for the next part in my series on using Google Sheets to help you organize and explore your Humanities data: Data validation. Though optional, this tool will enable you to create a dropdown list of choices for each cell in a column, minimizing the amount of typing you have to do and allowing you to focus on the less granular steps in the data collection process.
Have any questions? Suggestions? I’d love to hear your thoughts so be sure to leave them in the comments!
Nada Ammagui 