report

How to Create a Drop-Down Menu in Microsoft Excel

Method 1

  1. Open your Excel spreadsheet and select the cells where you want the drop-down list to appear.
  2. Select the 'Data' tab.
  3. Select the 'Data Validation' button. A dialogue box will appear.
  4. Under the 'Settings' tab, select the drop-down option 'List'. This will create a box entitled 'Source' below.
  5. In the new 'Source' box, write down the options you wish to appear in your drop-down menu, with each item separated by a comma.
  6. If you don't want anything else being written in these boxes, go to the 'Error Alert' tab and make sure the box is ticked next to the words 'Show error alert after invalid data is entered'. You can write your own error alert message in the boxes on the right-hand side of the dialogue box if you wish.
  7. Press 'OK'.

The drop-down menu should now work. When you click in one of the boxes you selected earlier, an arrow should appear on the right-hand side of the box allowing you to select one of your options.

Method 2

  1. Go to Sheet 2 (or a blank sheet) and write a list of the options you want to see in your drop-down menu.
  2. Go back to the table you want the drop-down menu to be in.
  3. Select the cells you want the drop-down list to appear in.
  4. Select the 'Data' tab.
  5. Select the 'Data Validation' button. A dialogue box will appear.Under the 'Settings' tab, select the drop-down option 'List'. This will create a box entitled 'Source' below.
  6. On the right-hand side of the 'Source' box, there is a little button showing a red arrow. Click on this button.
  7. The dialogue box will minimise. Go to Sheet 2 or wherever you wrote your list of options in step 1. Highlight your list of options, then click on the little button with the red arrow again. The dialogue box will appear in full again.
  8. If you don't want anything else being written in these boxes, go to the 'Error Alert' tab and make sure the box is ticked next to the words 'Show error alert after invalid data is entered'. You can write your own error alert message in the boxes on the right-hand side of the dialogue box if you wish.
  9. Press 'OK'.

As in Method 1 above, you should now be able to click in one of the cells you selected in step 3 and an arrow should appear on the right-hand side. When you click on this arrow, your list of options should appear.

To tidy up your spreadsheet, you may want to right click on the tab for Sheet 2, or wherever you wrote the list of your drop-down options, and select 'Hide'. This makes it harder for someone else to find and/or change the list of options.

Creating drop-down menu's saves time from having to type out the same things repeatedly and prevents errors, such as spelling mistakes. It's really easy to get the hang of and you can use several drop-down menu's in one sheet.

Hopefully, this article has been informative. If you'd like to learn anything further about Excel, please comment below and let me know what else you'd like to see.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article

    Menu

    Explore