Open In App

How to Filter in Google Sheets : Complete Guide

Last Updated : 29 Nov, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

How to Add Filters in Google Sheets : Quick Steps

  • Open Google Sheets>> Select your data range.
  • Go to the Data menu >>Select Create a filter
  • Click the filter icon in any column to sort or filter your data.

If you work with large datasets in Google Sheets, using filters is a must to easily manage and analyze your data. In this article, we will walk you through the process of creating filters, switching between filters, and sharing filters in Google Sheets to streamline your workflow. Whether you're organizing information, comparing data, or looking for specific values, filters provide a powerful way to focus only on the data you need. We’ll also cover how to delete filters when you’re done. This guide is perfect for anyone looking to improve their efficiency with Google Sheets, especially for teams or individuals who frequently work with complex data.

Disclaimer: Always double-check your data and filter settings to avoid any potential errors or confusion.

To delete filters in Google Sheets, open your sheet, click on the filter icon in the toolbar, and then tap "Remove filter" to clear the filters from your data. This will restore the original view without any active filters.

What is a Filter in Google Sheets

Filters help you locate specific information in your Google Sheets spreadsheet. When dealing with a large amount of data, finding a particular character or value can be challenging. Filters come in handy in such situations, allowing you to set criteria for Sheets to use when searching for the information you need. This way, you can narrow down the display to show only the data you are interested in, making it more manageable.

With filters, you can:

  • Sort Data: Arrange your data in ascending or descending order.
  • Filter by Condition: Display rows that meet certain conditions, such as containing a specific text, falling within a range of numbers, or matching a particular date.
  • Filter by Value: Show only the rows that contain specific values.
  • Create Filter Views: Set up different filter views for different users, allowing each person to analyze the data in a way that suits their needs without disrupting others.

How to Create a Filter in Google Sheets

Filters can be created both on Google Sheets web and the Google Sheets app on your phone. In this article, we will learn how to enable or add filters on the Google Sheets website. When adding a filter to your spreadsheet, keep in mind that anyone with access to the spreadsheet can see and use the filter.

Step 1: Select The Range

To initiate the process, choose a range of cells before creating the filter. To do this, open the spreadsheet in Google Sheets and manually select the cells. Begin by choosing a cell and dragging the cursor across your desired selection.

How to Filter in Google Sheets
Specific Selection

To choose whole columns, click on the headers at the top. To pick multiple columns, press and hold the Ctrl or CMD key on your keyboard while selecting the columns you want.

To choose all cells in the spreadsheet, click the rectangle at the meeting point of column A and row 1 outside the spreadsheet area.

How to Filter in Google Sheets
Full-Selection

Step 2: You can use Different ways to Create a Filter

There are three different ways to create a filter in Google sheets.

Way 1: Go to Data and Select Create Filter

After choosing the cell range, create a filter by clicking on the Data tab in the top toolbar and then choosing Create a filter.

How to Filter in Google Sheets
Data >> Click on Filter

Way 2: Using the Toolbar

Click on the More (three dot) icon and Select Filter option in the Toolbar.

How to Filter in Google Sheets
Click on the Filter Icon

Way 3: Using Right Click Menu

Right Click and Select the "Create Filter Option".

How to Filter in Google Sheets
Right Click and Select Create Filter

After this you will see the filter icons become visible at the top of the chosen columns. Customization of the filters for each column is then necessary, depending on specific requirements.

How to Filter in Google Sheets
Filters Created

Step 3: Choose Filter by Option

Click on the filter icon, now present on the selected header of the particular column. Here, you will get three option to add filter.

  • Filter by Color
  • Filter by Condition
  • Filter by Values
How to Filter in Google Sheets
Filtering Options

How to Use Filter in Google Sheets: 3 Methods

Filters in Google Sheets help you quickly view specific data by hiding irrelevant information, making it easier to analyze large datasets. Whether you're working with sales data, project status, or student records, filters allow you to focus on key details. There are three methods to apply filters in Google Sheets: filter by color, filter by values, and filter by condition, each suited for different needs.

Method 1: How to Filter By Color in Google Sheets

Learn how to filter cells based on background or font color, helping you quickly organize and analyze your data. Follow the below steps to create filter by color:

Step 1: Select Filter By Color

Choosing this option enables you to identify cells in a column distinguished by a particular color. Specify the color in Fill Color or Text Color to filter the desired data sets within the spreadsheet.

How to Filter in Google Sheets
Select Filter By Color

Step 2: Color Filter is Enabled

Upon selecting a color for column filtering, only rows and cells displaying the chosen color will appear in the spreadsheet.

How to Filter in Google Sheets
Color Filter is Enabled

Method 2: How to Filter by Condition in Google Sheets

Learn applying custom conditions like text, numbers, or dates to filter your data, making it easier to analyze and organize information. Follow the below steps to create filter by condition:

Step 1: Select Filter by Condition

This feature allows you to filter cells containing specific texts, numbers, dates, or formulas. It can also be used to isolate blank cells. To access additional options, click on the "Filter by condition" choice, revealing a dropdown menu where you can pick a condition. To select a condition, click on None.

How to Filter in Google Sheets
Select Filter by Condition

Step 2: For empty cells

To filter cells with or without content, choose Is empty or Is not empty from the dropdown menu.

How to Filter in Google Sheets
Is empty

Step 3: For cells with texts

If dealing with text, filter the column based on criteria such as containing certain characters, starting or ending with a word/letter, or having specific words. Options include Text contains, Text does not contain, Text starts with, Text ends with, and Text is exactly. Input the desired parameters in the text box below.

How to Filter in Google Sheets
For cells with texts

Step 4: For cells with dates

If the column has dates, filter based on Date is, Date is before, or Date is after. Choose a period or specific date from the date menu.

How to Filter in Google Sheets
For cells with dates

Step 5: For cells with numbers

If the cells have numbers, use criteria like Greater than, Greater than or equal to, Less than, Less than or equal to, Is equal to, Is not equal to, Is between, or Is not between. Enter desired parameters in the Value or formula box.

How to Filter in Google Sheets
For cells with numbers

Step 6: For cells with a formula

To find cells with a specific formula, choose "Custom formula is" from the dropdown menu. Enter the formula in the Value or formula box.

How to Filter in Google Sheets
For cells with a formula

Method 3: How to Filter by Values in Google Sheets

Know how to quickly display rows with specific values, helping you streamline data analysis and organization within your spreadsheets. Follow the below steps to Filter by values:

Step 1: Select Filter by Values

To filter columns based on numbers, you can use the Filter by values option, which makes the process easier. When you choose this filtering option, you will see all the values present in the selected column cells. By default, all these values will be selected, indicating that all cells are currently visible. If you want to hide specific values from the column, simply click on them.

How to Filter in Google Sheets
Select Filter by Values

Step 2: Choose the Values

Depending on how many values are in the column, you can click on "Select all" to choose all values or "Clear" to hide all values. Once you have selected your desired filter, click on OK at the bottom of the Filters overflow menu.

How to Filter in Google Sheets
Choose the Values

Your spreadsheet will now be organized based on the filter you applied using the options mentioned above.

How to Create a Filter View in Google Sheets

A Filter View in Google Sheets allows you to apply filters to data without affecting how others see the spreadsheet. It's a personal way to view and interact with your data while leaving the original data intact for other users. You can create multiple filter views in a shared document, each with different filters, and switch between them as needed. Follow the below steps to learn how to create your own filter view:

Step 1: Select Data

Click any cell or highlight the columns you want to filter.

Step 2: Go to Data and Select Create Filter View

Go to Data in the menu and choose Create filter view.

How to Filter in Google Sheets
Data >>Select "Create a Filter view"

Step 3: Apply Filters

Filter the data as you normally would. Here we have filtered the data based on condition of sales which is greater than 300.

How to Filter in Google Sheets
Filter Created

Step 4: Save the Filter

Click Save view to save your filter settings.

How to Filter in Google Sheets
Save the Filter

Step 5: Name Your View

Give your filter view a descriptive name, then click Save.

Note: If you have "View" permissions, you can only create temporary filter views. They won’t be saved or shared with others.

If you've already filtered data, you can save it as a filter view:

Step 6: Save as Filter View

Click Data and select Save as filter view.

How to Filter in Google Sheets
Data>>Select Save as Filter View

Step 7: Name and Save

Enter a name and click Save.

How to Filter in Google Sheets
Name and Press OK

How to Switch Filter View in Google Sheets

Switching filter views in Google Sheets allows you to save and switch between different filter settings without altering the original data. This is helpful when you need to analyze data from different perspectives or share a filtered view with others.

You can easily switch between filter views using the "Filter views" option under the "Data" menu. This ensures that your data remains intact while offering customizable views for better analysis.

Step 1: Go to Data Tab and Select Change View Option

Go to the Data Tab in the Ribbon and Select Change View

Step 2: Select Filter View as per your Need

Here you will see the list of Filter view created by you. You can now select your preferred Filter View.

How to Filter in Google Sheets
Data Tab>>Change View>>Choose your option

How to Share a Filter View in Google Sheets 

Sharing a filter view in Google Sheets allows you to share a specific filtered view of your data without changing the actual dataset. Simply create or select a filter view, then click "Share" to send the link, so others can access the filtered data.

Sep 1: Switch to the Filter View You Want to Share

Open your Google Sheets document. Apply or select the filter view that you want to share.

Open the Filter View you want to Share, and Go to Data tab, Click on View option and Select "Get the link to View"

How to Filter in Google Sheets
Go to Data Tab >> View Options>> Get the link to View

Alternatively: Copy the URL from the Browser

Once you're in the desired filter view, simply copy the URL from your browser's address bar. This link will automatically include the applied filter view.

Copy the link and share it with others, allowing them to view the filtered data.

How to Create and Use Filter in Google Sheet from Mobile

To create and use a filter in Google Sheets on mobile, open your sheet and tap the filter icon. Then, select the column you want to filter by and choose your filter criteria. You can filter data based on conditions like text, numbers, or dates directly from your mobile device.

Step 1: Open Google Sheets App

Launch the Google Sheets app on your Android or iPhone.

How to Filter in Google Sheets

Step 2: Select Spreadsheet

Choose the specific spreadsheet you wish to edit.

How to Filter in Google Sheets

Step 3: Go to Menu Option (three dots) Icon

Once the spreadsheet is open, tap on the 3-dots icon located at the top right corner.

How to Filter in Google Sheets

Step 4: Navigate to Filter Creation

Select the cell range option in the side bar that appears, and insert the cell range where you wish to add filter.

How to Filter in Google Sheets

Again click on the three dots icon and now there will be option for Create Filter on the side bar, click on it.

How to Filter in Google Sheets

Step 5: Apply Filters Across Columns

Filter icons will now be visible at the headers of all columns.

How to Filter in Google Sheets

Step 6: Customize a Column's Filter

To personalize a filter in a specific column, tap on the filter icon corresponding to that column.

How to Filter in Google Sheets

Step 7: Explore Filtering Options

  • A popup box will appear at the bottom half of the screen, providing various filtering options.
  • For "Filter by condition," select criteria and add parameters.
  • In "Filter by color," opt for Fill color or Text color and choose the desired color for filtering.
  • While "Filter by values" isn’t explicitly labeled, you can use it by selecting values from existing cells in the column, displayed under the “Filter by color” section.

How to Filter in Google Sheets

Once you've customized the necessary filters, tap on the checkmark at the top left corner to confirm the changes.

Step 8: View Updated Spreadsheet

The spreadsheet will now be rearranged based on the filters you've applied.

How to Delete Filters From Google Sheets

To delete filters in Google Sheets, open your sheet, click on the filter icon in the toolbar, and then tap "Remove filter" to clear the filters from your data. This will restore the original view without any active filters.

Step 1: Decide on Filter Removal Method

If you put a filter on a Google Sheets column and want to remove it, there are two ways: either remove specific details or completely eliminate the filter.

Step 2: Access Filter Icon

To reset a filtered column, click on the Filter icon located in the column's header.

Access Filter Icon

Step 3: Confirm Reset

A window will appear; click on OK to confirm. The column will return to its original appearance, but the filter icon will still be visible.

Confirm Reset

Step 4: Remove Filter Icon

If you want to eliminate the filter icon, go to the Data tab in the top toolbar.

Remove Filter Icon

Step 5: Choose "Remove Filter"

Select "Remove filter" from the options. This action will remove filters from all columns in the spreadsheet.

Choose Remove Filter

Conclusion

Using filters in Google Sheets makes it easier to organize and analyze data. Filters help find specific information in large datasets, improving collaboration. Whether on the web or the mobile app, the provided steps guide users through a smooth filtering process.

Understanding the benefits of filters and how they differ from Filter Views contributes to more efficient data management. Removing filters is a straightforward process, allowing for flexibility in adjusting spreadsheet views.


Similar Reads