There may be a time when your data is complete and clean, but not well organized. Maybe the data was entered by date not alphabetically. Or maybe the entries were added as they occurred not by ID number. No matter the reason, by sorting the data in different ways, we can more easily answer specific questions.
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
- To begin copying the practice workbook in a new browser window,
Right-click Copy practice workbook, Click Open link in new window
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
- To copy the workbook file, in the browser window,
Click 
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
Exploring the data
Before we begin asking questions and sorting the data to find answers, let's take a moment to become familiar with our worksheet.
- To open the workbook, if necessary, in the My Drive section of Google Drive,
Double-click Copy of Google Sheets_Sorting and filtering_Sorting data in Google Sheets.
This workbook contains one worksheet, Participants. This is a list of students who participated in a local summer reading program. There is identifying information about each participant including library card number, first and last name, card issue date, home branch, school, and grade. In addition, there is information about each student's participation: the number of books read, programs attended, and puzzles completed. Students involved in the program receive a t-shirt, so shirt size is also included.
This information is not sorted in any way. It isn't in order by card number or last name both of which would be obvious ways to organize this data. Without some type of order, it will be difficult to find the answers to questions asked about this data.
Freezing the header row
Before sorting data in Google Sheets, it is important to freeze the header row. This will keep the first row of the worksheet, which contains the column headers, from being sorted with the data.
- To select the header row, on the left side of the worksheet,
Click
- To freeze the header row, on the menu bar,
Click View, Click Freeze, Click 1 row
- To deselect the header row,
Click in the worksheet
There is now a thick line beneath the header row. This is a visual indicator that everything above the line has been frozen. This data will remain at the top of the screen as we scroll down and will not be included when the data is sorted.
- To see how the top row is frozen,
scroll down
Sorting by a single criteria
The first question we want to ask of our data is, "How many puzzles did Wanda Hart complete?" Because of the disorganization of the list, it may be difficult to find Wanda Hart's name and information. Let's sort the data alphabetically by last name so that we can easily find Wanda.
When sorting by a single crieria, it is not necessary to select the data to be sorted. In fact, selecting the entire column can cause errors in the sort. Instead, we will select a single cell in the Last Name column (column B).
- To select the Last Name column,
Click any cell column B
- To sort the data by Last Name, in the menu bar,
Click Data, Point Sort sheet Click Sort sheet by columnB (A to Z)
The data is now sorted alphabetically by last name. We can quickly find Wanda Hart and see that she completed one puzzle.
Let's add a new participant to the worksheet. Then we'll re-sort the data to include the new student.
- To correctly place the cursor, in the spreadsheet,
Click cell A48
- To add the participant's Library Card Number, type:
6655
- To move to the next cell, on the keyboard, press:
Tab key
- Continue adding the participant's information.
Heading Participant's information Last Name Blanton First Name Willow Card Issue Data 4/28/2009 Home Branch North School Smalley High School Grade 10 Books Read 9 Programs Attended 11 Puzzles Completed 4 Shirt Size Medium
Now that the new participant has been added, let's re-sort the data to put them in the correct place.
- To select the Last Name column,
Click any cell in column B
- To sort the data by Last Name, on the menu bar,
Click Data, Point Sort sheet Click Sort sheet by columnB (A to Z)
Willow Blanton is now the fifth student on the list.
Sorting by multiple criteria
Sorting can help us answer more complex questions about our data. Let's create a multi-level sort that will answer the question, "Which student from each school attended the most programs?"
Sorting by multiple criteria in Google Sheets is a different process than a single criteria sort. To begin we will need to select all of the data in the worksheet.
- To select all of the data in the worksheet, on the keyboard, press:
Control key + a
With the data selected, we can open the Sort range dialog box and design our multi-level sort.
- To open the Sort range dialog box, on the menu bar,
Click Data, Point Sort range, Click Advanced range sorting options
The Sort range from A1 to K48 dialog box appears:
In this dialog box, we can choose which column to sort by, add another column to the sort, and choose if we want to sort in ascending (A to Z) order or descending (Z to A) order. We can also indicate if our data has a header row.
Our data does include a header row. By checking the checkbox, we are ensuring that the headers are not sorted with the other data.
We are going to begin by sorting by School.
- To indicate that the data has a header row,
Click the Data has header row checkbox
- To sort by School, in the Sort by drop-down menu,
Click
, Click School
With the first criteria, School, selected, we can add another sort column to sort by the number of programs attended within each school.
- To add another sort column, in the dialog box,
Click
- To sort by Programs Attended, in the then by area,
Click
, Click Programs Attended - To complete the sort, in the dialog box,
Click
- To deselect the data,
Click anywhere in the worksheet
The Participant data is now sorted by school and then, within the school, by the number of programs attended.
Hiding columns
Because of the columns between School and Programs Attended, it may be difficult to understand the data. This can be remedied by hiding the middle columns, Grade and Books Read.
Hiding columns does not delete or remove the data. The columns are hidden and can be unhidden when the data analysis is complete.
- To select columns G and H, in the worksheeet,
Press & drag
- To hide the columns,
Right-click the selection, Click Hide columns G - H
- To deselect the selected data,
Click in another area of the worksheet
The column headers did not change. They now go directly from F to I. And there are two small arrows in place of the hidden columns.
With the intervening columns hidden, it is easier to compare the schools and number of programs attended. For example, we can see that Carmichael Elementary student, Rieka Takaki, attended 21 programs. The most for that school.
Evaluating data by selecting cells
When working in a spreadsheet, you may want to make some quick calculations without adding rows, columns, or formulas. In Google Sheets, we can select the cells we'd like to evaluate. The results of standard calculations appear in the lower right corner. Let's use this built-in feature to see how many students from Newton Elementary participated in the summer reading program.
- To select the Newton Elementary students,
Press & drag F11:F22
With all occurrences of Newton Elementary selected, in the lower right of the screen we see:
Because the selected cells contain text, not numbers, Google Sheets automatically counts them. If numerical data is selected, we will be given options for the calculation we wish to see.
- To select the Programs Attended by Newton Elementary students,
Press & drag I11:I22
In the lower right corner, we see the sum of Programs Attended:
- To see additional calculations for Programs Attended, in the lower right corner,
Click
- To choose the average,
Click Avg: 10.91666667
- To deselect the cells,
Click in another area of the spreadsheet
We have completed our evaluation of the Programs Attended by students in each school. Let's unhide columns G and H.
- To select the hidden columns, in the headers,
Press & drag
- To unhide columns G and H,
Right-click the selection, Click Unhide columns
- To deselect the cells,
Click in another area of the worksheet
The data is still sorted by School and number of Programs Attended.