Multifunctional drop-down list in Excel. How to hide a sheet with a list source


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 more new 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 necessary elements tables. 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 it. serial number element in the list. Then click OK.

Using ActiveX Controls

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

The differences are as follows: the ActiveX control can be in two variants - mode debugging, which allows you to change parameters, and - mode input, you can only sample data from it. Changing modes is done using the button Design Mode in the tab Developer.

Unlike other methods, this allows tune fonts, colors and perform a quick search.

How to create a drop-down list in Excel? Everyone has long known how well Excel works with tables and various kinds of formulas, but few people know that you can make drop-down lists here. And today we will talk about them.

And so there are several options for how to make drop-down lists to work in Microsoft Office Excel.

Option one is very simple. If you enter similar data in the same column from top to bottom, then you just need to stand on the cell below the data and press the key combination “Alt + down arrow”. A drop-down list will appear in front of you, from which you can select the data you need with one click.

Disadvantage this method is that it is designed for a sequential method of data entry and if you click on any other cell in the column, the drop-down list will be empty.

Option two gives more opportunities, it is still considered standard. This can be done through a data check. First of all, we need to select the range of data that will go into our list and give it a name.


You can edit this range through the “Formulas” menu tab by selecting the “Name Manager” icon. In it you can create a new drop-down list, edit an existing one, or simply delete an unnecessary one.

The next step is to select the cell where our drop-down list will be placed and go to the “Data” menu tab, click on the “Data Check” icon. In the window that opens, we need to select the type of data that will be entered in our cell. In our case, we select “Lists” and below, through the equal sign, write the name of our range, and click OK. In order to apply the list to all cells, just select the entire column or the area you need before turning on data validation.


There are other more complex options for creating a dropdown list, such as: inserting through the Developer menu tab, where you can insert dropdown lists as part of a form element or as part of ActiveX element. Or write appropriate macros to create and operate drop-down lists.

Enter data in cells A1:A10, which will act as the source for the list. In our example, we entered numbers, they will appear in the drop-down list. Select the cell (For example, E5) that will contain the drop-down list. Select the Data menu -> Data Validation to open the Validate Input Values ​​dialog box.

3. On the Options tab, select the List option from the drop-down menu. Make sure the correct boxes are checked.

4. Then, click on the button. The following dialog box will appear.

5. Select the items that will appear in the drop-down list on the sheet using the mouse, click on the button and return back to the “Check Input Values” window, then click the “OK” button.

6. A drop-down list in Excel will be created.

If your list is short, you can enter items directly into Source in the Setup tab of the Validate Input dialog box. Separate each list item with the separators specified in the regional settings.
If the list needs to be on another sheet, you can use the "=List" option before specifying the data range.
How to create a drop-down list in Excel based on data from the list

Let's imagine that we have a list of fruits:
How to make a dropdown list in Excel

To create a dropdown list we will need to do the following steps:

Go to the “Data” tab => “Working with Data” section on the toolbar => select the “Data Validation” item.

In the “Source” field, enter the range of fruit names =$A$2:$A$6 or simply place the mouse cursor in the “Source” value entry field and then select the data range with the mouse:

If you want to create dropdown lists in multiple cells at a time, then select all the cells in which you want to create them and then follow the steps above. It is important to ensure that cell references are absolute (for example, $A$2) and not relative (for example, A2 or A$2 or $A2).

How to make a dropdown list in Excel using manual input data

In the example above, we entered a list of data for a drop-down list by selecting a range of cells. In addition to this method, you can enter data to create a drop-down list manually (it is not necessary to store it in any cells).
For example, imagine that we want to display two words “Yes” and “No” in a drop-down menu.

For this we need:
Select the cell in which we want to create a drop-down list;
Go to the “Data” tab => “Working with Data” section on the toolbar =>
Validating Data in Excel

In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type:
Validating input values ​​in Excel

In the “Source” field enter the value “Yes; No".
Click “OK”
Not really

The system will then create a drop-down list in the selected cell. All elements listed in the “Source” field, separated by semicolons, will be reflected in different lines of the drop-down menu.

If you want to simultaneously create a drop-down list in several cells, select the required cells and follow the instructions above.
How to create a drop-down list in Excel using the OFFSET function

Along with the methods described above, you can also use the OFFSET formula to create drop-down lists.

For example, we have a list with a list of fruits:

In order to make a drop-down list using the OFFSET formula, you must do the following:
Select the cell in which we want to create a drop-down list;
Go to the “Data” tab => “Working with Data” section on the toolbar => select “Data Validation”:
Validating Data in Excel

In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type:
Validating input values ​​in Excel

In the “Source” field enter the formula: = OFFSET(A$2$,0,0,5)
Click “OK”

The system will create a drop-down list with a list of fruits.
How does this formula work?

In the example above, we used the formula =OFFSET(link,offset_by_rows,offset_by_columns,[height],[width]).
This function contains five arguments. The “link” argument (in the example $A$2) indicates which cell to start the offset from. In the arguments “offset_by_rows” and “offset_by_columns” (in the example the value “0” is specified) – how many rows/columns need to be shifted to display data.

The “[height]” argument specifies the value “5”, which represents the height of the range of cells. We do not specify the “[width]” argument, since in our example the range consists of one column.
Using this formula, the system returns to you as data for the dropdown list a range of cells starting with cell $A$2, consisting of 5 cells.

How to make a drop-down list in Excel with data substitution (using the OFFSET function)

If you use the OFFSET formula in the example above to create a list, you are creating a list of data captured in certain range cells. If you want to add any value as a list item, you will have to adjust the formula manually.

Below you will learn how to make a dynamic drop-down list that will automatically load new data for display.
To create a list you will need:
Select the cell in which we want to create a drop-down list;

Go to the “Data” tab => “Working with Data” section on the toolbar => select “Data Validation”;
In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type;
In the “Source” field, enter the formula: = OFFSET(A$2$,0,0,COUNTIF($A$2:$A$100;”<>”))
Click “OK”

In this formula, in the “[height]” argument, we indicate as an argument denoting the height of the list with data – the COUNTIF formula, which calculates the number of non-empty cells in the given range A2:A100.

Note: for correct operation formulas, it is important that there are no empty lines in the list of data to be displayed in the drop-down menu.

How to create a drop-down list in Excel with automatic data substitution

In order for new data to be automatically loaded into the drop-down list you created, you need to do the following:
We create a list of data to display in the drop-down list. In our case, this is a list of colors. Select the list with the left mouse button:
drop-down list with automatic substitution in Excel

On the toolbar, click “Format as table”:

Select a table design style from the drop-down menu

By clicking the “OK” button in the pop-up window, we confirm the selected range of cells:

Assign a name to the table in the upper right cell above column “A”:

The table with the data is ready, now we can create a drop-down list. To do this you need:
Select the cell in which we want to create a list;

Go to the “Data” tab => “Working with Data” section on the toolbar => select “Data Validation”:

In the “Checking input values” pop-up window, on the “Parameters” tab, select “List” in the data type:

In the source field we indicate = “the name of your table”. In our case, we called it “List”:
Source field automatic data substitution in Excel drop-down list

Ready! A drop-down list has been created, it displays all the data from the specified table:

In order to add a new value to the drop-down list, simply add information to the cell next after the table with the data:

The table will automatically expand its data range. The drop-down list will be replenished accordingly with a new value from the table:
Automatically inserting data into a drop-down list in Excel

How to copy a dropdown list in Excel

Excel has the ability to copy created drop-down lists. For example, in cell A1 we have a drop-down list that we want to copy to the range of cells A2:A6.

To copy a dropdown list with the current formatting:
left-click on the cell with the drop-down list that you want to copy;

select the cells in the range A2:A6 into which you want to insert the drop-down list;

Press the keyboard shortcut CTRL+V.
So, you will copy the drop-down list, saving original format list (color, font, etc.). If you want to copy/paste a dropdown list without saving the format, then:
left-click on the cell with the drop-down list that you want to copy;

press the keyboard shortcut CTRL+C;
select the cell where you want to insert the drop-down list;
right-click => call the drop-down menu and click “Paste Special”;
dropdown list in excel

In the window that appears, in the “Insert” section, select “conditions on values”:

Click “OK”
After this, Excel will copy only the data from the drop-down list, without preserving the formatting of the original cell.
How to select all cells containing a drop-down list in Excel

Sometimes, it is difficult to understand how many cells in an Excel file contain drop-down lists. There is an easy way to display them. For this:

Click on the “Home” tab on the Toolbar;
Click “Find and Select” and select “Select Group of Cells”:

In the dialog box, select “Data Validation”. In this field you can select the items “All” and “The Same”. “All” will allow you to select all drop-down lists on the sheet. The “same” item will show drop-down lists with similar data content in the drop-down menu. In our case, we select “all”:
Dropdown list in Excel. How to find all lists

Click “OK”
By clicking “OK”, Excel will select all cells with a drop-down list on the sheet. This way you can bring all the lists at once to general format, highlight boundaries, etc.

How to make dependent dropdown lists in Excel

Sometimes we need to create several drop-down lists, and in such a way that, by selecting values ​​from the first list, Excel determines what data to display in the second drop-down list.
Let's assume that we have lists of cities in two countries, Russia and the USA:

To create a dependent dropdown list we need:
Create two named ranges for cells “A2:A5” with the name “Russia” and for cells “B2:B5” with the name “USA”. To do this, we need to select the entire data range for the drop-down lists:
dependent dropdown list in Excel

Go to the “Formulas” tab => click in the “Defined names” section on the “Create from selection” item:
Dependent Dropdown Lists in Excel

In the “Create names from a selected range” pop-up window, check the “in the line above” box. Having done this, Excel will create two named ranges “Russia” and “USA” with lists of cities:
dependent-drop-down-list-in-excel

Click “OK”
In cell “D2” create a drop-down list to select the countries “Russia” or “USA”. So, we will create the first drop-down list in which the user can select one of two countries.

Now, to create a dependent dropdown list:
Select cell E2 (or any other cell in which you want to make a dependent dropdown list);
Click on the “Data” tab => “Data Check”;
In the “Validate input values” pop-up window, on the “Parameters” tab, in the data type, select “List”:
Validating input values ​​in Excel

Click “OK”

Now, if you select the country “Russia” in the first drop-down list, then only those cities that belong to this country will appear in the second drop-down list. This is also the case when you select “USA” from the first drop-down list.

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

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

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



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

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

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

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


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

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

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

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

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

7. Ready!

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

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

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

Needed to make it easy and quick to select when filling out desired value. We will look at two ways to create dropdown list in Excel 2007.

First way the simplest, but also the least suitable for use. Under the column with data, you need to right-click on the cell with the right mouse button, and click on the line Select from drop-down list . Then select the desired value, click on it, and this value appears in the cell. This method only applies to the cell directly below the list, and the list must not contain empty cells.

Second way Creating a drop-down list in Excel is more elegant and versatile. Select the data range for the drop-down list, then click on the menu item Formula - Name Manager - New . Fill in the field Name, and copy it (you will need it later). The name must begin with a letter or underscore, and must not contain spaces. Click OK. Close the window.

Then select the cell in which the Excel drop-down list will be (you can select several cells at once if they contain the same drop-down lists). After that, select the menu item Data - Data Validation , then in the window Data type select line List, in field Source put an equal sign, and without a space, paste what you copied (field value Name). Don't forget the sign = , otherwise nothing will work. The inscription in the field looks like Source like that: =Range_name . Click OK .

A drop-down list icon will appear to the right of the cell from which you can select one of the values. Once selected, this value will appear in the cell. Cell with drop-down Excel list can be done on another sheet, so that on one sheet there is data for lists, and on the other there is a cell with a drop-down list, or several such cells.

This could be a survey, test, or questionnaire with pre-written answer options. You can, for example, distribute an Excel file with a questionnaire to a group of people you want to interview, and receive back files with answers, or printed answers.

For reliability, you can hide or protect the sheet with data for drop-down lists. To hide Excel sheet, click on its name with the right mouse button, and select Hide. To display hidden sheets, right-click on the name of any open Excel sheet and select Display .

To protect Excel sheet, select a menu item Edit - Protect Sheet , and specify the password and actions that are allowed for users.

To remove a drop-down list from a cell, select the cell and select from the menu Data - Data Validation , and press the button clear all .

When filling cells with data, it is often necessary to limit the input to a specific list of values. For example, there is a cell where the user must enter the name of the department, indicating where he works. It is logical to first create a list of departments of the organization and allow the user to only select values ​​from this list. This approach will help speed up the input process and reduce the number of typos.

Drop-down list can be created using

In this article we will create Drop-down list using () with data type List.

Drop-down list can be formed in different ways.

A. The simplest drop-down list - entering list items directly into the Source field

Suppose in a cell B 1 need to create drop-down list to enter units of measurement. Select a cell B 1 and call Data verification.

If in the field Source indicate units of measurement separated by semicolons pcs;kg;sq.m;cub.m, then the choice will be limited to these four values.

Now let's see what happened. Select a cell B 1 . When you select a cell, a square arrow button appears to the right of the cell to select items from drop down list.

Flaws this approach: list items are easy to lose (for example, by deleting a row or column containing a cell B 1 ); It is not convenient to enter a large number of elements. The approach is suitable for small (3-5 values) immutable lists.
Advantage: Create a list quickly.

B. Entering list items into a range (on the same sheet as the drop-down list)

Items for a dropdown list can be placed in a range of EXCEL sheet and then into the field Source tool to specify a link to this range.

Let's assume that the elements of the list pcs;kg;sq.m;cub.m entered into range cells A 1: A 4 , then the field Source will contain =sheet1!$A$1:$A$4

Advantage: clarity of the list of elements and ease of modification. The approach is suitable for lists that change rarely.
Flaws: If new elements are added, you have to manually change the range reference. True, a wider range can be immediately identified as a source, for example, A 1: A 100 . But then the drop-down list may contain empty lines (if, for example, some of the elements were deleted or the list was just created). To make empty lines disappear, you need to save the file.

Second disadvantage: the source range must be located on the same sheet as drop-down list, because rules cannot use links to other sheets or workbooks (this is true for EXCEL 2007 and earlier).

Let's get rid of the second drawback first - we'll post a list of elements drop down list on another sheet.

B. Entering list items into a range (on any worksheet)

Entering list items into a range of cells that is in another workbook

If you need to move a range with drop-down list items to another workbook (for example, to a workbook Source.xlsx), then you need to do the following:

  • in the book Source.xlsx create the necessary list of elements;
  • in the book Source.xlsx assign to the range of cells containing the list of elements, for example ListExt;
  • open the workbook in which you intend to place the cells with the drop-down list;
  • select the desired range of cells, call the tool , in field Source indicate = INDIRECT("[Source.xlsx]sheet1!ListExt");

When working with a list of elements located in another workbook, the file Source.xlsx must be open and located in the same folder, otherwise you must specify the full path to the file. In general, it is better to avoid references to other sheets or use Personal macro book Personal.xlsx or Add-ons.

If you don't want to assign a name to the range in the file Source.xlsx, then the formula needs to be changed to = INDIRECT("[Source.xlsx]sheet1!$A$1:$A$4")

ADVICE:
If there are many cells with rules on the sheet Data checks, then you can use the tool ( Home/ Find and Select/ Selecting a group of cells). Option Data checking This tool allows you to select cells that are subject to data validation (specified using the command Data / Working with data / Validating data). When selecting a switch Everyone all such cells will be selected. When selecting the option These same Only those cells are highlighted for which the same data validation rules are set as for the active cell.

Note:
If drop-down list contains more than 25-30 values, it becomes inconvenient to work with it. Drop-down list displays only 8 elements at a time, and to see the rest, you need to use the scroll bar, which is not always convenient.

EXCEL does not provide font size adjustment Dropdown list. At large quantities elements, it makes sense to list elements and use additional classification of elements (i.e. split one drop-down list into 2 or more).

For example, to effectively work with an employee list of more than 300 employees, it should first be sorted by alphabetical order. Then create drop-down list containing letters of the alphabet. Second drop-down list should contain only those surnames that begin with the letter selected in the first list. To solve such a problem, the or structure can be used.


2024 gtavrl.ru.