Filter and month formula in excel

🔥 Unlock Hidden Insights with This FILTER + MONTH Formula in Excel (No VBA Needed!)

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!

🧠 What Does This Formula 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:D100 where 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:

NameDateStatusHours
John01-04-2024Present8
Sarah10-05-2024Present7
Amit20-05-2024Absent0
David05-06-2024Present9

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 FILTER is not supported there).

🔍 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

ErrorReasonFix
#VALUE!Using FILTER in unsupported Excel versionUse Excel 365 or Excel Online
#CALC!No data matches the conditionWrap with IFERROR: =IFERROR(..., "No data found")
Dates not filtered correctlyColumn B may not have valid date formatsCheck 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:

  1. Go to Data > Data Validation
  2. Choose List
  3. 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.

🖥️ Want To Learn With Practice?

💬 Final Thoughts

If you’re working with monthly reports, time-stamped logs, or financial data, then this FILTER + MONTH 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.

🔥 10 VLOOKUP Shortcuts in Excel That Will Save You Hours

VLOOKUP Shortcuts – Top 10 VLOOKUP Shortcuts Every Excel User Must Memorize (2025 Guide)

Sharing Is Caring:

Contact Me

Leave a Comment