Google Spreadsheets is a powerful web-based application that enables users to create, modify, update, and collaborate on data and information in real time. With many features, navigating can be overwhelming, but in this tutorial, we will focus on preparing your data for analysis using Google Spreadsheets. Imagine having a large dataset with thousands of rows, but it’s disorganized and difficult to work with. We will walk you through some basic steps to clean up your data for analysis. You can follow along with our sample spreadsheet, and we’ve included demonstration videos at the end of each subsection to ensure a clear understanding of the written instructions.
This tutorial covers the following:
- Making a copy.
- Cleaning your data.
- Sorting and filtering.
- Joining columns and splitting data.
Making a copy and cleaning your data
- Open this sample spreadsheet about public educational organizations in Connecticut and make a copy. Go to file, and click on Make a copy.
- To determine if the downloaded data has some inconsistency, spaces, or duplicates, go to Data on the menu and click on Data Cleanup -> Clean up suggestions. When importing data, if any errors are detected, this Google Speadsheet feature will promptly notify the user on the right of the screen. The cleanup suggestions may include removing duplicates or trimming white spaces, both of which can be easily resolved through this feature.
Short Instructional Video
Video 1: “How to make a copy and clean your data with Google Spreadsheet features,” Self-hosted video, 00:34 seconds, posted by Myri Ayala.
Data Analysis Made Easy with Sorting and Filtering
- Use the sample spreadsheet about public educational organizations in Connecticut.
- To filter data in a table, select all data and then click on the filter icon in the menu. Next, click on the filter header of the column where you want to apply the filter. This allows you to sort the data alphabetically, numerically, or in color order. Additionally, you can filter the data based on conditions such as =, ≤, ≥, or by date, to name a few. You can also create a custom filter condition to suit your needs.
Short Instructional Video
Video 2: “How to filter and sort data on Google Spreadsheet,” Self-hosted video, 00:16 seconds, posted by Myri Ayala.
Combining and splitting information for further analysis
- Use the sample spreadsheet about public educational organizations in Connecticut.
- To combine the address and town columns in your sample spreadsheet copy, follow these steps:
- Insert a new column next to the “Town” column.
- Name the new column something descriptive, such as “Address and Town.”
- In the first cell of the new column, enter a formula that combines the contents of the address and town columns. For example, if the address column is column A and the town column is column B, the formula might look like this: “=A2 & “, ” & B2″ (without quotes). This formula combines the text in cell A2 (the address) with a comma and a space, followed by the text in cell B2 (the town).
- Copy the formula down the entire column to combine the address and town for each row.
While we already know how to combine data in a column, there are times when we need to split data that is within a cell. This can be useful when information is separated by a common character, like a comma. Fortunately, Google Spreadsheets has a ‘split texts to columns’ option that automates this process and saves time and effort. Here are some steps to split data that is contained within a single cell:
- Select the cells or columns that contain the data you want to split.
- Click ‘Data’ in the top menu bar and then select ‘Split text to columns.’
- That’s it! Google Spreadsheets will automatically split the data into separate columns for you.
Short Instructional Video
Video 3: “How to join and split data on Google Spreadsheet,” Self-hosted video, 00:26 seconds, posted by Myri Ayala.