In Excel 365, =FILTER(A2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2),"No expenses this month") spills only the rows whose date falls in the month typed in H1 and the year typed in H2. Change the two input cells and the list updates. The third argument is the message shown when nothing matches.
How does the formula work?
FILTER(array, include, [if_empty]) keeps the rows where the include test is TRUE. Here the test is two conditions multiplied together: MONTH(A2:A500)=H1 gives TRUE or FALSE for every row, and so does the year test. Multiplying turns TRUE into 1 and FALSE into 0, so a row survives only when both are 1. That is how you write AND inside FILTER.
| Cell | Holds | Example |
|---|---|---|
| H1 | Month number | 4 for April |
| H2 | Year | 2026 |
| A2:A500 | Real dates | Not text that looks like a date |
| A2:D500 | Date, merchant, category, amount | The columns you want back |
FILTER needs Microsoft 365 or Excel 2021. It is not in Excel 2019 or older, so a file you share with someone on an older version will show errors for them.
Why do I get #VALUE! or nothing at all?
The usual reason is that the dates are text. A date imported from a bank export may look right but be stored as words, and MONTH cannot read it. Test with =ISNUMBER(A2): TRUE means a real date. If it is FALSE, convert the column, for example with Data, Text to Columns, choosing Date and the day-month-year order that matches your statement. Blank rows are harmless here: they return month 1 of year 1900, which fails the year test.
Is there a more reliable way than MONTH and YEAR?
A date-range test is shorter to reason about and still works when your dates carry a time. Put the first day of the month in H3 with =DATE(H2,H1,1), then:
=FILTER(A2:D500,(A2:A500>=H3)*(A2:A500<=EOMONTH(H3,0)),"No expenses this month")
EOMONTH(H3,0) returns the last day of the same month. If your dates include a time of day, use A2:A500<EOMONTH(H3,0)+1 so the final day is not cut off.
How do I total what I filtered?
Do not add up the spilled cells by hand. Use SUMIFS on the original data, with the same month boundaries:
=SUMIFS($D$2:$D$500,$A$2:$A$500,">="&H3,$A$2:$A$500,"<="&EOMONTH(H3,0))
Say April has 3 rows: ₹1,200, ₹850 and ₹2,450. The total shows ₹4,500, and it matches the spilled list when you add them up. If it does not, your list is missing a date stored as text.
How do I follow April to March instead?
Most Indian households and businesses think in financial years. Put the starting year in H4, build the first day with =DATE(H4,4,1) in H5, and use H5 and =EOMONTH(H5,11) as the two boundaries in the same FILTER. For a hypothetical H4 of 2025, that returns rows from 1 April 2025 to 31 March 2026.
Can I show the month name above the list?
Yes. A heading cell such as =TEXT(H3,"mmmm yyyy") shows April 2026 above the spilled rows and changes with your inputs. Beside it, a count of the month's rows is =COUNTIFS($A$2:$A$500,">="&H3,$A$2:$A$500,"<="&EOMONTH(H3,0)). When the month is empty it returns 0 rather than an error, which tells you at a glance that you have not pasted that month's statement yet.
Before you share the sheet
- Confirm every date cell is a real date
- Use a range that reaches beyond your last row, or switch to an Excel table
- Keep the spill area clear below the formula
- Check one month's total against your bank statement
A single-month view is handy, but you end up rebuilding it for categories, accounts and comparisons. When that happens, SpendixAI's Report builder lets you filter and group transactions without formulas, and you can download the result as CSV.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


