Excel Tip: Minimize date entry errors using a calendar drop down list

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, instead of entering dates manually, you can 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 screen shot 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 check box
• 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 cross hair. 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 screen shot below.

Wow, you are really good at Excel now!

For more Sage 300 and Excel Tips, subscibe to the Equation Technologies Blog!