Last Updated on October 31, 2023 by Jake Sheridan
Welcome to this tutorial where you will learn how to filter data by date range using the powerful Google Sheets Query function.
Understanding Google Sheets Query
Before we dive into examples, let’s first understand how the Google Sheets query function works.
The QUERY function in Google Sheets is a versatile tool that allows you to use data commands to manipulate and filter data. It is a powerful and flexible feature that replaces the need for multiple other functions, including pivot tables.
To use the QUERY function in Google Sheets, you will need to provide three arguments:
- The data range you want to analyze.
- The query you want to run, enclosed in quotation marks.
- An optional number indicating the number of header rows in your data.
Filtering Data by Date Range
Now, let’s explore how to filter data by date range using Google Sheets Query.
But before we begin, make sure you have a data range that you want to filter. In this example, we will filter the data to show values only prior to the year 2021.
Step 1: Identify the date range you want to filter. In this case, we want to filter data from before the year 2021.
Step 2: To start building the query formula, select an empty cell and enter the formula. In this example, we will use cell E2. Begin the formula by typing an equal symbol followed by ‘QUERY’ and an open bracket. You will see a blue question mark that you can click on to see the arguments required for this function.
Step 3: Now, it’s time to insert the arguments. We will select the data range A1:C8 as our first argument. Then, we will enter the query « select * where A < date ‘ »&TEXT(DATEVALUE(« 1/1/2021″), »yyyy-mm-dd »)& »‘ ». Remember to close the formula with a closing bracket.
Step 4: Press Enter, and voila! The rows before the year 2021 will appear, showing the filtered data.
Take Note!: The data returned from the Query function will be unformatted. You can edit the data to suit your preferences.
That’s it! You have successfully filtered data by date range using Google Sheets Query. Feel free to copy the example spreadsheet below to see how it’s done. Remember to have fun while experimenting with this powerful feature.
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, we covered how to filter data by date range using Google Sheets Query. If you want to learn more, check out our other Google Sheets Tutorials.
