When dealing with a lot of data and values in a spreadsheet, you may want to manage how you view them better. Google Sheets has a Filters tool that lets users analyze large data sets more efficiently by temporarily separating less significant data from the spreadsheet.
In this post, we’ll help you simplify the process of creating filters on Google Sheets, using different filtering options, explain how filters differ from filter views, and how to use filter views in a spreadsheet as well.
How to create a Filter on Google Sheets [on the web]
The ability to create filters is available on Google Sheets on the web and when using the Google Sheets app on the phone. In this section, we’ll stick to creating filters on the web and we’ll explain how you can do it on the Google Sheets app further below.
If you wish to add a filter to your spreadsheet, this filter can be viewed and accessed by anyone with whom you’re sharing the spreadsheet.
- Before creating a filter, you need to select a range of cells you can create a filter. For this, open a spreadsheet you want to edit on Google Sheets and select the cells manually for which you want to create a filter by selecting a cell and dragging the cursor throughout your selection.
- If you wish to select entire columns, click on the column headers at the top, and to select multiple ones, press and hold the Ctrl or CMD key on your keyboard when selecting your desired columns.
- To select all cells in the spreadsheet, click on the rectangle at the top left corner of the spreadsheet where column A and row 1 meet outside the spreadsheet area.
- When you’ve selected your desired cell range, you can create a filter by clicking on the Data tab from the toolbar at the top and then selecting Create a filter.
- When you do that, you will see filter icons at the top of the columns you selected to create them. You will then have to customize the filters for each of the columns based on your requirements.
- To start filtering a column, click on the filter icon present inside the header of that particular column.
- You will now be able to filter your data from the following options:
We’ll explain what each of these options does and how to use them below.
1. Filter by color
- When you select this option, you will be able to locate cells in a column that are marked by a specific color.
- You can specify the color inside Fill Color or Text Color to filter the data sets you’re looking for inside the spreadsheet.
- When you select a color to filter the column, only rows, and cells that feature the selected color will show up inside the spreadsheet.
2. Filter by condition
This option lets you filter cells that host specific texts, numbers, dates, or formulas. You can also use this filter to single out blank cells.
- To get further options, click on the Filter by condition option and this will reveal a dropdown menu from where you can choose a condition.
- To select a condition, click on None.
- From there, you can choose specific criteria from these options.
- For empty cells: If you’re looking to filter out cells with or without blank cells, you select Is empty or Is not empty from the dropdown menu.
- For cells with texts: If you’re dealing with textual characters, you can filter the column by looking for texts that contain certain characters, start or end with a word/letter, or have exactly the specific set of words you mention. You can do that by selecting any one of your desired options from – Text contains, Text does not contain, Text starts with, Text ends with, and Text is exactly.
- When you select these criteria, you will get a text box below to input the words, characters, or letters as a parameter.
- For cells with dates: If the column you’re filtering has dates listed inside its cells, you can filter it out from these options – Date is, Date is before, and Date is after.
- When you select any of these options, you will get a date menu where you can choose a period or specific date from the dropdown menu.
- For cells with numbers: If the cells in a column you’re filtering have numbers, you can choose from the following criteria for the cells to be filtered – Greater than, Greater than or equal to, Less than, Less than or equal to, Is equal to, Is not equal to, Is between, and Is not between.
- Selecting any of these options will reveal a “Value or formula” box where you can type your desired parameters.
- For cells with a formula: If you’re looking for cells that host a certain formula, you can filter the column using the Custom formula is option from the dropdown menu where you can enter the formula and view the cells that feature it.
- In the “Value or formula” box that appears below, type the formula you wish to search for.
3. Filter by values
- Perhaps an easier way to filter number-based columns is to use the Filter by values option.
- When you select this filtering option, you’ll see all the values that are specified inside the cells of the selected column. These values will be selected by default to indicate that all cells are currently visible. If you choose to hide certain values from the column, click on them.
- Depending on the number of values available in the column, you can click on Select all or Clear to select all the values or hide all the values respectively from the column.
- When you’re done selecting your desired filter, click on OK at the bottom of the Filters overflow menu.
- Your spreadsheet will now be aligned the way you filtered using the options above.
You can customize other columns of the sheet by choosing a filter option and inputting parameters the same way as above.
How to create a filter on Google Sheets app [Android/iPhone]
You can also use filters with the Google Sheets app on your phone.
- Open the Google Sheets app on your Android device or iPhone and select a spreadsheet you want to edit.
- When the spreadsheet opens, tap on the 3-dots icon at the top right corner.
- In the sidebar that appears on the right, tap on Create a filter.
- You will now see filter icons visible at the headers on all columns of the spreadsheet. Unlike on the web, you cannot create a filter for one specific column on the app. When you use the Create a filter option, Sheets will add filters to every column of your spreadsheet.
- To customize a filter in a column, tap on the filter icon corresponding to that column.
- You will see filtering options in a popup box that appears on the bottom half of the screen. Similar to the options on the web, you’ll get options to filter by condition, color, or values.
- When you select Filter by condition, you will be able to select your desired criteria to filter the data set and then add the required parameters to get the results.
- When you select Filter by color, you can select Fill color or Text color and choose the color you want to filter the values from.
- Although the Filter by values option isn’t labeled, it can be used by selecting your desired values from existing ones from cells in the column. These values will be listed underneath the “Filter by color” section as shown in the screenshot below.
- Depending on the number of values to choose from, you can use the Select all or Clear options to choose your preferred values to filter the data sets.
- Once you’ve created the necessary filters, tap on the tick mark at the top left corner to confirm the changes.
The spreadsheet will now be rearranged based on the filters you customized in it.
How to create a Filter View on Google Sheets
Filter View works similarly to Filters on Google Sheets without modifying the actual content or view of the spreadsheet. This way, you can analyze a specific set of data points without applying the filter permanently on the sheet. Filter View offers the same kind of filter options as Filters on Google Sheets; meaning you can use Filter by color, Filter by condition, and Filter by values options in a similar fashion to how you add parameters for filters.
- Before creating a filter view, select a range of cells where you wish to apply the view. You select entire columns by clicking on the column toolbar or select the entire sheet by clicking on the rectangle where column A and row 1 meet outside the sheet.
- When a cell range is selected, click on the Data tab from the top toolbar and go to Filter views > Create new filter view.
- You’ll now see a black bar at the top, outside the spreadsheet area with rows and columns now marked in dark grey tones.
- Similar to filters, you’ll see a Filter icon inside each of the column headers you selected to create the Filter view.
- To customize a column with a Filter view, click on a Filter icon inside the header of the column you wish to filter.
- Like with Filters, select how you wish to filter the spreadsheet view from these options – Filter by color, Filter by condition, and Filter by values.
- After selecting your filter option, specify the parameters that the cells should pass to be shown inside the spreadsheet.
- Once ready, click on OK to apply the filter view to the selected column.
- The spreadsheet will now be reorganized based on the filter view you set.
Depending on the number of columns you created a filter view for, you might have to repeat this step to customize them one by one. You can also create more filter views for other columns in a spreadsheet to see independent data sets at different times.
How to remove Filters and Filter Views on Google Sheets
Filters and Filter views work similarly in terms of functionality but if you wish to disable or delete them, the process is different for both of them.
1. Remove Filters from Google Sheets
If you had created a filter in a column with specific attributes, you can either remove the attributes to reset the filter or remove the filter entirely from the spreadsheet.
- To reset a filter on an existing column with a filter, click on the Filter icon in the column’s header.
- Now, click on the filtering option you chose to analyze the data points and select None from the dropdown menu that appears below the filtering option.
- To confirm the reset, click on OK.
- The column will be reset to its original view but the filter icon will still be visible.
- To remove the filter icon from the column, click on the Data tab from the top toolbar and select Remove filter.
Google Sheets will now remove filters across all the columns in your spreadsheet. When removing a filter from a column, you just need to keep in mind that filters across other columns will also be deleted.
2. Remove Filter Views from Google Sheets
If you have created a filter view, you have the option to close it for now without needing to remove it from your spreadsheet. You can also switch from one filter view to another inside the same spreadsheet or delete a filter view to stop it from showing on Google Sheets.
- To disable the current filter view from a spreadsheet, click on the x icon at the top right corner inside the dark grey bar at the top.
This will close the filter view and return it to the original view of the spreadsheet. - If you have multiple filter views and you want to delete one of them, apply the view you want to remove first. After you apply it, click on the Data tab at the top toolbar and go to Filter views > Filter view options > Delete.
The active filter view will now be deleted from the sheet. - If you want to delete all filter views from your spreadsheet, click on the Data tab at the top toolbar and go to Filter views > Delete all filter views.
All the filter views you have created inside the spreadsheet will now be deleted and you will no longer be able to apply them on Google Sheets.
FAQs
What are filters inside Google Sheets?
Filters let you find something that you’re looking for inside your spreadsheet on Google Sheets. If you have added a lot of data to your spreadsheet and having a hard time locating a certain character or value, you can use Filters to specify criteria that Sheets will employ to get you the desired results. This way, you can hide data from the Sheets that you don’t want to see so only things you search for are visible on the screen.
You can create filters based on different conditions, data points, or colors, and when you apply them, the newly formatted sheet will be visible not just to you but to everyone who has access to view your spreadsheet.
What happens when you create a filter?
When you create a filter inside Google Sheets, only rows and cells in the column that meet the criteria you mentioned in the filter will be visible inside the spreadsheet. The rest of the cells in the column as well as their respective rows will remain hidden for as long as the filter is applied.
Filtered columns will feature a funnel-like icon instead of the Filter icon inside the column header at the top.
Filters you create and customize aren’t temporary, meaning you can see them when you access the same spreadsheet in the future. This also means that anyone who has access to this spreadsheet can also view the filters you applied and change them if they have editing permissions.
If you wish to add filters to other columns when there’s an existing filter in one column of the spreadsheet, you will have to remove it first and then create filters on more columns. Similarly, if you have filters across multiple columns, you won’t be able to remove a filter from one column and keep the rest; removing a filter happens throughout the spreadsheet.
Filter vs Filter view: What’s the difference
Filters are useful when you’re analyzing data in a spreadsheet that only you have access to. If you’re collaborating with others in a spreadsheet, using filters or sorting columns will change the view for everyone who has access to the sheet and if they have editing privileges, they’ll also be able to change the filter on their own. However, users who only have viewing access to the spreadsheet won’t be able to apply or change filters here.
For a smooth collaboration experience, Google Sheets thus offers a Filter View option that people can use alternatively. With Filter Views, you will be able to create custom filters that highlight specific data sets without actually changing the original view of the spreadsheet. In contrast to filters, filter views don’t affect how the spreadsheet looks for others you’re collaborating with as they’re only applied on your end temporarily.
Unlike Filters, you can create and save multiple filter views to view different data sets. Filter views can also be applied by users who only have access to view a spreadsheet which isn’t possible with filters. You can also duplicate one view and modify it to show other data sets and you can also share them with others so that they can get the same view of the spreadsheet as you without altering the original view.
That’s all you need to know about using Filters on Google Sheets.
