How to create calendar in Excel (drop-down and printable)

1. Show the Developer tab on the ribbon

The datepicker control belongs to a family of ActiveX controls, which reside on the Developer tab. By default, the Developer tab is hidden in Excel, but you can force it to appear in no time.

• Right-click anywhere on the ribbon, and then click Customize the Ribbon…
• In the right-hand part of the window, select Main Tabs (usually it is selected by default), check the Developer box, and click OK.

2. Insert a calendar control

A drop-down calendar in Excel is technically called Microsoft Date and Time Picker Control. To insert it in your sheet, just do the following:

• Go to the Developer tab > Controls group, click Insert, and then click the More Controls button under ActiveX Controls.
• In the More Controls dialog window, find the Microsoft Date and Time Picker Control 6.0 (SP6), select it, and click OK.

  If you cannot find the Date and Time Picker Control in the list, please follow these instructions to register it.

• Finally, click on a cell where you want to insert the calendar control.

That's it! A drop down calendar control is inserted in your Excel sheet:

As soon as the datepicker control is inserted, the EMBED formula appears in the formula bar. It informs Excel what type of control is embedded in the sheet, and in no case you should change or delete it, because this would result in the "Reference is not valid" error.

Inserting any ActiveX control (including DTPiker) automatically turns the Design Mode on allowing you to modify the appearance and properties of the newly added control. The most obvious changes that you will want to make most of the time is to resize your calendar control and link it to a specific cell.

To activate your Excel drop down calendar, go to the Design tab > Controls group, and turn off the Design Mode:

And now, you can click on the dropdown arrow to display the calendar and select the desired date:

3. Customize the calendar control

After adding a calendar control to your Excel sheet, the first thing you may to do is to move it to the desired location and get it to fit into a cell.

To resize the datepicker control, turn the Design Mode on, and drag a corner of the control:

Alternatively, with the Design Mode on, select your calendar control, and click Properties:

In the Properties window, you can set the desired height, width as well as change the font theme and size:

To move the datepicker control, hover your mouse over it and as soon as the cursor changes to a four-pointed arrow, drag the control where you want it.

4. Link the calendar control to a cell

Now that you have successfully added a drop down calendar in Excel, you may also want to link it to a specific cell. It is absolutely necessary if you intend to use the selected dates in formulas.

Let's say, you've written a formula to count the number of orders between the specified dates. To prevent your users from inputting incorrect dates like 2/30/2016, you inserted the dropdown calendars in 2 cells (A2 and B2 in this example). However, your obviously correct COUNTIFS formula returns zero, although you can clearly see a few orders within the specified time period!

The reason is that Excel cannot recognize the value of a date picker control until you associate it with a certain cell. To fix this, link your date picker controls to some cells in this way:

1. With the Design Mode on, select the calendar control.
2. On the Developer tab, in the Controls group, click Properties.
3. In the Properties windows, find the LinkedCell property, and type a cell reference next to it (A3 in this example):

If Excel displays the "Can't set cell value to NULL…" error, just click OK to ignore it.

And now, as soon as you select a date in the dropdown calendar, that date immediately appears in a linked cell. As shown in the screenshot below, Excel has no problem with understanding such dates, and our formula referencing the linked cells (A3 and B3) works perfectly:

If you don't want to clutter your worksheet with "extra dates", you can link your date picker controls to the cells where they reside (A2 and B2 in this example). In this case, the underling dates won't be visible to the users, but you and Excel will know they are there and your formula will work faultlessly again:

How to register the Calendar control on your machine

In earlier versions of Excel, the Date and Time Picker Control (mscomct2.ocx in Excel 2010 and 2013 and mscal.ocx in Excel 2007) was usually included with the default installation. The users of Excel 2016 and higher will have to download and register this control manually. Here's how:

Ablebits Date Picker for Excel

The users of the Ultimate Suite can find their Excel drop-down calendar on the Ablebits tab in the Date & Time group. The Date Picker is turned off by default, to turn it on, just click this button:

Once the Date Picker is activated, a small calendar button appears on the sheet when you type or select a date in a cell. If necessary, you can change date format as usual and the calendar will supply dates in your custom format - a nice feature that Microsoft's Date and Time Picker control lacks. Additionally, the calendar shows how far away the selected date is from today's date. Also, it allows you to quickly get to today's date or switch between one-month and two-month views:

Moreover, you can also calculate dates right in the calendar! For this, select the target date in your worksheet or in the calendar, and click the Date Calculator button in the upper right corner. Then, click the desired unit on the upper pane and type how many years, months, weeks or days (or any combination of these units) you want to add to or subtract from the target date. After that, press the Enter key to insert the result into the currently selected cell or press F6 to display the calculated date in the calendar (or click one of the buttons shown in the image below).

As an example, let's add 1 year and 60 days to April 1, 2018:

If you don't have our Ultimate Suite in your Excel yet, you can download a trial version here, or you can go with one of the free drop-down calendars listed below.

Excel Date Picker

Excel Date Picker from samradapps.com is a beautifully designed and easy to use calendar that supports all versions of Excel 2007 to 2016.

It adds the Date Picker button to the ribbon, and optionally, an item to the right click menu. And I confirm that it works without a hitch in my Excel 2016 64 bit.

Pop-up Excel Calendar

It is another free pop-up calendar for Excel 2003 to 2016. Although support for 64 bit Excel is declared by the manufacture, it throws sporadic errors in my Excel 2013 and 2016 64 bit.

In all other respects, it's a handy little tool that displays a calendar whenever needed - for cells containing date values, date formats or certain labels.

Pop-up calendars for Excel Online

If you often manage time tables, schedules and to-do lists in Excel Online, you may find the following Calendar add-ins helpful.

XLTools.net Calendar - one inserted, the below calendar appears on the sheet enabling you to enter or change a date in a click.

Modern Calendar & Date Picker - a pop-up calendar that works with the desktop versions of Excel 2013 and 2016, Excel 2016 for Mac, and Excel Online. It includes dozens of themes for you to choose from and costs around $1.50.

Mini Calendar and Date Picker - another low-cost calendar for Excel Online, Excel 2013, Excel 2016 and Excel 2016 for Mac. It allows you to toggle the size of the calendar, show week numbers, switch between the U.S. or ISO calendar systems, and choose different themes.