Function Problems

How to Display Calendar Events for a Selected Month in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 868 views

Question details

The user needs to show only calendar or schedule entries whose dates belong to the month selected in cell A2, using the existing event table layout and date column.

How to Display Calendar Events for a Selected Month in Excel
Product
WPS Office Spreadsheet
Device & OS
not provided
Scenario
A worksheet contains an event table with real Excel dates, and the user wants a dynamic filtered list that changes when cell A2 is updated to a different month.
Observed behavior
Formula-based task: when the month selected in cell A2 changes, only matching event rows should appear in the output area.
Before you start

Make sure each event has a real Excel date in a dedicated Date column. If the dates are stored as text or use inconsistent formats, format the date column as Date before applying the month filter.

Solution 1Recommended

Use the FILTER Formula to Show Events for the Selected Month

Best for Microsoft 365 and WPS Office versions that support dynamic array functions; it automatically spills the matching event rows.

The FILTER function evaluates the EventTable date column and returns every row whose month matches cell A2. If A2 includes a year, add a year check so results do not mix the same month from different years.

1
Create or select an Excel Table

Select the event data including the Date column, go to the Insert tab, and click Table. Name the table EventTable in the Table Design tab.

2
Enter the selected month cell

Type a valid date such as 1/1/2025 into cell A2 and format it as a month or month and year if needed.

3
Enter the FILTER formula

In the first cell of the output area, enter =FILTER(EventTable,MONTH(EventTable[Date])=MONTH($A$2),"No events") and press Enter.

4
Add year criteria when needed

If the selected value contains a year, use =FILTER(EventTable,(MONTH(EventTable[Date])=MONTH($A$2))*(YEAR(EventTable[Date])=YEAR($A$2)),"No events").

Use the FILTER Formula to Show Events for the Selected Month
Dynamic events update: The results refresh automatically when cell A2 or the event table changes.

Display Calendar Events by Selected Month in WPS Office Spreadsheet

Use WPS Office Spreadsheet to create an event table, enter the FILTER formula, and return only events whose Date matches the month selected in cell A2. WPS Office opens Excel workbooks and supports familiar formulas for a free, lightweight spreadsheet workflow.

  1. 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheet, open the workbook that contains the event table, and select the sheet with the calendar data.
  2. 2. Convert the event range to a table: Select the data including headers, click the Insert tab, and choose Table. Confirm the range and enable the header row.
  3. 3. Enter the month-selection date: Click cell A2, enter a valid date such as 1/1/2025, and if desired apply a month number or month name format.
  4. 4. Enter the FILTER formula: In the first output cell, type =FILTER(EventTable,MONTH(EventTable[Date])=MONTH($A$2),"No events") and press Enter. Add a YEAR condition when the selected value includes a year.
Filter event rows by selected month using Excel-compatible FILTER and MONTH formulasWorks with existing .xlsx calendars and maintains Microsoft Excel format compatibilityFree, lightweight Office suite with a familiar ribbon and spreadsheet functionsDynamic results update when the selected month cell or event table changesNo subscription needed for everyday scheduling and reporting tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return #SPILL! when showing events?

The output area likely has data in the cells where the results need to spill. Clear the cells below and to the right of the formula, or move the formula to an empty area.

Do I need a real Date column for the month filter to work?

Yes. The MONTH function requires Excel serial dates. If dates are stored as text, use Data > Text to Columns, or reformat and re-enter the dates before applying the formula.

How can I include the year when cell A2 only displays a month name?

Store a full date in A2 and format it as mmmm/yyyy. Then add YEAR(EventTable[Date])=YEAR($A$2) to the FILTER formula so the same month in different years is not mixed.

Can I use this formula in older Excel without FILTER?

Older Excel versions do not support FILTER. Use a helper column with =MONTH([@Date])=MONTH($A$2) and filter TRUE rows, or open the workbook in WPS Office Spreadsheet.

How do I return "No events" when no rows match?

Use the third argument of FILTER as the if_empty message. In the formula =FILTER(EventTable,MONTH(EventTable[Date])=MONTH($A$2),"No events"), the message appears when no rows match.