MS Excel Dropdown List

Create a dropdown with Excel 2003

If you’re using Excel 2010, click here for an updated post.

Just like a drop-down list on a web page, the drop-down list on an Excel spreadsheet presents a [limited] number of options for you to select, thus limit potential data entry errors and make the output more consistent and uniform. The drop-down list mentioned in this post has a down arrow button, but it is not the Filter/AutoFilter functionality of Excel, which can be accessed through menu Data > Filter > AutoFilter.

Excel dropdown list = dropdown + list

Excel dropdown list basically has 2 parts: the dropdown options, and the list that constitutes the options behind the scene. The list and the dropdown can be located on the same worksheet, or different worksheets, or even on different workbooks.

The way to create and use a dropdown list is that you present the dropdown to your users, but you hide the list so that the users can’t access, thus can’t modify the list themselves. When a cell has a dropdown list, Excel displays an arrow next to that cell when people click on/select the cell. However, if they don’t select the cell, the arrow won’t be visible.

Create Excel dropdown list

Here’s how to create a dropdown list of months. In this example, the list and the dropdown are on different spreadsheets.

• Create the list: Type your list in a certain column on Sheet2. In this example, cells A1:A12 hold your month list.
  
• Define its name: Give your list a name by selecting the list from cell A1 to cell A12, then going to Insert > Name > Define. Let say you name your list month
• Create the drop-down: In Sheet1, select the cell or a range of cells you want to display the dropdown — you can type something like “Select month” or “Please select” or just leave the cell(s) blank — then go to Data > Validation > Settings tab and select List from Allow and type in =month in Source. Remember to have an equal sign in front of your list name when entering it in the Source field. You’re done!

Now if you click on “Select month,” you’ll see a down arrow button, which will expand the dropdown list of months when clicked on.

» DOWNLOAD SAMPLE FILE

Related Posts:

• MS Excel 2003 Custom Colors, Custom Chart Colors & Custom Color Palette
• MS Excel 2010 Custom Colors
• Excel – Highlight Cells That Meet Specific Criteria with Conditional Formatting
• How To Use Adobe Acrobat Professional 9
• Display Excel Data On a Web Page