The filter is a feature available in the Spreadsheet, used to filter any data out of the entire data set. The data can be filtered based on any specific value, the combination of values, color, conditions, etc. to make visible only the filtered data.
Once you enable the filter option, it will start reflecting as filter icons on the column headers of your data set. It helps users, to filter and view only the data that they want to see, and the rest of the data becomes hidden.
In this tutorial, we will show you how to apply, remove and use the Filter in Google Sheets.
Apply Direct Filter in Google Sheets
In Google Sheets, there are two types of filter options to apply based on your requirements. One is the “Filter” option which we call a direct filter and the other one is the “Filter view”.
Filtering data using the direct filter option will also get the data filtered for the other users working on the same file but using the filter view option, the filtered data will get the data filtered only for you and the rest of the users will not get affected with the same.
- First, Open the spreadsheet where on the data set you want to apply the filters.
- After that, go to the “Data” tab and then click on “Create a Filter” and the filters will get applied.
- Or You can simply click on the toolbar “Filter Icon” to apply the filters.
Apply Filter View in Google Sheets
- First, Open the spreadsheet where on the data set you want to apply the filters.
- After that, click on the toolbar “Filter Icon drop-down” and then click on “Create new filter view”
- Once you click on the “Create new filter view” option, it will get applied and you will get the notification as shown below.
- click on “Dismiss” and filters will get applied.
Now your filter view has been applied, if you filter the data, it will only get filtered for your view. For others, it will not get filtered and they will see the entire data.
Filter Data by Value in Google Sheets
Let’s take an example of the below data set where we have monthly sales of different kinds of items by different sales persons and we want to filter the data on “Hardware” sales.
- First, Apply the filters and then click on the filter icon of the column header where you want to filter the data by values.
- After that, choose the “Filter by values” option and you will find all the unique values of that column tick marked.
- Now, click on “clear” to untick all the selected values.
- In the end, to select only the values based on which you want to filter the data, click on that values and they will get tick marked.
- Once done, Click OK.
Your data has been filtered based on the value (Hardware), which we have chosen in the above steps, and at the same time, you will notice the filter icon of the column header got changed, which indicates, the data is filtered based on that column value.
Filter Data by Color in Google Sheets
This filter data by color option is used when you have some values in the data set highlighted with color and you only want to filter those items to view them only out of the entire data set.
- First, click on the filter icon of the column header in which you have values highlighted with color.
- After that, choose the “Filter by colour” option and then hover the mouse on “Fill Colour” and then click on the color based on which you want to filter the data.
- The moment, you click on the color, the data will get filtered based on that color.
Filter Data by Conditions in Google Sheets
The filtering data by conditions option comes in use when you want to filter the data based on certain conditions. Like, you have values in numbers and you only want to filter the values which are more than or less than by a given value or to filter the values with a text containing a specific condition, or cells with only blank values, or by text that starts and ends with the given condition and so on.
- First, click on the filter icon of the column header wherein you want to apply a condition to filter the data.
- After that, click on the “Filter by Condition” option and then click on the up and down drop-down icon to expand the condition options.
- Now, here you can choose and apply any condition based on which you want to filter the data.
For example, we want to filter the data by values less than or equal to $25000 in the data set.
Choose the “Less than or equal to” condition and enter the value 25000 and click Ok.
Here, you will find the data filtered with values less than or equal to 25000.
Filter by Date in Google Sheets
If you have data that contains date values and you want to filter the data by specific dates, you can filter it using the “Filter by Condition” option.
Expand the “Filter by condition” and choose any of the given “date” condition options, you want to apply, and then enter the date value in the input field.
To filter the data, between two given dates, select the “Is between” option and then enter the start and end date.
Extend the Filter Range
In Google Sheets, when you add more new rows within the existing data set and the filter is already applied, the filter will auto-extend to include the new range. But sometimes if it misses extending, simply, go to the data tab and click on remove filter and then click on Create a filter” to apply it again.
Disable Filter & Filter View
To disable the filters, go to the “Data” tab and then click on “Remove filter”.
