Organizing Data

After you collect your data you will need to organize your data. On this page you will learn several Google Sheets features and formulas that you can use to speed up your data processing time.

Your To Do:

1. On the form responses sheet freeze the first row of data so you can always see the headers.

How to freeze a row

Freezing a row/column keeps the data in the same place as you scroll through the sheet. Freezing the header row is highly suggested when you are working on large sheets.

Steps:

1. Click on View.
2. Hover over freeze and select how many rows or columns you want to freeze.

Your To Do:

1. On the form responses sheet add a new column in between columns C and D. Label the column Full Name.

2. Use =concatenate to combine the first and last name columns.

How to use =concatenate

The CONCATENATE formula takes data from different cells in a spreadsheet and combines the data into one cell. This is super helpful for combining first and last name columns into a student name column.

Steps:

1. Click in the cell where you want to place the combined data.
2. Start writing the formula =Concatenate(
3. Click on the cell with your first value.
4. Click on the cell with your second value.
5. To place a space between values write “ “ in between the cell numbers. (E.g A3, “ “, A4)

Your To Do:

1. On the form responses sheet use Text to Columns to split the CUE Affiliate full name from the affiliate abbreviation.

How to use Text to Columns

Text to Columns separates fixed-width text into multiple columns. This is super helpful for separating a single student name column into first and last name columns.

Steps:

1. Highlight the information you want to split.
2. Use Command+C or ⌘ + C to copy the information
3. Click on the cell where you want to put the information.
4. Use Command + V or ⌘ + V to paste the information.
5. You will see a clipboard in the lower right hand corner. Click on it and select text to columns.
6. Choose your separator value. The separator value is where the data will be split at.

Your To Do:

1. Sort the Google Form response sheet so it is organized based on job title and then affiliate.

How to use Sort Range

Sorting a range is helpful when you are trying to organize your data based off the data in two columns. This is extremely helpful for sorting multiple class or grade level data. In the example below I sorted the data by period and then by scores.

Steps:

1. Highlight the range you want to sort.
2. Click on Data and then Sort Range.
3. Click on Data has a header row to see the names of your columns.
4. Choose the order you want to sort your data by. E.g. Student Grade Level, Last Name, First Name would first sort the data by student grade level, then by last name, and finally by first name.

Your To Do:

1. Install the EZ Query Add-on.

2. Use the add-on to create a sheet that shows the form responses for each role in education.

How to use EZ Query

Sorting a range is helpful when you are trying to organize your data based off the data in two columns. This is extremely helpful for sorting multiple class or grade level data. In the example below I sorted the data by period and then by scores.

Steps:

1. Click on Add-ons and select EZ-Query.
2. Click on create.
3. This will open up the EZ-Query menu.
4. Select Unique from column. This will create a sheet for every unique value in the column.
5. Select the column that has your unique identifier.
6. If you do not want a column from the original sheet in your new sheet you can deselect it.
7. Click on create to create your new sheets.

Your To Do:

1. On the summary sheet use the =counta formula to find the total number of participants in the session.

How to use =counta

In Google Sheets there are two ways to count data. If your data is all numerical you can use =count. If your data contains text, numbers, or a combination of text and numbers you need to use =counta. The a in the formula stands for anything. Using this formula will count all of the values in your range.

Steps:

1. Click on the cell where you want your count to go.
2. Start typing =counta(
3. Either type or click on the range that you want to count. Keep in mind that your headers will be counted when you use this formula. If you do not want to count your headers make sure that you start with the first row of actual data.
4. Type ) to finish the formula.

Your To Do:

1. On the summary sheet use the =countif formula to find the total number of administrators in the session.

How to use =countif

The countif formula is great for creating summaries of your data. The formula looks in a specific range for data that meets certain criteria.

Steps:

1. Click on the cell where you want to place the formula.
2. Write =Countif(
3. Highlight the range where you can find the data.
4. Add a comma.
5. Type “ and then the exact name the data you are looking for.
6. Close the formula by typing “)

BONUS!

If you need to count your data based on multiple criteria use =countifs instead of =countif. Adding the s to the formula allows you to add more than one criteria. For example if you wanted to count the number of EL students in Period 4 you would need to use countifs instead of countif.

Steps:

1. Click on the cell where you want to place the formula.
2. Write =Countifs(
3. Highlight the range where your first criteria is.
4. Add a comma.
5. Type “ and then the exact name the data you are looking for. Close the criteria with by adding ".
6. Highlight the range where your second criteria is.
7. Add a comma.
8. Type “ and then the exact name the data you are looking for. Close the criteria with by adding ".
9. Continue this process until you find all of the criteria you are looking for.
10. Close the formula by typing )

Your To Do:

1. On the summary sheet use the =average formula to find the average skill level for all of the participants in this session.

How to use =average

The average formula finds the average for a range of data.

Steps:

1. Click on the cell where you want to place the formula.
2. Write =average(
3. Highlight the range where the data you want to average is.
4. Close the formula by typing )

Your To Do:

1. On the summary sheet use the =averageif formula to find the average skill level for participants based on job title.

How to use =averageif

The =averageif formula finds the average for a range of data based on one criteria.

Steps:

1. Click on the cell where you want to place the formula.
2. Write =averageif(
3. Highlight the range where your first criteria is.
4. Add a comma.
5. Type “ and then the exact name the data you are looking for. Close the criteria with by adding ".
6. Highlight the range where the data you want to average is.
7. Close the formula by typing )

BONUS!

If you need to find an averages based on multiple criteria use =averageifs instead of =averageif. Adding the s to the formula allows you to add more than one criteria.

Steps:

1. Click on the cell where you want to place the formula.
2. Write =averageifs(
3. Highlight the range that you want to find the avearge of.
4. Add a comma.
5. Highlight the range where your first criteria is.
6. Type “ and then the exact name the data you are looking for. Close the criteria with by adding ".
7. Highlight the range where your second criteria is.
8. Add a comma.
9. Type “ and then the exact name the data you are looking for. Close the criteria with by adding ".
10. Continue this process until you find all of the criteria you are looking for.
11. Close the formula by typing )