There may be times when you want to see part of your data. By filtering the data, you can quickly and easily see the information you need. Filtered data is not removed or deleted. It is simply hidden. It can be unfiltered, or unhidden, at any time.
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.
NOTE: If you have been working through the course, your file should be up to date and you will not need to copy the practice document. You may continue at Creating a filter.
- To begin copying the practice workbook, click the following button:
The file begins to open in a web browser. Before the document will open and can be edited, we need to make a copy of it.
- To copy the workbook file,
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.
NOTE: If you have been working through this course from the beginning, it is not necessary to open a new file.
- To open the workbook, if necessary,
Double-click Copy of SummerReading
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.
Currently, this worksheet is sorted by School and then by Programs Attended within each school. The top row of data, which contains the column headings, has been frozen and will remain at the top as we scroll through the worksheet.
Creating a filter
Before we can begin filtering our data, we have to create a filter. While this may sound like a complicated process, it is very simple.
- To create a filter, on the menu bar,
Click Data, Click Create a filter
- To deselect the data,
Click anywhere in the spreadsheet
The worksheet has visually changed. The column letters and row numbers are now green. This is an indicator that this worksheet has filters. There are also small filter, or funnel, icons next to each column heading. We will use these to filter the data.
Sorting filtered data
Let's use filtering combined with sorting to answer the question, "How many books did middle school students read?" We'll begin by filtering the data to see only the middle school students, 7th and 8th grade.
- To open the filter dialog box, in the workbook,
The Filter dialog box opens: (IS THIS A DIALOG BOX? OR DROP-DOWN?)
This dialog box gives us several ways to sort (ascending, descending, and by color) and filter (by color, condition, or value) the selected column.
Currently, all of the grades are selected (as indicated by the checkmarks). We want to see the 7th and 8th-grade students. We will clear all of the selections and then select just those grades.
- To clear the currently selected grades, in the dialog box,
- To select 7 and 8,
Click 7, Click 8
- To accept this filter,
The data has been filtered and now shows only the 7th and 8th-grade students. Note that the data is still sorted by School and Programs Attended within each school. Let's re-sort the data to put the grades together regardless of the school the student attends.
- To open the Filter dialog box,
- To sort the data by Grade, in the dialog box,
Click Sort A > Z
Evaluating filtered data
Now that the data has been filtered, we can begin to get answers about middle school students and their participation in the summer reading program.
When working in a spreadsheet, it is possible to make quick calculations by selecting the cells that contain the data you want to evaluate. Google Sheets will automatically perform some basic calculations on the selected data. The results appear in the lower-right corner of the screen.
Let's see how many books the middle school students read.
- To select the books read,
Press & drag H23:H45
With all of the Books Read selected, in the lower right corner of the screen, we see:
Knowing the number of books read is helpful, but let's see how many activities (Programs Attended and Puzzles Completed) the middle school student completed.
- To select the activities (Programs Attended and Puzzles Completed),
Press & drag I23:J45
The 7th and 8th-graders completed 267 activities.
Removing the filter
Now that we have the information we needed about the middle school students, we can remove the filter.
- To open the filter, in the spreadsheet,
- To remove the filter,
- To accept this change,
The data is once again in order by School and Programs Attended.
Filtering by condition
In addition to filtering by specific values, it is possible to filter by condition. Maybe we need to find a specific text string, a specific date, or a number greater than or less than another.
Let's create a conditional filter that will answer the question, "How many students read twenty or more books?"
- To open the filter dialog box, in the workbook,
- To open the Filter by condition options, in the filter dialog box,
Click , Click
The filter by condition options appear:
This list of conditions includes text options, date options, greater than, less than, and equal to options, and even the option to create a custom formula.
We are going to use the greater than or equal to condition to determine how many students read twenty or more books.
- To select the "Greater than or equal" to condition, in the conditions options,
Click "Greater than or equal to"
- To begin to insert a value,
- To insert the value, type:
The list is now filtered to show only the students who read twenty or more books. The list is still sorted by school and the number of programs attended.
Let's re-sort the list to show the student who read the most books at the top.
- To re-sort the list,
Click , Click Sort Z > A
The list of students who read twenty or more books is now sorted in descending order. We can see that a 5th-grade student named, Rieka Takaki, read the most books (44).
Let's remove the filter to see all of the data.
- To begin removing the filter,
- To remove the conditional filter,
Click , Click None, Click
The filter is removed. The list isn't sorted or filtered in any way.