How to Create a Calendar in a Pull-Down Excel Menu

Adding a calendar in an Excel 2007 pull-down menu can increase the functionality and accuracy of your spreadsheet. This is a very easy task that requires no add-ins or technical experience. Once you create a calendar using an ActiveX pull-down menu, you have the option to use it “as is” or link it to another cell and update its date content.

Smiling businessman using laptop
credit: Jupiterimages/Pixland/Getty Images

Create a Calendar Using Excel 2007

Step

Enable the "Developer" tab. The Excel "Developer" tab is inactive by default, so you may need to activate it to create a pull-down menu. To activate the "Developer" tab, click the "Office" button in the top-left corner of Excel. Locate and click the "Excel Options" button along the bottom right of the menu. Select and click the "Show Developer in Ribbon" check-box from the pop-up menu, then click "OK" to exit.

Step

Insert a pull-down calendar. Click the "Developer" tab from the Excel main menu ribbon. Click “Insert” to display a menu of options and then click the “More Controls” icon from the ActiveX controls section. The “More Controls” icon is the last icon in the second row. It appears as a hammer and wrench crisscrossing in an “X” pattern. A long list of additional controls will appear, so scroll down until the “Microsoft Date and Time Picker 6.0” option appears. Click the option and then click “OK” to return to your spreadsheet.

Step

Draw, position and size the pull-down calendar. Draw a rectangle the size you desire for the calendar. Use the resize handles on the drop-down box to adjust the size if necessary. To move the calendar to another location on the Excel spreadsheet, click inside the calendar, hold the mouse button down and drag it to a new location. Resize the cell to fit the calendar by double-clicking the top margin lines.

Step

Test the calendar. Deselect the “Design Mode” tab in the main menu ribbon by clicking it once. Click the black triangle to the right of the calendar date, and your calendar will appear. If you need to make additional adjustments, go back to “Design Mode.”

Link Calendar to Another Cell

Step

Select the link cell. Select the cell you want to update when the date on the calendar changes, such as H9. In the formula bar, type in “=B5” (or whatever cell the calendar is in). Then click anywhere outside the cell to deselect it.

Step

Create a link between the calendar and the update cell. Click “Design Mode” and then click on the drop-down calendar to select it. Select “Properties” option from the options box next to the “Design Mode” button and locate the “Linked Cell” option, and in the “Linked Cell” information box, type in “H9” (or whichever cell you select for the date update). Close the “Properties” box and click on “Design Mode” to deselect.

Step

Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.