Excel Tutorials · Lesson 119
A calendar you retype events into every month is just a chore with a grid around it. This lesson builds one that pulls events in automatically from a separate list, so adding a row to that list is the only maintenance the calendar ever needs.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
A dynamic calendar is a month grid built on the worksheet itself, where each day's cell shows any events scheduled for that date, pulled automatically from a separate events list rather than typed directly into the calendar. Add a row to the events list, and the right day updates on its own.
The grid itself is just a sequence of dates laid out Sunday through Saturday (or however your week starts), with WEEKDAY figuring out how many blank cells to leave before the 1st so it lands under the correct column. The part that makes it "dynamic" is the formula under each date: a TEXTJOIN wrapped around an IF check against the events list, pulling in every event whose date matches that cell.
Because the events list and the calendar grid are separate, the same technique scales past a single month. Point the grid's starting date at a dropdown-driven cell using EDATE, and the whole calendar, dates and events both, regenerates for whatever month is selected.
The honest framing: this is a formula-and-formatting build, not a native Excel calendar object. There's no built-in "insert calendar" button that does this. What you get in exchange is a calendar that lives inside your existing workbook, next to whatever data it's tracking, with no separate app or add-in required.
Step-by-step, matching the video above.
Set up a Table with a Date column and an Event column, one row per event. Converting it to a Table (Ctrl+T) lets it grow without touching the calendar formulas.
Put the first of the month in one cell, then use WEEKDAY to figure out how many blank cells to leave before it, so day 1 lands under the correct day-of-week column.
Starting from the first date cell, add 1 to each following cell across the grid so every day of the month gets its own cell in order.
Below each date, add a formula like =TEXTJOIN(", ",TRUE,IF(EventDates=A2,EventNames,"")) entered as an array formula, joining every event that matches that day.
Apply conditional formatting with the rule =A2=TODAY() across the date cells so today's cell highlights itself automatically, every day.
Point the calendar's starting date at a dropdown-driven cell using EDATE, so switching the dropdown regenerates the whole grid for a different month.
Add borders around each day cell, bold the day-of-week headers, and size the rows tall enough for two or three lines of event text to display cleanly.
| Date | Event |
|---|---|
| 3/12/2027 | Client Call |
| 3/12/2027 | Invoice Due |
| 3/15/2027 | Team Standup |
=TEXTJOIN(", ",TRUE,IF(EventDates=A2,EventNames,"")) run on the calendar cell for March 12th returns "Client Call, Invoice Due", pulling both matching rows automatically. The March 15th cell independently returns just "Team Standup." Add a fourth row to this list and the right calendar cell updates with no other change needed.
Four formulas doing four different jobs.
=WEEKDAY(DATE(2027,3,1)) returns which day of the week the 1st falls on, telling you how many blank cells to leave in the first row before day 1 appears.
Each date cell after the first is simply the previous cell plus 1, so the whole grid fills in as a running sequence of consecutive dates across rows and columns.
The IF checks every row of the events list against the current calendar date; TEXTJOIN stitches every match together into one readable string, skipping blanks automatically.
=EDATE(StartDate,MonthOffset) shifts a date forward or back by whole months, which is what a month-selector dropdown drives to regenerate the calendar.
Three formatting touches that make it read like an actual calendar.
Day cells need enough height for two or three lines of event text, with Wrap Text turned on so longer event names don't get cut off or overflow into neighboring cells.
A rule of =A2=TODAY() applied to the whole grid highlights whichever cell holds today's date, and re-evaluates itself every day without any manual step.
Format the date cells as d instead of a full date, so each cell shows just "12" or "15" in the corner, calendar-style, while still holding the real underlying date.
| Approach | Adding a new event | Switching months |
|---|---|---|
| Static (typed directly) | Retype it into a cell | Rebuild the whole grid |
| Dynamic (this lesson) | Add one row to the list | Change one dropdown |
The upfront setup for the dynamic version takes longer the first time. Every month after that, it costs nothing, while the static version means retyping or rebuilding the whole grid again.
Four things that go wrong on the first attempt.
WEEKDAY wasn't used to offset the grid, or its return type doesn't match how the week starts on your calendar. Check the optional second argument to WEEKDAY.
The date in the events list and the date in the calendar grid don't actually match, often because one is stored as text and the other as a real date value.
On older Excel versions, TEXTJOIN with an array condition needs to be entered as an array formula. Excel 365 handles this automatically with no special keys.
The events list wasn't referenced with an unlocked range, or the calendar's date sequence isn't actually rebuilding from the dropdown-driven start date.
Where a dynamic calendar earns its place over a static grid.
One events list, one calendar view, updated instantly whenever someone's shift changes without touching the grid itself.
Publish dates plotted alongside deadlines, with the calendar always reflecting the current plan pulled straight from the tracking list.
Sheets supports TEXTJOIN, IF, WEEKDAY and EDATE identically, so this entire technique carries over without modification.
For a one-off printable calendar with no recurring updates, a plain manually typed grid is faster to build and not worth the formula overhead.
This defeats the entire point of a dynamic calendar. Every event should live in the events list, never typed straight into a calendar cell.
A date typed or pasted as text won't match a real date value in the calendar grid, so TEXTJOIN silently returns nothing even when an event genuinely exists.
A fixed range like A2:A50 stops picking up new events once the list grows past it. Reference an Excel Table column instead so it expands on its own.
Without a WEEKDAY-based offset, every month starts in the same grid column regardless of what day the 1st actually falls on.
If the starting date isn't driven by a single dropdown-linked cell, switching months means manually rebuilding dates across the whole grid every time.
A day with two or three events needs enough row height and Wrap Text turned on, or the text gets clipped and part of the event list becomes invisible.
A schedule shouldn't need an array formula to stay current.
Free 14-day trial. No credit card required.
A calendar grid that automatically pulls in events from a separate list based on each date, instead of a static grid where events have to be typed directly into the calendar cells.
Two columns at minimum: a date column and an event name column, one row per event, ideally set up as an Excel Table so it expands automatically as events are added.
Each calendar day cell runs a formula, typically TEXTJOIN combined with an IF check, that scans the events list for any row whose date matches that cell and joins the matching event names together.
TEXTJOIN handles it by joining every match with a line break or comma, so a busy day shows multiple event names stacked in the same cell instead of overwriting each other.
Yes, if the calendar's starting date is built from a single dropdown-driven cell using EDATE or DATE, changing the dropdown regenerates the whole grid and its events for the new month.
The first date of the month usually isn't a Sunday or Monday. Use WEEKDAY on the first of the month to calculate how many blank cells to leave before day 1 lands in its column.
Apply conditional formatting with a rule like =A1=TODAY() across the calendar grid, so whichever cell holds today's date gets highlighted without any manual updating.
Add a category column to the events list, then use conditional formatting rules keyed to that category to color the matching calendar cells or the event text itself.
TEXTJOIN and IF work in Excel 2016 and later. A version using FILTER instead is cleaner but requires Excel 365 or 2021+.
Yes. Sheets supports TEXTJOIN, IF, WEEKDAY and conditional formatting the same way, so the whole technique carries over with no changes.
No. A calendar picker is a small popup for selecting a single date into a cell. This is a full month grid laid out on the worksheet itself, showing every day and its events at once.
Yes, it prints like any other worksheet range. Set the print area to just the calendar grid and use Page Layout view to confirm it fits on one page before printing.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data already connected to where it belongs.
Start Your Free Trial →