1. Excel: Create Drop-Down Menus

    Thanks to my popular Excel page, I’ve gotten several questions recently about how to create drop-down menus in Excel. Yes, a quick Google search for “Excel drop-down list” or similar would likely educate most folks well enough, but I’ve haven’t posted in a long while … and this seems like a fine little tutorial to add to my collection.

    NOTE: I’m using Excel 2010 for this tutorial. Instructions would be pretty much the same for Excel 2007 and later versions.

    Excel Feature: Data Validation

    The feature in Excel that allows for us to use drop-down menus in cells is called Data Validation.

    Data Validation

    In truth, Data Validation allows a spreadsheet creator to control all kinds of things about what the user might enter in cell or group of cells. But when it comes to creating a drop-down list for users to choose from, well, that’s where lots of questions tend to arise.

    So let’s pretend that we have a spreadsheet in which we want our users to enter, in Cell B6, one of four choices:

    • Visa
    • MasterCard
    • American Expresss
    • Discover

    First, we need to put those options somewhere in our spreadsheet for Excel to find. So I’m going to enter our “list” of options in Cells A1 through A4:

    Data Validation - Create a List

    Note that I don’t have to create my list of options in the same worksheet; it could be in a different worksheet, too, and in any group of consecutive cells you like. The list can also be in hidden rows; that’s what I end up doing most often in my own spreadsheets.

    Now that we have our list, and with Cell B6 as our active cell, we’ll do the not-so-hard part:

    1. In the Excel ribbon, select the DATA tab.
    2. Click the DATA VALIDATION button.
    3. In the SETTINGS tab, find the “Allow:” box, and select “List.”
    4. Now, below that, in the “Source” field, we’ll tell Excel where to find the list of choices we want users to have. In this case, our list is in the area defined by $A$1:$A$4. (The dollar signs tell Excel that these are absolute references to those rows and columns.)

    5. In the “Source:” field, type “=$A$1:$A$4” and then click OK.

    And just like magic, you’ll see the drop-down menu indicator next to Cell B6. Click that, and our four choices appear in a drop-down menu:

    Data Validation - Drop-Down Menu

    And that, folks, is all it takes to create a drop-down list in any cell (or cells) of your Excel spreadsheets!

    • Pinterest

  2. No Responses to "Excel: Create Drop-Down Menus" ...

    (No comments yet.)

    Follow comments on this post: Comments RSS Feed



    Leave a Comment

    Notice: By submitting a comment, you agree to the Money Musings Terms of Use. Comments deemed to be spam, or of a promotional nature, may be edited, deleted, or forwarded to Chuck Norris, all depending on the webmaster's discretion. So play nice.