The life of an analyst can be challenging. You will sometimes deal with situations where the dataset is large but you only need to focus on part of it.

You often want to see specific rows or rows that follow particular criteria and not the entire table. This will allow you to analyze the data precisely.

Filters can help you in such situations. There are various ways of filtering the data inside Google Sheets. Most of them are versatile enough to provide the custom view you need.

If you want to learn all the filtering techniques present in Google Sheets, then you must read this article until the end.

Yes, we promise you will learn every filtering technique available!

Get the example workbook using the above link to follow along.

The Dataset

We are using the same movie dataset from my previous blog all about sorting data. If you haven’t seen that yet, please check it out!

This dataset consists of fifteen rows and has five columns: Title, Release Date, Genre, Country, and Gross Revenue in USD.

Filter the Data From Filter Toggles

Filters allow you to restrict the rows inside your table and show only the data you want to see.

The biggest question is, how to apply filters in the data?

As we promised, there are several ways to filter data, of which the filter toggle is the easiest to use.

Select the entire range, including column headings, which you want to apply a filter on. In our case, the range is A1:E16.

• With the data range selected, click on the Data tab to open up all the options.
• Inside the Data tab, select the Create a filter option out of all. This option will apply a filter on the given range.

There is another way of applying the filter. It is pretty quick in comparison with the one discussed in the previous step.

Inside Google Sheets, below menu ribbon, there is a toolbar. In that toolbar, you can see a funnel-like shape. Click on that; it applies a filter on your selected data.

Note: Point to note here, You can also apply a filter on the given table without selecting the entire range. Just click on any header cell and hit the Create a filter option.

Once you create a filter, you will see a three lines that looks like an upsidedown pyramid. This symbol is your filter applied on the table.

You can use this to filter the table as per your needs. Let’s get this done.

Now, you can click on the filter icon of any columns to filter the data table based on values from that column.

Let’s try with the Title column.

1. Firstly, click on the filter icon. It will open up the filter menu.
2. Inside the filter menu, by default the Filter by values drop-down is active. You can see all the availabe filtering option there.
3. All values are by default checked as those are included in the table. To keep the first two movie names and uncheck everything else as shown.
4. Hit the OK button and tada! You’re done.

You can see in the image above that there is no pyramid-like filter icon in the Title column. Instead, there is a funnel icon suggesting that you have a filtered output.

The table is now reduced to only two rows based on the Title column. This is the most basic way of applying a filter to the data inside the google sheet.

Note: System has not deleted the remaining rows from the view. Those are just hidden for the moment as a filter is applied.

There are other ways of filtering inside the filter toggle. You can filter the data based on a cell or text color or a condition. These features are incredible. So let’s have a look at them.

Filtering the Data by Cell Color

It is no brainer that people usually use different cell colors to differentiate the data. Maybe some of the data has been colored green. It is also possible to filter the data based on cell colors.

Consider your data consists of colored rows, as shown in the screenshot above. You want to filter these rows only through the filter toggle. Follow the steps below to see how it gets done.

To filter the data based on cell/fill color, navigate to any header from the data and follow these steps.

1. Click on the filter icon present on the right hand side.
2. The filter menu will open up where you have to navigate towards the Filter by color.
3. There, you will see the Fill Color. Navigate to it.
4. You will see all the cell colors in your data. Click on the green to filter cells based on that color.

The output should look as shown in the screenshot above. All the cells are filtered based on the green color.

Filtering the Data by Text Color

Similar to the filtering by cell color, there is also an option available to filter the data based on text color.

As shown in the screenshot above, you have data with a few entries for which the text is colored red. You can filter these rows with the red-colored text through the filter toggle.

Navigate towards the Title column header and follow these steps.

1. Click on the filter icon to open up the filter menu in google sheets.
2. Navigate towards the Filter by color option. This opens up the posibilities to filter by color.
3. Navigate towards the Text Color option.
4. There are two options, you can filter the text with black text or red text. Choose red. This will filter all cells with red-colored text.

You can see the screenshot above and validate that only cells with red-colored text are now filtered.

This is again cool, isn’t it?

You can present to the user what you feel they should see. Besides, adding color to the cell or the text also lets them know that these cells are important.

Filtering the Data Based on Condition

Google Sheets filter toggle also allows you to filter the data based on a condition.

Inside the filter menu, you can see the Filter by condition dropdown, as shown in the screenshot above. Click on it.

A whole new dropdown list will appear with a default value of None as written in the condition box.

Click on the dropdown inside that condition box, and you can see a whole range of options, as shown in the screenshot above.

Note: Here, due to limitations, we have only shown options that fit our screenshot. You can use the dropdown on the right-hand side to see other conditions as well.

Covering every condition here is going to be huge. Instead, we will cover one condition associated with each data type for you. The filtering conditions available are for Text, Date, and Number data formats.

Filtering by Condition for Text Data

Suppose you want to filter out all the movie titles containing the word man in them. We can use the Filter by condition option to get this.

To filter the text that contains man in it, follow the steps below.

1. Go to the Title column click on the filter icon.
2. Click on the Filter by condition dropdown to open up the conditions.
3. Out of all the available conditions, select the Text contains condition. This condition filters those values that contain a specific text.
4. Inside the Value or formula box, enter the word man.
5. Finally, click on the OK button to apply the text filter to your data.

As you can see in the screenshot above, two movies are selected.

The film Batman Begins and Iron Man both have the word man in the title. Notice, the text conditions are not case sensitive, they don’t check if the text is in upper or lower case.

Note: There are several other conditions based on the text columns. You can try those on your own and check how the data changes. The procedure is almost the same for each one of them. So, it should not be a problem.

Filtering by Condition for the Dates

It is also possible to directly apply a date condition. You can filter the data based on a specific date, before a particular date, or after a particular date.

Suppose you want to filter in all the movies that are released after 01/01/2008 in your dataset. Follow the steps below to achieve this result.

1. Go to the Release Date column and click on the filter icon.
2. Click on the Filter by condition dropdown.
3. Out of all the conditions available, select the Date is after condition. This condition will enable you to filter the dates after a specific date.
4. On the next part, click on the exact date option, it will allow you to input a specific date manually. You also can set the values as Today, Tomorrow, etc.
5. Put on date as 01/01/2008 inside the Value or formula section.
6. Finally, hit the OK button to apply this filter on your data.

As shown in the screenshot above, only the movies released after 01/01/2008 are returned.

Note: Point to note down here. You have to feed the dates in google sheet compatible format while applying a date condition. Otherwise, it will not recognize the dates, and the filter will be of no use.

Filtering by Condition for the Numbers

Conditions can also be applied to the numbers.

You can filter numbers greater than, less than, or between, etc.

Suppose you want to filter in movies with revenue between $10,000,000 to $50,000,000. Follow the steps below to apply this conditional filter to the Gross Revenue column.

1. First of all, navigate towards the Gross Revenue column and open up the filter menu by clicking on the filter icon.
2. Go to the Filter by condition dropdown.
3. Click on Is between option. This condition returns all rows that are between two specified numeric values.
4. Inside the first Value or formula tab, put the value as 10000000. This is the lower limit.
5. On the next Value or formula tab, input the value as 50000000. This is the upper limit.
6. Finally, click on the OK button to apply this filter on the table.

The filtered output should look as shown in the screenshot above. It has returned all the movies that made revenue between $10,000,000 and $50,000,000.

Advantages of Filtering Through the Filter Toggle

• This is the most conventional method being used for filtering the data inside google sheets. Which makes it popular and everyone who knows uses Google Sheet should know this method.
• It is mouse-click based easy to use method.
• It allows you to filter the data based on cell or text color.
• You can easily filter text, values and dates by a condition.

Disadvantages of Filtering Through the Filter Toggle

• Even if you can filter the data based on text color or cell color, you can’t filter the data based on both at the same time. There is such option.
• While working with filter conditions has subtle rules that are not obvious such as text case does not matters when filtering text data.

How to Use Filter Views in Google Sheets

There comes a situation when you need to apply a filter on your data and then sort it to show the filtered output in a specific order (ascending or descending).

The challenge is that the original order will be disturbed even after removing the filter if you sort the data. This situation might not be ideal for you as you are losing your data’s original order.

To tackle this issue, you can use the Filter Views option in Google Sheets.

The Filter Views option works as a duplicate view of the given data where you can filter and sort the data based on your needs, and the original data is not altered.

Whatever changes you make are only limited to the current view that you create. It sounds incredible.

There are two ways of creating a Filter View. If you already have applied a filter on your data, you can make it through the filter toggle or the Data tab. In the other method, you can use the filter option through the toolbar.

To create a Filter View using the first method, follow the steps below.

1. Click on the Data tab from ribbon.
2. Click on the Filter Views option to open up the submenu.
3. Click on the Create new filter view option to create it.

Note: Please note that this method only works if you already have applied a filter on your data.

Now, what if the filter is not created on your data initially? You can still make a Filter View through the Create a filter option.

1. Click on the Filter icon dropdown. This option can be found inside the toolbar that is placed below the standard menu ribbon.
2. There you can see the Create new filter view option. Click on it to create a filter view.

This is how the filter view looks like. You must have noticed that the sheet interface has turned to a dark theme.

The filter view is a snapshot of the original data and is available only for me at this moment. Anyone else viewing the spreadsheet at the same time won’t see any of the sorting or filtering applied. The dark theme is just a way of sheets to let me know that this is a custom view available only for me.

Now, filter this view as per your interest. I will use it to filter out movies with a Gross Revenue of less than $25,000,000. You can literally put any filter condition of your own.

Now, rename this filtered view as Gross Revenue < $25M, and the system will save it under this name. See the screenshot above.

If I apply this view to the unfiltered data, it will return the movie’s data with a Gross Revenue of less than $25M.

To return to the original data view, we need to unfilter this view. Follow the steps below.

1. Go to the Data tab from menu ribbon.
2. Navigate through the Filter views side-drill.
3. Click on None to unfilter the current view.

Alternatively, you can also use the filter icon in the toolbar to remove the filter.

The output should now look like the one shown above. It has no filter views and returns the original data. The Filter View can be reapplied anytime you want to re-use it.

Advantages of Using the Filter View

• The main advantage of the Filter Views is that, it allows several people to work on the table simultaneously.
• If you are using the Filter Views, you are keeping the original data unchanged. Meaning, if any other user is opening the file simultaneously, they will see the original data.

Disadvantages of Using the Filter View

• Other users can view and edit your Filter Views even if they are on “View Only” permission for the worksheet you apply the Filter Views.
• You can access this feature only through the desktop browser. It doesn’t work through mobile phones and tablets.

The FILTER Function

If you thought that the only way to create a filter was using the previous standard methods, you were wrong.

There is also a powerful function for filtering data. You can use the FILTER function to create a filter.

The FILTER function takes a data range from your sheet and then returns the specified rows matching specific conditions. It has the following syntax.

FILTER Function Syntax

FILTER ( range, condition1, [condition2, …] )

• range – is a mandatory argument that specifies the data range on which you want to apply the filter.
• condition1 – it is again a mandatory argument that specifies a row or column based on which a logical test is applied to evaulate what to return.
• condition2 – is an optional argument where a second condition apart from first one can be added based on a column or a row.

Example 1 Using FILTER on TEXT Values

Suppose you want to filter out only those movies for which value in the Country column is Australia.

= FILTER ( A1:E16, D1:D16 = "Australia" )

The range on which the should apply the filter is A1:E16.

The filter criteria to apply is based on the entire Country column (D1:D16), where the country name is equal to Australia.

Explanation

The conditioning formula, D1:D16 = “Australia” runs on each row of the dataset and checks if the value for the Country column (D1:D16) is “Australia” or not. If the condition is met, the system includes that row in the view.

Note: column headers are not included in the filtered output when using the FILTER function on text values as those are also text.

Example 2 Using FILTER on DATE Values

You can also use the FILTER function on the date values.

Suppose you want to filter all the movies with the Release Date after 01/01/2011. You can use the FILTER function to apply this condition and return all the rows following the criteria.

= FILTER ( A1:E16, B1:B16 > DATE ( 2011, 1, 1 ) )

As shown in the screenshot above, the FILTER function is applied on the range A1:E16.

To set the condition on column B of Release Dates, you need to use the DATE function to construct the comparison.

You can also use the DAY, MONTH, YEAR, TIME, etc. functions based on your requirements.

Explanation

The function checks on each row of A1:A16 that the value in column B is greater than 01/01/2011, then it includes all rows that fulfill this condition into the output results.

Example 3 Using FILTER with Multiple Conditions

We can also use multiple conditions inside the FILTER function.

Consider a situation where you want to get the movie details with revenue between $100M and $200M. You can do this using the optional condition2, argument inside the function.

= FILTER ( A1:E16, E1:E16 >= 100000000, E1:E16 < 200000000 )

As shown in the screenshot above, the condition on column E returns the movies for which the Gross Revenue is between $100M and $200M. This is achieved using the additional condition arguments of the FILTER function.

Advantages of the FILTER Function

• The function is dynamic and gets updated as you update the original data.
• It is helpful as you can apply the formula anywhere in the sheet to filter the data. It generates the filtered preview. This way, you are keeping the original data safe.
• It can also be used in combination with other functions to filter the data.
• It allows you to filter the data both horizontally and vertically.

Disadvantages of the FILTER Function

• The FILTER function can work on either rows or columns. It can’t filter the data based on rows and columns simultaneously in a single formula.
• If the condition we provide is not compatible with the arrays, the formula will return a #VALUE! error.
• If no value from your dataset follows the condition, the function will return the #NA error.

Filter Data Using the QUERY Function

The QUERY function is by far the best function that got included inside Google Sheets.

It can be helpful in data manipulation using an SQL-like language of its own.

Check out my complete guide to the QUERY function to explore this amazing function further.

One of the many things the QUERY function allows you to do is filter your data.

The WHERE clause from the google sheets QUERY function does the task for you. It sets up the condition and then returns the rows following that condition.

Consider a situation where you would like to filter all those movies with the action genre.

You can do this by using the QUERY function, and inside it, the WHERE clause will filter the data based on a condition.

= QUERY ( A1:E16, "SELECT * WHERE C = 'Action'" )

To filter the data using the QUERY function, use the formula above.

Explanation

The WHERE clause filters all the rows from column C (Genre) with the value Action, and the SELECT clause returns those rows.

Advantages of Filtering Through QUERY Function

• If you are filtering the data through the QUERY function, you can limit the columns returned. This is by far the biggest advantage of using it. Only keep columns that you need.
• You can filter the data based on multiple conditions.
• You can use the QUERY function in combination with other functions while filtering the data.
• If you are filtering the data based on a numeric column, you can use the aggregation functions to generate summaries with filters.

Disadvantages of Filtering Through QUERY Function

The google sheet QUERY function has its own language to build the code. Some users might find it challenging to understand and get hold of the same.

Filter Data in Pivot Table

Using Pivot Tables, you will always be able to use a customized filter option.

You have a dedicated Filter option in any pivot table, and you can select the column on which to apply the filter.

Suppose you have a pivot table, as shown above. You have the Title, Release Date, Country, and Sum of Gross Revenue selected inside the pivot table. Suppose you want to apply a filter based on the Genre column.

To add the Genre column inside the Filter menu in a pivot table, follow the steps below.

1. Navigate towards the Pivot table editor option.
2. Go to the Filters section.
3. Click on the Add button on the right hand side inside the Filters section. It will open up the list of columns that are available for filtering.
4. Click on the Genre column. It’s the column you’re going to apply the filter on.

By default, the filtered column shows all items as the screenshot above suggests. Click on that dropdown.

You will see the filter menu opening up, as shown above.

1. Click on the Filter by values dropdown.
2. You will see all the values selected. Untick the other values and just keep Adventure selected.
3. Click on OK button to apply this filter on the pivot table.

The output should look like the one pictured above. The entire pivot table is filtered based on the Adventure Genre.

Advantages of Filtering Through Pivot Table

• It has several built-in conditions based on which you could filter the data.
• The filtered column need not to be in the pivot table view.

Disadvantages of Filtering Through Pivot Table

• You can’t use multiple filter condition inside the custom filter options.

Filter Data via App Script

It is also possible to automate the filtering tasks you have. You can write a script for it.

Navigate the Tools tab in the menu and click on the Script editor option to access it.

The Script editor interface looks like the one shown in the screenshot above.

Consider this, you want to write a script to apply a filter to return all the values for which Gross Revenue is not between $10M and $25M.

function filter_Data() {
  //Getting the active spreadsheet, sheet, and range

  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  let activeSheet = spreadsheet.getSheetByName("Films Data")
  let rng = activeSheet.getRange("A1:E")

  //Applying filter on column Gross Revenue where number is not between $10M and $25M
  let filt = rng.createFilter()
  filt_criteria = SpreadsheetApp.newFilterCriteria().whenNumberNotBetween(10000000,25000000)
  let filt_apply = filt.setColumnFilterCriteria(5, filt_criteria)
}

Now, inside the script editor, the code written above adds the filter on the data automatically.

Explanation

• We first define a function with the name filter_Data. Everything will be developed inside this function.
• Anything we write with a double forward slash (‘//’) is a comment and is not executed at run time.
• The SpreadsheetApp.getActiveSpreadsheet method returns the active spreadsheet from the google sheets.
• The getSheetByName method allows you to access the sheet you mention the name of. Make sure the name you mention has a sheet with the same name in your workbook.
• This uses the activeSheet.getRange method to get the range on which the filter should be applied.
• The createFilter method allows you to create a filter on the specified range.
• Then, the script applies the filter criteria using the newFilterCriteria.whenNumberNotBetween() method. This method is used on SpreadsheetApp. The filtering limits would be 10,000,000 and 25,000,000. This excudes any gross revenue between these two value and returns everything else.
• Finally, the setColumnFilterCriteria allows you to apply the filter criteria defined in the previous step to a column from the data table. Since the filter has been created on the Gross Revenue column, we put the column index as an input to let the system know where to apply the condition.

Now, hit the Run button to run this code.

Once the script is executed successfully, you can see an Execution log, as shown above. It means there is no glitch in this code, and the applied filter is set on the data range.

The filtered output looks like the one shown above. The system returns all the rows where the Gross Revenue is not between $10M and $25M.

Advantages of the Filter Through App Script

• This is a generic way of applying the filter. Everything is being done using a script. You just have to run the script, and the filter will automatically be added based on the criteria.
• You can also set multiple criteria to filter the rows.
• If you want, you can set triggers to further enhance the abilities of the automation.

Disadvantages of the Filter Through App Script

• You have to turn the filter off on the current selection before running the same script again.

Conclusions

Filtering data is essential when you are dealing with large datasets. Knowing different filtering methods is as good as having multiple remedies for your issue.

• Users can access the conventional filtering option through the Data menu. It is rich in features and provides multiple options such as filtering based on values, conditions, text color, cell color.
• The Filter Views option is exciting and can be used to keep the original data safe from changes you made. It allows you to have a customized filter view of your own which others will not see when collaborating on the same sheet.
• The FILTER function is a powerful function from google sheets. It allows you to filter the data based on multiple conditions with different data types. You can also use this function in combination with other functions to generate interesting results.
• The capabilities of the QUERY function include the WHERE clause that can filter the data based on a single condition or multiple conditions.
• Pivot Tables are again a powerful tool provided by Google Sheets to summarize your data and they also provide many filtering options.
• Finally, the apps scripts allow you to automate adding filters to your datasets.

Everything about filtering has been covered! However, if you feel that any other methods can be added or should be covered, please let us know in the comments below, and we will try to add those as well!

Check Out These Essential Google Sheets Tutorials

• VLOOKUP and INDEX & MATCH Formulas
• The Definitive Guide to the QUERY Function
• The Definitive Guide to Data Validation
• Everything You Need to Know About Sorting Data
• The Beginners Guide to Apps Scripts
• Everything You Need to Know About Smart Fill