How to Insert a Date Picker in Excel (With Step-by-Step Procedure)

In Microsoft Excel, many essential tools create a great user experience. One of them is the Date Picker. With this tool, you can insert any date and time in a worksheet. It pops up like a calendar. You can select a date from that. In this tutorial, you will learn to insert a date picker in Excel with suitable examples and proper illustrations. A lot of details are coming in later sections. So, I hope you will stay tuned.

Download Practice Workbook

Why Date Picker is Useful in Excel?

Now, people love to work with user interfaces. It eases your work stress. How do we insert a date in a cell? By typing it in the cell, right? We all know typing is a hectic matter. What if you have 500 rows in a dataset? You wouldn’t like to insert all the dates manually in Excel!

Here comes the Date Picker to help us. It is a pop-up calendar that you can use to insert dates and control them. Take a look at the following screenshot:

You can see the date picker here. With this tool, you can choose any date and perform any operations in Microsoft Excel.

Step by Step Guide to Insert Date Picker in Excel

In the following sections, we will provide you with a step-by-step guide to inserting a date picker in Excel. We recommend you closely look and learn all these steps. It will obviously develop your Excel knowledge.

1. Enable Developer Tab in Excel for the Date Picker

First of all, this date picker tool is only available in the Developer tab. So, before you start, you have to enable the developer tab in Microsoft Excel.

So, let’s enable the developer tab first.

📌 Steps

• First, click on the File tab.
• Next, click on Option.

• Now, from the Excel Options dialog box, click on Customize Ribbon option on the left side.

• From the right-hand side of the windows, select Main Tabs.
• Finally, check the Developer box.

As you can see from the Excel ribbon, we are successful in inserting the Developer tab in Microsoft Excel.

2. Insert a Date Picker

It’s time to insert the date picker in the worksheet. Follow these steps to do that.

📌 Steps

• First, go to the Developer tab.
• From the Controls tab, click on Insert.

• From the ActiveX Controls, click on the More Controls.

• Now, select Microsoft Date and Time Picker Control 6.0 (SP6) from the More Controls dialog box.

• After that, click on OK.
• Finally, click on the cell where you want to insert the date picker.

As you can see, we have inserted a date picker control in the Cell.

3. Customize the Date Picker

You can see our date picker control is not looking good here. So, we have to customize it to give a better look.

When you insert the date picker, the Design mode is activated automatically. It allows you to modify it. Of course, we will do that. We will resize it and also change some of its properties.

📌 Steps

• To make it larger or smaller, you can simply drag the date picker.

• While the Design mode is on, right-click on the date picker. After that, click on Properties.

• Here, you will see various options. We will work with a few of them.

• You can change the height, width, font, color, etc.
• Now, drag the date picker to the location of the cell where you want to place it.

Now, our date picker is almost ready. All we have to do is link the calendar to a cell.

4. Link the Date Picker Control to a Cell

You may think we have inserted it and can perform any procedure now. But here is a catch. You can do any operation without linking the date picker to a cell. Microsoft Excel won’t recognize the date associated with any cell automatically. Remember, no formula will work without this.

📌 Steps

• First, right-click on the date picker.

• From the contextual menu, click on Properties.

• Now, in the Linked Cell option, type the cell reference you want to connect.

• When you select a date from the calendar, you will automatically see the date on the linked cell. Click on OK if Excel shows the “Can’t set cell value to NULL…” blunder.
• To accept Null values, change the value from FALSE to TRUE in the CheckBox.

• If you right-click on the date picker and click on View Code you will see the VBA codes associated with it.

How to Insert Date Picker on a Whole Column in Excel

Now, what we have done until now is insert a date picker in a cell. We can insert a date picker in a range of cells or a particular column. Whenever you click on the cell, a calendar will show up and you can choose a date from there. In the following sections, we will show you to insert both single columns and multiple columns.

1. Insert Date Picker for a Single Column

📌 Steps

• To assign a date picker on a whole column, right-click on the date picker. After that, click on View Code.

• After that, you will see some code if you have customized it.
• Now, clear the VBA code and type the following code that we are showing here:

Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  With Sheet1.DTPicker1
    .Height = 20         
    .Width = 20         
    If Not Intersect(Target, Range("B:B")) 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

This code basically sets column B as a date picker.

• Now, deselect the Design mode.
• After that, click on any cell to remove the Date Picker.
• Now, click on any cell of column B. You will see date picker control from every cell.

Code Explanations:

  With Sheet1.DTPicker1
    .Height = 20         
    .Width = 20    

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

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

   Else             
      .Visible = False  

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

2. Insert Date Picker for Multiple Columns

Now, if you want to set multiple columns with a date picker, you have to make a simple change. Remember, before you set multiple columns with date pickers, you have to insert another date pickers again.

If you want to set a date picker for adjacent columns, you don’t have to write another code segment. Just change in the IF segment:

If Not Intersect(Target, Range("C:D")) Is Nothing Then

Now, the following code will set a date picker for columns B, D, E, G:

Here, we are not assigning the date picker in the entire column. Rather than, we are inserting it in a range of cells. Date picker 1 for B5:B14, Date picker 2 for D5:E14, and Date picker 3 for G5:G14.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    

  With Sheet1.DTPicker1    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("B5:B14")) 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 Sheet1.DTPicker2    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("D5:E14")) 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 Sheet1.DTPicker3    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("H5:H14")) 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

Look here, we have three date pickers here. One for column B, one for columns D and E combined, and another one for column G. After clicking each cell of these columns you will see a calendar. In this way, you can insert a date picker for multiple columns in Excel.

Big Issue With the Date Picker in Excel

If you are using 64 bit of any Microsoft Excel software or you are using Excel 365 or Excel 2019, you are already confused by now. It is because you couldn’t find the date picker in the ActiveX control.

We are sorry to say Microsoft’s Date Picker control is only available in 32-bit versions of Excel 2016, Excel 2013, and Excel 2010, but it won’t work on Excel 64-bit. So, if you really want to insert a calendar in your worksheet, use any third-party calendar. I hope Microsoft will bring some kind of date picker in the future.

💬 Things to Remember

✎ Make sure to link the date picker with a cell if you are working with one.

✎ Your file should be saved as a Macro-Enabled Workbook (.xlsm).

✎ To make any change to the date picker, make sure to select it from the developer tab.

✎ To see changes from VBA codes, deselect the date picker.

Frequently Asked Questions

1. Are there any Excel add-ins available for date pickers?

Yes, there are various add-ins available for Excel that provide date picker functionality. Some popular add-ins include “Mini Calendar and Date Picker” and “Excel Date Picker (DateDialer)”.

2. Can I restrict the selectable dates in an Excel date picker?

Yes, it is possible to restrict the selectable dates in an Excel date picker. With VBA programming, you can control the range of dates available for selection, such as setting a minimum and maximum date range or excluding certain dates.

3. Can I use an Excel date picker in multiple cells or worksheets?

Yes, an Excel date picker can be used in multiple cells or worksheets. You can either duplicate the code or add multiple instances of the date picker control within your VBA solution. To use VBA for date pickers, check out this article.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to insert a date picker in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

Excel Date Picker: Knowledge Hub

• [Solved!] Datepicker Not Showing in Excel
• How to Use Date Picker in Excel 64-Bit
• Excel Date Picker for Entire Column
• Make an Alternative to Datepicker in Excel
• How to Insert Calendar in Excel Cell
• Insert Drop Down Calendar in Excel Without Date Picker
• How to Use Excel UserForm as Date Picker

<< Go Back to Learn Excel