How To Use Filter In Google Sheets

Don't feel like reading? View our video tutorial for the Google Sheets FILTER function:

Google Sheets has some bang-up functions that tin can help slice and die information easily. Although in that location is an in-congenital filter feature in Google Sheets, you too accept a FILTER function at your disposal.

FILTER role, as the name suggests, volition let yous to filter a dataset based on a condition (or multiple weather condition).

For instance, if y'all take a list of names with their country names and the sales value, yous tin use the FILTER function in Google Sheets to quickly get all the records/name one specific state (as shown below).

1 benefit of using the FILTER function over the regular filter feature in Google Sheets is that FILTER office results are dynamic. In instance you modify anything in the original data, the resulting filtered information would automatically update.

This makes the Google Sheets FILTER function is a swell choice when creating interactive reports or dashboards.

In this tutorial, I volition show you how the FILTER part works in Google Sheets and as well cover some useful examples to use it on day-to-day work.

So let'southward get started by learning well-nigh the syntax of this function.

Google Sheets Filter Role Syntax

the Google Canvass filter formula goes every bit follows:

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

range: This is the range of cells that y'all want to filter.
condition1: This is the columns/row (respective to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to be of the same size as that of the range
[condition2]: This is an optional argument and can exist the second condition for which you check in the formula. This over again can be a cavalcade/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to be of the aforementioned size as that of the range.

When you use multiple conditions, those results that render truthful for both the conditions would be filtered.

In instance the FILTER function tin can not find any result that matches the condition, it would render an #N/A mistake.

If you're wondering how this works, go through a couple of examples (listed beneath) and information technology volition become clear on how to use the FILTER function in Google Sheets.

Instance 1 – Filter Function Google Sheets Based on a Single Condition

Suppose you have the dataset as shown below and you want to quickly filter all the records where the country name is Florida.

The below formula will practise this:

          =          FILTER          (          A2:C11          ,          B2:B11          =          "Florida"          )

The in a higher place formula takes the data range equally the argument and the condition is B2:B11 = "Florida". This condition checks each cell in the range B2:B11 and if the value is equal to Florida, that tape is filtered, else information technology's non.

In this case, I accept difficult-coded the value, but you can also accept this value in a cell and and so refer to this jail cell. For example, if you take the text Florida in cell H1, you can likewise apply the below formula:

          =          FILTER          (          A2:C11          ,          B2:B11          =          H1          )

A few things to know well-nigh the FILTER function.

The FILTER part in Google Sheets returns an assortment of values that spill over the next cells (this is called a dynamic array). For this to piece of work, y'all need to make sure that the side by side cells (where the results would be placed) should be empty.

If any of the prison cell(due south) is not empty, your formula will render a #REF! error. Google Sheets likewise tells you why information technology's giving an error by showing a ruby-red triangle at the top-correct of the jail cell and when you hover over information technology, it will show a message:

Array result was not expanded because it would overwrite data in F3

And every bit before long as you delete the filled cell that prevents the FILTER function to give the result, information technology volition automatically fill up the range with the issue.

Too, the result of the FILTER formula is an array and you can change a part of the array. This means that you cant not modify or delete one cell (or couple cells) in the result. You will have to delete the entire formula result. To delete the result, yous can select the cell where you entered the formula and so hit the delete key.

Example 2 – Filter Google Sheets Based on Multiple Atmospheric condition (And Condition)

Yous can also utilise the FILTER function to check for multiple conditions in such a mode that it just returns those records where both the conditions are met.

For example, suppose you have the beneath data set and you want to filter all the records where the state is Florida and the sale value is more than 5000.

You lot can do this using the below formula;

          =          FILTER          (          A2:C11          ,          B2:B11          =          "Florida"          ,          C2:C11          >          5000          )

The above formula checks for ii conditions (where the state is Florida and auction value is more than 5000) and returns all the records that meet these criteria.

Similarly, if yous want, you can have multiple atmospheric condition in the same FILTER formula.

Example 3 – Filter Records Based on Multiple Conditions (OR Condition)

In the above instance, I accept checked for two conditions and return results where both the atmospheric condition are TRUE.

You can too cheque for OR condition in the FILTER formula.

For case, suppose you have the dataset as shown beneath and yous desire to go all the records for California and Iowa. This means that the condition should be the state is either California or Iowa (which makes this an OR condition).

The below formula will practise this:

          =          FILTER          (          A2:C11          ,          (          B2:B11          =          "California"          )          +          (          B2:B11          =          "Iowa"          )          )

The to a higher place formula uses the add-on operator in the status to first check both the conditions and and so add the upshot of each. Since these weather condition return an array or TRUEs and FALSEs, yous tin can add these (since a True is 1 and False is 0 in Google Sheets).

This will give you lot 0 (or Fake) where both the atmospheric condition are non met, 1 where one of the two atmospheric condition are met, and 2 where both the conditions are met.

And then the FILTER formula volition return all the records where the atmospheric condition return value more than than 0.

Example 4 – How to Filter In Google Sheets for Top 3 or Top 5 Records

You tin can besides use the FILTER office to quickly become the top iii or top five (or whatsoever number of pinnacle/bottom number of records yous choose).

For instance, suppose I take the dataset equally shown below and I desire to quickly get the records for the tiptop 3 sales values.

The below formula will do this:

          =          FILTER          (          A2:C11          ,          C2:C11          >=          LARGE          (          C2:C11          ,          3          )          )

The above formula uses the Large part to get the 3rd largest value in the dataset. This value is then used in the condition to cheque whether the values in column C are greater than or equal to this value or not.

This would return all the records that match the criteria, which would be the summit three records.

In case you want to go the bottom three records, you lot tin use the beneath FILTER formula:

          =          FILTER          (          A2:C11          ,          C2:C11          <=          Pocket-sized          (          C2:C11          ,          iii          )          )

Example 5 – How to Employ Filter Function in Google Sheets to SORT the Filtered Data

So far, all the filtering office examples that we take seen would filter the information in the aforementioned order in which it occurs in the dataset.

But what if y'all want to get the sorted dataset.

For example, suppose you lot're filtering the top 5 records, it would be more useful to have these sorted in descending guild (largest at the top).

Below is the formula that will filter the data and show it in descending social club:

          =          SORT          (          FILTER          (          A2:C11          ,          C2:C11          >=          LARGE          (          C2:C11          ,          3          )          )          ,          3          ,          False          )

The above formula uses the same FILTER formula we used in the previous example to fetch the top 3 records based on the sales value.

And since I wanted the concluding effect to exist sorted in descending guild, I have wrapped the FILTER function within the SORT function. The SORT function uses the result given past the FILTER formula and sorts it based on the third column (which has the sales value).

The third statement in the SORT function is Simulated, which is to specify that I want the final result in descending lodge. In case you get out it blank (or get in True), the upshot will be in ascending order.

Example 6 – Spreadsheet Filter All Even Number Records (Or Odd Number Records)

This is not such a common use-case, but it's something I have had to practice when I get my data from someone else or from a database or webpages.

Many times, the data y'all need will just be in alternate rows (or every tertiary/fourth/fifth row), and you would have a need to go rid of the extra rows and then that you can get all the useful data together.

In such a case, you can use the FILTER function to quickly filter and get all the fifty-fifty number rows together (or all odd-numbered rows together). And y'all can also change the formula to filter every tertiary, quaternary, or nth row in Google Sheets.

Suppose yous have a dataset every bit shown below and y'all want to filter all the even-numbered rows in this dataset.

Below is the formula that will filter all the even rows:

          =          FILTER          (          A2:C11          ,          Modernistic          (          ROW          (          A2:A11          )          -          1          ,          2          )          =          0          )

The above formula uses the ROW office to become the row numbers of all the rows in the dataset. It and then subtracts 1 from information technology as our dataset starts from the second row onwards.

Now, information technology uses the MOD office to bank check the following status – Modern ( ROW ( A2:A11 ) – 1 , two ) = 0 )

This would return TRUE for all the even-numbered rows and Simulated for all odd-numbered rows. And this array of TRUE and Fake is used past the FILTER function to extract the records.

Similarly, if yous want merely the ODD numbered records, yous can employ the below formula:

          =          FILTER          (          A2:C11          ,          Mod          (          ROW          (          A2:A11          )          -          1          ,          2          )          =          1          )

And in case you desire to filter every third row, you can use the below formula in Google Sheets:

          =          FILTER          (          A2:C11          ,          Modern          (          row          (          A2:A11          )          -          1          ,          3          )          =          0          )

Google Sheets Filter Function FAQ

How Do I Filter Top 10 Values in Google Sheets?

Bank check out example 4 in our guide above, it volition testify you how to filter by peak numbers no matter how many you decide to contain

What Does the FILTER Function Do?

The FILTER function makes it easy to filter out any unimportant information from your spreadsheet and then you only run into what is needed for your other functions or calculations.

Tin I Do a Filter of a Filter?

Technically yep, but y'all'd be much better off using the VLOOKUP role instead. It's a much more than efficient style of doing the aforementioned thing.

Can I Reference a Criteria Prison cell With the Filter Function in Google Sheets?

Yep, you just have to use the cell reference in the conditionsection of the FILTER part syntax.

Then these are some of the examples where the Google Sheets FILTER function can be a existent fourth dimension saver. And when y'all combine it with other formulas, you can get a lot of stuff done with it.

Hope you found this tutorial useful.

Other Google Sheets tutorials you may like:

How to Filter By Color in Google Sheets (Using a Formula)
How to Number Rows in Google Sheets (Add Serial Numbers)
Count Cells IF NOT Blank (Not-Empty cells) in Google Sheets
How To Remove Duplicates In Google Sheets (3 Like shooting fish in a barrel Means)
How to VLOOKUP from Another Sheet in Google Sheets?
How to Use IMPORTRANGE Function in Google Sheets
How to Utilize the INDIRECT part in Google Sheets