How to filter data by month in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))Test the month and the year of each date: =FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1))) returns the rows in the same month as the date in H1.
- Result on the example
- a 4-row list that spills from G3 in
G3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
MONTH and YEAR run on every date in B2:B11 and give a column of numbers each. Comparing them with the month and year of H1 and multiplying the tests keeps only the rows where both match. Any date in April 2026 in H1 gives the same list.
=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))Result{4 rows × 5 cols} "SO-1047", 46118, "Wingtip", "West", …
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", …A range: 10 rows × 5 columns.
B2:B1110 rows × 1 col: 2/23/2026, 3/2/2026, 3/9/2026, 3/15/2026, …A range: 10 cells in one column.
MONTH(B2:B11){10 rows × 1 col} 2, 3, 3, 3, …MONTH: Month of a date, 1–12.- serial_number
B2:B1110 rows × 1 col: 2/23/2026, 3/2/2026, 3/9/2026, 3/15/2026, …
- serial_number
H14/1/2026The value in H1.
MONTH(H1)4MONTH: Month of a date, 1–12.- serial_number
H14/1/2026
- serial_number
MONTH(B2:B11)=MONTH(H1){10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
YEAR(B2:B11){10 rows × 1 col} 2026, 2026, 2026, 2026, …YEAR: Year of a date.- serial_number
B2:B1110 rows × 1 col: 2/23/2026, 3/2/2026, 3/9/2026, 3/15/2026, …
- serial_number
YEAR(H1)2026YEAR: Year of a date.- serial_number
H14/1/2026
- serial_number
YEAR(B2:B11)=YEAR(H1){10 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1){10 rows × 1 col} 0, 0, 0, 0, …The * operator multiplies.
FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1))){4 rows × 5 cols} "SO-1047", 46118, "Wingtip", "West", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", … - condition1
MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1){10 rows × 1 col} 0, 0, 0, 0, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1))). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:E11,(B2:B11>=H1)*(B2:B11<=EOMONTH(H1,0))) | spills 4 × 5 | A date range from the first to the last day of the month. Needs H1 to be the 1st, but it's faster on very large tables. |
| =FILTER(A2:E11,TEXT(B2:B11,"yyyy-mm")=TEXT(H1,"yyyy-mm")) | spills 4 × 5 | Compares year and month as one piece of text. |
Common mistakes
- Testing MONTH alone mixes years: April 2025 and April 2026 both return 4. Add the YEAR test unless the data covers one year.
- Typing a month name in
H1("April") doesn't work with MONTH. Use a real date, or compareTEXT(B2:B11,"mmmm")=H1.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Filter data by month
G3, return every order placed in the same month and year as the date in H1.Questions
How do I filter the current month?
Use TODAY in place of H1: =FILTER(A2:E11,(MONTH(B2:B11)=MONTH(TODAY()))*(YEAR(B2:B11)=YEAR(TODAY()))).
How do I filter by quarter?
Turn each month into a quarter: =FILTER(A2:E11,ROUNDUP(MONTH(B2:B11)/3,0)=2) returns April to June.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.