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

In Microsoft Excel, it’s effortless to insert drop down calendar. From the drop down calendar, you can easily insert any date. You can also create a link to the cell with 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.


Download Practice Workbook

Download this Practice workbook


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

To insert 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 Developer Tab in the Ribbon

Before doing anything on inserting 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 the Options.

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

Enable Developer Tab in the 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.

Enables Developer Tab in the Ribbon

Read More: How to Insert Day and Date in Excel (3 Ways)


Step 2: Insert Drop Down Calendar

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

Steps

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

Insert Drop Down Calendar

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

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

Insert Drop Down Calendar

  • 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 a Date Picker in Excel (With Step-by-Step Procedure)


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 simple drag it.

Customize Drop Down Calendar

  • 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 Excel (7 Simple Methods)


Similar Readings


Step 4: Link Drop Down Calendar to a Cell in Excel

In the Properties of 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.

Link Drop Down Calendar to a Cell

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

Link Drop Down Calendar to a Cell

  • Now, turn the Design Mode off and select any date in the drop down calendar, it will appear at 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.

Link Drop Down Calendar to a Cell

Read More: Excel Automatically Enter Date When Data Entered (7 Easy Methods)


Insert Drop Down Calendar in Entire Column

Another interesting thing can be done by using drop down calendar. You can insert 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

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

Drop Down Calendar in a Single Column

  • 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, so delete this and 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.

Drop Down Calendar in a Single Column

Explanation of the VBA code:

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

This code denotes that you need to select your sheet name where you want to apply this code and the date picker number. You can also modify the height and width values.

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

This code denotes that if you select any cell in this range, drop down calendar will be visible in every cell within this range.

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

Top property denotes the top belongings value of a specified cell.

Left property denotes the next right cell of the specified cell.

LinkedCell connects drop down calendar with the specified cell.

Else
 .Visible = False

This denotes if you select any other cell than this given cell, drop down calendar won’t be visible.

Read More: How to Insert Dates in Excel Automatically (3 Simple Tricks)


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 for doing this.

Steps

  • Insert multiple drop down calendars from the Developer tab.
  • Place these drop down calendars to your desired position by dragging.
  • We would like to insert 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 drop down calendar in that range.

Drop Down Calendar in Multiple Columns

Note:

You have to change CheckBox from False to True to avoid any error message.

Read More: Excel Macro: Insert Date and Time in a Cell (4 Examples)


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.


Conclusion

We have discussed the step-by-step process to insert drop down calendar in Excel. We have also tried to show how to use a drop down calendar both for the single column 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, and don’t forget to visit our Exceldemy page for more knowledge.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo