Filtering data is one of the common tasks in data analysis in google sheets. Creating a filter is super easy and sorting the data based on filters works great in google sheets. But there are times when they do not work. It’s frustrating to debug the reason why it’s not working.
In this guide, I will show you a common reason for Google sheet filters not working. These issues have been reported by many users across different forums.
First, let me show you the most common fix to the problem of filters not working in google sheets.
How to solve google sheets filter not working:
1. Open the sheet which has issues
2. Click on Data from the top menu
3. From the available options, select Remove filter
4. This will remove the filter from the sheet
5. Now click on Data again and choose to create a filter
6. This will bring back the filter and fix the issue
The problem it solves is that many times the filter is not applied to the entire range of cells. This happens most commonly when you add or remove rows to the sheet.
Here is a video showing how to remove and then add the filter.
While this is the most common fix that works, you may want to dig into different methods if that does not work.
Let’s see some of the common issues and fixes.
Table of content:
- Google sheets filter not working after adding rows
- Google sheets filter not working while typing in search box
- Filter working only up to certain rows not all
- Refresh filter view
- Filter only for the current user, not all users
Google sheets filter not working after adding rows
So you had a working google sheet with a filter. Everything was working fine until you added new rows to the bottom because you ran out of the default number of rows.
How to solve it?
When you create a filter in google sheets it will automatically take the currently visible range of cells in a sheet as the total area for applying the filter.
If you have added rows later, then you need to tell the google sheets filter to apply to the new area you just created.
To do so, simply turn off the filter. Make sure you have not selected any range of cells but only the header row. Then go to the Data tab from the menu and select “Create a filter” from the options.
This will add a fresh filter and now this will apply the filtering to entire cells including the new rows.
Google sheets filter not working while typing in Filter in values
Here is the problem statement:
You have created a filter and you want to filter a column by certain value .
You clicked on the filter and in the search box, you typed a text but the column is not getting filtered by the text.
This happens to a few people when they forget to select the filter values after searching.
Here is a demo showing this problem: In the below video, we are trying to filter column D with the text “Pencil”. But its not getting filtered.
To solve this, first make sure you uncheck all selections.
To do so, click on “Clear” inside the Filter by values option. (Refer screen below)
Now you can search the text in the search bar and once the filter values show up, make sure they are checked.
If its not checked, then add check mark on it by clicking the search result. Then press OK button.
This will ensure that the column will filter only for the selected text. Here is the final result:
Filter working only up to certain rows not all
You have a sheet with filters applied. But when you try to apply the filter with certain conditions, it applies to specific areas and not the entire cell.
And you have not added any new rows as well.
This happens because when the filter was created, knowingly or unknowingly a range of cells was selected. Hence the filter only works on that range.
To solve it, you can remove the filter by going to Data > Remove filter. And then create a new filter and this time make sure no range of cells except the header is selected.
How to refresh filter view in google sheets
You can refresh the filter view in google sheets by simply clicking on any of the filters in a column and clicking the Ok button in the Filter options popup.
An alternative way is to remove the filter and create a new one. This will also refresh the filter view.
How to filter only for the current user not all users in google sheets
If you are working on a shared sheet, the best way to do data analysis is to create a copy of the sheet for your use. But if you need to use the same sheet for any reason and you want to create a filter then follow the below method.
To create a filter only for yourself, you can create a user-defined filter view.
- Go to Data > Filter views.
- From the options choose “Create new filter view”
- This will create a black border around the sheet ruler
- Click on the cell next to Name and change it to a custom name
As soon as you create a new filter view, Google Sheets will show a small guide popup to tell you that this is only for you and that others can not see it.
Now go ahead and create your filter and conditions for filtering.
FAQ
How do you refresh a Google sheet filter?
To refresh a filter simply remove the filters and create a new one.
How do filter views work in Google Sheets?
Filter views allow you to create custom views for applying filters. You can use this to create a custom filter only for one user which will not be shown to any other user in a shared sheet.
Which is the quickest method to ensure all filters are reset?
Click on the filter to open filter options. Then click on the Ok button without changing anything. This is the quickest way to reset filters.
Wrapping up
In this guide, we explored different scenarios of google sheets filter not working. You can use one of these methods to solve your issue. If it still is not solved, I suggest you write it in the comments. Also, you can post your issue on several forums like StackExchange, Google Sheets official forum, etc.
Appendix
[1] FILTER formula in google sheets – Link
Further Reading
New to google sheets ? Start here
More about Data analysis in google sheets:
