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.

• 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.

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

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

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.

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.

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

• 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.

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

• 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.

• Afterward, upon the previous selection, go to the Home tab >> Cells group >> Format tool>> 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.

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

• 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.

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.

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.

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

• 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.

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.

• 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.

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

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

• 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.

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

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.

• 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.

• 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.

• 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.

• After choosing the month as Jan, click on the B6 cell and insert the following formula.

• Subsequently, hit the Enter button.

• 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.

• 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.

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

• 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.

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

• 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.

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

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

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.

How to 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.

• Afterward, click on the B6 cell and insert the following formula.

• Subsequently, press the Enter button.

• 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.

• 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.

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

• 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%.

• 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.

• 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.

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

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

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

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.

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

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

• Afterward, click on the Create button.

As a result, a new Excel file will be created, and thus you will get your desired monthly schedule 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

• How to Make a Blank Calendar in Excel (Download Free Template)