Ensuring data integrity by limiting date entry errors in a Microsoft® Excel® worksheet can easily be achieved by adding a calendar drop down list, so that instead of entering dates manually you can rather select them from the list. In ten simple steps, we show you how you can add a calendar drop down list using only control buttons and no VBA code.
Note: Download the sample workbook to practice this exercise.
Applies to: Microsoft Excel 2007, 2010 (32-bit) and 2013 (32-bit)
The screen shot below will be used for this example.
1. Activate the developer tab.
a. In Microsoft Excel 2010:
- Select the File tab.
- Select Options.
- Select Customize Ribbon.
- Tick the Developer box and select OK as per the screenshot below.
- The Developer tab will now appear on the ribbon.
b. In Microsoft Excel 2007:
- Select the Office button
- Select Excel Options
- Select the Popular tab
- Tick the Show Developer tab in the ribbon checkbox
- The Developer tab will now appear on the ribbon.
2. Select the Developer tab.
3. Select Insert. A drop down list with icons for Form Controls and ActiveX Controls will appear.
4. Select the icon on the bottom right hand corner under the ActiveX Controls.
5. More Controls will appear. Select Microsoft Date and Time Picker Control 6.0 (SP4)
6. Select OK.
7. Your cursor will turn into a crosshair. Click and hold, draw a rectangle in cell C3.
8. Right-click on the Date Control and select Properties.
9. Then type the cell where the date will be displayed next to linked cell as below.
- Ignore any error that you might get by clicking OK on the error message dialog box.
- Close the properties window.
- Select Design Mode to turn it off as per the screenshot below.
10. When you select the date from the drop down menu the date in cell C4 will change.
