Recommendations on how to quickly make a drop-down list in MS Excel. Creating a drop-down list in a cell Creating a drop-down list in Excel

The easiest way to complete this task is as follows. By pressing right button by cell under the data column call context menu. The field of interest here is Select from drop-down list. The same can be done by pressing the key combination Alt+Down Arrow.

However, this method will not work if you want to create a list in another cell that is not in the range and in more than one before or after. The following method will do this.

Standard method

Required select a range of cells, from which it will be created drop-down list, then Insert – Name – Assign(Excel 2003). In a newer version (2007, 2010, 2013, 2016), go to the tab Formulas, where in the section Specific names find the button Name Manager.

Press the button Create, enter a name, you can use any name, after which OK.

Select cells(or several) where you want to insert a drop-down list of required fields. From the menu, select Data – Data type – List. In field Source enter the previously created name, or you can simply specify the range, which will be equivalent.

Now the resulting cell can be copy anywhere on the sheet, it will contain a list of necessary table elements. You can also stretch it to get a range with drop-down lists.

An interesting point is that when the data in the range changes, the list based on it will also change, that is, it will dynamic.

Using the controls

The method is based on insert control called " combo box", which will represent a range of data.

Select a tab Developer(for Excel 2007/2010), in other versions you will need to activate this tab on the ribbon in parameters – Customize your feed.

Go to this tab - click the button Insert. In the controls select Combo box(not ActiveX) and click on the icon. Draw rectangle.

Right click on it - Object Format.

By linking to a cell, select the field where you want to place the serial number of the element in the list. Then click OK.

Using ActiveX Controls

Everything, as in the previous one, just select Combo box(ActiveX).

The drop-down list in Excel is a rather convenient function that will help you create more complex documents and make the work easier for the user to visually perceive.

A few of the most common types of drop-down lists that you can create in Excel are:

Make a list in Excel with multi-select

You can create a list in a program cell using the built-in tools of the control panel.

Let's take a closer look at all the main and most common types, and the process of creating them in practice.

Advice! The classic pop-up list of the program has a multi-select function, that is, the user, by clicking on the corresponding group, can select the appropriate option for it.

Multi-selection is necessary when you need to set the value of several document cells using one list.

Multi-select appearance to fill multiple cells at once

• Select cells. If you look at the figure, you need to select starting from C2 and ending with C5;
• Find the “Data” tab, which is located on the main toolbar in the program window. Then click on the data verification key as shown in the figure below;

Data checking

• In the window that opens, open the very first tab and select the element, as shown in the figure. Thus, a list will be created in these places. In the text field, specify the range of cells that will be filled in each time you select items.

Filling in information to create a drop-down list with multiple selection of cell values

EtcFilling number:

This way you will create a classic horizontal list with multi-select functionality.

However, in order for it to be filled in automatically, you need to create a macro with the following source code, which is shown in the figure.

Program code for creating a macro

• Excel Hot Keys - The Most Essential Options
• EXCEL formulas with examples - Instructions for use
• How to build a graph in Excel - Instructions

Create a list in Excel with content

A standard list with content will allow you to automate the process of entering information. When you click on it, a selection of its possible values ​​will appear.

The user can only select the required value to fill.

Custom list with content

The easiest way to create such a list is to use the program’s “smart tables”.

With their help, you can easily and quickly format the types of lists you need with content:

• Select the required cells and click on the “Format as table” button in the main tab;

Example formatting and key layout:

Formatting process

• Using the built-in designer, you can change table columns and their purpose.

Formatting a list with content using “smart tables”

Create a drop-down list in a cell (program version 2010)

You can also create a list in a worksheet cell.

An example is shown in the figure below:

Example in a sheet cell

To create one, follow the instructions:

Filling and checking entered values ​​during the creation of a list with filling a worksheet cell

You may also be interested in:

• Rounding in Excel - Step-by-step instructions
• Excel table - Creation and configuration

Results

The article discussed the main types of drop-down lists and how to create them. Remember that the process of creating them is identical in the following versions of the program: 2007, 2010, 2013.

Drop-down lists will help you not only make the entire document work faster, but also make it adapted for working with large projects.

Thematic videos for the article:

Creating Dropdown Lists in Excel

4 ways to create a dropdown list in an Excel sheet.

How to Make a Dropdown List in Excel

How to make a drop-down list in Excel How to add a pop-up list in Excel How to create a drop-down list in Excel

One of the most useful features when entering data is the ability 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 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 can 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 context menu command “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 into the sheet - a "Combo Box" control element and then bind it to the data on the 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 in 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 the INDEX arguments, specify the range of cells (F5:F11) and the address with the resulting sequence 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 inserting 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 in a 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 with a large margin in the list: 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 differs from a regular range in that it is given a specific name. It is much easier to work with, 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 want each new entry to be included in the dropdown 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 in the usual 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. The appearance can be anything: it is nothing more than an external design and does not 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.

Consequently, automatic updating of the set of used information in the list can be organized if you use 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...

Drop-down lists in Microsoft Excel are indispensable when creating large tables and working with databases. What exactly is the convenience of this tool?

Why are such lists needed?

If, when filling out a table, some data is periodically repeated, there is no need to manually enter a constant value each time - for example, product name, month, employee's full name. It is enough to pin a repeating parameter in the list once. Often, some list cells are protected from entering extraneous values, which reduces the likelihood of making an error in operation. A table designed this way looks neat.

In the article I will tell you how to make a drop-down list in a cell in Excel e and how to work with it.

Formation of a drop-down list

One of the common examples of using drop-down lists is online stores, in which all products are divided into categories. This structure makes it easier for users to search the site. Let's take a closer look:

All products listed in the table must be classified under the “Clothing” category. To create a drop-down list for this list, you will need to do the following:

• Select any cell in which the list will be created.
• Go to the “Data” tab, in the “Data Check” section.
• In the window that opens, select the “Parameters” tab, and in the “Data type” list, select the “List” option.
• In the line that appears, you must indicate all available list names. This can be done in two ways: select a range of data in the table with the mouse (in the example, cells A1-A7) or enter the names manually, separated by a semicolon.
• Select all cells with the desired values, and right-click and select “Assign a name” from the context menu.
• In the “Name” line, indicate the name of the list – in this case, “Clothing”.
• Select the cell in which the list was created and enter the created name in the “Source” line with the “=” sign at the beginning.

How to add values ​​to a list

Sometimes it becomes necessary to supplement an existing list. In the drop-down list, all new items are displayed automatically when added. However, to associate a range of cells with a newly added element, the list must be formatted as a table. To do this, you need to select a range of values, find the “Format as table” item on the “Home” tab and select any style you like, for example:

When filling out a table in Excel, the data is often repeated. For example, you write the name of the product or the full name of the employee. Today we will talk about how to make a drop-down list in Excel so that you don’t constantly enter the same thing, but simply select the desired value.

There is the easiest way to cope with the task. You right-click on the cell under the data column to open the context menu. Here we look for the “Select from drop-down list” item. The same action can be easily performed using the combination Alt + down arrow.

But this method will not work if you need to make such an object in Excel in another cell and in 2-3, etc. before and after. If necessary, use the following option.

Traditional way

Select the area of ​​the cells themselves from which you will create a drop-down list, then proceed:

Insert/Name/Assign (Excel 2003)

In the latest versions (2007, 2010, 2013, 2016) go to:

Formulas/Defined Names/Name Manager/New

Enter any name and tap OK.

Then select the cells (or several) where you plan to insert the list of specified fields. Go to the menu:

Data/Data Type/List

In the “Source” section, enter the previously written name, simply mark the range. You can copy the resulting cell to any place; it will already have a menu of specified table elements.

Additionally, you can stretch it to create a range. By the way, if the information in it changes, the list information will also change; it is dynamic.

How to make a drop-down list in Excel: using management methods

When you use this option, you insert a control that represents the data range itself. For this:

• firstly, look for the “Developer” section (Excel 2007/2010), in other editions you activate it yourself through the “Customize Ribbon” parameters;

• secondly, go to the section, click “Insert”;
• thirdly, select “Field...” and click on the icon;
• draw a rectangle;

• right-click on it and click Format Object;

• look for “Form…”, select the required boundaries;
• mark the area where you want to set the serial number of the element in the list;
• Click OK.

How to create a drop-down menu in Excel: using ActiveX elements

The steps are similar to those described above, but we are looking for “Field with ActiveX”.

The main differences here are that the ActiveX special element can be in 2 variants - debugging mode, which allows you to change parameters, and input mode, which only allows you to select information from it. You can change the mode using the key Design Mode In chapter Developer. Using this method, you can customize the color, font, and perform quick searches.

Additional features

Excel allows you to create a linked drop-down list. That is, when you select a value, you can select the parameters it needs in another column. For example, you choose a product, and you need to mark the unit of measurement, for example, gram, kilogram.

First of all, you should make a sign with the lists themselves, and then separate windows with the names of the products.

For the second, we activate the information check window, but in the “Source” item we write “=INDIRECT” and the address of the 1st cell. Everything worked out.

After this, so that the windows below are supplemented with the same properties, select the upper section and drag everything down with the mouse pressed. Ready.

How to make a drop-down list in Excel? As you can see, this is easy to do using any of the above methods. Just choose the most optimal one for yourself. And the choice will depend on the purpose of creation, purpose, area of ​​use, amount of information and other things.