Create a monthly Excel calendar, based on a few short formulas. Next, add formatting and a heading, and if you’re feeling fancy, show a list of the month’s holidays too! This example uses functions that are available in Excel 365, and in Excel for the Web.
Make an Excel Calendar
In the video below, you’ll see the steps to create a printable monthly Excel calendar, based on a few short formulas.
After the calendar is set up, you can change the year or month number, to update the calendar, with that month’s holidays highlighted.
- Note: The sample file uses the SEQUENCE, FILTER and TEXTJOIN functions that are available in Excel 365. If you don’t have that version, you can download the sample file, and then open the workbook in Excel for the Web.
Video: Make a Monthly Excel Calendar Step-by-Step
This video shows how to make the monthly calendar, starting from scratch.
It’s about 11 minutes long, and here’s the timeline of all the steps that are included. Most of the formulas are short – only the last formula is a bit complicated. I’ll walk you through that formula, so you can see how it works.
Video Timeline
- 00:00 Introduction
- 00:13 Start Calendar
- 00:58 Year and Month
- 01:25 Date Formulas
- 02:20 Month End
- 02:50 Sunday Formula
- 04:56 Calendar Formula
- 05:32 Calendar Format
- 06:15 Calendar Heading
- 07:13 Hide Dates
- 08:35 Highlight Holidays
- 09:48 List Holidays
- 10:22 Get the Excel File
Get the Excel Monthly Calendar
In this example, the Excel worksheet has 5 cells with formulas, and two data entry cells — for the year and the month. Change the year or month number, to see a different calendar. Holidays from the selected month are highlighted, and the holiday names are listed at the top of the calendar.
Visit my Contextures site to download the Excel sample file, and for the written steps.
Note: The sample file uses the SEQUENCE, FILTER and TEXTJOIN functions that are available in Excel 365. If you don’t have that version, you can download the sample file, and then open the workbook in Excel for the Web.
_____________________
Create Excel Monthly Calendar From 6 Formula Cells
