If you work with a spreadsheet, you’ve probably dealt with a large amount of data on a single worksheet.
Sometimes, you need to filter out only these values you currently need, removing the rest from the view.
Google Sheets offers special tools that can help you with that.
Filtering in Google Sheets is quite advanced, and there are a few ways you can do that. In this lesson, I’m going to show you how to do it.
Filtering table
In this example, we are going to use a table with a list of people.
In this example, there are two columns we are going to filter: “State” and “Earnings”.
The first thing you need to do is to apply a filter to the table:
- Click any cell inside the table
- Go to Data >> Create a filter
There is a quicker way to create a filter. At the right side of a toolbar, there is an icon, called create a filter.
Click it to apply a filter to all columns.
After you add formatting to a table, the visual formatting also changes. The first thing that changed is a contour add to the whole table.
There is a second thing that changed. Each column header has a little icon to the right of the cell.
Let’s click the filtering icon next to the “State” column.
There are several options here.
Sorting
You can sort strings alphabetically from A to Z and Z to A. You can also sort numbers. They are sorted from smallest to largest and vice versa.
You can also filter by colors. If the cells are transparent these options are disabled.
Filter by values
If you choose this option, you can check and uncheck different values. Each type of value is displayed once. Let’s say we want to display only people for Ohio and South Dakota.
In this case, you can click the Clear button, and choose OH and SD. Click OK to apply changes.
The icon in the “State” column change. The application informs you this way that the data is filtered by this column. You can have your table formatted by multiple columns.
Select all values in column D and fill them with a color.
Click the filter icon for “State” and display all values.
Filter by color
After you filled the cells, you can filter them by color.
If you click this option, only values with the light magenta 2 fill will be displayed in the table. In our case, Ohio and South Dakota.
Filter by condition
The last type of filtering is filtering by a condition. Here, you can filter only cells that are empty or non-empty.
There are also different options to filter dates, numbers, or text.
The last formatting option is a custom formula, where you can create your own formula.
Let’s filter earning between 60K and 120K.
To do it, click the filtering option next to Earnings. From Filter by condition, choose Is between and enter the minimum and maximum values.
This is the result.
You can see by looking at the icons, that the table is filtered by two columns.
Filter Views
Filters vs filter views
So far, we’ve been working with filters. There are some reasons you may want to use filters:
- You want everybody who opens your spreadsheet to see a specific filter
- You can also use the filter if you want your data to stay filtered
There are a few reasons, you may want to use filter views.
- You want to have multiple views
- You can name views
- You want other people to view the data differently. Filter view has to be turned on by each person, so they can view different filter views at the same time
- You can send different filter views to different users so everyone receives information that most important to them
- You want to create a copy of a filter view with similar rules
- You want to filter data, but you don’t have edit access.
Create a filter view
To create a filter view, you have to click a table and, inside a toolbar, click the Create new filter view button.
After you click the button, part of the screen will turn black.
We are going to filter people who live in Ohio and South Dakota. The same way as we did before, but this time using filter view.
In the upper-left corner, there is a filter view name. By default, it’s called Filter 1.
Let’s change it to SD_and_OH.
Click the filtering icon next to “State” and select only SD and OH.
Create the second filter view and call it Above 100K. This time choose only people who earn more than 100K.
After that, you are going to have two filter views. You can view them by clicking the little triangle icon, next to the filter button.
The active filter view has a tick next to it.
Remove filter view
There are two views in the sheet. Let’s remove one of them.
To do it, you have to activate a view first and then choose Delete.
The view is deleted and we are back to a standard sheet view.
Duplicate a filter view
We want to modify the remaining view, but also keep it.
What we have to do, is to create a copy of that view.
Select SD_and_OH and click Duplicate.
It seems that nothing happened. But if you look at the filter view name, you are going to notice that now we are inside a copy of the view.
Click inside this box and change the name to SD_and_OH_over_100K.
This view will display people in Ohio and South Dakota who earn more than 100K. Apply a filter in earnings (Filter by condition >> Greater than or equal to) and set value to 100000.
This is the final result:
Rename filter view and update a range
As you probably noticed there are two additional options: Rename and Update Range. They are not necessary because you can change both values by clicking inside the Name and Range boxes.
Quick access to view
The option we were using can be quickly accessed by clicking on the options button on the right side of the filter view.
Oat the very right side of a view you can click the X button to close a view.
