What is Filter in Google Sheets?
The filter in Google Sheets is a powerful tool that lets you select the data to be displayed according to certain conditions. It helps you understand the data better without deleting it. You can hide it temporarily and display it again at any time. Google Sheets has many filter options that can change the way you view and manage your information. It helps in the efficient and organized analysis of data. Adding filters also helps you focus on specific information in your spreadsheet.
Let us look at the example below. We have the number of pounds of different fruits sold at a supermarket in a day. We wish to sort the fruits in alphabetical order. To apply a filter, select the range and click on the filter icon. Click on the inverted pyramid and choose “Sort A to Z.” Your data gets sorted as shown on the right side of the image.
Thus, applying filter in Google sheets is as easy as it gets!
Table of contents
Key Takeaways
- Filter in Google Sheets is a tool that filters out a part of your data based on specific conditions you set. Here, you don’t delete the data; you hide it temporarily.
- You can apply filter by color, filter by condition, or filter by values to filter out text, dates, numbers etc. Here, the most basic filter is by value.
- You can use the custom formulas option when you need to apply more complex conditions.
- To remove a filter in a range, just click on the already selected Filter icon to unselect it.
- In Filter in Google sheets, you can also use the options Is blank or Is not blank to filter by blank cells or hide them.
How to add Filter in Google Sheets?
Adding filters in Google sheets involves a series of steps, which we will demonstrate below.
Step 1: Range Selection
When you want to use a Google Sheets filter, select the range. It can be done the regular way: you select a range or click a single cell within your range and go ahead and turn the filter on. It will select the entire range automatically.
If you wish to filter only a part of your table, you must first specify the range of interest.
Step 2: Turn the Google Sheets filter on
There are two different ways in which you can do this.
– With filter option under the Data tab
Now, go to Data – Create a filter in the Google Sheets menu. Thus, the filter is enabled in Google Sheets.
– With Filter icon in the Google Sheets toolbar
You can also click the filter icon in the Google Sheets toolbar.
– With the shortcut key
For filter in Google Sheets shortcut, you can apply the short cut keys, Alt+D+F after selecting the column you want to filter. Then, use Ctr+Alt+R to open the filter drop-down menu within the first cell of the filtered column.
Step 3: Once you do this, each column header will have an upside-down triangle icon. It shows you have successfully created a Google filter.
Examples to use Filter in Google Sheets
In this section, let us look at the steps to set up different filters, sort the data, and make viewing the data more user-friendly. We will also cover the uses and benefits of using filter in Google Sheets.
Example #1 – Filter out blanks / non-blanks
Let us look at an example of how to filter out cells when certain cells are empty in a column. Look at the table below.
Step 1: First, go to one of the cells in the range. Now, go to the Google toolbar and click on the ‘Filter’ icon.
Step 2: The range gets selected. Now, click on the inverted pyramid of the sales made column.
Step 3: In the pop-up menu that appears, click on Filter by Condition.
Step 4: You can see the option “None.” Now, click on the arrow, and you get another drop-down menu. From here, choose the option “Is Empty.”
Step 5: Click on OK. Now, you can see the rows with the empty cells.
Similarly, to view only non-empty rows, click on Is Not Empty.
Example #2 – Filter text values
This option is used to filter based on text values such as specific words, parts of words, etc. Since we are dealing with text, you can also use wildcard characters and look for specific text. The two wildcard characters include:
- * for any number of characters
- ? for a single character
Let us use the same example as above.
Here, we look for words that begin with J in column A. For this, we do the following.
Step 1: Apply the filter by selecting the table and clicking on the “filter” icon. Now, click on the inverted pyramid in the first column to apply the filter.
Step 2: Under ‘Filter by Condition,’ choose the option ‘Text starts with,’ and enter J. Click on OK.
Step 3: Now, we obtain the desired result in the table.
Now, let us use the option “Text Does not contain” under “Filter by Condition.”
All rows without the letter ‘a’ are obtained.
Wildcard characters
To see how wildcard characters work, lets add another row of data, as shown below.
We add a filter, as shown below. Here, we are looking for all values beginning with J and ending with n, as well as any number of characters(*) in between.
Press OK. You will observe that values, as mentioned in the filter above, are obtained.
There are many more filtering options with text, which you can try for practise.
Exact Matches
Here, we set a filter that is similar to a filter based on cell value. But here, we do not tick off each value from the list; you directly enter the value you wish to view. Note that wild card characters cannot be used here.
Use the “Text is exactly” option under “Filter by Condition.”
Example #3 – Filter numbers
Numbers are an integral part of any Google Sheet and make up a significant part of the data. Filtering numbers helps us study the data in many ways, which can help our business. For example, you can look up all employees who have sales beyond a target value and suitably reward them. Now, let us look at the different ways in which we can filter numbers.
The different options for numbers under “Filter by condition” include:
- greater than
- greater than or equal to
- less than
- less than or equal to
- is equal to
- is not equal to
- is between
- is not between
Now, let us look at the employees who have sales between 200 and 300. Go to column C and click on the inverted pyramid. Perform the same actions as below. Under “Filter by Condition,” choose “Is Between.”
Enter the two boundary values (200 and 300) and check the result.
You can see rows displayed where the sales values are between 200 and 300 (included).
Example 4 – Filter dates
We can use the filter in Google sheets to filter ranges by dates. Similar to the above examples, you should choose “Filter by condition” and you get the following options.
- exact date
- date before the exact date.
- date after the exact date
Let us work on the dataset below and apply some of these conditions.
We should look up for those students who were born after 1/1/2001. Let us apply this filter. Click on the filter icon and apply the following filter against the Date of Birth column.
As observed below, once you choose any date condition, you get many options.
Since we are looking at the students born after 1/1/2001, select the option “Exact Date” and enter the date in the box below it.
Click OK and check the result.
Example #5 – Filter by color
Sometimes, you may use colors to present the data in an easy-to-understand way. When you do so, you can filter by color. Look at the table below. We have been presented with the rainfall in some cities, marking different colors for different levels of rainfall.
Filter by fill color
Let us check on how to filter by fill color. To filter data by fill color, follow these steps.
Step 1: Create a filter for the table by clicking on the Filter icon.
Step 2: Click on the inverted triangle of the March column and choose filter by color. Then, click on Fill Color.
Step 3: You can see the color options available in the column. Choose Light Yellow.
As observed, the filter by color fetches those Google Sheets city details, where the cells are light yellow in March.
Filter data by one color
Similarly, let us filter data in the table below by font color green.
First, we apply the filter. Go to column C and choose the filter option, as shown in the examples above.
Filter by color – Text color – Green.
Filtering by multiple colors in not yet possible in Google sheets. The above options allow you to filter only by a single fill color or font color. However, this feature is extremely useful to understand the data at a glance.
Example #6 – Filter with search
To filter with search:
Filter in Google Sheets allows you to search for data that contains some text, number, or date when you use the Filter option. Here, we’ll use this feature to show how students of a particular age can be filtered.
In the range shown above, click on the Filter icon in the Google toolbar. An inverted triangle will appear in the header cell for each column.
Click the drop-down for the column you want to filter. Here, we are filtering Column B.
The Filter menu appears. Enter the search term into the search box below “Filter by Values.” First, press on Clear to de-select all values.
In this example, we will type the number 12. The search results will appear below the box field as you type 12. Select 12. Click OK
The Google sheet will be filtered according to the search term. In this example, the worksheet is filtered to show only children of age 12. Thus, you can apply the search filter to the Google Sheets.
Example #7 – Filter by selected cell’s value or format
To apply a filter by a cell’s value:
Look at the table below.
Right-click on the cell that contains the value for which you must filter. Choose the option “Filter by Cell Value” which will help you choose the value to filter.
The filter will be applied to the column.
How to remove filter in Google Sheets?
Google Sheets is not as comprehensive as Excel when it comes to clearing a single column’s filter. Hence, we can only clear the filters of the table together.
To clear all types of filter in Google Sheets, click on the Turn off filter icon in the toolbar.
It clears all the filters from the range. Alternatively, you can also go to Data – Remove filter in the Menu.
However, there is a roundabout way to filter for specific columns.
Click on the Filter icon.
- If Filter by Values is being used, click Select All. So, the filter for that column will be removed.
- For Filter by condition, click the drop-down with the selected criteria and choose None.
Filter in Google Sheets not working
If you are facing issues when your Google sheets filters are not working as expected, check for the following information.
- Data Format: One reason your filter does not work correctly is the data format. If the data is not neatly organized in a table/list, it will not work properly.
- Ensure you are picking the right type of filter. Sometimes, picking the wrong filter also would make sure that the Google Sheets filter is not working.
- Double-check the condition you have specified. It should be an exact match to whatever you want, an incorrect condition may seem like the filter is not working.
- Finally, if the data does not satisfy the filter applied, you will not see any changes.
Important Things to Note
- When using filters, you can freeze the first row of your table, i.e., the headers that contain the filters. It is helpful as you don’t have to scroll all the way up to adjust filter settings.
- To filter a certain part of your table and not the whole table, you can select the range manually.
- If the condition for filtering is too complex, you can use the custom formulas that will fetch an accurate result.
- If using the Google Sheets filter is not enough, you can opt for the more comprehensive FILTER function, which copies the selected rows and puts them where you entered the formula.
- When filtering text values, wildcard characters are not allowed for the option Text ends with.
- You can only apply one filter view at a time.
Frequently Asked Questions (FAQs)
If you wish to apply multiple conditions in the same column, filter in Google Sheets by multiple condition can be applied. However, this can only be achieved by using the Custom formula option from the Filter by condition. Using operators OR and AND here can help you specify multiple conditions.
To remove the filters from Google Sheets, follow the below steps:
For removing the filter of a column alone,
• If Filter by Values is being used, click Select All. So, the filter for that column will be removed.
• For Filter by condition, click the drop-down with the selected criteria and choose None.
For an entire range, go to the Data tab in the top toolbar. Choose “Remove Filter. It will remove filters from all columns in the spreadsheet in Filter in Google sheets.
• Remember, you can only apply a single filter view at one time.
• Open a spreadsheet in Google Sheets. Click Data – Create Filter View.
• Select a filter view. Saving existing filter as filter view. Your filter will be applied to the spreadsheet.
When this is done, you customize your view without affecting the other users using the spreadsheet and save it for later.
Download Template
This article must be helpful to understand the Filter in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Filter in Google Sheets. Here we learn how to add filter in google sheets with examples to use it, reasons and points to remember. You can learn more from the following articles –
Leave a Reply