Buzz
Inserting a dropdown menu in Excel 2007 can speed up the data entry process by providing users with a list of options to select from, instead of typing in information every time. Once you insert the dropdown into a cell, the cell will display an arrow. Users will enter data by clicking on the arrow and choosing the desired option. Setting up the dropdown only takes a few minutes and can significantly improve the efficiency of data entry.
Steps
Open the spreadsheet where you want to add the dropdown menu.
Create a list of items to display in the dropdown. Enter the data in the order you want them to appear in the list. The items should be entered in a column or row and must not contain any blank cells.
- To create a list on a separate sheet, click the tab of the sheet where you want to input data. Enter and highlight the data you want to appear in the list. Right-click the highlighted range and select 'Name a Range' from the dropdown. Enter a name for the range in the 'Name' field and click 'OK.' You can then protect or hide the sheet to prevent other users from modifying the list.
Click on the cell where you want to place the dropdown menu.
Click on the 'Data' tab in the Microsoft Excel 2007 ribbon.
Click the 'Data Validation' button in the 'Data Tools' group on the ribbon. The 'Data Validation' dialog box will appear.
Click on the 'Settings' tab and select 'List' from the 'Allow' dropdown menu.
Click the toggle button at the end of the 'Source' box. Select the list of items you want to display in the dropdown.
- If you have created a named range, in the 'Source' box, type an equal sign and the name of the range.
Select or deselect the 'Ignore blank' checkbox (Skip blank cells), depending on whether you want to allow dropdown cells to be empty. Ensure that the 'In-cell dropdown' option is checked.
Click on the 'Input Message' tab to display a message box when the dropdown cell is clicked. Make sure the 'Show input message when cell is selected' option is checked, then enter a title in the 'Title:' box and the message content in the 'Input message:' field.
Click on the 'Error Alert' tab to show an error message if invalid data is entered in the dropdown cell. Ensure that the 'Show error alert after invalid data is entered' option is checked. To display a warning or information without blocking invalid data entry, select 'Warning' or 'Information' from the 'Style' dropdown. To block invalid data entry and show an error message, select 'Stop' from the 'Style' dropdown. Enter a title in the 'Title:' box and the error message in the 'Error message:' field.
Click 'OK' to save your validation criteria and create the dropdown menu.
Tip
- If the dropdown list is longer than the container box, you can adjust the width of the box to display the full text.
- To remove the frame or dropdown list, click on the box containing the frame/list. Go to the "Data" tab in the Microsoft Excel 2007 ribbon. Click on the "Data Validation" button in the "Data Tools" group. In the "Settings" tab, click on the "Clear All" button and then click "OK."
