Date data is a key attribute in many datasets. When entering date data, getting the right date format can sometimes be a challenge, and thus leading to errors in the dataset. By inserting a drop down calendar in Excel, dates can be entered into excel sheets error-free.
If you work with any kind of dataset, you want to make sure that the correct date is entered and also in the right format. And one way to avoid these problems is by inserting a drop down calendar into the worksheet that allows the user easily insert the dates into the cells with just a mouse click.
In this post, you will learn two different ways to create and insert a drop down calendar, also called date picker in excel.
The following topics will be covered:
- Step 1: Add the Developer tab to the Ribbon
- Step 2: Insert a Date Picker Control
- Step 3: Resize/Customize the Date Picker Control
- Step 4: Link the Date Picker Control to a Cell
1. What is Date picker in Excel?
The Date picker is a control that inserts a mini drop down calendar in Excel worksheet, and allows users enter dates and time into the cells.
It is also usually referred to as a dropdown calendar in Excel because it comes with arrow keys on the left and right sides that allows you search and select the months and years.
2. How Does the Date Picker Drop down Calendar Work?
When the drop down calendar/date picker is launched into the worksheet, it displays the current month by default. If you click on any date, the date value will be entered into the active cell.
The arrow keys on the left and right sides are used to search and change the months and years. However, you can quickly jump the years by double-clicking on the year to enter the desired year instead of clicking the arrow button continuously.
3. How to insert a Date Picker Drop down calendar in Excel Using the Control Button?
This method involves four steps to follow and they are:
Step 1: Add the Developer tab to the ribbon
The Developer tab houses a number of controls that can be found in the Insert button. And in the ActiveX Controls is where you will find the date picker control.
By default, the Developer tab is not added to the ribbon, and so you will need to add it for it to appear.
To do this,
1. Right-click anywhere on the ribbon and select Customize the Ribbon
2. Under Main Tabs, check the Developer box
3. Click ok.
This makes the Developer tab appear in the Excel ribbon.
Step 2. Insert a Date Picker Control
1. Go to Developer tab and click on the Insert button
2. Under ActiveX Controls, select More Controls
3. In the More Control dialog box, scroll to select Microsoft Date and Time Picker Control 6.0 (SP6)
4. Click Ok
Finally, go ahead to click on anywhere in the cell where you want to insert the calendar control.
Now, the dropdown calendar control has been inserted into the Excel worksheet.
Immediately the date picker control is inserted in the worksheet, it will display the EMBED formula in the formula bar. This allows Excel to know the type of control that is embedded in the sheet.
You are required NOT to change or delete this formula. If you do, it will result in the error message “Reference is not valid”.
If you insert an ActiveX control, it automatically turns on the Design Mode command. This allows you to make changes to the appearance and properties of the newly added control.
For the date picker control, you will need to adjust the size of the calendar and link it to a specific cell.
After inserting the control in the cell, the dropdown calendar will need to be activated. To activate it,
Click on the Design Mode command to turn off the design mode.
Then click on the arrow drop down in the control and the calendar will be displayed.
To enter a date in a cell, select a date from the calendar and it will be displayed in the control box.
Step 3: Resize/Customize the Date Picker control
After you have inserted the date picker control into your Excel worksheet, the next thing to do is to move the control to the desired cell location and resize it to fit the cell in the worksheet.
To move the control, click on Design Mode to turn it ON, hover your mouse over the control to get the four-pointed arrow. Then drag it to the preferred location in the sheet.
On the other hand, to resize the control, again turn on the Design Mode and move the edges to resize.
Alternatively, you can go through the following steps to customize the calendar control.
1. Click on Design Mode to turn it on
2. Select the calendar control in the sheet and click on Properties
3. In the Properties dialog box, set the desired Font theme and size, as well as the Height and Width.
Step 4. Link the Date Picker Control to a Cell.
When you have successfully inserted a drop down calendar in Excel worksheet, it is necessary to link it to a specific cell. This will avoid errors when using the selected dates in formulas.
For example, if you want to count the number of transactions or orders within a specified date period, say 1/10/2016 – 31/12/2016. Even if the formula is entered correctly, Excel will return a result of 0.
This is because Excel only recognizes the value of a date picker control only after it has been linked to a specific cell. Hence, the reason you have to link the date picker control to a cell.
To link the control to a cell,
1. Click on Design Mode to turn it on and select the date picker control in the sheet
2. Go to Developer tab and click on Properties.
3. In the Properties dialog box, go to LinkedCell and type the cell reference in the box. (We use cells B3 and C3 in this example). Ignore the error message displayed “Can’t set cell value to Null….”
4. Click ok.
Now, when you select a date in the drop down calendar, it will appear in the linked cell. And Excel understands these dates and the formula linking the cells (B3 and C3) will work just fine.
However, to avoid having duplicated dates in the worksheet, link the date picker control to the same cells where the control is. In this case, the users won’t be able to see the underlying dates. Excel will recognize that it is there and the formula will work just fine.
4. How to Insert Drop Down Calendar Date Picker via Plugin
The mini calendar date picker is available as an add-in plugin in the Microsoft office store. You can simply go to the store to add the plugin which will automatically be inserted into the Excel worksheet.
To do this, here are the steps to follow:
1. Open the Excel worksheet
2. Go to Insert tab and click on My Add-ins command
3. In the Office Add-ins dialog box, search for Calendar or Date Picker in the search bar. It will bring up Mini Calendar and Date Picker. Then click the Add button.
4. In the box that pops up, click on Continue
It will take you back to the worksheet and the mini calendar and date picker will be inserted into your worksheet.
If you want to enter a date in the cell, just click on the cell you want to input the date and select a date in the mini calendar. It will be inserted into the active cell.
5. Important Notes to Remember
- Microsoft Date Picker control only works for Office 32-bit version of Excel 2010, 2013 and 2016. It does not work for the 64-bit Excel.
- The worksheet file must be saved in an Excel Macro-Enabled Workbook to all the macro function work.
I hope you found this post useful. If you have other easier options kindly share in the comment section.