To choose a date from a calendar in an Excel cell, use the built-in Date Picker control available in Excel 365 and Excel 2021 for Windows. Go to Developer > Insert > More Controls, select Microsoft Date and Time Picker Control 6.0, and draw it on your sheet. If you don’t see the Developer tab, enable it via File > Options > Customize Ribbon. On Mac or Excel for the web, there is no native date picker, but you can use a drop-down list of dates or a calendar add-in.
Why Excel Doesn’t Always Show a Calendar
Many users expect a calendar icon to appear automatically when they click a date-formatted cell, but Excel only shows a calendar in specific situations. The built-in date picker is an ActiveX control, not a standard cell feature. It was introduced in Excel 2002 and remains available in modern Windows versions, but it is hidden by default because it requires the Developer tab. On Mac, Microsoft removed ActiveX support entirely, so the control doesn’t exist there. Excel for the web also lacks it, so users on those platforms must rely on alternative methods.
How to Insert the Date Picker (Windows)
Follow these steps to add a clickable calendar to a cell or a shape:
- Open Excel and go to File > Options > Customize Ribbon.
- In the right panel, check the Developer box and click OK.
- Click the Developer tab, then in the Controls group click Insert.
- In the ActiveX Controls section, click More Controls (the hammer icon).
- Scroll to Microsoft Date and Time Picker Control 6.0 and click OK.
- Click and drag on the worksheet to draw the calendar control.
- Right-click the control and choose Properties to set the format (e.g.,
dd/mm/yyyy) or link it to a cell.
To link the selected date to a cell, open the Properties window (right-click > Properties), find the LinkedCell property, and type a cell address like B2. The chosen date will appear in that cell. You can also resize the control to fit a single cell by dragging its handles.
Using the Date Picker Control
Once inserted, the control acts like a small calendar. Click the drop-down arrow next to the date field to open a monthly calendar. You can navigate months with the left/right arrows and click a day to select it. The control also supports keyboard input: type a date directly, and Excel validates it. The selected value is stored as a real Excel date serial number, so you can use it in formulas like =YEAR(B2) or =TODAY()-B2 to calculate days.
Alternatives for Mac and Excel for the Web
Since the ActiveX control isn’t available on Mac or in the browser, you have several workarounds:
- Data Validation drop-down list: Create a list of dates in a hidden column, then go to Data > Data Validation > Allow: List and select the range. This gives a calendar-like picker, but you must pre-fill the dates.
- Form Control Scroll Bar: Link a scroll bar to a date cell using a formula like
=DATE(2025,1,1)+A1where A1 is the scroll bar’s linked cell. This lets you scroll through dates. - Third-party add-ins: Some free add-ins like “Date Picker” for Excel (from various developers) add a calendar icon to the ribbon. Be cautious with downloads—only use reputable sources.
- Use a formula with TODAY(): For quick entry, type
=TODAY()to insert today’s date, or press Ctrl+; (Windows) or Cmd+; (Mac) to insert the current date as a static value.
Common Problems and Fixes
Users often run into these issues:
- Control is grayed out: Ensure you’re in Design Mode (Developer > Design Mode) to move or resize the control. Click it again to exit.
- Date shows as numbers: If the linked cell displays a serial number like 45678, format that cell as a date (Home > Number Format > Short Date).
- Control doesn’t work after saving: ActiveX controls require macros to be enabled. Save as a macro-enabled workbook (.xlsm) and enable content when opening.
- LinkedCell property missing: In some versions, the property is named CellLink instead. Check both.
- Control appears as a white box: Right-click > Properties and set BackColor to a standard color, or check the Value property for a valid date.
Comparing Date Picker Methods
Here’s a quick comparison to help you choose:
- ActiveX Date Picker: Best for Windows desktop users who want a true calendar. Requires Developer tab and macro-enabled file.
- Data Validation list: Works everywhere, no macros, but needs a pre-defined date range (e.g., a year of dates). Good for fixed options like delivery slots.
- Scroll bar: Compact and interactive, but less intuitive for users unfamiliar with scroll bars.
- Typing manually: Fastest if you know the date, but prone to format errors. Use
=TODAY()for dynamic dates.
Frequently Asked Questions
How do I add a calendar to a single cell in Excel?
Insert the ActiveX Date Picker control as described above, then set its LinkedCell property to that cell. The control will float above the cell; you can position it exactly by holding Alt while dragging to snap to the cell grid.
Can I use a date picker in Excel for Mac?
No, the ActiveX control is Windows-only. On Mac, use Data Validation with a list of dates, or install a third-party add-in. Some add-ins like “Date Picker” from the Office Store work on Mac, but verify compatibility.
Why does my date picker show a number instead of a date?
That’s because the control stores the date as a serial number. Format the linked cell as a date: select the cell, press Ctrl+1 (Windows) or Cmd+1 (Mac), choose Date, and pick a format.
Is the date picker available in Excel 2016 or 2019?
Yes, the ActiveX Date Picker control is available in Excel 2016, 2019, and 2021 for Windows, as long as you have the Developer tab enabled. It’s not in Excel for the web or mobile.
Can I make the date picker automatically update with today’s date?
Yes, set the control’s Value property to =TODAY() in the Properties window, or use a formula in the linked cell like =IF(B2="",TODAY(),B2) to default to today.
Final Tips for Reliable Date Entry
Always test the date picker in a copy of your workbook before relying on it. If you share the file with others, remember that ActiveX controls may be blocked by default in Protected View. To avoid issues, consider using Data Validation for shared workbooks. Also, note that Excel stores dates as numbers (1 = Jan 1, 1900), so any date math works seamlessly with the picked value.
Related
- DepEd Class Attendance Sheet Template Excel: Free Guide
- JavaScript String substring() Explained
- Splunk Split Command: How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Find and Replace Highlight Color in Microsoft WordIn Word for Microsoft 365 on Windows, use Home > Find, then search for the Highlight format and…
- Track Changes in Word 365: A Practical How-To GuideTo track edits in Word 365, open your document, go to Review > Track Changes, and choose whether…
- Excel Attendance File to Print: Set Up and Print ItTo print an Excel attendance file clearly, open it in Excel for Microsoft 365 on Windows, select the…
- Analysis Report in Excel: How to Build a Useful ReportTo make an analysis report in Excel, organize clean records in a table, summarize them with a PivotTable…
- Side-by-Side Box Plot in Excel: Create and Compare DataTo create a side-by-side box plot in Excel for Microsoft 365 on Windows, arrange each group’s numeric observations…
- Excel Green Color Code: RGB, Hex, and Theme ShadesThere is no single Excel green color code: a commonly used Excel/Office green is RGB (0, 176, 80),…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.