Filtering data in Google Sheets

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. 

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.

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.

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. 

The Filter drop-down menu opens:

This drop-down menu 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.

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. 

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. 

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.

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. 

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?"

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.

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.

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). 

NOTE: Depending on the location of the active cell, it may be necessary to scroll up to see Rieka Takaki's information.

Let's remove the filter to see all of the data.

The filter is removed. The list isn't sorted or filtered in any way.