Using Google Sheets to Create, Organize & Explore Your Humanities Data Nada Ammagui nada.ammagui@nyu.edu Postgraduate Research Fellow at WIDH@NYCDH 2021 Workshop Agenda 1. Introduction 2. Tutorials & demonstrations a. Starting in Google Sheets b. Code tables c. Data Validation d. Conditional Formatting e. VLookup f. Column Statistics & Filters g. Pivot Tables 3. Mapping your data Starting in Google Sheets Setting up Your Spreadsheet ➢ Start a new Google Sheets project “Blank spreadsheet.” ➢ Enter your data manually or import an existing dataset. ➢ Create column/row headers to distinguish categories of information. ○ Color, date, name, material, acquisition, location, coordinates, true/false, etc. *Images taken from sample dataset. Entering and Viewing Data ➢ Tips: ○ Hit the enter key to start typing in a cell and to jump one cell down. ○ Use option/alt + enter to create a new line in a cell. ○ Use the arrow keys to move to adjacent cells one at a time. ○ Hit the tab key to move one cell to the right. ○ Hide columns by highlighting them, right-clicking, and selecting “Hide column.” ○ Freeze rows by highlighting them, selecting “View” in the menu, then choosing “Freeze.” ○ Drag the bold blue corner of a cell to copy function and formats to cells. Tool 1: Code Tables Code Tables ➢ Create a table of codes to pull from (with recurring data) to be used for Data Validation and VLookup. ○ Helpful in saving you time and effort! *Image taken from sample dataset. ➢ Tip: ○ Install the Geocode add-on (by Awesome Table) to automatically add latitude + longitude coordinates. Creating a Code Table 1. Open a new tab at the bottom of your file and name it “Codes.” 2. Determine what information you will use the most and that recurs in every row (something with a more limited range of options). a. Example: the 15 authors published by a publishing house, the 20 venues at an art exhibition, etc. *Image taken from sample dataset. ➢ Tip: ○ Organize the information in the same order in which it will appear in your main spreadsheet to facilitate the VLookup process. Tool 2: Data Validation Data Validation ➢ Using the Data Validation tool will create a dropdown selection list in specified columns to limit the possible entries. *Image taken from sample data. Enabling Data Validation 1. Highlight the column or cells to which you’d like to apply this tool. 2. Right-click and scroll down to “Data validation.” 3. In the dialogue box that appears, edit your range (1) and inputs (2). *Image taken from sample dataset. 1 2 ➢ Tips: ○ Format the range as: [Tab]![StartLocation]:[EndLocation] ○ “Data!E2:E” indicates a range starting in tab “Data,” cell E2 and encompassing the rest of E. Tool 3: Conditional Formatting Conditional Formatting ➢ Conditional Formatting is a tool that enables you to format columns or cells according to your instructions for specific entries. *Images taken from sample dataset. Enabling Conditional Formatting 1. Highlight the columns or cells you’d like to format. 2. Right-click and scroll to “Conditional formatting.” 3. For the range, use the same structure as in DV. 4. Specify rules for Sheets to follow (i.e., if cell is empty, if text is exactly, if greater than, etc.). 5. Choose the format you’d like to apply, from editing the text to filling in the cell. *Image taken from sample dataset. Tool 4: VLookup Function VLookup ➔ =VLOOKUP([search_key], [range], [index], [is_sorted]) ❖Search Key: cell/information you want to look up in another table ➢ Use a VLookup formula to automatically populate your table with information found elsewhere in your sheet (code table!). ❖Range: where you want the function to find your information ❖Index: the number of the column that contains your return value ❖Is Sorted: TRUE/1 if approximate match, FALSE/0 if exact match ❖[( )]: used to group together the formula ❖[$]: used to create absolute references (so that the range does not change when the formula is applied elsewhere) Using the VLookup Function ➔ =VLOOKUP([search_key], [range], [index], [is_sorted]) ➔ ➢ Recall the VLookup function structure: ➢ So, to automatically fill in the information for columns F–K that corresponds to the value in column E, use this formula: *Images taken from sample dataset. Tool 5: Column Statistics & Filters Column Statistics and Filters Filtering columns Column statistics *Images taken from sample dataset. ➢ These two features allow you to get a bird's-eye view of your data. Viewing Column Stats and Adding Filters *Images taken from sample dataset. ➢ Column Statistics 1. Highlight your desired column, right-click, and select “Column stats” at the bottom. 2. Check out your spreadsheet statistics on the right side of the screen. ➢ Filters (2 ways) ○ Highlight your desired column and, in the toolbar, click on the funnel icon to add a filter. ○ Highlight your desired column and click on “Data” in the menu then select “Create a filter.” ○ Use the triangle icon in the column header to customize filters. Tool 6: Pivot Table Pivot Table ➢ A Pivot table allows you to summarize, sort, average, and count parts of your data, giving you an overall view of your spreadsheet. ○ This is especially useful for making heatmaps or generating % calculations. *Images taken from sample data. Creating Pivot Tables 1. Highlight the data (rows and columns) that you’d like to analyze. 2. In the menu, select Data → Pivot table to create one in a new sheet. 3. Select the data category to analyze and format it into rows or columns. 4. Apply a function under Values (COUNTA, COUNTUNIQUE, etc.) a. Values is where you can choose what about your data you would like to analyze (like the # of unique residential hall names per campus). 5. Adjust viewing settings in Values to see data as % of total data if needed. 6. Add latitude and longitude columns to later turn into a map. Editing Pivot Table Settings *Images taken from sample dataset. *Hide totals here to minimize clutter. Bonus Feature! Use the Explore option on the bottom right of your spreadsheet window to view various analyses and visualizations of your data generated by Google Sheets. Mapping Your Data Mapping Your Data ➢ Choose a mapping software (Google Maps, ArcGIS/QGIS, UMap). ➢ Import data sheet and pivot table as CSV into your software. ➢ Manipulate map features to highlight different elements of your data. *Images taken from sample dataset maps. All done! Congratulations on picking up new skills (or sharpening your expertise) in these Google Sheets features! I hope this short tutorial brought you one step closer to organizing and analyzing your data, no matter the subject.