Welcome to Excel Jet Consult blog. In this tutorial, we are going to learn how to use the elegant dynamic array FILTER Function to filter dataset by Month and Quarter. Let’s get started
Video
Sample Data
In the screenshot below, we have sample dataset for filtering by month. We are going to use the same dataset for for filtering by quarter. In addition, we have drop-down in cells M2 and N2 that contains Start of Month and End of Month respectively.
FILTER BY MONTH
To filter for the selected Month of March 2021 based on the selection in cells M2 and N2:
- In cell G2of the filter by month sheet, execute the formula: =FILTER(Data,(Data[Date]>=M2)*(Data[Date]<=N2))
- Click Enter
From the screenshot below, we have the filtered result showing all the transaction from March 1st 2021 to March 31st 2021. If we choose another start month and corresponding end month in cells M2 and N2, the formula will update automatically
FILTER BY QUARTER
To filter by quarter
- In cell G2 of the filter by quarter sheet, execute the formula: =SORT(FILTER(SalesData,(SalesData[Date]>=M2)*(SalesData[Date]<=N2)),1,1)
- Click Enter
From the above formula, we used the filter function and the array argument input value is SalesData which is the Table name. Then, we referenced the Date column and used greater than or equal to maths operators to check whether greater than or equal to the select Start of Quarter in cell M2. Conversely, we referenced the date column again and used less than or equal to operators to check if the date column in less than or equal to the selected End of Quarter in cell N2. Finally, we wrapped the formula inside SORT Function and then we use the Date as the sort index and specified 1 for Ascending order sorting. In addition, we applied Freeze Panes to row 2 so that we can verify the result when we scroll down to the last filtered row
From the caption below, we can see that the dataset is accurately filtered – we have the result for Quarter 1 which starts from January 1st 2021 to March 31st 2021
See you in the next video