How To Create A Drop Down Calendar In Excel 2007

Dropdown calendar in Excel

UPDATE on 7/24/20202: Don't miss the video at the end of this post.

Did you ever ask why Excel does not provide Dropdown Calendar for date selection? Like the one shown below:

I did. I asked that because I didn't know its existence in Excel as it is so well hidden. So I would like to share with you, just in case you need it. First, you need to go to the Developer Tab. In case it is not on your ribbon, follow the following steps

Go to File Tab (or Ribbon icon for Excel 2007)
Go to Customize Ribbon
Check the "Develop" on the right under Main Tabs
OK
There you go

Now go to Developer tab
Select "Insert"
Click on "More Controls" – the lowest right icon

The "More Controls" dialog box is opened Scroll down and look for " Microsoft Date and Time Picker Control 6.0 (SP6) "

Now you should see the DTPicker (Dropdown box)

Pls note that the "Design Mode" is turned ON, meaning you can edit (move/resize/change properties) the DTPicker. Don't worry about the formula shown. Properties Setting – Most of the time, you want to Link the value in the DTPicker to a specific cell.

To do this:

Right Click the DTPicker (Make sure the "Design Mode" is ON)
Select Properties
In the Properties Windows, input the Cell Reference you want in the Linked Cell . Let's input A1 for this example

You may see an error message box. Just ignore it
Now you may want to resize the DTPicker by drag and drop a corner of it

When you have finished property setting, resizing and moving it to your designated location, you need to turn OFF the "Design Mode" in order to activate the DTPicker To experiment it, click on the dropdown arrow to display the calendar below:

Select the date you want
The date selected is LINKED to the cell you have assigned, i.e. A1 in this case

That is it. Make every day count. Reminders:

If you want to edit or even move the DTPicker, you have to TURN ON the "Design Mode" first
The date on the Linked Cell is actually set as Text not Date. Normally it won't affect date-related calculation but you may not apply Date format on that cell directly.

UPDATE:

Mr. Excel shares a great tip in having a date picker in all versions of Excel! Check it out!

Thanks Mr. Excel for sharing the tip; and Thanks SAM RADAKOVITZ for the creation! Super!

Want more date-related topics? Please read:

What we need to know about inputting Date in Excel?
Advantages of having date input correctly in Excel
Fixing trouble dates
Convert an 8-digit number into Excel-recognizable Date

Excel Dashboard Course

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.

This entry was posted in Excel Tips and tagged Dropdown Calendar, DTPicker. Bookmark the permalink.