How to filter data by quarter in Excel?
As we all know, a quarter contains three months of a year. Supposing, you have a large worksheet which contains the sale data of a year, and now you want to filter the rows of a specific quarter – Quarter 3. That is to say, you need to filter three months (July, August and September) data, how could you deal with this task quickly and easily in Excel?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Fortunately, in Excel, there is a built-in feature called Filter can help you to quickly filter the data by quarter, please do as this:
1. Select the date column that you want to filter.
2. And then click Data > Filter.
3. And a button will be displayed at the right down corner of the header cell, click it, and then choose Date Filters > All Dates in the Period, and then click the Quarter that you want to filter out. See screenshot:
4. And all the data which meet your criteria has been filtered out as following screenshot shown:
If you are not attention to the above feature in Excel, you can also apply a helper column to solve it.
1. Firstly, you need a formula to extract the Quarter name from the date, please enter this: =(TRUNC((MONTH(D2)-1)/3))+1, (D2 indicates the date that you want to convert to Quarter name )see screenshot:
2. Then drag the fill handle over to the cell that you need to apply this formula, and all the Quarter names have been extracted as following screenshot shown, 1 stands for Quarter 1, 2 is Quarter 2, 3 is Quarter 3 and 4 is Quarter 4.
3. And in this step, you can filter by this new column, click Data > Filter, and then click button, from the context menu, check the Quarter which you want to filter. See screenshot:
4. And then click OK button, all the rows which is belonged to the Quarter 3 have been filtered out.
Besides the above two methods, here, I can introduce you a powerful utility-Kutools for Excel, with its Super Filter function, you can finish more complex filter problem.
1. Please apply Kutools for Excel by clicking Enterprise > Super Filter, see screenshot:
2. In the popped out Super Filter dialog, do the following settings:
- A: Click button to select the data range that you want to filter. (The default selection is your used range)
- B: Select a relationship as you need.
- C: Then choose your criteria from the condition box.
3. After finishing setting the conditions, please click Filter button, and your specific Quarter data will be filtered out.
Note: To recover the original data, you can click Clear button in Super Filter dialog.