How to Insert Drop Down Calendar in Excel (With Quick Steps)

In Microsoft Excel, it’s effortless to insert a drop-down calendar. From the drop-down calendar, you can easily insert any date. You can also create a link to the cell with a drop-down calendar where your defined date will be visible. In this article, we give a valuable overview of how to insert a drop-down calendar in Excel. I hope you’ll find it fairly easy to use.

Insert Drop-Down Calendar in Excel: Step-by-Step Procedure

To insert a drop-down calendar in Excel, we’ll show you how to do it step-by-step. All of the following steps are really easy to use and it enhances your Excel knowledge to apply it for a bigger purpose.

Step 1: Enable the Developer Tab in the Ribbon

Before doing anything on inserting a drop-down calendar in Excel, you need to show the Developer tab on the ribbon. As an Excel user, when you open your Excel workbook, primarily, there is no Developer tab on the ribbon. So, you need to change it by customizing the ribbon.

Steps

• Primarily, click on the File tab. In the File tab, select Options.

• That will open up the Excel Options dialog box. Select Customize Ribbon.

• Now, in the right corner, there is Customize the Ribbon, select Main Tabs from there, and in the Main Tabs, click on the Developer option box. After that, click on ‘OK. This will open up the Developer tab on the ribbon.

Step 2: Insert Drop Down Calendar

To insert a drop-down calendar, you have to follow the following steps.

Steps

• First, select the Developer tab. From the Controls group, select the Insert option.

• Now, in the Insert option, click on More Controls from ActiveX Controls.

• A More Controls dialog box will appear, and select Microsoft Date and Time Picker Control 6.0 (SP4). Click on ‘OK’.

• Now, click on any cell where you want to put this.

• After inserting the drop-down calendar, you’ll see an EMBEDDED formula in the formula bar.

Read More: How to Insert Static Date in Excel

Step 3: Customize Drop Down Calendar

After inserting your drop-down calendar in your preferred cell, you can customize this drop-down calendar.

Steps

• You can customize your drop-down calendar by simply dragging it.

• You can alter the Properties of your drop-down calendar by right-clicking on the drop-down calendar. But remember, to do this you have to keep Design Mode on.

• In the Properties dialog box, you can change the height, width, and some more things.

• You can put the drop-down calendar into any place by just dragging it to that place.

Read More: How to Insert Date in Footer in Excel

Step 4: Link the Drop-down Calendar to a Cell in the Excel

In the Properties of the drop-down calendar, you may see there is a ‘LinkedCell’ option. Excel cannot read any date from your drop-down calendar, so, you need to link it to a cell.

Steps

• From the Developer tab, turn on the Design Mode.

• Now, right-click on the drop-down calendar, and from the Context Menu, select Properties.

• In the Properties dialog box, put any cell number in the LinkedCell option.

• Now, turn the Design Mode off and select any date in the drop-down calendar, it will appear in that referred cell.

• A warning dialog box may appear. Click on ‘OK’.

• Change the Checkbox value from False to True in the Properties dialog box to accept null values.

• If you want to see the VBA code associated with this, just right-click on the drop-down calendar and select View Code.

Read More: How to Remove Time from Date in Excel

Insert Drop-down Calendar in Entire Column

Another interesting thing can be done by using the drop-down calendar. You can insert a drop-down calendar in the entire column or multiple columns. Whenever you click any cell, a calendar will open up and you can select a date from there. Both of these things can be done particularly by VBA codes.

1. Drop Down Calendar for a Single Column

Steps

• We want to insert a drop-down calendar for a single column. First, insert a drop-down calendar by using the above steps.
• Now, right-click on the drop-down calendar and select View Codes.

• When you open the view code option, a visual basic interface will appear and there are random codes in that sheet. If you have to customize it, delete this, copy the following code, and paste it there.

Sub Worksheet_SelectionChange(ByVal Target As Range)

  With Sheet3.DTPicker1
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range("B5:B7")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With    

End Sub

• Now, turn off the Design Mode.
• Select any cell within the given cells in the VBA code, you will find drop-down calendar in every cell within the cell limit.

Explanation of the VBA code:

With Sheet3.DTPicker1
    .Height = 20
    .Width = 20

 If Not Intersect(Target, Range("B5:B7")) Is Nothing Then
      .Visible = True

 .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address

Else
 .Visible = False

2. Drop Down Calendar for Multiple Columns

If you want to use your drop-down calendar for multiple columns, we can follow these steps. Remember, you have to insert multiple drop-down calendars to do this.

Steps

• Insert multiple drop-down calendars from the Developer tab.
• Place these drop-down calendars in your desired position by dragging them.
• We would like to insert a drop-down calendar in column B and column D. Right-click on the drop-down calendar and select View Code. Now, copy the following code and paste it into that sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Sheet5.DTPicker1
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range("B5:B9")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With

  With Sheet5.DTPicker2
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range("D5:D9")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With
End Sub

• That will create two drop-down calendars in column B and column D in a given range. You can put any date from the drop-down calendar in that range.

Read More: Formula for Weekly Dates in Excel

Problem with Drop-down Calendar

If you are an active user of Microsoft 365 or Microsoft Excel 2019, you won’t find the drop-down calendar. This drop-down calendar is only available in Microsoft Excel 2007 and 32-bit versions of Excel 2010,2013 and 2016.

Download Practice Workbook

Download this Practice workbook

Conclusion

We have discussed the step-by-step process to insert a drop-down calendar in Excel. We have also tried to show how to use a drop-down calendar both for single columns and multiple columns. I hope you can gain a lot of knowledge from this article. If you have any questions, feel free to ask in the comment section.

Related Articles

• How to Display Day of Week from Date in Excel
• How to Insert Last Saved Date in Excel

<< Go Back to Insert Date | Date-Time in Excel | Learn Excel