How To Filter By Date In Google Sheets

For small business owners and freelancers, Google Sheets is a suitable replacement for Microsoft Excel. It offers most of the features that piece of work pretty similar to the ones in excel and so switching to Google Sheets isn't a big deal.

Filter Function is one of the most powerful features Google Sheets has for treatment the data. It filters out the data from multiple rows and columns of an array that encounter a specified condition. Thus, it'due south not limited to filtering data from but a unmarried row or column that increases its scope of use.

An Overview of the Syntax of Filter Office

By using this function, you tin go multiple results of matches. And since your information is filtered with a role, the results volition update automatically. Allow'south rapidly go over the syntax of the Filter Office earlier moving forwards on the formula implementation.

The filter part has three arguments; Range, Condition 1, and [Condition two ...]. The start two arguments are required, while the tertiary argument is optional.

Practice notation that there is no limit to the number of conditions you tin can specify. So, you can continue to add weather to the Filter functions for equally long as needed. Permit's talk over what this range and condition arguments represent:

Range: The range argument refers to the data you intend to filter from an assortment. It can comprise any number of rows or columns.
Test condition 1: The status arguments correspond to a set of true or fake values in a cavalcade or row, whether they are associated with the first column or row of 'range' or a formula evaluating truthful or false.
[Condition 2....]: It refers to additional rows or columns containing the boolean value True or Simulated, which indicates whether the corresponding row or column should pass through the 'FILTER.'

If you use the Filter Function, you must specify filtered data and 1 status statement. Creating a dropdown for the test status volition be easier to filter out the desired data past updating the selection.

Note that the filter office spills all the time. Then, you won't have to add together dollar signs ($), which y'all usually put in while executing range references. You lot can add i formula, and it'll return results from the unabridged array.

Example for Filter Function

Let'southward take a look at the below information to run across the implementation of the Filter Function.

Sample Data For Filter Function in Google Docs

You lot tin can find data well-nigh the sales and profits for various grocery items beyond different states in the US.

Let'south say you want to filter the grocery items, sales, and profit by land. Here you have multiple matches for each state, and your range is these three columns.

You tin use the filter role to filter the desired consequence. As an array column containing the names of states is your condition one, permit's create a dropdown for it.

1. Correct-click on a prison cell to open the dropdown card.

2. Gyre down and become to the data validation section.

3. In data validation, set criteria to be List from a range.

Changing Criteria in Data Validation Settings

4. Click on the square boxes in the criteria section to choose a range or formula.

Locating Data Range Boxes in Data Validation Settings

5. In this example, the land column will exist the range. You lot volition need to specify the entries from Cells A2 to A13 here.

Selecting a Data Range in Data Validation

six. Save the Data Validation settings by clicking Save.

This will create a dropdown menu consisting of unique items from the selected range.

Now that yous take the dropdown menu created, permit's begin filtering grocery items, sales, and profit for each state using this Filter role.

Implementing the Filter Function

Range, in this example, is the data in the three columns, Sales, Grocery items, and Profit. Let's choose cells B2 to D13 in the first argument of the Filter part.

Suppose you wish to add more than data to already selected columns as a range. Keep the second value in the range open-ended. Yous can practice this by removing the cell number, which in this case is 13.

Making Range Open Ended in Filter Function

As before long as you add a new value, the filter function volition take these into account without requiring yous to filter them once more.

The condition statement in this example will be the state. Select the country column by keeping it open-ended and equal it to the jail cell containing the dropdown carte du jour from where yous will assign filters for each region.

Assigning Condition for Condition Argument in Filter Function

To ensure consistency across the two datasets, make sure that both ranges have the same pinnacle.

Once you press Enter to execute the formula, information technology will filter out the data of grocery items, sales, and profits for the land of taxes from the mixed dataset.

Additionally, note that the Filter part merely includes valid values. If the tested value is true, the value in the array is considered; otherwise, the function ignores information technology and skips to the next.

In this example, when you select Utah from the dropdown, yous will see the relevant data for that country in the filter expanse.

Filtering Data by Testing Multiple Conditions

Allow's update the function to include a profit greater than 5000 as the second condition. To filter data coming together both conditions, yous need to add this new condition to the filter formula.

ane. Go to Cell F8, where you added the formula before.

Editing the Existing Filter Function in Cell F8

two. Choose the profit cavalcade as a range for the second status, and the new formula looks like this:

          =Filter(B2:D,A2:A=G5,D2:D>5000)

Adding Second Condition in Filter Function

three. Printing Enter to execute the formula.

The turn a profit for flour in Utah was 1240, and the filter function ignored information technology because it did not run across the second condition. In the same way, you tin can filter the information past testing any number of conditions.

Have More Control Over Filtering Information in Google Sheets

In Google Sheets, the Filter Function comes in handy when filtering some data afterward testing a big dataset. It is important to remember that the filter role in Google Sheets differs slightly from Microsoft Excel. And then keep its syntax in mind during implementation.

You can further enhance your productivity past integrating Google services like Google Forms with Google Sheets.