How to create a drop-down calendar (date picker) in Excel?
Creating a drop-down calendar in Excel can save time and reduce errors when entering dates manually. This tutorial provides step-by-step instructions for adding a date picker to your worksheet using an ActiveX control or a simpler method with Kutools for Excel.
In this tutorial:
Which method should you use?
| Situation | Recommended method | Why |
|---|---|---|
| Embed a calendar control and link it to a specific cell | ActiveX control | Creates a calendar directly on the worksheet, but requires the legacy Microsoft Date and Time Picker control and has limited compatibility. |
| Insert or replace dates in any cell without configuring ActiveX | Kutools Date Picker | Works with all cells, date cells only, or cells identified by Smart Picker, depending on the mode you choose. |
Create a drop-down calendar in Excel with an ActiveX control
To create a drop-down calendar in Excel with an ActiveX control, follow these steps.
- First, if you cannot see the Developer tab on the ribbon, display it as follows:
- In Excel 2010 and later versions, click File > Options. In the Excel Options dialog box, click Customize Ribbon, select the Developer check box, and then click OK.
- In Excel 2007, click the Office button > Excel Options. Click Popular, select the Show Developer tab in the Ribbon check box, and then click OK.
- In Excel 2010 and later versions, click File > Options. In the Excel Options dialog box, click Customize Ribbon, select the Developer check box, and then click OK.
- Click Developer > Insert > More Controls.
- In the More Controls dialog box, select Microsoft Date and Time Picker Control 6.0 (SP6), and then click OK.
- Draw the calendar control on the worksheet where you want it to appear. Drag a corner handle to resize it.
- Right-click the calendar control and select Properties. In the Properties window, enter a cell reference in the LinkedCell box, and then close the window.
- An error dialog box may appear after you close the Properties window. If it does, click OK.
- Turn off Design Mode by clicking Developer > Design Mode.
- Click the arrow button to display the drop-down calendar. When you select a date, Excel automatically enters it in the linked cell.
Tip: For example, if you enter B3 in the LinkedCell box, the selected date will appear in cell B3.
Compatibility: Microsoft Date and Time Picker Control 6.0 (SP6) is a legacy ActiveX control and may not appear in all Excel installations, particularly 64-bit Office. ActiveX controls are also disabled by default in Microsoft 365 and Office 2024.
Troubleshooting: If the calendar does not respond, make sure Design Mode is turned off, the workbook is trusted, and ActiveX controls are allowed.
Easily insert or replace dates with Kutools for Excel
If the required ActiveX control is unavailable, the upgraded Date Picker feature in Kutools for Excel provides a simpler alternative. It can display a pop-up calendar for any cell, only for date cells, or automatically when Smart Picker identifies a cell intended for date entry. This lets you insert dates into blank cells as well as replace existing dates without creating or linking a worksheet control.
- Click Kutools > Date Picker to activate the feature. If you do not see it here, find it under Kutools > View.
- Choose the mode that suits your worksheet:
- Enable for All Cells is selected by default and makes the Date Picker available for any cell, including blank or unformatted cells.
- Enable for Date Cells Only displays the Date Picker only for cells that contain dates or are formatted as dates.
- Smart Enable automatically determines whether the selected cell is intended for date entry based on its content or formatting.
- Enable for All Cells is selected by default and makes the Date Picker available for any cell, including blank or unformatted cells.
- Select a supported cell, and a calendar icon will appear beside it. Click the icon, choose a date, and then click the Insert Date icon or move to another cell to confirm the entry.
Formatting: The inserted date follows the cell’s existing date format. If the cell has no date format, the system’s default date format is used.
Shortcut: Press Alt + Down Arrow in a supported cell to open the Date Picker.
Auto popup: Select Auto popup in the calendar window if you want the Date Picker to open automatically when you select a supported cell.
Important: Inserting a date replaces any existing content in the selected cell.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
For another way to insert dates—especially when you want to choose a specific format before insertion—use the Insert Date feature in Kutools for Excel. It lets you select a formatted date from a calendar and insert it into the selected cell with a double-click.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Frequently Asked Questions
Why can’t I find Microsoft Date and Time Picker Control 6.0 in Excel?
The control is not included with every Office installation and is generally unavailable in 64-bit Office. In Microsoft 365 and Office 2024, ActiveX controls are also disabled by default. If the control is not listed, use the Kutools method instead.
Why doesn’t the selected date appear in the linked cell?
Check that the LinkedCell property contains a valid cell reference and that Design Mode is turned off. If ActiveX is blocked, the calendar will not work until the control is permitted in a trusted workbook.
Will the ActiveX calendar work when I share the workbook?
Only if the other computer supports the same ActiveX control and allows it to run. If the control is missing or blocked, the calendar may not appear or respond.
Related Articles:
Autocomplete when typing in an Excel drop-down list
If you have a data validation drop-down list with many items, you may need to scroll through the list to find the right one or type the entire entry. Allowing autocomplete when you type the first letter makes the list easier to use. This tutorial explains how to set it up.
Create a drop-down list from another workbook in Excel
It is easy to create a data validation drop-down list using data from another worksheet in the same workbook. If the source data is located in another workbook, follow this tutorial to create the list.
Create a searchable drop-down list in Excel
When a drop-down list contains many items, finding the right one can take time. In addition to autocomplete, you can make the list searchable. This tutorial explains how.
Auto-populate other cells when selecting values from an Excel drop-down list
Suppose you have created a drop-down list based on the values in B8:B14. When you select an item, you may want the corresponding value from C8:C14 to appear automatically in another cell. This tutorial provides methods for doing that.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in