How to Create a Drop-Down List Calendar in Excel

This guide shows you how to add a clickable calendar to any Excel cell, letting you pick a date from a drop-down instead of typing it. You’ll end up with a date picker that works like a mini calendar, saving time and preventing typos. You’ll need Excel 2013 or later (including Microsoft 365) and a few minutes to set it up.

What You’ll Need

• Excel 2013 or newer (Windows or Mac)
• A worksheet where you want the date picker to appear
• Basic familiarity with Excel’s ribbon menus

Step-by-Step

1. Open the worksheet where you want the calendar drop-down. Click on the cell (or a range of cells) where you want the date picker to appear.
2. Go to the Developer tab. If you don’t see it, right-click anywhere on the ribbon and choose “Customize the Ribbon.” In the right pane, check the box next to “Developer” and click OK. The Developer tab will now appear.
3. Insert the Date Picker control. On the Developer tab, click “Insert” (in the Controls group). Under “ActiveX Controls,” click the “More Controls” icon (a small hammer and wrench). In the list that appears, scroll down and select “Microsoft Date and Time Picker Control 6.0 (SP6)” and click OK.
4. Place the control. Your cursor will turn into a crosshair. Click and drag on the worksheet to draw the date picker. It will appear as a box with a small arrow. Right-click it and select “Properties.”
5. Adjust the properties. In the Properties window, set the “LinkedCell” to the cell where you want the date to appear (e.g., A1). You can also change the “Format” to a date style like 3/14/2021 or 14-Mar-21. Close the Properties window.
6. Test it. Click the arrow on the control. A calendar should pop up. Click any date, and it appears in the linked cell. You can also type a date directly into the control.
7. Resize and position. Click on the control to select it. Use the handles to resize it so it fits your cell. To move it, drag its border. If you want it to snap to a cell, hold down Alt while dragging.
8. Protect the sheet (optional). If you want to prevent users from moving or deleting the control, right-click it, select “Properties,” and under “Locked,” choose “True.” Then protect the sheet via Review > Protect Sheet.

Common Mistakes

• Developer tab not visible – The most common issue is forgetting to enable it. Follow step 2 carefully.
• Choosing the wrong control – There are two types of date pickers: ActiveX and Form. The Form one (a simple drop-down) is not a calendar. You must use the ActiveX “Microsoft Date and Time Picker Control.”
• Forgetting to link the cell – If you don’t set the LinkedCell property, the date won’t appear in any cell. You’ll just see the control show the date, but it won’t be usable in formulas.
• Control is too small – If the control is too small, the calendar arrow may be hidden. Make sure it’s at least as tall as a normal cell row height.
• Control not working in protected sheets – If you protect the sheet, the control may become unresponsive unless you set the Locked property to False or allow editing of objects.

Tips

• Use a cell that’s easy to reference: Link the control to a cell you’ll actually use in formulas, like a start date or deadline.
• Combine with data validation: You can also use Excel’s built-in Data Validation to create a drop-down list of dates, but it won’t show a calendar. The ActiveX control is the only true calendar picker.
• Copy the control to multiple cells: You can copy the control (Ctrl+C, Ctrl+V) and paste it into other cells. Each copy will have its own LinkedCell property—set it individually.
• Use the control in a userform: If you’re comfortable with VBA, you can add the same control to a UserForm for a more polished interface.
• Keyboard navigation: Once the control is active, you can type a date directly into it, or use the arrow keys to change the date.

Troubleshooting

• No Developer tab appears after checking the box – Close Excel and reopen it. If it still doesn’t show, go to File > Options > Customize Ribbon and make sure “Developer” is checked in the right pane under “Main Tabs.”
• The control is grayed out or not clickable – This usually happens when the worksheet is protected. Unprotect the sheet (Review > Unprotect Sheet) and try again. Also, check the control’s “Enabled” property in the Properties window—it should be “True.”
• The date picker doesn’t show a calendar when clicked – Make sure you selected the correct control. If you used a Form control, it will just be a drop-down list, not a calendar. Delete it and re-insert the ActiveX version.
• The date appears as a number (e.g., 44210) – This is just Excel’s date serial number. Format the linked cell as a date: right-click the cell, Format Cells, and choose a date format.
• The control is too large or covers other cells – Click the control and drag the handles to resize. If you want it to stay within a cell, hold Alt while dragging to snap to cell boundaries.
• Error “Cannot insert object” – This can happen if the ActiveX control is not registered. Try repairing Office via Control Panel > Programs > Microsoft Office > Change > Quick Repair.

Frequently Asked Questions

Can I use this date picker on Excel for Mac?

Yes, the ActiveX Date and Time Picker control is available in Excel for Mac 2016 and later, but the steps to insert it are slightly different. Go to Developer > Insert > More Controls, and look for “Microsoft Date and Time Picker Control.”

Is there a way to make the calendar appear automatically when I click a cell?

Yes, you can use VBA to show the control when a specific cell is selected. For example, in the worksheet’s code module, add a Worksheet_SelectionChange event that makes the control visible and positions it over the selected cell.

Can I have multiple date pickers on one sheet?

Absolutely. You can insert as many as you need. Each one should have its own LinkedCell property set to a different cell.

Why doesn’t the date picker work in Excel Online or Google Sheets?

The ActiveX control is a Windows desktop feature and is not supported in Excel Online or Google Sheets. For Google Sheets, you can use a script to create a calendar picker, but it’s not built-in.

Can I use the date picker in a protected sheet?

Yes, but you need to allow editing of objects. Go to Review > Protect Sheet, and check the box “Edit objects” (or “Edit shapes and objects”) before protecting.

How do I remove the date picker?

Right-click on the control and select “Cut” or press Delete. If you can’t select it because it’s locked, unprotect the sheet first.

Related

• Safari Running Slow on iPhone? Fixes That Work
• Microsoft Rewards: What It Is & How It Works
• macOS Big Sur Features & Public Beta Install Guide