Selecting a value from a dropdown list in excel. Related Dropdown Lists


Section 3: Working with Table Groups

The concept of a list in Excel

A list is an Excel table that consists of one or more columns. The list columns are assigned unique field names, which are entered in the first row of the list. All cells in a column have the same data format, so all rows, or records as they are also called, are of the same type.

Surname

Age

Floor

Petukhova

Petrov

Zaitseva

Morev

Ivanov

Petrova

Rice. 1. Excel list example

In the above Excel table, the data in the first and third columns is in text format, and the data in the second column is in numeric format. The names of the list fields must be placed in one cell. The list data and other data on the same sheet must be separated by at least one empty cell, that is, they must not touch each other. An Excel list is a typical database and a large number of specific operations apply to it. Most of the tables that Excel users work with are lists or can be converted to list form. So, if the table shown in Fig. 2 was created for each group of students, then they could be combined into one table, which would also be a list, by adding another “Group” field.

Group

Surname

Age

Floor

Height

Weight

99-l-3

Petukhova

99-l-3

Petrov

99-l-3

Zaitseva

97-l-1

Popov

97-l-1

Kozlov

Fig. 2. Combining tables into a list

Working with lists in Excel

Most operations designed to work with lists are concentrated in the “Data” menu. If the list is created correctly, then just select one of the cells inside the list and click the desired command in the “Data” menu. Excel will automatically determine the boundaries of your list.


Rice. 2. Expanded “Data” menu

The “Sorting” item allows you to sort by selected criterion by one or, in order of priority, by two or even three list fields.


Fig. 3. Two-stage sorting of a list

“Filter” makes it possible to show only those entries in the list that meet some criterion. Thus, setting a custom autofilter, shown in Fig. 3, will display in the list only surnames ending with the letter “v”.



Fig3. Using a custom autofilter

The “Form” item allows you to quickly enter data into the list. The “Results” item allows you to summarize the data under each group in the list. Figure 4 shows the summary dialog box, and Figure 5 shows the result of this operation.


Fig.4. Summarizing

Group

Surname

Age

Floor

Height

Weight

99-l-3

Petukhova

99-l-3

Petrov

99-l-3

Zaitseva

19

99-l-3 Total

97-l-1

Popov

97-l-1

Kozlov

19

97-l-1 Total

Grand total

Fig. 5. Result from summing up

“Consolidation” allows you to summarize results for several tables of the same type. The capabilities of the “Consolidation” and “Totals” items are completely covered by the powerful mechanism for building Excel pivot tables.

The “Group and Structure” item is applicable not only to Excel lists and allows you to change the levels of detail in the presentation of information on an Excel sheet. To create a simple structure on a sheet, you can select several columns or rows and click “Group”.


Fig.6.Creating a structure

An additional bar with a square icon will appear above the sheet. Clicking on this symbol will hide the columns that have a black line over them. If you select the columns under the black line and select “Group” again, a two-level structure will be created. You can group rows in a similar way.


Fig 7. Result of the grouping operation

Creating a structure makes it possible to view large Excel tables on one screen, hiding data that is not needed at the moment.

Excel Pivot Tables

Pivot tables allow you to perform group operations on data located either in lists, across multiple consolidation ranges, or in external databases. When you click on the “Pivot Table” item in the “Data” menu, the first dialog box of the Pivot Table Wizard appears (see Fig. 8).


Fig.8. Pivot Table Wizard - step 1.

To process the list, you need to select the first of the proposed options and go to the second step of the wizard. In the second step, you need to select the list to be processed and proceed to the third step. At the third step of the wizard (see Fig. 9.) a constructor for processing list data will appear.


Fig. 10. Pivot table wizard - step 3.

To construct a group operation on one of the list fields, you need to drag the name of the corresponding field into the “Data” area with the mouse. So, to get the total weight, you need to drag it into the “Data” area. The “Row”, “Column” and “Page” areas allow you to obtain, in addition to the total application of a group operation on all records in the list, partial group operations on records with the same values ​​of the fields selected in the area. So, to get not only the total weight of all people, but also the weight of people in each group, you need to drag it into the “Row” area. In Fig. Figure 11 shows the result of the described dragging.

Fig. 11. Constructing a group operation using the “Weight” field

To remove any field from the design area, you just need to drag it with the mouse and drag it out of the given area.

By clicking on the “Next” button, you can go to the fourth step of the wizard. In the fourth step, you need to select the sheet in which the pivot table will be placed. Here you can choose to create a pivot table on a new sheet and complete the wizard. The resulting summary table is shown in Fig. 12.


Fig. 12. Pivot table obtained as a result of the wizard’s work

Use the button in the PivotTables panel to return to the PivotTable Wizard to edit the batch operation query. Let's change the query by adding the "Last Name" field to the data area. The summary table will take the form shown in Fig. 13.

Fig. 13. Pivot table with group operation for the Last Name field

Let's go back to the PivotTable Wizard and add the "Gender" field to the "Column" area and the "Age" field to the "Page" area. The resulting summary table is shown in Figure 14.


Fig. 14. Resulting pivot table

The “Page” area differs from the “Row” and “Column” areas in that it allows you to either perform a group operation on all field values ​​at once, or on one selected value. In the example given, the value "19" was selected.

Exercise

Convert the table shown below to an Excel list or lists. Based on the summary tables, determine:

1. Area of ​​enterprises subject to reconstruction by region;

2.Number of enterprises subject to reconstruction by type of activity;

3. Total area and number of enterprises by area;

4.All of the above in one summary table.

List of enterprises subject to reconstruction

Airport

Household services

Organization

Kind of activity

Square

LLC Success

Dry cleaning

CJSC Udacha

Salon

LLC Player

Dry cleaning

Trade

Fialka LLC

Flowers

CJSC Bublik

Bread

Iris LLC

Flowers

Falcon

Household services

LLC Boot

Shoe repair

Volos LLC

Salon

Trade

LLC Moroz

Appliances

JSC Kalach

Bread

LLC Roman

Books

JSC Herring

Fish

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:

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 checking;
  4. Select a value from the drop-down list List;
  5. In 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 checking for the second drop-down list the formula will be =INDIRECT("Table1["&F2&"]"). Cell F2 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 creating a 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 drop-down group, 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 specify a range of unique group values. 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 hardest part is to specify 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.

When creating tables, it is sometimes very convenient to use drop-down (in other words, drop-down) lists. Excel 2010 allows you to do this in several ways. Let's look at them.

Method 1: Create a Dropdown List in Excel 2010 Using the Data Validation Tool

This method is considered standard because it is simple and convenient.

1. On the free space of the sheet, write down all the elements of the drop-down list in a column, each element in its own cell.

2. Give the cell range a name. For this:

  • Click the top cell of the list and, holding down the left mouse button, drag the cursor down until the entire list is selected.
  • place the cursor in the “Name” field, to the left of the formula bar;
  • enter a name for the list and press Enter.

Please note that the list name must always begin with a letter and not contain spaces.

3. Select the cell in the table where the drop-down list will be placed.

4. Open the “Data” tab and click the “Data Check” button. In the window that opens, go to the “Options” tab. In the “Data type” drop-down list, select “List”.

5. In the “Source” line, you need to indicate the address from where the elements of the created list will be taken. The address will be the name you give to the range of cells. There are several ways to set the address.

  • Enter it manually, placing an equal sign in front of it, for example, “=month”. Letter case is not important.
  • By clicking the mouse in the “Source” line (to activate), select with the cursor all the list elements in the table.

6. If you need to create a message for input, open the tab of the same name. Write text that will appear next to the dropdown cell when it is selected. On the next tab - “Error message”, in the same way you can write text notifying about errors.

7. Confirm your entry by clicking “OK” and the drop-down list is ready. To open it, click on the down arrow button that appears next to the cell containing the list.

Method 2. Quickly create a drop-down list

A drop-down list in Excel 2010 can be created with a single keyboard shortcut, but it can only be located in one place - in the cell below the list items.

1. List in a column all the elements of the future drop-down list.

2. Select the cell located under the last element and press the key combination “Alt” + “down arrow” - the list will be created. This method will allow you to set the cell to the value of one of the elements.

Method 3: Create a dropdown list as a control

To use this method, enable the display of the “Developer” tab: open the menu “File” - “Options” - “Customize Ribbon”. In the “Main Tabs” column, check the “Developer” box. Confirm the action by clicking “OK” - the tab will be created.

1. List the elements of the future list in a column.

2. From the “Insert” menu of the “Developer” tab, select “Form Controls” - “Combo Box”.

3. Draw your future drop-down list on the sheet with the cursor. Right-click on it and select “Format Object” from the menu.

4. The value of the “Generate list by range” field should be a list of elements - select it with the cursor, and the field will be filled in automatically. In the “Link to cell” field, indicate the address of the cell where the serial number of the selected element will be shown. Select a cell and click on it. The Number of List Rows field allows you to configure how many items will be displayed when the list is expanded.

5. Confirm your entry and click OK. The list will be created.

Method 4: Create a Dropdown List as an ActiveX Control

The most complex method, but with the most flexible settings.

1. Create a list using the method described above.

2. From the “Insert” menu of the “Developer” tab, select “ActiveX Controls” - “Combo Box”.

3. Draw the future drop-down list on the sheet.

4. The option that allows you to edit the drop-down list is called “Design Mode”. If this mode is active, the button of the same name will be highlighted in the “Controls” section, next to the “Insert” button. If the button is not highlighted, the editing mode is disabled.

5. To set the list parameters, click the “Properties” button in the same section. The “Properties” settings window will open. Both tabs of this window contain the same settings, sorted in the first case alphabetically, in the second - by category.

6. Most settings can be left as default, but the most necessary ones are listed below.

  • ListRows - analogous to the “Number of rows in the list” value, will show how many rows will be displayed.
  • Font—font settings. Allows you to select the font and its style.
  • ForeColor - selecting a font color from the table.
  • BackColor - background color.
  • ListFillRange - location of the list of elements in the format: sheet ("!" - separator) and a range of cells. For example: Sheet2!D2:D6. Registered manually.
  • LinkedCell - link to a cell. Manually specify the address of the cell where the serial number of the selected list item will be displayed.

7. Save the settings and deactivate the design mode by clicking on the button of the same name. The drop-down list will be created and you can check how it works.

How to make a drop-down list in Excel, today we will consider this question in practice. With the help of this article, we will learn how to make not only drop-down lists, but also simple, dependent, expanding ones. Then copy the made list into the Excel program and paste it.

Why do you need lists in Excel?

Hello, friends! As far as we know, Excel is a very convenient software for recording and maintaining various records in a document. In it you can fill in data about your income and expenses, calculate formulas, and so on. But some people ask the question, how to make a drop-down list in Excel? Why is it needed?

In fact, drop-down lists are very necessary if you often work with tables in this program and fill it with various numbers and symbols. They are needed to save your work time and not spend it on filling out tables for a long time. In practice, the drop-down list works efficiently and remembers all the data you previously recorded in the tables. Next, we will create such a list, and make other similar lists that will help you simplify your work in this program.

Let's create a simple drop-down list. Open the Excel program and write some words or numbers in the first table. Select one column with words and then, at the top of the program control panel, click on the “Data” section and then “Data Check” (Screen 1).

Then you should click on “Source” and once again select the column we filled in, only after that click “OK”. So, we now know how to make a drop-down list in Excel; it is also called a “pop-up”. After its creation, it looks like this (Screen 3).

You can click on the arrow and select previously written words in the table. We considered a simple option. Further, there will be more lists, but with a more complicated version.

Dropdown list from another sheet in Excel

If you work from different sheets of the Excel program, then a drop-down list from another sheet will greatly help us in further work. It's quite simple to make. First, select the desired column of data in the program, and press the “Data Check” button again on the second sheet. Next, go to the first sheet and in the “Source” section, then you should select the cells and click on the “OK” button. After this, the drop-down list should be moved to another sheet.

Dependent Dropdown Lists in Excel

What are dependent dropdown lists? These are lists that are created from two sources. For example, there are two tables in which you have written down the names of your acquaintances and friends. And in the next column, a dependent drop-down list will be displayed, which shows the same data that was recorded in other tables. Next, we will create such dependent lists and expanders.

A drop-down list in Excel can be created using a special function, which we will talk about below. It is somewhat similar to the drop-down one, only using the given “OFFset” value. So, let's move on to the creation process.

Do all the same actions that we did in the first part of the article and then in the “Source” specify the following formula “ = OFFSET(A$2$;0;0;5”, then click “OK” to create a drop-down list in .

Above we talked a little about dependent lists. Now, let's create them. But first, create and fill two columns with any data. Next, select these columns in the tables, and click the button at the top - “Create from selected fragment” (Screen 4).

Next, we create a drop-down list (the creation process was shown above in the article). Then, go to the “Sources” function and write the following command in the field - =INDIRECT($D$2). It will help you create dependent lists. Click the “OK” button, after which another column should appear in the table, with a dependent pop-up list.

How to copy a dropdown list in Excel

Copying a drop-down list will not be difficult for users. To do this, select the cell we need with the left mouse button and press CTRL+C (Copy). Then, click on any column in the Excel table. And right-click the command – “Paste Special”. In this insert function you need to set – “Conditions on values” and click on “OK” to save the changes.

After this, press the keys simultaneously again - CTRL + V, which means “Paste” and subsequently, the entire copied drop-down list in this program will be successfully pasted.

Conclusion

So, in the article we looked at the question of how to make a drop-down list in Excel and at the same time made other lists. This instruction has been tested in practice. Perhaps this tool from the Excel program will help you resolve some issues when filling out tables or when working with documents. Use these functions, and working with Excel will become fast and convenient. Thank you for your attention, and good luck to you!

Receive new blog articles directly to your email. Fill out the form, click the "Subscribe" button


2024 gtavrl.ru.