How to Add a Calendar Drop-Down or Date Picker in Excel

Excel has no built-in calendar drop-down for worksheet cells that Microsoft documents for current desktop versions. The most reliable way to let people pick a date from a drop-down is a list of dates: select the cells, go to Data > Data Validation, choose List in the Allow box, and point the Source box at a column of dates. If you need a real pop-up calendar, the legacy Date and Time Picker control works only in 32-bit Excel for Windows, and an Office Add-in is the other route.

Applies to: Excel for Microsoft 365, Excel 2024 and Excel 2021 for Windows (drop-down list and date validation), Excel for Mac (drop-down list); 32-bit Excel for Windows only (Date and Time Picker control). Checked against Microsoft Support on October 7, 2026.

Which date picker option should you use?

People searching for a “calendar drop-down” or a “date picker” in Excel usually want one of three things: a cell with an arrow that offers dates, a cell that refuses anything that is not a date, or a small calendar that pops up. Excel handles each one differently, and only the first two are fully supported built-in features.

Option	What the reader sees	Where it works	Best for
Drop-down list of dates (Data Validation, List)	An arrow beside the cell with a list of dates	Excel for Windows and Mac; Microsoft says data validation in Excel for the web works the same way	Shared workbooks, schedules, sign-up sheets with a fixed set of dates
Date validation (Data Validation, Date)	No arrow; typed entries that are not valid dates are rejected	Excel for Windows; Excel for the web	Any date column where people may type freely
Date and Time Picker control (ActiveX)	A small calendar that fills a linked cell	32-bit Excel for Windows only; ActiveX is off by default in Microsoft 365 and Office 2024	Personal or controlled workbooks on known 32-bit PCs
Office Add-in from the Store	Depends on the add-in	Depends on the add-in and on your organization’s add-in policy	64-bit Excel users who want a pop-up calendar

For most readers, the drop-down list of dates is the right answer: it needs no special controls, no macros and no security changes, and it keeps working when you send the file to someone else.

Before you start

• Make sure your dates are real dates. Excel stores dates as serial numbers so it can calculate with them (by default, January 1, 1900 is serial number 1). A date typed as text looks right but will not sort, filter or calculate correctly.
• Check that the sheet is not protected or shared. Microsoft notes that if the Data Validation command is unavailable, the sheet might be protected or the workbook might be shared.
• Find out whether your Excel is 32-bit or 64-bit if you plan to use the Date and Time Picker control. In Excel for Windows, select File > Account > About Excel. The dialog box shows the full version number and whether Excel is 32-bit or 64-bit.

Method 1: Add a drop-down list of dates

This method gives the cell a drop-down arrow, and clicking the arrow shows the dates you allowed. It is not a calendar grid, but it is the closest built-in, fully supported option, and it works in Excel for Windows and Mac.

1. Create the list of dates. In an empty column, or on a separate sheet, type the dates you want to offer, one per cell, with no header in the range you will use. In Excel for Microsoft 365, Excel 2024 or Excel 2021, you can instead type =SEQUENCE(31,1,TODAY(),1) in the first cell; it fills 31 cells down the column with consecutive dates starting today. If the results show as plain numbers, format them as dates (see the troubleshooting section).
2. Select the cells that should get the drop-down. You can select one cell, a block of cells or part of a column. Every selected cell gets the same list.
3. Open Data Validation. On the Data tab, select Data Validation. In the dialog box, select the Settings tab.
4. Choose List. In the Allow box, select List. A Source box appears.
5. Point to your dates. Click in the Source box, then select the cells that hold your dates (for example, E2:E32). Leave out any header cell.
6. Keep the arrow turned on. Make sure In-cell dropdown is checked; without it, no arrow appears. Leave Ignore blank checked if an empty cell is acceptable.
7. Add an optional prompt. On the Input Message tab, check Show input message when cell is selected and type a short hint such as “Pick a delivery date.” Microsoft allows up to 225 characters for the message.
8. Decide how strict to be. On the Error Alert tab, keep Show error alert after invalid data is entered checked and choose a Style. Stop blocks anything not in the list; Warning and Information show a message but still let people type another value.
9. Select OK. Click one of the cells: an arrow appears to its right. Click the arrow and pick a date; the date is entered in the cell.

Tips for the date list

• Use a table if the dates change often. Microsoft recommends putting list items in an Excel table (select a cell in the range and press Ctrl+T). A drop-down based on a table updates when you add or remove items.
• Know that a TODAY-based list moves. A list built with TODAY() recalculates, so each day the earliest date drops out of the list and a new one is added. Type fixed dates if the list must not change.
• Hide the source list. To keep people from changing the dates, Microsoft suggests putting them on a sheet that you hide and protect.
• Keep the list a manageable size. A list with hundreds of dates is slow to scroll. For long ranges such as “any date this year,” Method 2 is easier for the person filling in the sheet.

Method 2: Allow only dates in a cell

If you do not need an arrow but you do want to stop typos and text entries, restrict the cell to dates. Excel then rejects anything outside the range you set. This method does not show a calendar or a list; people type the date as usual.

1. Select the cells.
2. On the Data tab, select Data Validation.
3. On the Settings tab, in the Allow box, select Date.
4. In the Data box, choose a condition such as between, greater than or less than or equal to.
5. Fill in the date boxes that appear. You can type fixed dates or formulas: for example, =TODAY() as the start date and =TODAY()+3 as the end date allows only today and the next three days.
6. Optionally add an input message and an error alert, as in Method 1, then select OK.

You can combine both ideas: the list in Method 1 already restricts entries to the listed dates when the error style is Stop, so you do not need a second rule on the same cells.

Method 3: Insert the Date and Time Picker control (32-bit Excel for Windows only)

The Date and Time Picker is an old ActiveX control from the Windows Common Controls file MSComCt2.ocx. When it is available, it shows a small calendar on the worksheet and writes the chosen date to a cell you link it to. Read these limits before you try it:

• It does not work in 64-bit Excel. Microsoft states that native 64-bit Office cannot load 32-bit binaries, including the MSComCt2 controls such as DateTimePicker. Check your version first (see “Before you start”).
• It does not work on Mac or in Excel for the web. Microsoft says ActiveX controls are supported only in the Windows desktop versions of Office.
• ActiveX is turned off by default in Microsoft 365 and Office 2024. Microsoft strongly recommends leaving it off unless you absolutely need it, and turning it on affects every file in Word, Excel, PowerPoint and Visio.
• Anyone who opens the file needs the same setup. If a colleague uses 64-bit Excel, a Mac or a browser, the calendar will not work for them. Use Method 1 for shared workbooks.

Step 1: Show the Developer tab

1. Select File > Options.
2. Select Customize Ribbon on the left.
3. Under Customize the Ribbon on the right, choose Main Tabs, then check Developer.
4. Select OK. The Developer tab now appears on the ribbon.

Step 2: Insert and link the control

1. On the Developer tab, in the Controls group, select Insert. Under ActiveX Controls, select More Controls.
2. In the list, look for the Microsoft date and time picker entry and select it, then select OK. If it is not in the list, it is not installed or cannot load in your version of Excel; use Method 1 or Method 4 instead.
3. Click the worksheet where you want the control. You can drag its edges to resize it while Design Mode is on.
4. With the control selected and Design Mode on, select Properties in the Controls group (or right-click the control and choose Properties).
5. Find the LinkedCell property and type the cell that should receive the date, such as B2. Microsoft describes LinkedCell as the worksheet range linked to the control’s value: changing the control changes the cell, and a value in the cell changes the control.
6. Close the Properties window, then select Design Mode again to turn it off. Open the control’s calendar and pick a date; it appears in the linked cell.

If ActiveX content is blocked

In Microsoft 365 and Office 2024, Excel blocks ActiveX controls by default, so you cannot create or use them until the setting changes. Only change it on your own PC, for a file you trust, and never because a pop-up or an email tells you to. To allow controls with a prompt, select File > Options > Trust Center > Trust Center Settings > ActiveX Settings, choose Prompt me before enabling all controls with minimal restrictions, and select OK twice. On a work or school PC, your IT department may lock this setting.

Method 4: Use a date picker add-in

If you use 64-bit Excel and still want a pop-up calendar, look for an Office Add-in. Add-ins are made by Microsoft or by other developers and are listed in the Office Store inside Excel.

1. In Excel for Microsoft 365 on Windows, select File > Get Add-ins, or select Home > Add-ins.
2. Select More Add-ins and use the Store tab to search for “date picker.”
3. Read the add-in’s description, permissions and reviews, then select it and select Add.

Each add-in works in its own way, so follow its instructions for inserting dates. Keep two points in mind: anyone else who edits the workbook needs the add-in too, and your organization may limit which add-ins you can install.

What about Excel for the web and Excel for Mac?

Excel for Mac: Microsoft’s drop-down list instructions apply to Excel for Mac, so Method 1 works there. ActiveX controls, including the Date and Time Picker, are not supported on Mac.

Excel for the web: Microsoft says data validation in Excel for the web works the same way as in the desktop app, so a drop-down list of dates made on the desktop keeps working when the file is opened in a browser. ActiveX controls are not supported on the web. Microsoft has not published a support article describing a built-in calendar picker for worksheet cells. In Microsoft Q&A answers, a Microsoft support agent has said that Excel for the web shows a date picker when you edit a cell formatted as a date; treat that as unofficial and check whether it appears in your browser before you rely on it for other people.

Quick ways to enter dates without a picker

• Press Ctrl+; (semicolon) to insert today’s date as a fixed value.
• Press Ctrl+Shift+; to insert the current time.
• Type =TODAY() for a date that updates. Microsoft notes that TODAY changes only when the worksheet recalculates, not continuously.

For a printable monthly calendar rather than a date picker, see our guide on how to insert a calendar in Excel.

How to check that it worked, and how to remove it

• Test the list. Click a cell with the drop-down, pick a date, then try typing a word. With the Stop style, Excel refuses the word. If you left the error title and message blank, Excel shows its generic message that a user has restricted the values that can be entered.
• Confirm you have a real date. Temporarily change the cell’s format to General. A real date turns into a serial number; text stays as text. Change the format back afterward.
• Remove a drop-down or date rule. Select the cells, go to Data > Data Validation, select Clear All, then select OK.
• Remove the Date and Time Picker. Turn on Design Mode, select the control and delete it.

Troubleshooting

Data Validation is greyed out

The sheet is probably protected or the workbook is shared. Unprotect the sheet or stop sharing, add the validation, then protect or share again.

No arrow appears in the cell

Open Data > Data Validation for that cell and check In-cell dropdown. Also make sure you chose List, not Date: date validation never shows an arrow.

The list shows numbers like 46302 instead of dates

The source cells are formatted as numbers. Select them, press Ctrl+1, open the Number tab, choose Date in the Category list, pick a format under Type, and select OK. Format the cells with the drop-down the same way.

The Date and Time Picker is missing from More Controls

Your Excel is most likely 64-bit, which cannot load the MSComCt2 controls, or the control is not installed on the PC. There is no supported way to load the 32-bit control in 64-bit Excel. Use the drop-down list of dates or an add-in instead.

Excel says “Cannot insert object”

Some ActiveX controls can be used only on VBA UserForms, not directly on a worksheet. Pick a different control or use Method 1.

The calendar control does nothing

Check that Design Mode is off, that ActiveX content is not blocked for the file, and that LinkedCell holds a valid cell reference.

You cannot find Get Add-ins or the Store

Your organization may have restricted add-ins. Ask your IT administrator, or use Method 1, which needs no add-in.

The drop-down works for you but not for a colleague

Check that your colleague opened your latest copy of the workbook. If you used the Date and Time Picker or an add-in, your colleague needs 32-bit Excel for Windows with ActiveX allowed, or the same add-in.

Frequently asked questions

Does Excel have a built-in date picker?

Not one that Microsoft documents for worksheet cells in current desktop versions. The supported built-in options are a drop-down list of dates and date validation. The older Date and Time Picker ActiveX control works only in 32-bit Excel for Windows.

Why don’t I see a calendar after setting Allow to Date?

Date validation only checks what people type. It does not add an arrow or a calendar. Use List with a range of dates if you want an arrow.

Can I add a calendar drop-down in Excel for Mac?

You can add a drop-down list of dates with Data Validation. The ActiveX Date and Time Picker is not available on Mac.

Can I use the Date and Time Picker in 64-bit Excel?

No. Microsoft states that 64-bit Office cannot load the 32-bit MSComCt2 controls, which include the DateTimePicker. An add-in or a date list is the alternative.

Is it safe to turn on ActiveX controls?

Microsoft strongly recommends keeping ActiveX disabled unless you absolutely need it, because the setting applies to all files and attackers try to trick people into changing it. If you only want a date drop-down, Method 1 avoids the risk entirely.

How do I add the drop-down to a whole column?

Select all the cells in the column that should get it (for example, B2:B500) before opening Data Validation.

Can the drop-down depend on another choice?

Yes. Our guide to creating a dependent drop-down list in Excel walks through it.

Start with the drop-down list of dates; Microsoft’s guide to creating a drop-down list and its page on applying data validation to cells cover the remaining options, and its notes on ActiveX being disabled by default and 32-bit and 64-bit compatibility explain the picker’s limits.

Related: How to create a multi-select drop-down in Excel.

Related: copy the same date in Excel.

Related: add 7 days to a date in Excel.