How to Use FILTER and MONTH Formula in Excel to Extract Monthly Data (No VBA)
Are you tired of manually filtering your Excel data month-by-month? Want to pull out all rows for a specific month with just one formula?
Here’s a powerful trick:
Use FILTER with the MONTH function in Excel to dynamically extract rows by month — no filters, no VBA, and no extra effort.
In this detailed tutorial, you’ll learn exactly how to use:
=FILTER(A2:D100, MONTH(B2:B100)=5)
…to instantly filter all rows where the date falls in May.
This simple but powerful formula can save hours of work, especially for HR teams, finance professionals, marketers, or anyone working with time-based data.
Let’s dive in!
Table of Contents
🧠 What Does FILTER and MONTH Formula in Excel Do?
The formula:
=FILTER(A2:D100, MONTH(B2:B100)=5)
does the following:
- Looks at the dates in column B (
B2:B100) - Checks if each date falls in May (
MONTH = 5) - Filters and returns full rows from
A2:D100where this condition is true
It’s like applying a dynamic filter without even touching the Filter button in Excel!
📌 Real-World Example
Let’s say you have a dataset of employee attendance:
| Name | Date | Status | Hours |
|---|---|---|---|
| John | 01-04-2024 | Present | 8 |
| Sarah | 10-05-2024 | Present | 7 |
| Amit | 20-05-2024 | Absent | 0 |
| David | 05-06-2024 | Present | 9 |
If you want to extract only the rows for May, just use:
=FILTER(A2:D100, MONTH(B2:B100)=5)
You’ll get only Sarah and Amit’s rows returned!
✅ Requirements
This formula works in:
- Excel 365
- Excel 2021 (with dynamic arrays)
- Excel for Web
❌ It will not work in Excel 2019 or earlier versions (as
FILTERis not supported there).FILTER and MONTH Formula in Excel
🔍 Why This Formula Is So Useful
- ✅ Automatically updates when new data is added
- ✅ Avoids using manual filters or pivot tables
- ✅ Cleaner dashboards and reports
- ✅ No helper columns needed
- ✅ Works great for automation and templates
🧪 Understanding the Breakdown
📌 MONTH(B2:B100)
This part converts each date in column B into its numeric month (e.g., 5 for May).
📌 MONTH(B2:B100)=5
This creates an array of TRUE or FALSE values:
- TRUE for rows in May
- FALSE for others
📌 FILTER(A2:D100, ...)
Finally, FILTER includes only those rows from A2:D100 where the condition is TRUE.
🎯 How to Change the Month Dynamically?
Don’t hardcode =5. Make it dynamic using a reference cell:
=FILTER(A2:D100, MONTH(B2:B100)=E1)
If E1 contains 5, it’ll return May data. Change E1 to 6, and boom — you now have June’s data.
Perfect for drop-down filters or dashboards!
💡 Bonus: Add YEAR for More Precision
Want to extract data for May 2023 only?
=FILTER(A2:D100, (MONTH(B2:B100)=5)*(YEAR(B2:B100)=2023))
This ensures you’re not pulling May data from other years.
⚠️ Common Errors and Fixes
| Error | Reason | Fix |
|---|---|---|
| #VALUE! | Using FILTER in unsupported Excel version | Use Excel 365 or Excel Online |
| #CALC! | No data matches the condition | Wrap with IFERROR: =IFERROR(..., "No data found") |
| Dates not filtered correctly | Column B may not have valid date formats | Check with ISNUMBER(B2) |
🎯 Use Cases for FILTER + MONTH Formula
This trick isn’t just academic — here are real-world scenarios where it shines:
✅ HR Attendance Reports
Pull only data for a selected month to review leaves, late entries, etc.
✅ Sales Team
Filter invoices generated in a specific month for reconciliation.
✅ Marketing Campaign Logs
Extract leads generated in a certain month for ROI tracking.
✅ School or College Records
Get attendance or assignment data filtered by academic month.
🚀 Supercharge It With Drop-Down Month Filter
Create a dropdown in cell E1:
- Go to Data > Data Validation
- Choose List
- Enter
1,2,3,4,5,6,7,8,9,10,11,12
Now link the FILTER formula to E1 and give users dynamic control. FILTER and MONTH Formula in Excel
🖥️ Want To Learn With Practice?
🔍 FAQs: FILTER and MONTH Formula in Excel
What does the FILTER and MONTH formula in Excel do?
The FILTER and MONTH formula in Excel extracts data based on a specific month. For example:
=FILTER(A2:D100, MONTH(B2:B100)=5)
Can I use the FILTER and MONTH formula for any month?
Yes! Simply change the month number (e.g., 6 for June, 12 for December). For dynamic filtering, reference a cell:
=FILTER(A2:D100, MONTH(B2:B100)=E2)
Why is my FILTER and MONTH formula not working?
Common issues:
#VALUE! Error: Column B contains non-date values.
#CALC! Error: No matching data—use =FILTER(..., "No Data").
#SPILL! Error: Output cells are blocked—clear the range below.
How do I filter by month AND year in Excel?
Combine MONTH and YEAR:
=FILTER(A2:D100, (MONTH(B2:B100)=5)*(YEAR(B2:B100)=2024))
Does the FILTER and MONTH formula work in older Excel versions?
No—FILTER is available only in Excel 365, Excel 2021, and Excel Online. For older versions, use:
=IFERROR(INDEX(A2:D100, SMALL(IF(MONTH(B2:B100)=5, ROW(B2:B100)-ROW(B2)+1), ROWS(A$2:A2))), “”)
Can I filter by month name (e.g., “June”) instead of a number?
Yes! Use TEXT with FILTER:
=FILTER(A2:D100, TEXT(B2:B100,”mmmm”)=”June”)
How do I avoid errors when no data matches the month?
=FILTER(A2:D100, MONTH(B2:B100)=5, “No Records Found”)
What’s the difference between FILTER + MONTH and PivotTables?
FILTER + MONTH: Dynamic, formula-based, auto-updates.
PivotTables: Manual refresh needed but better for summaries.
Can I use FILTER and MONTH with multiple conditions?
Absolutely! Add more criteria with * (AND logic):
=FILTER(A2:D100, (MONTH(B2:B100)=5)*(C2:C100=”Paid”))
Is there a way to extract unique months from a dataset?
Combine FILTER, MONTH, and UNIQUE:
=UNIQUE(MONTH(B2:B100))
💬 Final Thoughts
If you’re working with monthly reports, time-stamped logs, or financial data, then tFILTER and MONTH Formula in Excel combo is one of the best time-saving Excel hacks out there.
No more scrolling and filtering — just set the formula once and let Excel do the magic.
📌 Pro Tip: Combine this with SORT, UNIQUE, and TEXT for next-level automation.
VLOOKUP Shortcuts – Top 10 VLOOKUP Shortcuts Every Excel User Must Memorize (2025 Guide)
