How to Create a Monthly Calendar in Excel (3 Effective Ways)

Excel is a great tool to automate several calculations and templates. You can create many types of monthly calendars through Excel. If you are looking for ways to do this, you have landed in the perfect place. In this article, I will show you 3 effective ways to create a monthly calendar in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Effective Ways to Create a Monthly Calendar in Excel

1. Create a Monthly Calendar from Excel Templates

Excel itself has some pre-made templates for calendars. You can create a monthly calendar from any of those templates by following the ways below.

📌 Steps:

  • First and foremost, go to the File tab.

Access the File Tab

  • Afterward, choose the New option from the expanded File tab.
  • Subsequently, a New window will appear.
  • Following, type calendar in the search box and click on the Start searching icon.

Search Calendar Template to Create a Monthly Calendar in Excel

  • As a result, the available templates would come to you as suggestions.

Available Monthly Calendar Templates

  • Now, choose any of the templates you like and click on the Create button.

Create A Monthly Calendar in Excel by Premade Templates

Consequently, you will see a calendar will appear in a new Excel window as per the template. For our chosen template, the outcome would be as follows.

Created Monthly Calendar from Premade Templates

Read More: How to Make a Calendar in Excel Without Template (2 Examples)


2. Create a Custom Monthly Calendar in Excel 2003 | 2007 | 2010 | 2013 | 2016 and Later Versions

Now, it might happen that you don’t like any pre-made templates, rather you want to make your own monthly calendar according to your own choice. In this regard, you can create a monthly calendar in Excel by going through the steps below.

📌 Steps:

  • First, create the headers for your month and select 7 rows and 7 columns.

Selecting Necessary Cells

  • Afterward, upon the selection click on the All Borders icon from the Home tab.

Clicking on All Borders Icon

  • As a result, all the cells will have all borders.
  • Following, at the first selected row, write the name of the weekdays and format them according to your desire.

Weekdays Header Row

  • Now, select the other 6 rows and 7 columns >> go to the Home tab >> Cells group >> Format tool >> Row Height… option.

Change Row Height

  • As a result, the Row Height window will appear.
  • Put the value 40 at the Row height: text box and click on the OK button.

Fix Row Height as 40

  • Afterward, upon the previous selection, go to the Home tab >> Cells group >> Format toool >> Column Width… option.

Access the Column Width Option

  • Consequently, the Column Width window will appear.
  • Afterward, put the value as 12 in the Column width: text box and click on the OK button.

Fix Column Width as 12

  • Now, upon the previous selection, right-click on your mouse and choose the Format Cells… option from the context menu.

Chose the Format Cells Option

  • At this time, the Format Cells window will appear.
  • Subsequently, go to the Alignment tab >> choose the Right (Indent) option in the Horizontal: options list >> choose the Top option in the Vertical: options list >> click on the OK button.

Format Cells Window

Thus, your calendar formatting is fully ready. Now, google the first day of the following month of the following year and put the values accordingly to the weekdays. Finally, the monthly calendar should look like this.

Created a Custom Monthly Calendar in Excel

Read More: How to Make an Interactive Calendar in Excel (2 Easy Ways)


3. Make a Monthly Calendar Using VBA

Another way to create a monthly calendar is to use a VBA code. You can follow the steps below to accomplish this.

📌 Steps:

  • At the very beginning, create a new worksheet and go to the Developer tab >> Visual Basic tool.

Access the Visual Basic Tool

  • As a result, the Microsoft Visual Basic for Applications window will appear.
  • Following, go to the Insert tab >> choose the Module option.

Insert a New Module to Create a Monthly Calendar in Excel

  • As a result, a new module named Module1 has been created.
  • Subsequently, double-click on Module1 and write the VBA code below in the code window.

Access the Module1

This VBA code is taken from this docs.microsoft.com.

Sub CalendarMaker()

       ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
          Scenarios:=False
       Application.ScreenUpdating = False
       On Error GoTo MyErrorTrap
       Range("a1:g14").Clear
       MyInput = InputBox("Type in months and years")
       If MyInput = "" Then Exit Sub
       StartDay = DateValue(MyInput)
       If Day(StartDay) <> 1 Then
           StartDay = DateValue(Month(StartDay) & "/1/" & _
               Year(StartDay))
       End If
       Range("a1").NumberFormat = "mmmm yyyy"
       With Range("a1:g1")
           .HorizontalAlignment = xlCenterAcrossSelection
           .VerticalAlignment = xlCenter
           .Font.Size = 18
           .Font.Bold = True
           .RowHeight = 35
       End With
       With Range("a2:g2")
           .ColumnWidth = 11
           .VerticalAlignment = xlCenter
           .HorizontalAlignment = xlCenter
           .VerticalAlignment = xlCenter
           .Orientation = xlHorizontal
           .Font.Size = 12
           .Font.Bold = True
           .RowHeight = 20
       End With
       Range("a2") = "Sunday"
       Range("b2") = "Monday"
       Range("c2") = "Tuesday"
       Range("d2") = "Wednesday"
       Range("e2") = "Thursday"
       Range("f2") = "Friday"
       Range("g2") = "Saturday"
       With Range("a3:g8")
           .HorizontalAlignment = xlRight
           .VerticalAlignment = xlTop
           .Font.Size = 18
           .Font.Bold = True
           .RowHeight = 21
       End With
       Range("a1").Value = Application.Text(MyInput, "mmmm yyyy")
       DayofWeek = Weekday(StartDay)
       CurYear = Year(StartDay)
       CurMonth = Month(StartDay)
       FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
       Select Case DayofWeek
           Case 1
               Range("a3").Value = 1
           Case 2
               Range("b3").Value = 1
           Case 3
               Range("c3").Value = 1
           Case 4
               Range("d3").Value = 1
           Case 5
               Range("e3").Value = 1
           Case 6
               Range("f3").Value = 1
           Case 7
               Range("g3").Value = 1
       End Select
       For Each cell In Range("a3:g8")
           RowCell = cell.Row
           ColCell = cell.Column
           If cell.Column = 1 And cell.Row = 3 Then
           ElseIf cell.Column <> 1 Then
               If cell.Offset(0, -1).Value >= 1 Then
                   cell.Value = cell.Offset(0, -1).Value + 1
                   If cell.Value > (FinalDay - StartDay) Then
                       cell.Value = ""
                       Exit For
                   End If
               End If
           ElseIf cell.Row > 3 And cell.Column = 1 Then
               cell.Value = cell.Offset(-1, 6).Value + 1
               If cell.Value > (FinalDay - StartDay) Then
                   cell.Value = ""
                   Exit For
               End If
           End If
       Next

       For x = 0 To 5
           Range("A4").Offset(x * 2, 0).EntireRow.Insert
           With Range("A4:G4").Offset(x * 2, 0)
               .RowHeight = 65
               .HorizontalAlignment = xlCenter
               .VerticalAlignment = xlTop
               .WrapText = True
               .Font.Size = 10
               .Font.Bold = False
               .Locked = False
           End With
           With Range("A3").Offset(x * 2, 0).Resize(2, _
           7).Borders(xlLeft)
               .Weight = xlThick
               .ColorIndex = xlAutomatic
           End With

           With Range("A3").Offset(x * 2, 0).Resize(2, _
           7).Borders(xlRight)
               .Weight = xlThick
               .ColorIndex = xlAutomatic
           End With
           Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
              Weight:=xlThick, ColorIndex:=xlAutomatic
       Next
       If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _
          .Resize(2, 8).EntireRow.Delete
       ActiveWindow.DisplayGridlines = False
       ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
          Scenarios:=True

       ActiveWindow.WindowState = xlMaximized
       ActiveWindow.ScrollRow = 1

       Application.ScreenUpdating = True
       Exit Sub
MyErrorTrap:
       MsgBox "You may not have entered your Month and Year correctly." _
           & Chr(13) & "Spell the Month correctly" _
           & " (or use 3 letter abbreviation)" _
           & Chr(13) & "and 4 digits for the Year"
       MyInput = InputBox("Type in Month and year for Calendar")
       If MyInput = "" Then Exit Sub
       Resume
   End Sub
  • Afterward, press Ctrl+S.
  • At this time, a Microsoft Excel dialogue box will appear. Click on the No button here.

Microsoft Excel Dialogue Box

  • As a result, the Save As dialogue box will appear.
  • Subsequently, choose the Save as type: option as .xlsm and click on the Save button.

Save As Dialogue Box

  • Now, close the code window and go to the Developer tab >> Macros tool.

Access the Macros Tool

  • As a result, the Macro window will appear.
  • Following, choose the CalendarMaker macro and click on the Run button.

Run Macro to Create Calendar in Excel

  • As a result, a Microsoft Excel dialogue box will appear.
  • Here, write the first 3 letters of the month and 4 digits of the year.
  • Finally, click on the OK button.

Microsoft Excel Dialogue Box

Thus, you will see the following month’s calendar will be created in your worksheet and the result would look like this.

Created a Monthly Calendar in Excel with VBA


Create an Interactive/ Dynamic Calendar in Excel with Formula

Now, if you want to create an interactive/dynamic calendar in Excel with formula, you have to do several extra works. Go through the steps below carefully to achieve this.

📌 Steps:

  • First, create two helper tables, one with the holiday list and another with the month name list.

Helper Tables

  • Now, create a custom monthly calendar template as per your choice.
  • Next, for dynamic purposes, we want to automate the month’s name now.
  • To do this, select the H4 cell >> go to the Data tab >> Data Tools group >> Data Validation tool >> Data Validation… option.

Access Data Validation Tool

  • Subsequently, the Data Validation window will appear.
  • Afterward, go to the Settings tab here >> choose the List option from the Allow: options list >> refer to the E5:E16 cells at the Source: text box >> click on the OK button.

Data Validation Window

  • As a result, you will see that there will be a dropdown list in the H4 cell and you can select the month by clicking.

Month in Dropdown List

  • After choosing the month as Jan, click on the B6 cell and insert the following formula.
=IF(MONTH(DATE($D$4,MATCH($H$4,'Helper Columns'!$E$5:$E$16,0),1)+SEQUENCE(6,7)-WEEKDAY(DATE($D$4,MATCH($H$4,'Helper Columns'!$E$5:$E$16,0),1),2))=MATCH($H$4,'Helper Columns'!$E$5:$E$16,0),DATE($D$4,MATCH($H$4,'Helper Columns'!$E$5:$E$16,0),1)+SEQUENCE(6,7)-WEEKDAY(DATE($D$4,MATCH($H$4,'Helper Columns'!$E$5:$E$16,0),1),2),"")
  • Subsequently, hit the Enter button.

Insert Formula to Fill Dates

  • Thus, you can get all the dates according to the weekdays for the following month correctly.
  • But, the values are not in the proper format.
  • To correct this, right-click on the whole B6:H11 selection and choose the Format Cells… option from the context menu.

Choose Format Cells Option

  • As a result, the Format Cells dialogue box will appear.
  • Go to the Number tab here >> choose the Custom option from the Category: pane >> write dd in the Type: text box >> click on the OK button.

Format Cells Dialogue Box

  • Thus, your calendar dates are formatted properly and you can see the full calendar for the following month.

Created a Monthly Calendar in Excel

  • Now, to mark the weekends differently, select the weekends’ cells >> go to Home tab >> Font group >> Fill Color icon >> choose Orange, Accent 2, Lighter 40% color.

Access Fill Color Icon

  • Now, to mark the holidays, select B6:H11 cells >> go to Home tab >> Conditional Formatting tool >> New Rule… option.

Access Conditional Formatting Tool

  • Consequently, the Edit Formatting Rule window will appear.
  • Following, choose the last option from the Select a Rule Type: options list >> write the formula below in the Format values where this formula is true: formula bar >> click on the Format… option.
=ISNUMBER(VLOOKUP('Dynamic Monthly Calender'!B6,'Helper Columns'!$C$5:$C$15,1,0))

Edit Formatting Rule Window to Create a Monthly Calendar in Excel

  • Subsequently, go to the Fill tab from the appeared window >> choose red color >> click on the OK button.

Choose your Desired Format

  • As a result, the Edit Formatting Rule window will appear again with a preview. Click on the OK button.

Edit Formatting Rule Window

Thus, you will get your interactive monthly calendar where the weekends are displayed in different colors and the holidays are also displayed in different colors. You can change month by dropdown in the H4 cell and thus, the weekdays, weekends, and holidays would also change dynamically.

Created a Dynamic Monthly Calendar in Excel


Create a Yearly Calendar in Excel

Now, if you want to create a yearly calendar in Excel, you can follow the steps below.

📌 Steps:

  • At the very beginning, create a template for the days and months according to your own choice.

Craete a Primary Template for Yearly Calendar

  • Afterward, click on the B6 cell and insert the following formula.
=IF(MONTH(DATE($B$3,MATCH($B$4,'Helper Columns'!$E$5:$E$16,0),1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$3,MATCH($B$4,'Helper Columns'!$E$5:$E$16,0),1),2))=MATCH($B$4,'Helper Columns'!$E$5:$E$16,0),DATE($B$3,MATCH($B$4,'Helper Columns'!$E$5:$E$16,0),1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$3,MATCH($B$4,'Helper Columns'!$E$5:$E$16,0),1),2),"")
  • Subsequently, press the Enter button.

Insert Formula to Autofill Dates

  • As a result, you will see the dates will be set according to the particular days of the following month.
  • But, for lesser space and formatting issues, it might show ### sometimes.
  • To recover this, select the whole month’s dataset and right-click on the cells.
  • Following, choose the Format Cells… option.

Choose the Format Cells... Option

  • Consequently, the Format Cells window will appear.
  • Now, go to the Number tab >> choose the Custom option from the Category: pane >> write dd in the Type: text box >> click on the OK button.

Format Cells Window

  • Repeat these procedures for each month of the following year.
  • Thus, you will get the dates according to the weekdays for the full year.

Custom Yearly Calendar in Excel

  • Now, to mark the weekends, select the weekend cells >> go to the Home tab >> Font group >> Fill Color icon >> choose the color Orange, Accent 2, Lighter 40%.

Highlight the Weekends

  • Afterward, for marking the January holidays, select all the days of the month >> go to the Home tab >> Conditional Formatting tool >> choose New Rule… option.

Access the Conditional Formatting Tool

  • As a result, the Edit Formatting Rule window will appear.
  • Following, choose the last option from the Select a Rule Type: options >> insert the following formula in the Format values where this formula is true: text box >> click on the Format… button.
=ISNUMBER(VLOOKUP(B6,'Helper Columns'!$C$5:$C$15,1,0))

Edit Formatting Rule Window

  • Afterward, go to the Fill tab >> choose the Red color >> click on the OK button.

Choose Format

  • As a result, the Edit Formatting Rule window will appear again with a preview. Click on the OK button.

Confirm Format

Repeat these procedures for all the other months using the same formula and format dynamically and thus you will get your dynamic yearly calendar.

Created Yearly Calendar with Holidays in Excel

Read More: How to Create a Weekly Calendar in Excel (3 Suitable Ways)


How to Create a Monthly Schedule in Excel

You can also create a monthly schedule in Excel easily. To do this, follow the steps below carefully.

📌 Steps:

  • Initially, go to the File tab.

Access File Tab

  • Afterward, select the New option from the expanded File tab >> write Monthly Schedule in the search box >> click on the Start searching icon.

Search Monthly Schedule

  • As a result, several templates will appear.
  • Following, click on the 12-Month Calendar template.

Choose your Template

  • Afterward, click on the Create button.

Create a Monthly Schedule in Excel

As a result, a new Excel file will be created, and thus you will get your desired monthly schedule in Excel.

Created a Monthly Schedule Calendar in Excel


Conclusion

So, in this article, I have shown you how to create monthly calendars in Excel. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!


Related Article

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo