How To Sort And Filter In Google Sheets

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

Google Sheets has some great functions that can help slice and dice data easily. Although in that location is an in-built filter characteristic in Google Sheets, y'all also have a FILTER function at your disposal.

FILTER function, as the name suggests, will allow you to filter a dataset based on a condition (or multiple conditions).

For example, if you lot have a list of names with their state names and the sales value, you lot can apply the FILTER part in Google Sheets to quickly become all the records/name one specific state (as shown beneath).

One benefit of using the FILTER function over the regular filter characteristic in Google Sheets is that FILTER office results are dynamic. In example you change annihilation in the original data, the resulting filtered data would automatically update.

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

In this tutorial, I will show you how the FILTER function works in Google Sheets and also embrace some useful examples to use information technology on day-to-day piece of work.

So let's get started by learning about the syntax of this role.

Google Sheets Filter Function Syntax

the Google Sheet filter formula goes as follows:

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

range: This is the range of cells that yous desire to filter.
condition1: This is the columns/row (corresponding to the column/row of the dataset), that returns an assortment of TRUEs/FALSES. This needs to be of the same size as that of the range
[condition2]: This is an optional argument and tin exist the 2d condition for which you cheque in the formula. This again can be a column/row (corresponding 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.

When you use multiple conditions, those results that return true for both the weather would be filtered.

In case the FILTER role can not find any result that matches the status, information technology would render an #N/A fault.

If you're wondering how this works, become through a couple of examples (listed beneath) and it will become clear on how to use the FILTER part in Google Sheets.

Example i – Filter Function Google Sheets Based on a Single Condition

Suppose you lot take the dataset equally shown beneath and you want to quickly filter all the records where the state proper noun is Florida.

The below formula volition practise this:

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

The in a higher place formula takes the data range as 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 record is filtered, else it'southward not.

In this example, I have hard-coded the value, but you can besides have this value in a cell and and then refer to this cell. For example, if you have the text Florida in cell H1, you tin as well utilize the beneath formula:

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

A few things to know about the FILTER function.

The FILTER function in Google Sheets returns an array of values that spill over the adjacent cells (this is called a dynamic array). For this to piece of work, you need to brand sure that the next cells (where the results would be placed) should be empty.

If any of the cell(s) is not empty, your formula volition return a #REF! error. Google Sheets besides tells yous why it's giving an error by showing a red triangle at the superlative-correct of the prison cell and when you hover over it, it will show a message:

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

And as soon as yous delete the filled cell that prevents the FILTER function to requite the consequence, information technology volition automatically fill the range with the effect.

Also, the consequence of the FILTER formula is an array and you can change a part of the array. This means that yous cant not change or delete one jail cell (or couple cells) in the effect. You lot will have to delete the unabridged formula event. To delete the result, you can select the prison cell where you entered the formula and so hit the delete key.

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

You lot can also use the FILTER office to check for multiple weather in such a manner that it just returns those records where both the atmospheric condition are met.

For example, suppose you take the below data set and y'all desire to filter all the records where the state is Florida and the sale value is more 5000.

Y'all can do this using the below formula;

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

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

Similarly, if you want, you can have multiple conditions in the same FILTER formula.

Instance iii – Filter Records Based on Multiple Atmospheric condition (OR Condition)

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

You tin can also check for OR condition in the FILTER formula.

For example, suppose you accept the dataset as shown below and you want to get all the records for California and Iowa. This means that the condition should exist the state is either California or Iowa (which makes this an OR condition).

The below formula will do this:

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

The higher up formula uses the add-on operator in the condition to first check both the weather and then add together the issue of each. Since these conditions return an array or TRUEs and FALSEs, you can add these (since a True is 1 and Simulated is 0 in Google Sheets).

This will requite yous 0 (or Fake) where both the weather are not met, ane where i of the two weather are met, and 2 where both the atmospheric condition are met.

And so the FILTER formula will return all the records where the weather return value more 0.

Case four – How to Filter In Google Sheets for Pinnacle three or Top 5 Records

You can also apply the FILTER role to speedily become the peak 3 or superlative 5 (or whatever number of peak/bottom number of records you lot cull).

For instance, suppose I have the dataset as shown below and I desire to quickly get the records for the top 3 sales values.

The below formula will practice this:

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

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

This would render all the records that lucifer the criteria, which would be the top 3 records.

In instance you desire to become the lesser three records, you tin utilize the below FILTER formula:

          =          FILTER          (          A2:C11          ,          C2:C11          <=          Small          (          C2:C11          ,          three          )          )

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

And then far, all the filtering function examples that we have seen would filter the data in the same order in which it occurs in the dataset.

But what if you want to get the sorted dataset.

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

Beneath is the formula that will filter the data and bear witness information technology in descending guild:

          =          SORT          (          FILTER          (          A2:C11          ,          C2:C11          >=          Big          (          C2:C11          ,          iii          )          )          ,          3          ,          Fake          )

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

And since I wanted the last result to be sorted in descending society, I have wrapped the FILTER part inside the SORT function. The SORT office uses the result given by the FILTER formula and sorts it based on the third column (which has the sales value).

The third argument in the SORT office is FALSE, which is to specify that I want the final result in descending order. In instance you leave it blank (or brand it Truthful), the result volition be in ascending lodge.

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

This is not such a common utilize-case, merely it'southward something I have had to do when I get my data from someone else or from a database or webpages.

Many times, the information you demand will only be in alternate rows (or every third/fourth/5th row), and yous would have a demand to become rid of the extra rows and then that yous tin get all the useful data together.

In such a case, you tin can employ the FILTER role to rapidly filter and get all the even number rows together (or all odd-numbered rows together). And you can as well modify the formula to filter every third, 4th, or nth row in Google Sheets.

Suppose you lot have a dataset every bit shown below and yous want to filter all the fifty-fifty-numbered rows in this dataset.

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

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

The above formula uses the ROW role to become the row numbers of all the rows in the dataset. Information technology then subtracts one from it every bit our dataset starts from the 2d row onwards.

Now, it uses the MOD office to bank check the following condition – MOD ( ROW ( A2:A11 ) – 1 , 2 ) = 0 )

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

Similarly, if you want simply the ODD numbered records, yous can use the below formula:

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

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

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

Google Sheets Filter Function FAQ

How Do I Filter Top 10 Values in Google Sheets?

Check out case iv in our guide above, it volition evidence yous how to filter past pinnacle numbers no thing how many you lot make up one's mind to comprise

What Does the FILTER Function Do?

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

Can I Do a Filter of a Filter?

Technically yes, just you'd be much improve off using the VLOOKUP function instead. It's a much more efficient way of doing the aforementioned thing.

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

Yes, you just have to use the jail cell reference in the conditiondepartment of the FILTER role syntax.

Then these are some of the examples where the Google Sheets FILTER part can exist a real time saver. And when you lot combine it with other formulas, you can become 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 (Non-Empty cells) in Google Sheets
How To Remove Duplicates In Google Sheets (3 Like shooting fish in a barrel Means)
How to VLOOKUP from Some other Canvas in Google Sheets?
How to Use IMPORTRANGE Function in Google Sheets
How to Use the INDIRECT function in Google Sheets