How to make a list in one Excel cell. Create linked drop-down lists in Excel - the easiest way

Drop-down lists make entering data into spreadsheets easy and convenient. Just click the arrow and select an option. You can add drop-down lists to Excel cells containing options such as Yes and No, Men and Women, or any other custom list of options.

Add a dropdown list to a cell in Excel quite simple, but the process is not intuitive. Dropdown lists are created using the data validation feature. We're going to create a drop-down list with a set of age ranges to show you how it's done.

To begin, enter a list of age ranges in sequential cells by column or row. We entered our age ranges in cells A9 to A13 on the same worksheet as shown below. You can also add your list of options to another worksheet in the same workbook.

We'll now name our range of cells to make adding them to the dropdown list easier. To do this, select all the cells that contain drop-down list items, and then enter a name for the range of cells in the Name box above the grid. We named our range Age.

Now select the cell where you want to add the dropdown list and go to the Data tab.

Under the Data Tools tab Data Click the Data Validation button.

The Data Validation dialog box opens. On the Options tab, select List from the Data Type drop-down list.

We will now use the Name we assigned to the range of cells containing our dropdown list options. Enter =Age in the "Source" field (if you named the range of cells something else, replace "Age" with that name). Make sure the checkbox Ignore empty cells marked.

You can add a pop-up message that displays when a cell contained in a drop-down list is selected. To do this, click the tab Message to be entered in the Data Validation dialog box. Make sure the checkbox Show tooltip if cell is current installed. Enter a message in the Title and Message fields, and then click OK.

When the cell containing the dropdown list is selected, you will see a down arrow button to the right of the cell. If you have added an input message, it will appear below the cell. The down arrow button appears only when a cell is selected.

Click the down arrow button to display a list of options and select one.

If you decide that you want to remove the drop-down list from a cell, open the Data Validation dialog box as described above and click the Clear All button, which is available no matter which tab is selected in the dialog box.

The options in the Data Validation dialog box are reset to their default values. Click OK to remove the dropdown and restore the cell to its default.

If an option was selected, then when the drop-down list is deleted, the cell will be filled with the value of that option.

Follow this process to add dropdown lists to other cells as needed. You can use the same list for multiple dropdowns.

If you have many drop-down lists that you need to add to a worksheet, we recommend that you place the option lists on a separate Excel worksheet and hide the worksheet to prevent changes to the options.

Related Dropdown Lists

Download the file used in the video tutorial:

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; text-align:left;","textbgcss":"display:absolute; left:0px; ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

To make it easier for users to work with a worksheet, add drop-down lists to cells so they can select the item they want.

On a new worksheet, enter the data you want to appear in the drop-down list. It is desirable that the list items be contained in an Excel table. If this is not the case, you can quickly convert the list to a table by selecting any cell in the range and pressing CTRL+T.



Notes:

1. Select the cell on the worksheet where you want to place the drop-down list.

   On the ribbon, open the tab Data and press the button Data verification.

   Note: If the button Data verification unavailable, the sheet may be protected or shared. Unlock specific areas of a protected workbook or unblock general access to the sheet, and then repeat step 3.

   On the tab Options in the field Data type select item List.

   Click the field Source and highlight the list range. In the example, the data is on the Cities sheet in the range A2:A9. Note that the header row is not in the range because it is not one of the options available for selection.

   

   If you can leave the cell blank, select the checkbox Ignore empty cells.

   Check the box List of valid values

   Open the tab Message to be entered.

   
   

2. Open the tab Error message.

   
   

3. Don't know which option to select in the field View?

Working with a Dropdown List

Once you create your dropdown, make sure it works the way you want it to. For example, you might want to check whether you need to change the column widths and row heights to ensure that all records are displayed.

Downloading examples

We suggest downloading a sample book with several examples of data verification, similar to the example in this article. You can use them or create your own data verification scripts. Download Excel data validation examples

You can enter data faster and more accurately by limiting the values ​​in a cell to options from a drop-down list.

First, create a list of valid elements on the worksheet, and then sort or arrange them in the desired order. These elements can later serve as a source for a drop-down list of data. If the list is small, you can easily reference it and enter items directly into the data checker.

The list element is familiar to us from forms on websites. It is convenient to select ready-made values. For example, no one enters the month manually; it is taken from such a list. You can fill out a drop-down list in Excel using various tools. In this article we will look at each of them.

How to make a dropdown list in Excel

How to make a drop-down list in Excel 2010 or 2016 using one command on the toolbar? On the “Data” tab, in the “Working with Data” section, find the “Data Validation” button. Click on it and select the first item.

A window will open. In the “Options” tab, in the “Data type” drop-down section, select “List”.


A line will appear at the bottom to indicate sources.


You can provide information in different ways.

First let's assign a name. To do this, create such a table on any sheet.

Select it and right-click. Click on the “Assign a name” command.

Enter your name in the line above.

Call the “Data Check” window and in the “Source” field, specify the name by placing the “=” sign in front of it.


In any of the three cases you will see the desired element. Selecting a value from the dropdown Excel list happens with the mouse. Click on it and a list of specified data will appear.

You learned how to create a dropdown list in an Excel cell. But more can be done.

Dynamic Excel Data Substitution

If you add some value to the range of data that is inserted into the list, then no changes will occur in it until new addresses are manually specified. To link a range and an active element, you need to format the first one as a table. Create an array like this.

Select it and on the “Home” tab, select any table style.


Be sure to check the box below.

You will receive this design.

Create an active element as described above. For the source, enter the formula

To find out the table name, go to the Design tab and look at it. You can change the name to any other.


The INDIRECT function creates a reference to a cell or range. Now your element in the cell is bound to the data array.

Let's try to increase the number of cities.


The reverse procedure is to substitute data from a drop-down list into Excel spreadsheet, works very simply. In the cell where you want to insert the selected value from the table, enter the formula:

Cell_address

For example, if the list of data is in cell D1, then in the cell where the selected results will be displayed, enter the formula

How to remove (delete) a drop-down list in Excel

Open the drop-down list settings window and select "Any value" in the "Data type" section.

The unnecessary element will disappear.

Dependent Items

Sometimes in Excel there is a need to create several lists when one depends on the other. For example, each city has several addresses. When selecting the first one, we should receive only the addresses of the selected locality.


In this case, give each column a name. Select without the first cell (title) and right-click. Select "Name".

This will be the name of the city.


When naming St. Petersburg and Nizhny Novgorod, you will receive an error, since the name cannot contain spaces, underscores, special characters etc.


Therefore, we will rename these cities with an underscore.


Create the first element in cell A9 in the usual way.


And in the second we write the formula:

INDIRECT(A9)

You will first see an error message. Agree.

The problem is that there is no selected value. As soon as a city is selected in the first list, the second one will work.

How to Set Up Dependent Dropdown Lists in Excel with Search

Can be used dynamic range data for the second element. This is more convenient if the number of addresses grows.
Let's create a drop-down list of cities. The named range is highlighted in orange.


For the second list you need to enter the formula:

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

MATCH returns the number of the cell with the city selected in the first list (E6) in the specified area SA:$A.
COUNTIF counts the number of matches in a range with the value in the specified cell (E6).

We got linked dropdown lists in Excel with a match condition and a range search for it.

Multi-select

Often we need to get multiple values ​​from a data set. You can display them in different cells, or can be combined into one. In any case, a macro is needed.
Right-click on the sheet label at the bottom and select View Code.


The developer window will open. The following algorithm must be inserted into it.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Please note that in the line

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

You should enter the address of the cell with the list. For us it will be E7.

Return to Excel sheet and create a list in cell E7.

When selected, the values ​​will appear below it.

The following code will allow you to accumulate values ​​in a cell.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval)<>0 And oldval<>newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

As soon as you move the pointer to another cell, you will see a list of selected cities. To read this article.


We told you how to add and change a dropdown list in Excel cell. We hope this information helps you.

Have a great day!

In this lesson I will tell you how to make a drop-down list in Excel. Basically, it is created for one purpose - to limit the possibility of entering data to a certain list. For example, when there is a special cell in the table in which you mark which department this or that employee belongs to. This list is always the same. It is much more convenient to select a department from the list than to enter it manually each time or copy it from other cells.

To create a drop-down list in Excel we will use the option Data verification. It is located in the tab Data.

After clicking on the icon, a new window will open Validation of entered values. Here it is necessary in the dropdown list Data type choose List, and what to enter in the field that appears Source We'll figure it out now.

You can create a list in at least 3 ways.

Specifying elements directly in the source

This method is very simple and suitable for small lists.

• We stand on the cell where we need to create a list;
• We enter Check data;
• In the field Source We list the elements of the list, which we separate with semicolons.

After this, press the key OK and we get a ready-made drop-down list.

This cell can be safely used throughout the table. Just copy it and paste it in the right place.

List items on the same sheet

This method allows you to already use ready list departments that are on the sheet.

• We stand on the cell;
• We enter Check data;
• Let's stand on the field Source and use the mouse to select the range that should be a list. The range should be located on the same sheet!

Now you can simply copy and paste this cell into all rows of the table.

Using a Named Range

This method is suitable if the list elements are placed on another sheet.

• Create a list of departments on another sheet;
• Create a Named Range. Select a range with list elements. To the left of the formula bar is now the cell from which you started the selection. In my case - A2;
• Instead of A2 we give Name our range. For example, let's call it Departments. After this, press the key Enter, Congratulations, we have created Named range.

We return back to the original sheet. We stand on the cell where we will create the list. Go to "Data -> Check data". In the field Source, through the sign = enter the name of the range created at the previous stage Departments.

The result is a list whose elements are on another sheet.

Congratulations, now you know how to make a drop-down list in Excel. If you have any questions, I will gladly try to answer them.

In this lesson I will tell you what it is special insert in Excel and how to use it.

In this lesson I will tell you how to put a plus sign or zero before a number at the beginning of a cell in Excel. Let's imagine a situation where you need to enter a phone number in a cell in the format "+7 987...". IN normal condition Excel will simply remove the plus sign.