Creating a drop-down list in Excel. Creating a Dropdown List

IN Microsoft program Excel has the ability to create a drop-down list - that is, make one cell contain several values ​​that the user can switch between. This is very handy tool for working with tables, in particular when it comes to checking entered data. Therefore, if you do not know how to make a drop-down list in Excel, then the instructions given in the article will help you.

First, let's look at the simplest way that will help you make a drop-down list. To implement it, do everything as in the instructions below:

Step 1. Go to the tab "Data", which is located on the top panel, then in the block "Working with data" select the data verification tool (the screenshot shows what icon it is represented by).

Step 2. Now open the very first tab "Options" and install "List" in the data type list.

Step 3. Now in the data entry field "Source" you can specify the values ​​that will make up the dropdown list. You can do this in the following ways:

Note! In order not to enter a range of cells manually, just left-click to select the first cell with a value, put a colon and select the last cell with a value with the mouse.

After you use any of the methods and specify the value range name, you will get the result as in the picture below.

Note! There is another way to specify a value in the source - write the name of the range in the input field. This method is the fastest, but before you use it, you need to create a named range. We'll talk about how to do this later.

Video - Creating Dropdown Lists in Excel

Dropdown list with data substitution

If your table is subject to constant changes and you don't want it to affect the contents of your dropdown list, then the following method will help you. Use it, and then editing cell data will be automatically displayed in the drop-down list. To do this, do the following:

1. Select the range for the list with the left mouse button (in this example it will be a list of trees), then open the tab "Home" and select menu "Format as table".

   

2. After this, the style selection menu will open. Style doesn’t play any role other than visual, so choose any one to suit your taste.

   

3. Next, a confirmation window will appear, the purpose of which is to verify that the entered range is correct. It is important to check the box next to "Table with headers", since the presence of a title in this case plays a key role.

   

4. After completing the procedures, you will receive the following type of range.

   

5. Now left-click the cell in which the drop-down list will be located and go to the tab "Data"(the previous method tells you how to do this).

   

6. In the input field "Source" you need to enter a function with the syntax “=INDIRECT(“TableName[Header]”)”. The screenshot shows a more specific example.

   

So, the list is ready. It will look like this.

Note! This method has a fundamental difference from the first - a ready-made one is used as a list, and not a range of values. This means that any changes in the table will be reflected in the drop-down list.

Let's test this. First, let's add a new "Christmas tree" cell to our new formatted table. As you can see, the same value has been added to the list.

If you delete a cell (in this case we deleted “birch”), this will also be reflected in the contents of the drop-down list.

Note! In this method we dealt with the so-called “smart table”. It is easily extensible, and this property is useful for many manipulations with Excel tables, including creating a drop-down list.

Dependent Dropdown Lists

To create dependent dropdown lists, follow these step-by-step instructions:

1. First, you need to create a named range. Go to the tab "Formulas", then select "Name Manager" And "Create".

   

   

2. Enter a name and range, then click "OK". If you previously selected the required cells with the mouse, the range will be specified automatically. Also note that the range name must be unique and cannot contain punctuation with spaces.

   

3. Using the same technique, make as many named ranges as there are logical dependencies you want to create. In this example, these are two more ranges: "Shrubs" And "Herbs".

   

4. Open the tab "Data"(in the first method the path to it is indicated) and indicate the names of the named ranges in the source, as shown in the screenshot.

   

   

5. Now you need to create an additional dropdown using the same design. This list will reflect those words that match the title. For example, if you selected “Tree”, then it will be “birch”, “linden”, “maple” and so on. To do this, repeat the above steps, but in the input field "Source" enter function "=INDIRECT(E1)" . In this case "E1"– this is the address of the cell with the name of the first range. Using the same method, you can create as many interconnected lists as you need.

   

   

Now you know several ways to create a drop-down menu in . This may be useful to you in a number of cases, and especially in data verification. Choice the right way should be done depending on what type of table you are working with. If this is a “one-time” table, then the first method is suitable - it is fast and easy. If the table requires constant changes, then it would be best to use a method that includes creating “smart tables” and named ranges.

Video - Related Dropdown Lists: Easy and Fast

A drop-down list in a cell allows the user to select only specified values ​​for entry. This is especially useful when working with files structured like a database, where entering an inappropriate value into a field can lead to undesired results.

So, to create a drop-down list you need:

1. Create a list of values ​​that will be provided to the user to choose from (in our example this is a range M1:M3), then select the cell in which the drop-down list will be (in our example this is the cell K1), then go to the " tab Data", group " Working with data", button " Data verification"

2. Select " Data type" -"List" and indicate the range of the list

3. If you want to prompt the user about his actions, then go to the " tab Message to be entered" and fill in the title and text of the message

which will appear when you select a cell with a drop-down list

4. You can also optionally create a message that will appear when you try to enter incorrect data

If you do not do steps 3 and 4, then data verification will work, but when the cell is activated, a message to the user about his intended actions will not appear, and instead of an error message with your text, a standard message will appear.

5. If the list of values ​​is on another sheet, then you will not be able to create a drop-down list using the above described method (up to Excel 2010). To do this, you will need to give the list a name. This can be done in several ways. First: select the list and right-click, in the context menu select " Assign a name"

For Excel versions below 2007, the same steps look like this:

Second: use Name Manager(Excel versions above 2003 - tab " Formulas" - group " Specific names"), which in any version of Excel is called by a keyboard shortcut Ctrl+F3.
Whatever method you choose, in the end you will have to enter a name (I named the range with a list list) and the address of the range itself (in our example this is "2"!$A$1:$A$3)

6. Now in the cell with the drop-down list, enter the name of the range in the "Source" field

7. Ready!

To complete the picture, I’ll add that the list of values ​​can be entered directly into the data check, without resorting to putting the values ​​on a sheet (this will also allow you to work with the list on any sheet). This is done like this:

That is, manually, through ; (semicolon) enter the list in the field " Source", in the order in which we want to see it (values ​​entered from left to right will be displayed in the cell from top to bottom).

With all its advantages, the drop-down list created in the manner described above has one, but very “bold” disadvantage: data verification only works when you directly enter values ​​from the keyboard. If you try to paste into a cell with data verification values ​​from the clipboard, i.e. copied previously in any way, then you will succeed. Moreover, the pasted value from the buffer WILL REMOVE DATA CHECKING AND DROPPING LIST FROM THE CELL into which the previously copied value was pasted. Avoid it regular means Excel is not possible.

If you need to click on one of the cells in a table Excel document, a list with possible values ​​was revealed, then you have come to the right place. In this article we will tell you about the most common and popular ways to do this. It doesn't take long. You don't need any special knowledge or skills. Only desire, attentiveness and strictly follow the written instructions. So, let's go!

Method 1. Standard.

First you need to set the range of values ​​that you want to see in your drop-down list. For example, let's talk about the cell "program". Let's create a list that should fall out of the cell.

Enter values ​​for the drop-down list

If you have Excel 2003, then you need to follow these steps. Stand on the cell that you want to make as a drop-down list, select the menu Data – Verification.

Selecting the future list cell

In Excel 2007 and higher, this window is called up through the “ Data» -> « Data verification».

List in a cell in MS Excell 2010

You will see a verification dialog box where you need to enter range of values.

Specify the range of cells with list values

We set a specific type of input values, in our case we consider the element "List".

To specify the values ​​of the drop-down list, there is a specific field - "source". Here you specify the range of cells from which the values ​​for the drop-down list will be taken. This is done by clicking on the icon at the end of the line. Next, select a range of cells and press Enter.

This is the final result.

Ready cell with dropdown list

In order to in the field " Source" Do not constantly set a range of values. You can combine these values ​​into one category, assign it a name and write this name in this column.

We indicate a specific list of values ​​that should appear. Let's go and perform the following steps.

1. Step 1 – select menu – "Insert";
2. Step 2 - go to the menu "Name";
3. Step 3 - open the dialog box "Assign".

Create a constant with list values

If you have the English version then like this

1. Insert;
2. Name;
3. Define.

If you are working with the seventh office version or later of Excel 2007. Then the tab will help you "Formulas" - "Name Manager" (Namemanager), and select create. The choice of name is not limited in any way. You can write, for example "Review".

Creating a value range name in Excel 2010

Specify the name of the created range

Pay attention to this moment. The data source can be any named data range, for example, a price list.

In this case, when new items are added to the price catalog, they are automatically displayed in the drop-down menu. Also, one important point for such lists is the creation of related drop-down elements. In such cases. When the content changes in one element, the structure automatically changes and updates in the other.

Method 2 – Control element.

This method considers the option of adding a new object and linking it to a specific range in Excel. What steps need to be taken:

If you have an Excel version of 2007 or later, then select the Developer menu. If the version is early then View - Toolbars - Forms.

Hello everyone, dear friends and guests of my blog. And again I am with you, Dmitry Kostin, and today I would like to tell you more about Excel, or rather about one wonderful feature that I now always use. Have you encountered the situation? when you fill out a table and in some column you need to constantly enter one of several values. Uhhh. Let me tell you better with an example.

Let’s say, when I created a computer equipment accounting table (a long time ago) at my work, in order to make the whole work process more convenient and faster, I made a drop-down list in certain columns and inserted certain values ​​there. And when I filled out the column " operating system“(But it’s not the same on all computers), then I entered several values ​​(7, 8, 8.1, 10), and then simply selected it all with one click of the mouse button.

And thus, you no longer need to type the version of Windows into each cell, or copy from one cell and paste into another. In general, I won’t bore you, let’s get started. Let me show you how to create a dropdown list in excel using data from another sheet. To do this, let's create some kind of table to which we can apply this. I will do this in the 2013 version, but the process is identical for other versions, so don't worry.

Preparation

Basic steps

Now work with graphs in exactly the same way "Specialist's name" And "Result of elimination", then return to the main sheet again and start working fully with the table. You will see for yourself how cool and convenient it is when you can select data from available pre-prepared values. This makes routine filling of tables easier.

By the way, in such documents, for more convenient display, it is better. Then everything will be cool.

Well, I’m finishing my article for today. I hope that what you learned today will be useful to you when working in Excel. If you liked the article, then of course do not forget to subscribe to my blog updates. Well, I’ll be looking forward to seeing you again on the pages of my blog. Good luck and bye-bye!

Best regards, Dmitry Kostin

One of the most useful functions When entering data, it is possible to use a drop-down list. It allows you to select a value from a predefined list and allows you to enter only those data that meet your requirements. We will offer you several simple ways how to create dropdown lists in Excel. We will also look at more complex methods based on dynamic ranges and using data from other tables.

How can this be useful to us?

It often happens that you need to enter identical repeating values ​​in one of the columns of your table. For example, names of employees, names of products. What could happen? Of course, first of all there will be typing errors. The human factor has not been canceled. What does this threaten us with? For example, when we decide to count how many orders each manager completed, it turns out that there are more names than employees. Next, you will have to look for errors, correct them and repeat the calculation again.

And of course, typing the same words by hand all the time is just pointless work and a waste of time. This is where drop-down lists come in handy. When clicked, a list of predefined values ​​appears, of which only one needs to be specified.

The important thing is that you will now not enter, but select them using the mouse or keyboard. This significantly speeds up work and also guarantees protection against accidental errors. Checking what we have entered into the table is no longer necessary.

1 - The fastest way.

What's the easiest way to add a dropdown list? Just one right-click on the empty cell under the data column, then the command context menu"Choose from drop-down list". Or you can just stand in the right place and press the key combination Alt+down arrow. A sorted list of unique previously entered values ​​appears.
The method does not work if our cell and column with records are separated by at least one empty line or you want to enter something that has not yet been entered above. This is clearly visible in our example.

2 - Use the menu.

Let's look at a small example in which we need to constantly enter the same product names into the table. Write down in a column the data that we will use (for example, product names). In our example - in the range G2:G7.

Select a table cell (you can have several at once) in which you want to use input from a predetermined list. Next, in the main menu, select the Data – Validation tab. Next, click Data Type (Allow) and select the List option. Place the cursor in the Source field and enter into it the addresses with the reference values ​​of the elements - in our case G2:G7. It is also recommended to use absolute links here (press F4 to set them).

A bonus here is the ability to set a hint and an error message if you want to change the automatically inserted value manually. To do this, there are tabs Input Message and Error Alert.

You can also use a named range as a source.

For example, the range I2:I13, which contains the names of the months, can be given the name "months". The name can then be entered into the Source field.

In addition, both the source as a regular range of cells and the named range can be on other sheets in your workbook.

But you don't have to use ranges or links, but simply define possible options directly in the "Source" field. For example, write there -

Use a semicolon, comma, or other character that you have set as an element separator to separate values. (See Control Panel - Clock and Region - Formats - Advanced - Numbers.)

3 - Create a control element.

Let's insert a new object onto the sheet - the "Combo Box" control element and then bind it to the data on Excel sheet. We do:

1. Open the Developer tab. If it is not visible, then in Excel 2007 you need to click the Office button – Options – the Show Developer Tab on the Ribbon checkbox (Office Button – Options – Show Developer Tab in the Ribbon) or in versions 2010–2013, right-click on the ribbon, select the command Customize Ribbon and enable the display of the Developer tab using the checkbox.
2. Find the desired icon among the controls (see picture below).

After inserting the control onto the worksheet, right-click on it and select “Format Object” from the menu that appears. Next, we indicate the range of cells in which the valid values ​​for entry are recorded. In the "Link to cell" field we indicate exactly where to place the result. It is important to consider that this result will not be the value itself from the range we specified, but only its serial number.

But we don’t need this number, but the word corresponding to it. We use the INDEX function (INDEX in English). It allows you to find one of them in the list of values ​​according to its serial number. As arguments INDEX, specify the range of cells (F5:F11) and the address with the received serial number(F2).

We write the formula in F3 as shown in the figure:

INDEX(F5:F11,F2)

As in the previous method, links to other sheets and named ranges are possible here.

Please also note that here we are not tied to any specific place in the table. This list is convenient to use because it can be freely “dragged” with the mouse to any convenient place. To do this, on the "Developer" tab, you need to activate the design mode.

4 - ActiveX element

We proceed similarly to the previous method, but select the icon just below - from the “ActiveX Elements” section.

We define a list of acceptable values ​​(1). Please note that here you can select multiple columns for display at once. Then we select the address at which the desired position from the list will be inserted (2). We indicate the number of columns that will be used as source data (3), and the number of the column from which the selection will be made for insertion into the sheet (4). If you indicate column number 2, then not the last name, but the position will be inserted into A5. You can also specify the number of lines that will be displayed in the list. The default is 8. The rest can be scrolled with the mouse (5).

This method is more complicated than the previous one, but it immediately returns the value, not its number. Therefore, there is no need for an intermediate cell and its processing using INDEX. I think this list is much more convenient to use.

5 - List with autocomplete

Task: Create a list to which values ​​from a specified dynamic range will be automatically added. If changes are made to the range, the set of proposed values ​​will immediately change. No formulas or settings need to be adjusted here.

Here's what autocomplete might look like: simple example:

Method 1. Indicate a known large source.

The simplest and most uncomplicated trick. First, we follow the usual algorithm of actions: in the menu, select Data – Validation on the tab. From the Data Type list (Allow), select the List option. Place the cursor in the Source field. Let's reserve a set in the list with a large margin: for example, up to the 55th line, although we only have 7 occupied. Be sure to remember to check the "Ignore empty..." checkbox. Then your "reserve" of empty values ​​will not bother you.

Indeed the easiest way, but not very convenient. After all, the reserved place may run out...

Of course, you can also specify the entire column as the source:

But processing such a large number of cells can slow down the calculations somewhat.

Method 2: Use a named range.

A named range is different from regular topics that it has been given a specific name. It is much easier to work with it, since you do not need to enter a link, but simply indicate its name. Let's look at a small example.

Column A contains the names of the employees that we will enter. The list can be as long as you like. We need everyone new entry was included in the drop-down list without any additional action on our part.

Let us highlight the list of names at our disposal A2:A10. Then give it a name by filling out the “Name” field located to the left of the formula bar. Let's create a list of values ​​in C2. As a source for it we indicate the expression

The disadvantage of working with such a list is that new values ​​cannot simply be appended to the end of the used list - they will remain outside the named range. They will have to be inserted inside it using insert a blank line.

The list can also be sorted to make it easier to use.

The main inconvenience of using such a list is that the range we use is static. Its size cannot be changed automatically. Agree, this is not a very convenient and technologically advanced method. Too many manual steps.

Method 3. A “smart” table will help us.

Since 2007, an Excel table is no longer just a set of rows and columns. If you simply arrange the indicators as we are accustomed to tabular form, then it will not consider them a table. There is special formatting, after which the range begins to behave as a single whole, acquiring a number of interesting properties. In particular, it begins to track its own size, dynamically changing as the data is adjusted.

Any set of values ​​in a table can be transformed in this way. For example, A1:A8. Select them with the mouse. Then convert to table using menu Home - Format as Table. Indicate that the first line contains the column name. This will be the header of your table. Appearance can be anything: it's nothing more than external design and it doesn't affect anything else.

As mentioned above, a “smart” table is good for us because it dynamically changes its size when information is added to it. If you enter anything into the line below it, it will immediately attach it to itself. Thus, new values ​​can simply be added. For example, enter the word “coconut” in A9, and the table will immediately expand to 9 lines.

Hence, automatic update The set of information used in the list can be organized by using the contents of any column of the “smart” table.

All that remains is to designate it as the source. The problem is that the program, as a source in the list, does not understand an expression of the form

Table1[Column1]

and does not consider it a formula. Although in ordinary expressions on a sheet of your workbook this will work quite well. This construct denotes a reference to the first column. But for some reason it is ignored in the "Source" field.

To use the "smart table" as a source, we will have to use a little trick and use the INDIRECT function. This function converts a text variable into a regular link.

The formula will now look like this:

INDIRECT("Table5[Product]")

Table5 is the name automatically assigned to the "smart table". It may be different for you. On the Design menu tab, you can change the standard name to your own (but without spaces!). Using it, we can then access our table on any sheet of the book.

“Product” is the name of our first and only column, assigned by its heading.

Be sure to also enclose the entire expression in quotation marks to denote it as a text variable.

Now, if you add another fruit (for example, coconut) to A9, it will automatically appear in our list. It will be the same if we delete something. The problem of automatically increasing the drop-down list of values ​​has been solved.

We hope you can now use lists to enter frequently repeated data into a table without errors.

Here's some more useful information for you:

- One of the most useful data validation features is the ability to create a drop-down list that allows you to select a value from a predefined list. But once you start applying this to your spreadsheets...