How to Insert a Calendar When Clicking on a Cell in Excel

By Darrin Koltow

When manually entering dates in one of your business' Excel workbooks, choosing from a limited set of predefined values is easier than choosing from a continuous range of values. This entry method is also less prone to error. Microsoft invented the Date Picker control for such reasons. The control's default configuration lets users click the control to display a pop-up calendar, from which they can select a date. The control has several parameters for customization, including those for choosing date formats. You can find Date Picker on the Developer tab, which is hidden by default.

Create a new Excel workbook, click the "File" menu and choose "Options." Click the "Customize Ribbon" category and check the "Developer" check box in the Main Tabs pane. Click "OK" to see the Developer tab among Excel's other tabs.

Click the Developer tab's "Insert" button, which is in the Controls group. Excel will display a list of form controls and a button labeled "More Controls" for displaying additional controls. This button's icon shows a hammer crisscrossed with a wrench.

Click "More Controls" to display a dialog box filled with controls. Double-click the "Microsoft Date and Time Picker" control. The dialog box will close and Excel will display crosshairs in place of the default mouse cursor.

Click and drag on the cell in which you want the calendar to appear. When you release the mouse, Excel will display a drop-down control that shows the current date. Excel also will highlight the Design Mode button. Date Picker doesn't allow date selection in design mode.

Click the "Design Mode" button to leave Design Mode and click the down arrow on the Date Picker control you inserted. Excel will display a monthly calendar. Click any date on the calendar to close the calendar. The date you chose will appear in the format "MM/DD/YYYY."

Click the "Design Mode" button again to enter design mode and right-click on the Date Picker control. Click "DTPicker Object" and click "Properties." Excel will display a dialog box for customizing the Date Picker control. Click the "dtpLongDate" item from the Format control and close the dialog box.

Right-click the Date Picker and click the "Properties" item to display the Properties pane. Type "A1" or any other cell address in the Linked Cell property. This action specifies the cell that holds the date chosen from Date Picker. Until you perform this step, the chosen date doesn't exist as usable data in your workbook.

Click the "Design Mode" button to exit design mode. Date Picker will display the date in a long format, e.g. "Tuesday, December 25, 2012."

Click another date from the control. The date will appear in the cell you specified with the Linked Cell property.


Information in this article applies to Excel 2010. It may vary slightly or significantly with other versions or products.