How to Create a Yearly Calendar in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

An Excel calendar could be pretty helpful if you prefer to plan ahead and have a busy schedule. Remembering this, in this article, we’ll go through the steps along with the explanation of how to create a yearly calendar in Excel. Interestingly, this calendar is dynamic and interactive. That means the calendar updates itself when you change the year’s value. In addition, we’ll also discuss how to create a monthly calendar using Excel VBA.

Create a Yearly Calendar in Excel: 4 Steps

Now, before we take a thorough analysis of this tutorial, let me show you what the final output will look like. Here, we have a yearly calendar, where the Saturdays and Sundays (weekends) are shown in light orange while the Holidays appear in bright orange.

how to create a yearly calendar in excel

how to create a yearly calendar in excel

Here, we have used Microsoft Excel 365 version, and you may use any other version according to your convenience.

📌 Step 01: Create Primary Outline

  • In the first step, we’ll create an outline of the calendar by dividing the Months across 3 columns and 4 rows.
  • Next, enter the 7 days in a Week and highlight the Weekends, in this case, Saturdays and Sundays.

Creating Basic Outline

📌 Step 02: Enter Month Names

  • Second, go to the B4 cell >> type in the number 1 >> hit the CTRL + 1 keys on your keyboard.

Entering Month Names

Now, this opens the Format Cells dialog box.

  • In the beginning, move to the Number tab >> choose the Custom option >> in the Type field, enter “January” (within inverted commas) >> lastly, jump to the Font tab.

Using Format Cells Wizard

  • Next, choose the Bold-Italic Font style >> select a Color, here we’ve chosen purple >> hit the OK button.

Setting Font style and Color

  • In turn, select the B4:H4 range of cells >> press CTRL + 1 keys to navigate to the Format Cells window.
  • Following this, proceed to the Alignment tab >> in the Horizontal section, choose the Center Across Selection option >> press the OK button.

Setting Text Alignment

Finally, this should display the month name January as shown in the image below.

Entering months on a yearly calendar in excel

  • Similarly, repeat the same procedure for the other months and your output should look like the following picture.

months on a yearly calendar in excel

yearly calendar months

📌 Step 03: Utilize Excel Functions to Make Dynamic Calendar

  • Third, move to the B6 cell and enter the formula given below.


Here, the B2 and B4 cells refer to the Year 2022 and the Month of January respectively.

📃 Note: The SEQUENCE function is available on Excel 365, Excel 2021, and Excel for the web, so please make sure to have the compatible version.

Utilize Excel Functions to Make Dynamic Calendar

  • Now, press the ENTER key and the formula will populate all the days for the Month of January.

📃 Note: Please make sure to change the Date format to only dd in the Format Cells window by pressing the CTRL + 1 keys on your keyboard.

Showing dates on yearly calendar in excel

Eventually, the results should look like the image given below.

how to create a yearly calendar in excel

a yearly calendar in excel

📌 Step 04: Indicate Holidays in the Calendar

  • In the fourth and final step, make a List of the Holidays in a new sheet as shown below.

List of Holidays

  • Then, select the B6:H11 cells >> go to the Conditional Formatting drop-down >> choose the New Rule option.

Conditional Formatting

In an instant, the New Formatting Rule wizard pops up.

  • Next, choose the Use a formula to determine which cells to format option.
  • Then, in the Rule Description enter the following formula.


Here, the B6 cell points to the Monday in the Month of January while the Holidays!$C:$C represents the Dates in the Holidays sheet.

Formula Breakdown:

  • VLOOKUP(B6,Holidays!$C:$C,1,0)) → the VLOOKUP function looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, B6 ( lookup_value argument) is mapped from the Holidays!$C:$C (table_array argument) array. Next, 1 (col_index_num argument) represents the column number of the lookup value. Lastly, 0 (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → #N/A
  • ISNUMBER(VLOOKUP(B6,Holidays!$C:$C,1,0)) becomes
    • ISNUMBER(#N/A) → the ISNUMBER function checks whether a value is a number and returns TRUE or FALSE. Here, #N/A is the value argument, and since it is not a number so the function returns FALSE.
    • Output → FALSE

Highlighting Holidays with Conditional Formatting

Now, this should highlight the holiday on January 1st in bright orange color.

shoing holidays in a yearly calendar in excel

In a similar style, repeat the same process for the other months and your output should look like the screenshots shown below.

how to create a yearly calendar in excel

how to create a yearly calendar in excel

How to Create a Monthly Calendar

What if you want to create a monthly calendar? Our next method has you covered! Here, we’ll use VBA Code to automate the task for us, therefore let’s see it in action.

📌 Steps:

  • First and foremost, navigate to the Developer tab >> click the Visual Basic button.

How to Create a Monthly Calendar

This opens the Visual Basic Editor in a new window.

  • In the following step, go to the Insert tab >> select Module.

Inserting Module

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub CalendarMaker()
'This code was taken from
       ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
       Application.ScreenUpdating = False
       On Error GoTo MyErrorTrap
       MyInput = InputBox("Type in Month and year for Calendar ")
       If MyInput = "" Then Exit Sub
       StartDay = DateValue(MyInput)
       If Day(StartDay) <> 1 Then
           StartDay = DateValue(Month(StartDay) & "/1/" & _
       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

       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, _
               .Weight = xlThick
               .ColorIndex = xlAutomatic
           End With

           With Range("A3").Offset(x * 2, 0).Resize(2, _
               .Weight = xlThick
               .ColorIndex = xlAutomatic
           End With
           Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
              Weight:=xlThick, ColorIndex:=xlAutomatic
       If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _
          .Resize(2, 8).EntireRow.Delete

       ActiveWindow.DisplayGridlines = False
       ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _

       ActiveWindow.WindowState = xlMaximized
       ActiveWindow.ScrollRow = 1

       Application.ScreenUpdating = True
       Exit Sub

       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
   End Sub

VBA Code window

  • In turn, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

  • Following this, select the CalendarMaker macro >> hit the Run button.

Running VBA Macro

Now, this opens an Input box >> enter the Month Name and Year as shown below.

Entering Month and Year

Subsequently, the results should look like the picture given below.

how to create a monthly calendar in excel

Download Practice Workbook

You can download the practice workbook from the link below.


I hope this article helps you understand how to create a yearly calendar in Excel. If you have any queries, please leave a comment below.

Related Article

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF