Tnhelearning.edu.vn - Various useful general information portal

How to Create a Drop-Down List in Excel

by Category: How To

You are viewing the article How to Create a Drop-Down List in Excel  at Tnhelearning.edu.vn you can quickly access the necessary information in the table of contents of the article below.

This wikiHow teaches you how to use a computer to create drop-down lists on a Microsoft Excel worksheet. This feature allows you to create a list of items to select from, and insert a drop-down selector into any empty cell on the worksheet. The drop-down box feature is only available on the desktop version of Excel.

Create a list

Image titled Create a Drop Down List in Excel Step 1

Image titled Create a Drop Down List in Excel Step 1

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d9/Create-a-Drop-Down-List-in-Excel-Step-1-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-1-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d9/ Create-a-Drop-Down-List-in-Excel-Step-1-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-1-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Open the Excel worksheet file you want to edit. You can find and double-click the Excel file saved on your computer, or open Microsoft Excel and create a new workbook.
Image titled Create a Drop Down List in Excel Step 2

Image titled Create a Drop Down List in Excel Step 2

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/eb/Create-a-Drop-Down-List-in-Excel-Step-2-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-2-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/eb/ Create-a-Drop-Down-List-in-Excel-Step-2-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-2-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter a list of values for the drop-down box. You must enter the drop-down items in separate, consecutive cells in the same column.

  • For example, if you wanted the drop-down list to include “New York”, “Boston”, and “Los Angeles”, you could enter “New York” in cell A1, “Boston” in cell A2, and “Los Angeles” in cell A3.
Image titled Create a Drop Down List in Excel Step 3

Image titled Create a Drop Down List in Excel Step 3

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/9d/Create-a-Drop-Down-List-in-Excel-Step-3-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-3-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/9d/ Create-a-Drop-Down-List-in-Excel-Step-3-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-3-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click in the empty cell where you want to insert the drop-down box. You can insert a drop-down list into any empty cell on the worksheet.
Image titled Create a Drop Down List in Excel Step 4

Image titled Create a Drop Down List in Excel Step 4

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/2e/Create-a-Drop-Down-List-in-Excel-Step-4-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-4-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/2e/ Create-a-Drop-Down-List-in-Excel-Step-4-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-4-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the Data tab located in the toolbar ribbon. This button is above the toolbar at the top of the worksheet. The data tools will open.
Image titled Create a Drop Down List in Excel Step 5

Image titled Create a Drop Down List in Excel Step 5

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/33/Create-a-Drop-Down-List-in-Excel-Step-5-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-5-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/33/ Create-a-Drop-Down-List-in-Excel-Step-5-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-5-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the Data Validation button located in the “Data” toolbar. This button consists of two separate box icons with a green checkmark and a red ban. A new dialog box will pop up.
Image titled Create a Drop Down List in Excel Step 6

READ More:   How to Convert Minutes to Hours

Image titled Create a Drop Down List in Excel Step 6

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/33/Create-a-Drop-Down-List-in-Excel-Step-6-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-6-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/33/ Create-a-Drop-Down-List-in-Excel-Step-6-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-6-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the Allow drop-down box located in the “Data Validation” dialog box that just popped up. This menu is under the “Settings” tab on the dialog box.

  • The Data Validation pop-up dialog will automatically open with the Settings tab.
Image titled Create a Drop Down List in Excel Step 7

Image titled Create a Drop Down List in Excel Step 7

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/1a/Create-a-Drop-Down-List-in-Excel-Step-7-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-7-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/1a/ Create-a-Drop-Down-List-in-Excel-Step-7-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-7-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select List from the “Allow” drop-down menu. This option will allow you to create a list in the selected empty cell.
Image titled Create a Drop Down List in Excel Step 8

Image titled Create a Drop Down List in Excel Step 8

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/30/Create-a-Drop-Down-List-in-Excel-Step-8-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-8-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/30/ Create-a-Drop-Down-List-in-Excel-Step-8-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-8-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Tick the option Windows Unchecked

{“smallUrl”:”https://www.wikihow.com/images_en/e/ed/Windows10unchecked.png”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ed/ Windows10unchecked.png/27px-Windows10unchecked.png”,”smallWidth”:460,”smallHeight”:477,”bigWidth”:27,”bigHeight”:28,”licensing”:”<div class=”mw-parser-output” “></div>”}

In-cell dropdown . When you check this option, you will create a drop-down list located in the selected cell on the worksheet.

Image titled Create a Drop Down List in Excel Step 9

Image titled Create a Drop Down List in Excel Step 9

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/16/Create-a-Drop-Down-List-in-Excel-Step-9-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-9-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/16/ Create-a-Drop-Down-List-in-Excel-Step-9-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-9-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Tick the option Windows Unchecked

{“smallUrl”:”https://www.wikihow.com/images_en/e/ed/Windows10unchecked.png”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ed/ Windows10unchecked.png/27px-Windows10unchecked.png”,”smallWidth”:460,”smallHeight”:477,”bigWidth”:27,”bigHeight”:28,”licensing”:”<div class=”mw-parser-output” “></div>”}

Ignore blank (optional). When this box is checked, the user will be able to leave the drop-down box blank without an error message.

  • If the drop-down box you are creating is a required field, you need to make sure that this box is unchecked. Or you may not tick.
Image titled Create a Drop Down List in Excel Step 10

Image titled Create a Drop Down List in Excel Step 10

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d8/Create-a-Drop-Down-List-in-Excel-Step-10-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-10-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d8/ Create-a-Drop-Down-List-in-Excel-Step-10-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-10-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the text frame below “Source” on the pop-up dialog. You can select the list of values you want to insert into the drop-down box here.
Image titled Create a Drop Down List in Excel Step 11

Image titled Create a Drop Down List in Excel Step 11

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/29/Create-a-Drop-Down-List-in-Excel-Step-11-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-11-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/29/ Create-a-Drop-Down-List-in-Excel-Step-11-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-11-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select the value list for the drop-down box on the worksheet. Use your mouse to select the list of values that you want to insert into the drop-down box on the worksheet.

  • For example, if you already have “New York”, “Boston” and “Los Angeles” data in cells A1, A2, and A3, you need to select the cell range A1 to A3.
  • Or you can manually enter the values of the drop-down list in the “Source” box here. In this case, you need to be sure to separate each item separately with a comma.

Customize property list

Image titled Create a Drop Down List in Excel Step 12

Image titled Create a Drop Down List in Excel Step 12

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a3/Create-a-Drop-Down-List-in-Excel-Step-12-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-12-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a3/ Create-a-Drop-Down-List-in-Excel-Step-12-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-12-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the Input Message tab at the top of the “Data Validation” dialog window that pops up. This tag allows you to create a pop-up message that displays next to the drop-down list.
Image titled Create a Drop Down List in Excel Step 13

READ More:   How to Remove Scratches on Glass

Image titled Create a Drop Down List in Excel Step 13

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/7a/Create-a-Drop-Down-List-in-Excel-Step-13-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-13-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/7a/ Create-a-Drop-Down-List-in-Excel-Step-13-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-13-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Tick the option Windows Unchecked

{“smallUrl”:”https://www.wikihow.com/images_en/e/ed/Windows10unchecked.png”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ed/ Windows10unchecked.png/27px-Windows10unchecked.png”,”smallWidth”:460,”smallHeight”:477,”bigWidth”:27,”bigHeight”:28,”licensing”:”<div class=”mw-parser-output” “></div>”}

Show input message… . This option allows you to display a small pop-up message if the drop-down box is selected.

  • If you don’t want the pop-up message to be displayed, there’s no need to check the box.
Image titled Create a Drop Down List in Excel Step 14

Image titled Create a Drop Down List in Excel Step 14

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3c/Create-a-Drop-Down-List-in-Excel-Step-14-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-14-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/3c/ Create-a-Drop-Down-List-in-Excel-Step-14-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-14-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter “Title” and “Input Message”. You can use this area to explain, describe, or provide more information about the drop-down list.

  • The input message and title you type here will show up in a small yellow message that pops up next to the drop-down box when the cell containing the list is selected.
Image titled Create a Drop Down List in Excel Step 15

Image titled Create a Drop Down List in Excel Step 15

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/9e/Create-a-Drop-Down-List-in-Excel-Step-15-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-15-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/9e/ Create-a-Drop-Down-List-in-Excel-Step-15-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-15-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the Error Alert tab at the top of the dialog box. This tag allows an error message to pop up every time invalid data is entered in the drop-down box.
Image titled Create a Drop Down List in Excel Step 16

Image titled Create a Drop Down List in Excel Step 16

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Create-a-Drop-Down-List-in-Excel-Step-16-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-16-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6e/ Create-a-Drop-Down-List-in-Excel-Step-16-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-16-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Tick the option Windows Unchecked

{“smallUrl”:”https://www.wikihow.com/images_en/e/ed/Windows10unchecked.png”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ed/ Windows10unchecked.png/27px-Windows10unchecked.png”,”smallWidth”:460,”smallHeight”:477,”bigWidth”:27,”bigHeight”:28,”licensing”:”<div class=”mw-parser-output” “></div>”}

Show error alert… (Show error alert). When this option is checked, an error message will pop up when the user enters invalid data in the drop-down box.

  • If you don’t want the error message to pop up, don’t check this box.
Image titled Create a Drop Down List in Excel Step 17

Image titled Create a Drop Down List in Excel Step 17

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4b/Create-a-Drop-Down-List-in-Excel-Step-17-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-17-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4b/ Create-a-Drop-Down-List-in-Excel-Step-17-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-17-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select the error style in the Style drop-down box. You can choose from Stop, Warning and Information here.

  • The Stop option will display a pop-up with an error message, which prevents the user from entering data that is not in the drop-down list.
  • The Warning and Information options do not prevent the user from entering invalid data, but will display an error message with a yellow ” ! ” or a blue ” i “.
Image titled Create a Drop Down List in Excel Step 18

Image titled Create a Drop Down List in Excel Step 18

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/fc/Create-a-Drop-Down-List-in-Excel-Step-18-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-18-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/fc/ Create-a-Drop-Down-List-in-Excel-Step-18-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-18-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter a custom “Title” and “Error message ” text (optional) . A custom title and error message will pop up when invalid data is entered in the drop-down box.

  • You can leave these fields blank. The default title and error message will then be the generic Microsoft Excel error template.
  • The default error template is titled “Microsoft Excel” and it says “The value you entered is not valid. A user has restricted values that can be entered into this cell”. limited values can be entered in this box). [1]
READ More:   How to Calculate Distance
  • Image titled Create a Drop Down List in Excel Step 19

    Image titled Create a Drop Down List in Excel Step 19

    {“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b6/Create-a-Drop-Down-List-in-Excel-Step-19-Version-3.jpg/v4- 728px-Create-a-Drop-Down-List-in-Excel-Step-19-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b6/ Create-a-Drop-Down-List-in-Excel-Step-19-Version-3.jpg/v4-728px-Create-a-Drop-Down-List-in-Excel-Step-19-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
    Click the OK button in the “Data Validation” dialog box that pops up. A drop-down list will be created and inserted into the cell you selected.

    • Advice

    • After creating the drop-down list, open the drop-down list to make sure that all the items that you have entered are displayed properly. In some cases, you may need to expand the cell to show all the items.
    • When entering items for the list, you need to enter the order in which you want the data to appear in the drop-down menu. For example, you can enter data in alphabetical order to make it easier for users to find items or values.

    Warning

    • You won’t be able to access the “Data Validation” menu if the sheet is protected or shared. In this case, you need to remove the protection or unshare the document, then try accessing the Data Validation menu again. [2]

    This wikiHow teaches you how to use a computer to create drop-down lists on a Microsoft Excel worksheet. This feature allows you to create a list of items to select from, and insert a drop-down selector into any empty cell on the worksheet. The drop-down box feature is only available on the desktop version of Excel.

    Thank you for reading this post How to Create a Drop-Down List in Excel at Tnhelearning.edu.vn You can comment, see more related articles below and hope to help you with interesting information.

    Related Search:

    Related Posts

    How to Create Curved Text in Photoshop
    How to fall asleep faster
    How to Install FBReader to Read eBooks