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 = 20This 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 = TrueThis 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.AddressTop 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 = FalseThis 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.
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.
re: M365 -how can we create a drop-down calendar wth this version?
Thanks in advance,
Aaron
Thank you, Aaron, for your query. To create a drop-down calendar in Microsoft 365 version, you can use the Mini Calendar and Date Picker add-in. To learn how to use the add-in, you can follow this article. I hope, it helps.
Regards
Aniruddah