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.
- Skill level:
Other People Are Reading
Things you need
- Microsoft Excel 2007
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.
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.
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.
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."
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.
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.
Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.
Tips and warnings
- While you can configure the pull-down menu to any size, the calendar default size is not adjustable.
- A pull-down calendar can link to one additional cell only. To update multiple cells, you need to create multiple calendars.
- Creating a pull-down calendar in Excel 2003 (and earlier editions) requires a third-party add-in. Visit the Microsoft Office Marketplace for ideas and third-party vendor information.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for