Excel drop down menu. How to make a complex dropdown list in excel? List items on the same sheet

There are several ways to create a dropdown list. The choice of one depends on the structure of the data you have.

The first way to create a two-level list

The first method is based on creating a “smart” table, the header of which contains the values ​​of the first drop-down list (group), and the table rows correspond to the values ​​of the second drop-down list (subgroup). The values ​​of the subgroup elements should be located in the corresponding group column, as in the figure below.

Now let's start creating the first drop-down list of the group (in my case, a list of countries):

  1. Select the cell into which you will insert the drop-down list;
  2. Go to the ribbon tab Data;
  3. Choosing a team Data verification;
  4. Select a value from the drop-down list List;
  5. In the field Source indicate the following formula =INDIRECT("Table1[#Headers]").
Formula INDIRECT returns a reference to the range of smart table headers. The advantage of using such a table is that as you add columns, the dropdown list will automatically expand.

It remains to create a second dependent drop-down list - a list of subgroups.

We boldly repeat the first 4 points described above. Source in the window Data verification for the second drop-down list the formula will be =INDIRECT("Table1["&F2&"]"). Cell F2 V in this case- the value of the first drop-down list.

You can also use a regular dumb table, but in this case you will have to manually change the header and row ranges. In the example considered, this happens automatically.

The second way to create a two-level list

The second method is convenient to use when the drop-down list data is written in two columns. The first contains the name of the group, and the second contains the name of the subgroup.

IMPORTANT! Before creation dependent list By subgroups, you need to sort the source table by the first column (the column with the group); then it will be clear why this is being done.

To create a group dropdown, we need an additional column containing the unique group values ​​from the source table. To create this list, use the Remove Duplicates feature or use the Unique command from the VBA-Excel add-in.

Now let's create a drop-down list of groups. To do this, follow the first 4 points from the first method of creating a two-level list. As Source indicate range unique values groups. Everything is standard here.

Recommendation: It is convenient to specify a named range as a source. To create it, open Name Manager from tab Formulas and give a name to the range with unique values.

Now the most the hard part- indicate in Source a dynamic link to a range with the values ​​of the second drop-down list (list of subgroups). We will solve it using the function OFFSET(link, row_offset, column_offset, [height], [width]), which returns a reference to a range that is a specified number of rows and columns away from a cell or range of cells.

  • Link in our case - $A$1- upper left corner of the source table;
  • Offset_by_rows - MATCH(F3,$A$1:$A$67,0)-1- line number with the value of the desired group (in my case, country cell F3) minus one;
  • Offset_by_columns - 1 - since we need a column with subgroups (cities);
  • [Height] - COUNTIF($A$1:$A$67,F3)- number of subgroups in the desired group (number of cities in the country F3);
  • [Width] - 1 - since this is the width of our column with subgroups.

In this lesson I will tell you how to make a drop-down list in Excel. Basically, it is created with 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 use a ready-made list of departments that is 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...". Normally, Excel will simply remove this plus sign.

In this article we will look at how to create drop down list in excel 2007. Let's take an example when we need to select specified values ​​from 1 to 5 from a drop-down list in a cell. We create the list itself and select it with the left mouse button. Click right click mouse in the selected area and select Range name.

In the Name field that opens, enter the name of our list, let's call it Meaning. In the Area field, select from the drop-down list Book(or the Sheet number to which you want to apply the list). Click OK.

Also, to set the name of the list, you should select the existing list and enter the cell name in the cell name field and assign the list name. Look at the picture below.

The list has been created. Now we apply this list to the cell.

Select the cell to which the list will be attached. In the ribbon, go to the Data tab and in the group Working with data Click on the Data Check button. In the next window, in the Parameters tab, in the Data type field, select the item from the list List.

In the Source field, put the equal sign and write the name that you assigned to the list. The list is called "Value". Accordingly, the entry should be as shown in the figure below.

Click OK, and now you have a cell with a drop-down list. See what it looks like in the picture below. When we click on a cell, we see a square with a downward triangle to the right of it. Click on the square and thereby open the list.

You can create without assigning a list name. That is:

  1. create the list itself;
  2. go to the Data tab in the Ribbon and click the button Data verification;
  3. in the window that opens, in the Parameters tab, in the Data type field, select List;
  4. in the Source field, left-click to activate of this field. Next, select the cells forming the list;
  5. Click OK.

All, dropdown list in excel 2007 ready.

Working in Excel with tables and the data in them is structured in such a way that the user can comfortably process and analyze them. For this purpose, various tools are built into the program. Their use requires the user to have some knowledge, but with them Excel turns into a powerful analysis tool. The Office developer tries to simplify most of its programs so that anyone can fully use them.

A spreadsheet can be turned into a data analysis tool

One of the most common reasons for creating a pop-up list is to use data from a cell in Excel formula. It is easier to provide a finite number of options, so it would be advisable to give a choice of several values ​​so that the user can choose from a ready-made set. In addition, there may be another reason: a predetermined document style. For example, for reports or other official documents. The same department name can be written in different ways. If this document will later be processed by a machine, it would be more correct to use uniform style filling, rather than setting it the task of recognition, for example, by keywords. This may introduce an element of inaccuracy into her work.

Technical side of the issue

Before making a drop-down list in Excel, create the necessary options on the sheet in a range of cells. Make sure that there are no empty lines in this list, otherwise Excel will not be able to create desired object on a sheet. The entered values ​​in the lines can be sorted alphabetically. To do this, find the data tab in the Settings Ribbon and click on “Sorting”. When you are finished working with the data, select the desired range. It should not contain empty lines, this is important! The program will not be able to create a list with an empty element inside it because empty string will not be accepted as selection data. In this case, you can create a list of data on another sheet, not only on the one where the input field will be located. Let's say you don't want them to be editable by other users. Then it makes sense to place them on a hidden sheet.

After you have generated a list of data, select the cell in which the drop-down list should be created. In Lenta Excel settings On the “Data” tab, find the “Check” button. When you click on it, a dialog box will open. In it you need to select the “Allow” item and set its value to “List”. So in this cell the input method will be changed to select from available options. But these options have not yet been determined. In order to add them to the created object, enter the data range in the “Source” field. In order not to type them manually, click on the enter icon on the right side of the field, then the window will collapse, and you can select the desired cells using the usual mouse selection. Once you let go left button mouse, the window will open again. All you have to do is click OK, and a triangle, a drop-down list icon, will appear in the selected cell. By clicking on it, you will receive a list of the options you entered earlier. After this, if the options are located at separate sheet, you can hide it by right-clicking on its name at the bottom of the working window and selecting the item of the same name in the context menu.

When you select this cell, several buttons will appear next to it. To make the input task easier for the user, you can use this button to specify the cell name. You can do the same above; next to the formula entry window there is a corresponding item. This way the list will be clearer, because the user will not have to guess from its values ​​what exactly needs to be selected here. In addition, in the dialog box you can enter a hint message that will be displayed when you hover the cursor over a cell. If you do not want the cell to remain empty, uncheck “Ignore empty values.” Checkbox "List acceptable values" must be installed in any case.

Delete a list

When a drop-down list is no longer needed, it can be removed from the document. To do this, select the cell Excel sheet containing it, and go to the Settings Ribbon to the “Data” - “Data Check” tab. There, in the options tab, click on the “Clear all” button. The object will be deleted, but the data range will remain unchanged, that is, the values ​​will not be deleted.

If you are filling out a table in Excel, and the data in the column may sometimes be repeated, for example, the name of a product, or the name of an employee, then do not enter required parameter each time, it is simpler and easier to create a drop-down list once and select a value from it.

In this article we will look at how to make drop-down lists various types in an Excel table.

Create a simple dropdown list

To do this, in cells A1:A7 we enter the data that will be displayed in the list. Now let's select the cell in which we will create a drop-down list - B2.

Go to the “Data” tab and click on the “Data Check” button.

On the “Parameters” tab, in the “Data type” field, select “List”. You can enter values ​​in the Source field in different ways:

1 – enter values ​​for the list manually, separated by semicolons;

2 – indicate the range of cells into which the data for the drop-down list is entered;

3 – select the cells with names, right-click on them and select “Assign a name” from the menu.

Select cell B2 and put “=” in the “Source” field, then write the created name.

So we created a simple dropdown list in Excel.

If you have a heading for a column, and you need to fill each row with values, then select not one cell, but a range of cells - B2:B9. Then you can select from the drop-down list desired value in each cell.

Adding values ​​to a drop-down list - dynamic list

In this case, we will add values ​​to the required range, and they will be automatically added to the drop-down list.

Select the range of cells – D1:D8, then on the “Home” tab, click “Format as table” and select any style.

Confirm the location of the data and check the “Table with headers” box.

At the top we write the title of the table - “Employees”, and fill it with data.

Select the cell in which the drop-down list will be and click on the “Data Check” button. In the next window, in the “Source” field, write the following: =INDIRECT(“Table1”). I have one table on a sheet, so I write “Table1”, if there is a second one – “Table2”, and so on.

Now let's add a new employee name to our list: Ira. It appeared in the drop-down list. If we delete any name from the table, it will also be deleted from the list.

Dropdown list with values ​​from another sheet

If the table with drop-down lists is on one sheet, and the data for these lists is on another, then this function will help us a lot.

On Sheet 2, select one cell or range of cells, then click on the “Data Validation” button.

Go to Sheet 1, place the cursor in the “Source” field and select the desired range of cells.

Now you can add names on Sheet 1, they will be added to the drop-down lists on Sheet 2.

Creating dependent drop-down lists

Let's assume we have three ranges: first names, last names, and patronymics of employees. For each, you need to assign a name. We select the cells of this range, you can also empty ones - over time you can add data to them, which will appear in the drop-down list. Right-click on them and select “Assign a name” from the list.

We call the first “Name”, the second – “Last Name”, the third – “Father”.

Let's make another range in which the assigned names will be written. Let's call it "Employees".

We make the first drop-down list, which will consist of the names of the ranges. Select cell E1 and on the “Data” tab select “Data Validation”.

In the “Data Type” field, select “List”; in the Source field, either enter “=Employees” or select a range of cells that have been assigned a name.

The first dropdown list has been created. Now in cell F2 we will create a second list, which should depend on the first. If we select “Name” in the first one, a list of names will be displayed in the second one; if we select “Last Name”, a list of surnames will be displayed.

Select the cell and click on the “Data Check” button. In the “Data type” field, select “List”; in the source field, enter the following: =INDIRECT($E$1). Here E1 is the cell with the first drop-down list.

Using this principle, you can make dependent drop-down lists.

If in the future, you will need to enter the values ​​​​into a range that is given a name, for example, “Last Name”. Go to the Formulas tab and click Name Manager. Now select “Last Name” in the range name, and below, instead of the last cell C3, write C10. Click the check mark. After this, the range will increase, and you can add data to it, which will automatically appear in the drop-down list.

Now you know how to make a drop-down list in Excel.

How to create a drop-down list consisting of several cells at once (let’s say the name has a cost)

Thank you, everything worked out.

A drop-down list with values ​​from another sheet does not work, since the window when data verification is open does not allow working with other windows, especially with another sheet!

A dependent dropdown list allows you to do a trick that is very often praised by users Excel templates. A trick that makes work easier and faster. A trick that will make your curves comfortable and pleasing.

Example of creating a dependent drop-down list in an Excel cell

Example of using a dependent dropdown list to create convenient form filling out documents with which sellers ordered goods. From the entire assortment, they had to choose the products that they were going to sell.

Each seller first identified a product group, and then a specific product from this group. The form must include full name groups and a specific product index. Since typing this by hand would be too time-consuming (and annoying), I came up with a very quick and simple solution - 2 dependent dropdowns.

The first was a list of all product categories, the second was a list of all products in the selected category. So I created a dropdown list dependent on the selection made in the previous list (here you will find material on how to create two dependent dropdown lists).

The template user wants to get the same result home budget where the category and subcategory of expenses are needed. An example of the data is in the figure below:

So, for example, if we select the Entertainment category, then the list of subcategories should include: Cinema, Theater, Pool. Very quick solution, if you want to analyze more detailed information in your home budget.

List of categories and subcategories in Excel dependent dropdown list

I admit that in the version of my proposed home budget, I limit myself to only a category, since for me such a division of expenses is quite enough (the name of expenses / income is considered as a subcategory). However, if you need to separate them into subcategories, then the method I describe below is ideal. Feel free to use it!

And the end result looks like this:

Dependent dropdown list of subcategories

In order to achieve this, we need to make a slightly different data table than if we were creating a single drop-down list. The table should look like this (range G2:H15):

Working Excel Source Spreadsheet

In this table you must enter a category and its subcategories next to it. The category name must be repeated as many times as there are subcategories. It is very important that the data is sorted by the Category column. This will be extremely important when we write the formula later.

You could also use the tables from the first image. Of course, the formulas would be different. Once I even found such a solution on the Internet, but I didn’t like it because it had a fixed list length: which means that sometimes the list contained empty fields, and sometimes did not display all elements. Of course, I can avoid this limitation, but I admit that I like my solution better, so I never returned to that solution.

OK then. Now, I will describe the steps of creating a dependent dropdown list one by one.

1. Cell Range Names

This is an optional step, without it we can handle this without any problems. However, I like to use names because they make the formula much easier to both write and read.

Let's assign names to the two ranges. List of all categories and working list of categories. These ranges would be A3:A5 (list of categories in the green worksheet in the first image) and G3:G15 (list of repeating categories in the purple worksheet).

To name a list of categories:

  1. Select range A3:A5.
  2. In the Name box (the box to the left of the formula bar), enter the name "Category."
  3. Confirm with the Enter key.

Perform the same action for the category worklist range G3:G15, which you can call “Work_List”. We will use this range in the formula.

2. Create a drop-down list for a category

It will be simple:

  1. Select the cell where you want to place the list. In my case it is A12.
  2. From the DATA menu, select the Data Validation tool. The “Check Input Values” window appears.
  3. Select List as the data type.
  4. As the source, enter: =Category (image below).
  5. Confirm with OK.

The result is as follows:

Dropdown list for category.

3. Create a dependent dropdown list for a subcategory

Now it will be fun. We know how to create lists - we just did it for a category. Just one question: “How do I tell Excel to select only those values ​​that are intended for a specific category?” As you can probably guess, I will be using a worksheet here and, of course, formulas.

Let's start with what we already know, that is, by creating a drop-down list in cell B12. So select that cell and click Data/Data Validation and the data type is List.

In the list source, enter the following formula:

View of the “Checking input values” window:

Validating input values ​​for a subcategory in a dependent dropdown list

As you can see, the whole trick to a dependent list is to use the OFFSET function. Okay, almost all of it. The MATCH and COUNTIF functions help her. The OFFSET function allows you to dynamically define ranges. First, we define the cell from which the range shift should begin, and in subsequent arguments we define its size.

In our example, the range will move across the Subcategory column in the worksheet (G2:H15). We'll start moving from cell H2, which is also the first argument of our function. In the formula, cell H2 is written as an absolute reference because I assume that we will use the drop-down list in many cells.

Because worksheet sorted by Category, the range that should be the source for the dropdown will start where the selected category first appears. For example, for the Food category we want to display the range H6:H11, for Transport - the range H12:H15, etc. Notice that we are moving along the H column all the time, and the only thing that changes is the beginning of the range and its height ( that is, the number of elements in the list).

The beginning of the range will be moved relative to cell H2 by as many cells down (in number) as the position number of the first occurring category in the Category column. It will be easier to understand with an example: the range for the Food category has been moved 4 cells down relative to cell H2 (starts from 4 cells from H2). In the 4th cell of the Subcategory column (not including the heading, since we are talking about a range called Work_List), there is the word Nutrition (its first appearance). We use this fact to actually determine the beginning of the range. The MATCH function (introduced as the second argument of the OFFSET function) will serve us for this purpose:

The height of the range is determined by the COUNTIF function. She counts all occurrences of repetitions in the category, that is, the word Nutrition. The number of times this word occurs, the number of positions there will be in our range. The number of positions in a range is its height. Here's the function:

Of course, both functions are already included in the OFFSET function described above. Also, notice that in both the MATCH and COUNTIF functions, there is a reference to a range called WorkList. As I mentioned earlier, you don't have to use range names, you can just enter $H3:$H15. However, using range names in the formula makes it simpler and easier to read.

That's it:

Download an example of a dependent drop-down list in Excel

One formula, well, not so simple, but it makes the work easier and protects against errors when entering data!