Creating a drop-down list in Excel. Related drop-down lists How to select from a list in Excel

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.

Now we'll 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.

If you are filling out a table in Excel, and the data in a column can sometimes be repeated, for example, the name of a product, or the name of an employee, then in order not to enter the desired parameter every 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 of 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 the desired value in each cell from the drop-down list.

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 (for example, so that the name has a cost)

Thanks, all worked well.

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!

The dependent dropdown list allows you to do a trick that is often praised by Excel template users. 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

An example of using a dependent drop-down list to create a convenient form for 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 the full name of the group and the 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 user of a home budget template wants to get the same result where they need a category and subcategory of expenses. 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. A 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, even I 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 it did not display all the 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 the worksheet is sorted by Category, the range that should be the source for the drop-down list 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 all:

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!

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, full name of an employee. 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:

If you have to work with an Excel workbook in which the same list of data constantly appears, or maybe you just don’t want to use the action many times Copy > Insert, then it would be very convenient to have a pre-configured and saved list so that Excel can help you with your work. Using custom lists is a good solution for this situation. Next, I'll show you how to create a custom list in Excel.

Create a custom list in Excel

I'll demonstrate this using a list of school clubs. I need to keep track of the money on the balance sheet of each of them, as well as outline a daily budget. I started by entering the names of the clubs - this is the information that I will need constantly.

At this point I would also run the spell check, this is done by simply clicking F7.

Then I selected this area, clicked on the button Office and at the bottom of the menu that opens, click on Excel Options(Excel Options).

In chapter Popular(General) You will find the item Create lists for use in sorts and fills sequences(Create lists for sorting and filling) – click on Edit Custom Lists(Change lists).

If you are working in Excel 2010, then you need a different route. Open the tab File(File) and click Options(Options). Then scroll down to find the button Edit Custom Lists(Change lists).

Next, a dialog box will open in which you can add information that you plan to use repeatedly. If you look at the lists already prepared by Microsoft, you will see among them those that everyone uses so often. They cannot be changed or deleted. However, if you want to delete or change the list you created, you can do this at any time. To do this, click on your list and do whatever you want with it.

Creating a list from scratch

I have two ways to create a custom list. I can create it manually by entering each value in the field List entries(List Items) and clicking Add(Add). If you choose this path, you will encounter some restrictions. Field List entries(List Items) allows you to enter a maximum of 255 characters. Be careful about how many characters each of your entries consists of!

Clue: If you plan to enter a list into a field List entries(List elements) manually, do not put extra spaces between elements. If there are spaces before or after an element, then Microsoft simply will not take them into account, but if between the words of one element, then all of them will be saved.

Creating a list from an existing data range

Another way to add items to a custom list is to import data. If you select them before opening the menu window, the selected range will be automatically inserted into the corresponding field. All you have to do is click Import(Import) and Excel will create a list from the text contained in the cells. If you have not selected the text in advance, place the cursor in the field next to the button Import(Import) and select the data cells for the new list.

Remember, we had a limit on the number of characters that can be entered in the field List entries(List items)? Not when importing! Now the maximum list size is about 2000 characters! Click OK to close the window with list parameters, and again OK to close the Excel Options window.

For a custom list, you can only import text values. If you need to create a custom list with calendar dates or numbers, you will have to use the field List entries(List elements).

Here are some things you should know about custom lists... All lists are linked to your computer. Their settings are saved on the computer you are currently working on. If you take a file from your work computer to work with it at home, you will have to create the same custom list again on your home computer. If you used a custom list to do the sorting, its elements will remain in the Excel cells, but it will not be shown among the lists.

Using custom lists in Excel

Now our custom list is ready to use. Select a cell and enter any element of this list using the keyboard. Click on the autofill marker (the small square in the lower right corner of the cell) and drag it to continue the sequence. A little to the right I entered “Monday”, then clicked on the marker and pulled to the right, Excel filled in the cells automatically.

The great thing about this feature is that Excel will fill both column and row equally, forward or backward, whether you start with the first item in the list, the middle, or the last item... Once you click and start dragging mouse, Excel will determine what you want to do and insert the appropriate data.

Sorting by custom list

One of the nice features that comes with working with custom lists is the ability to organize data according to any of the lists saved on your computer. Click one or more columns, then click Sort & Filter(Sort and Filter), expand the drop-down menu Order(Order), click Custom List(Custom List) and select the list you want to sort by.

A sort can include more than one column. If you want to add another level and sort first by month and then by account number, you can click Add Level(Add Level) and define how the data should be displayed. When you're done, click OK. The information is now organized according to the selected list!

It's quite simple! Using custom lists is very convenient especially for those who want to avoid repeated copying and pasting of data. If you know you have a set of data that you might need at any time, why not make a custom list out of it? Who knows, it might save you a lot more time than you realize... and leave your boss feeling like you had to work hard. Let him think so.

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 “Operating system” column (but it’s not the same on all computers), I filled in 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 "Name of specialist" 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