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.
Welcome to Part 3 of my Google Sheets for Humanities Data workshop! In this lesson, you’ll learn all about the data validation tool and how to use it in your spreadsheets. Check out the first two parts of this workshop series to create a spreadsheet from scratch and make a code (or reference) table on my blog.
And remember, if you’d like to practice these skills but are worried about messing up your own data, feel free to make a copy of my (fictional) sample dataset of artworks at NYU residential halls.
About this tool
The data validation tool in Google Sheets is an optional feature which allows you to specify a type of input (digits, date, text, etc.) or a range of inputs (i.e., a selection of names or words) for cells within a column or row. If you enable this tool, Sheets will require every entry within those cells to follow the criteria set by the creator/editor, whether in terms of format or content.
Technically speaking, Google Sheets will only “validate,” or allow, entries that abide by the rules or criteria set by the editor. Cells containing incorrect inputs will then display error messages or be rejected entirely as per your instructions. Basically, with data validation, you can control what kind of data can be entered into certain cells in order to mitigate errors and manage inconsistencies in your spreadsheets.
In my data organization process, I use this feature to help minimize spelling errors when working with toponyms. Using the dropdown list created by the data validation tool enables me to simply choose from the small range of entries I’ve allowed in a specific column rather than having to type out full names and phrases. After entering them once in my code table (which is the source of the range I allow in the dropdown list), I hardly have to do any work in the rest of my spreadsheet!
Other potential uses of this tool include:
- Allowing only entries formatted as MM/DD/YYYY and not DD/MM/YY;
- Requiring an entry of digits between 10 and 100;
- Tracking the progress of a specific task with a dropdown list of text options “Not Yet Started,” “In Progress,” and “Complete;”
- Limiting text in a cell to “TRUE” and “FALSE” or “YES” and “NO.”
Tool 2: Data Validation
Now for the fun part…
With data validation, you can create a dropdown list of entry options, as shown above, so that you can more quickly input your data. In my experience, this tool is most useful when the specified range is more limited, that way your dropdown list isn’t so long that it takes more time to scroll through it than it does to type out information.
This example, taken from the sample dataset, contains residence hall name options, as those are the only buildings I might ever need to type in this column considering the dataset I’m working with. Again, this tool is entirely optional, but is helpful when dealing with large amounts of data as it will flag errors in your dataset when they don’t abide by your entry criteria.
Follow these steps to get started:
For cell range (1) above, enter the sheet and cells where you’d like to define rules. The exclamation mark “!” signifies the end of the name of a sheet and the colon “:” indicates a range. “Tab” refers to the name of the sheet/tab that you’re working in—mine is called “Data”—and “start cell“and “end cell” refer to the first and last cells encompassed by your range.
So “Data!E2:E” can be read as: “apply this rule in the tab called Data, from cell E2 to the rest of column E.” I started the cell range in cell E2 because E1 contains the column header/title, which does not fit within my specified list. If I included E1, which reads “Residential Hall Name,” in the desired column range, I would get an error flag in the cell.
You could tailor the range according to your needs:
- To cover the entirety of column E in tab Data: “Data!E“
- To limit the range (for example to cells E2 to E10): “Data!E2:E10“
- To encompass multiple columns: “Data!E2:G50“
Alternatively, you can click on the little 4-square table symbol beside “Cell range” and “Criteria” to highlight your desired cells.
For criteria (2), you can specify whether cells will have to abide by a range of texts, numbers, functions, or dates, or whether you’d like a checkbox in the selected cells. Again, this will help you limit the range of entries possible in a cell so that you can have greater control over the accuracy of your data.
In the picture above, I’ve chosen to create a dropdown list using data validation. In order to create this list, I entered the range of cells containing my accepted entries: the names of the residential halls where fictional artworks are displayed. Because I have all of this data saved in my code table (tab 2 in the practice dataset), I entered the corresponding range: “Codes!A2:A12“.
Now, in column E in my primary spreadsheet (the tab called Data), I have created a dropdown list in each cell containing my possible entries.
To edit or delete any criteria you’ve applied using this tool, highlight the range again and select “Data validation.” You can edit criteria here or delete them by selecting “Remove validation” at the bottom of the settings panel.
Time to Practice!
Now it’s your turn to give this tool a try. In the practice spreadsheet, try adding a dropdown list to column E, Residential Hall Name, by following the steps above. What you want to be able to do is simply choose a residential hall name from the list every time you enter a new artwork rather than typing it into each cell. It should look like the right-hand picture here.
You’re doing great! You’ve picked up yet another handy tool in the vast toolbox of Google Sheets possibilities and are one step closer to mastering its inner workings. In the next part of my workshop series, you’ll learn about Conditional formatting, another optional feature that helps you visually organize your spreadsheet. If you’ll be spending dozens to hundreds of hours staring at your data, might as well make it colorful and clean!
And, as always, if you have any questions, thoughts, or suggestions, be sure to leave them in the comments 🙂
Nada Ammagui 