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

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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.

Insert Drop Down Calendar

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

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

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

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

Link Drop Down Calendar to a Cell

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.

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

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 the 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 within this range, the 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 the drop-down calendar with the specified cell.

Else
 .Visible = False

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


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.

Drop Down Calendar in Multiple Columns

Note:

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

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


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

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.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

2 Comments
  1. re: M365 -how can we create a drop-down calendar wth this version?
    Thanks in advance,
    Aaron

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo