How To Edit Filtered Cells In Sheets

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

Google Sheets has some swell functions that can assistance piece and dice data easily. Although at that place is an in-congenital filter characteristic in Google Sheets, you also have a FILTER role at your disposal.

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

For example, if you have a listing of names with their land names and the sales value, yous can use the FILTER office in Google Sheets to quickly become 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 role results are dynamic. In case you change anything in the original data, the resulting filtered data would automatically update.

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

In this tutorial, I volition testify you how the FILTER role works in Google Sheets and also cover some useful examples to use it on mean solar day-to-day work.

Then permit's become started by learning about the syntax of this function.

Google Sheets Filter Function Syntax

the Google Canvass filter formula goes as follows:

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

range: This is the range of cells that you want to filter.
condition1: This is the columns/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to exist of the same size as that of the range
[condition2]: This is an optional argument and tin can be the second status for which you check in the formula. This again can exist 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 every bit that of the range.

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

In instance the FILTER function tin can not find any consequence that matches the status, information technology would return an #Due north/A fault.

If y'all're wondering how this works, become through a couple of examples (listed beneath) and it will go clear on how to employ the FILTER function in Google Sheets.

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

Suppose you accept the dataset as shown below and y'all want to speedily filter all the records where the state name is Florida.

The below formula will do this:

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

The to a higher place formula takes the data range every bit the statement and the condition is B2:B11 = "Florida". This status checks each cell in the range B2:B11 and if the value is equal to Florida, that tape is filtered, else information technology'south not.

In this example, I have difficult-coded the value, but yous tin can also accept this value in a cell and so refer to this jail cell. For example, if y'all have the text Florida in cell H1, yous can also use the below formula:

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

A few things to know almost the FILTER role.

The FILTER function in Google Sheets returns an array of values that spill over the side by side cells (this is chosen a dynamic array). For this to work, you need to make certain that the side by side 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 also tells you why information technology's giving an error by showing a red triangle at the meridian-right of the cell and when y'all hover over it, it will show a message:

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

And equally before long equally y'all delete the filled cell that prevents the FILTER function to give the result, information technology volition automatically make full the range with the consequence.

Besides, the outcome of the FILTER formula is an array and you tin modify a part of the array. This means that yous cant not change or delete one cell (or couple cells) in the consequence. You lot will take to delete the entire formula result. To delete the result, y'all tin select the cell where you entered the formula and so hit the delete key.

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

You can too use the FILTER function to check for multiple weather in such a way that it only returns those records where both the weather condition are met.

For instance, suppose you have the below information gear up and you want to filter all the records where the state is Florida and the sale value is more than 5000.

You tin can do this using the below formula;

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

The in a higher place formula checks for two weather condition (where the state is Florida and sale value is more than 5000) and returns all the records that meet these criteria.

Similarly, if you desire, you tin can have multiple atmospheric condition in the aforementioned FILTER formula.

Example three – Filter Records Based on Multiple Conditions (OR Status)

In the above example, I take checked for ii weather and return results where both the conditions are TRUE.

You can besides check for OR condition in the FILTER formula.

For example, suppose you lot have the dataset as shown beneath and you lot desire to get all the records for California and Iowa. This means that the status 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 in a higher place formula uses the addition operator in the status to starting time bank check both the weather condition and so add the result of each. Since these conditions return an assortment or TRUEs and FALSEs, you lot tin add together these (since a TRUE is 1 and FALSE is 0 in Google Sheets).

This will give you 0 (or Fake) where both the weather condition are not met, one where one of the two weather condition are met, and two where both the conditions are met.

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

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

You can also utilise the FILTER function to rapidly get the top 3 or superlative five (or whatsoever number of meridian/bottom number of records you choose).

For example, suppose I take the dataset as shown below and I want to quickly get the records for the top 3 sales values.

The below formula volition do this:

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

The in a higher place formula uses the LARGE function to get the 3rd largest value in the dataset. This value is then used in the condition to bank check whether the values in cavalcade C are greater than or equal to this value or not.

This would return all the records that match the criteria, which would exist the top three records.

In case you want to go the bottom three records, you can use the below FILTER formula:

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

Example 5 – How to Utilize 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 information in the same order in which it occurs in the dataset.

Only what if you desire to get the sorted dataset.

For example, suppose you're filtering the pinnacle 5 records, information technology would be more useful to accept these sorted in descending order (largest at the tiptop).

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

          =          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 tiptop three records based on the sales value.

And since I wanted the final event to be sorted in descending order, I have wrapped the FILTER function within the SORT function. The SORT office uses the event given past the FILTER formula and sorts it based on the tertiary column (which has the sales value).

The tertiary argument in the SORT role is FALSE, which is to specify that I want the concluding result in descending society. In example you leave it blank (or arrive True), the result will exist in ascending order.

Instance vi – Spreadsheet Filter All Fifty-fifty Number Records (Or Odd Number Records)

This is non such a common utilise-example, but it's something I have had to do when I get my data from someone else or from a database or webpages.

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

In such a instance, you can apply the FILTER role to quickly filter and get all the fifty-fifty number rows together (or all odd-numbered rows together). And you can also modify the formula to filter every third, quaternary, or nth row in Google Sheets.

Suppose you have a dataset as 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 fifty-fifty rows:

          =          FILTER          (          A2:C11          ,          MOD          (          ROW          (          A2:A11          )          -          ane          ,          two          )          =          0          )

The above formula uses the ROW role to get the row numbers of all the rows in the dataset. It and then subtracts 1 from it as our dataset starts from the 2d row onwards.

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

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

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

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

And in instance y'all want to filter every third row, you can use the below formula in Google Sheets:

          =          FILTER          (          A2:C11          ,          Mod          (          row          (          A2:A11          )          -          1          ,          iii          )          =          0          )

Google Sheets Filter Function FAQ

How Do I Filter Top ten Values in Google Sheets?

Check out example 4 in our guide above, it will show you how to filter by acme numbers no matter how many you determine to incorporate

What Does the FILTER Part Do?

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

Can I Do a Filter of a Filter?

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

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

Yes, you just accept to employ the cell reference in the conditiondepartment of the FILTER function syntax.

So these are some of the examples where the Google Sheets FILTER function can be a real time saver. And when you combine it with other formulas, you can go a lot of stuff done with it.

Promise yous found this tutorial useful.

Other Google Sheets tutorials you may similar:

How to Filter By Colour in Google Sheets (Using a Formula)
How to Number Rows in Google Sheets (Add together 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 Ways)
How to VLOOKUP from Another Sail in Google Sheets?
How to Use IMPORTRANGE Function in Google Sheets
How to Employ the INDIRECT office in Google Sheets