Create drop-down lists in your Excel spreadsheets, all based on predefined lists you specify within the worksheet itself. Drop-down lists are a quick way to enter information into spreadsheets. They are especially useful when you need to provide predefined options to someone filling out the spreadsheet, or when you have many similar items that could appear in a list, such as states or countries.
This article will show you multiple methods for adding customized drop-down lists to an Excel spreadsheet.
How to create a drop down list in Excel
It’s easier than it sounds
Image Credit: Microsoft(Image: © Microsoft)
Want to know how to create a drop down list in Microsoft Excel? Well, we’ve got you covered. Creating a simple drop down list in Excel might sound a bit intimidating at first, but it’s actually very simple. It’s so simple, in fact, that even an elementary school kid can do it – all it takes is a few quick clicks of the mouse.
That’s the beauty of Excel and spreadsheet applications like it. Their existence is to make what would be otherwise complicated tasks so much easier to do and much more accessible to even those who are not very computer savvy.
Creating a drop down list is as easy as pie, regardless of your Excel skill level. There’s a more technical process, but we’re only covering the easy methods. Whether it’s for your small business or for work, you’ll master soon enough how to create a drop down list in Excel.
1. How to create a drop down list in Excel using existing data
In this first method of creating a drop down list in Excel, we’re working under the presumption that you already have a working list of items you want to use in your drop down list, already entered in your spreadsheet.
Step 1. Select the cell for your drop down list.
Make sure that this is the cell you want to use for the drop down list, so you won’t have to adjust later.
Step 2. Go to the Data tab then click on Data Validation.
This opens the Data Validation dialogue box.
Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.
Step 4. Click on the Source field. Then click and drag your mouse pointer over all the cells that contain the items you want to use for your drop down list.
This selects those cells all at once and automatically fills in the Source field with the data you need for your drop down list.
Step 5. Click OK.
2. Creating a drop down list in Excel by manually entering items
Using the second method, you don’t have an existing list in your spreadsheet to work with so you will be entering the items manually. To do that, you just need to follow the steps above until Step 4.
Step 1. Select the cell for your drop down list.
Step 2. Go to the Data tab then click on Data Validation to open the Data Validation dialogue box.
Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.
Step 4. Click on the Source field. Then enter every single item for your drop down list, separating each item by a comma.
Step 5. Click OK.
3. How to create several drop down lists at once
If you want to create several of the same drop down list all at once, you’ll be happy to know that the process is just as easy as creating a single drop down list. The only difference is that you’re selecting several cells – the cells you want your drop down lists in.
Step 1. Select the exact cells you want to use for the drop down lists.
Step 2. Go to the Data tab then click on Data Validation to open the Data Validation dialogue box.
Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.
Step 4. Click on the Source field. Then click and drag your mouse pointer over all the cells that contain the items you want to use for your drop down list. If you’re entering the items manually, simply enter every single item, separating each item by a comma.
Step 5. Click OK.
Data entry is quicker and more accurate when you use a drop-down list to limit the entries people can make in a cell. When someone selects a cell, the drop-down list’s down-arrow appears, and they can click it and make a selection.
Create a drop-down list
You can make a worksheet more efficient by providing drop-down lists. Someone using your worksheet clicks an arrow, and then clicks an entry in the list.
- Select the cells that you want to contain the lists.
- On the ribbon, click DATA > Data Validation.
- In the dialog, set Allow to List.
- Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.
How to Make a Drop Down in Excel 2010
- Create the list for the dropdown
- Select the items, enter a name, then press Enter.
- Click the cell where the dropdown should be.
- Choose the Data tab.
- Click Data Validation.
- Choose the List option
- Type an “=” sign, then the Name from step 2.
- Click the OK button.
There are some other settings you can or should apply to the list, which we discuss below. Our article continues below with more information and pictures for these steps.
Adding a Drop-Down List in Excel 2010
The steps in the article below will result in a drop-down list that you can click to select an option. This is ideally used in situations where you are looking for a specific value or type of text to occur in a cell, and you want to avoid problems that can arise when people manually enter the values themselves.
Step 1: Open your spreadsheet in Excel 2010.
Step 2: Type the items that you want to include in the list into a column in your spreadsheet. It does not need to be the first column. It can be any column you wish.
Step 2: Use your mouse to select all of the items to include in the list, type a name into the Name field above the top-left corner of the spreadsheet, then press the Enter key on your keyboard. Note that you cannot use any spaces or special characters when creating this name.
Step 4: Select the cell where you want the drop-down list to appear.
Step 5: Click the Data tab at the top of the window.
Step 6: Click the Data Validation button in the Data Tools section of the Office ribbon.
Step 7: Click the drop-down menu under Allow, then click the List option.
Step 8: Type an “=” sign into the Source field, followed by the name that you created for your range of cells. For example, I am typing =DaysOfTheWeek in the image below.
Step 9 (optional): Click the Input Message tab at the top of the window.
Step 10 (optional): Type a title for the drop-down list into the Title field, then type an input message into the Input Message field that you want to display whenever the cell is selected. This is a good place to add instructions for the drop-down list.
Step 11 (optional): Click the Error Alert tab.
Step 12 (optional): Select the style of alert, then enter a title and message for the alert. Note that a Stop alert will prevent anyone from entering a value that is not on the list, while a Warning or Information style of alert will allow invalid entries and only inform the user that their entry is not valid.
Step 13: Click the OK button at the bottom of the window to apply your settings.
You can make changes to the drop-down list by clicking the cell to select it, then clicking the Data Validation button on the Data tab.
The first few times you create a dropdown list it’s likely that you will discover that there are some things you want to change. For example, if other people are going to be entering data, then you are probably going to want to customize the settings marked as “optional” above.
These various alerts and validation settings help to reduce errors and eliminate confusion, which you may discover is a very important element of Excel dropdown menus.
Do you need to print your spreadsheet, but there’s one column that is printing on its’ own page? This guide will show you how to force all of your columns to print on one page and save yourself some pages.
Conclusion
Excel allows you to display data in several ways, so you can decide what best suits your needs. The data range could be displayed as an AutoFilter. AutoFilter allows for sorting the data according to specified criteria. Data could also be sorted by different types of pivot tables. On the other hand, if you are looking for a faster way to display specific information that does not require further manipulation, then drop-down list is your best choice.