How to Create a Drop Down List in Excel (the Only Guide You Need)
- -- By Sumit Bansal
FREE EXCEL TIPS EBOOK - Click here to get your copy
A drop-down list is an excellent way to give the user an option to select from apre-defined list.
It can be used while getting a user to fill a form,or while creating interactive Excel dashboards.
Drop-down lists are quite common on websites/apps and are very intuitive for the user.
Watch Video – Creating a Drop Down List in Excel
In this tutorial, you’ll learn how to create a drop down list in Excel (it takes only a few seconds to do this) along with all the awesome stuff you can do with it.
This Tutorial Covers:
- How to Create a Drop Down List in Excel
- #1 Using Data from Cells
- #2 By Entering Data Manually
- #3 Using Excel Formulas
- Creating a Dynamic Drop Down List in Excel (Using OFFSET)
- Copy Pasting Drop-Down Lists in Excel
- Caution while Working with ExcelDrop Down List
- How to Select All Cells that have a Drop Down List in it
- Creating a Dependent / Conditional Excel Drop Down List
Working with your drop-down list
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries.
If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.
If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.
To delete a drop-down list, see Remove a drop-down list.
Download our examples
You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples.
Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.
Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool.
-
Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells.
-
Select the cells that you want to restrict data entry in.
-
On the Data tab, under Tools, click Data Validation or Validate.
Note:If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
-
Click the Settings tab, and then in the Allow pop-up menu, click List.
-
Click in the Source box, and then on your sheet, select your list of valid entries.
The dialog box minimizes to make the sheet easier to see.
-
Press RETURN or click the Expand
button to restore the dialog box, and then click OK.Tips:
-
You can also type values directly into the Source box, separated by a comma.
-
To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.
-
You can specify your own error message to respond to invalid data inputs. On the Data tab, click Data Validation or Validate, and then click the Error Alert tab.
-
See also
Apply data validation to cells
-
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table.
Notes:
-
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.
-
Now is a good time to Sort your data in the order you want it to appear in your drop-down list.
-
-
Select the cell in the worksheet where you want the drop-down list.
-
Go to the Data tab on the Ribbon, then click Data Validation.
-
On the Settings tab, in the Allow box, click List.
-
If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this:
Fruit,Vegetables,Grains,Dairy,Snacks
-
If it’s OK for people to leave the cell empty, check the Ignore blank box.
-
Check the In-cell dropdown box.
-
Click the Input Message tab.
-
If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
-
-
Click the Error Alert tab.
-
If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message. If you don’t want a message to show up, clear the check box.
-
-
Click OK.
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list. To delete a drop-down list, see Remove a drop-down list.
How to make drop down list allowing free text (manually entry) in Excel?
By default, for the cells with created data validation drop down lists, you are not allowed to enter texts beyond the list values. When entering words that do not include in the list, an error dialog box will pop up as the below screenshot shown. This tutorial is going to show you how to make drop down list allowing free text in Excel.
Make drop down list allowing free text in Excel
More tutorial for drop down list...
Make drop down list allowing free text in Excel
Please do as follows to make drop down list allowing free text.
1. Select the cells which contain the drop down lists you have already created, and then click Data > Data Validation> Data Validation. See screenshot:
2. In the Data Validation dialog box, uncheck the Show error alert after invalid data is entered box under Error Alert tab, then click the OK button.
Then you can enter any text into drop down list cells as you need without getting an error alert.
Alternatively, you can keep the Show error alert after invalid data is entered box checked, choose Warning from the Style drop-down list and then click the OK button.
After that, when entering a value doesn't match the data validation restrictions defined for a cell, a Microsoft Excel warning dialog will pop up. Click Yes to continue.
Easily select multiple items from drop-down list in Excel:
Have you ever tried to select multiple items from a drop down list in Excel? Here the Multi-select Drop Down List utility of Kutools for Excel can help you easily select multiple items from the drop-down list in a range, current worksheet, current workbook or all workbooks. See below demo:
Download the full feature 30-day free trail of Kutools for Excel now!
Related articles:
Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.
Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.
Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.
Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.
More tutorial for drop down list...
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More... Free Download... Purchase...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Read More... Free Download... Purchase...
- Comment
- Login
Sort by Newest
- Best
- Popular
- Newest
- Oldest
Say something here...
symbols left.
You are guest
- Login Now
or post as a guest, but your post won't be published automatically. Post
Loading comment... The comment will be refreshed after 00:00.
To post as a guest, your comment is unpublished.
PeterR · 4 years ago
You can also keep the error alert checked, but change the style from "Stop" into "Warning" with a message.
- Reply
How to Create Drop-down Lists in Cells – Data Validation Lists
August 8, 2018
21 comments
Bottom Line: The complete Excel guide on how to create drop-down lists in cells (data validation lists). Includes keyboard shortcuts to select items, copying drop-downs to other cells, handling invalid inputs, updating lists with new items, and more.
Skill Level: Beginner