Drop down list in excel как создать
A drop-down list is an excellent way to give the user an option to select from a pre-defined list.
It can be used while getting a user to fill a form, or while creating interactive Excel dashboards.
Drop-down lists are quite common on websites/apps and are very intuitive for the user.
Watch Video – Creating a Drop Down List in Excel
In this tutorial, you’ll learn how to create a drop down list in Excel (it takes only a few seconds to do this) along with all the awesome stuff you can do with it.
This Tutorial Covers:
How to Create a Drop Down List in Excel
In this section, you will learn the exacts steps to create an Excel drop-down list:
- Using Data from Cells.
- Entering Data Manually.
- Using the OFFSET formula.
Let’s say you have a list of items as shown below:
Here are the steps to create an Excel Drop Down List:
- Select a cell where you want to create the drop down list.
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
- As soon as you select List, the source field appears.
- In the source field, enter =$A$2:$A$6 , or simply click in the Source field and select the cells using the mouse and click OK. This will insert a drop down list in cell C2.
- Make sure that the In-cell dropdown option is checked (which is checked by default). If this option in unchecked, the cell does not show a drop down, however, you can manually enter the values in the list.
Note: If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).
In the above example, cell references are used in the Source field. You can also add items directly by entering it manually in the source field.
For example, let’s say you want to show two options, Yes and No, in the drop down in a cell. Here is how you can directly enter it in the data validation source field:
This will create a drop-down list in the selected cell. All the items listed in the source field, separated by a comma, are listed in different lines in the drop down menu.
All the items entered in the source field, separated by a comma, are displayed in different lines in the drop down list.
Note: If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps.
Apart from selecting from cells and entering data manually, you can also use a formula in the source field to create an Excel drop down list.
Any formula that returns a list of values can be used to create a drop-down list in Excel.
For example, suppose you have the data set as shown below:
Here are the steps to create an Excel drop down list using the OFFSET function:
This will create a drop-down list that lists all the fruit names (as shown below).
Note : If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).
How this formula Works??
In the above case, we used an OFFSET function to create the drop down list. It returns a list of items from the ra
It returns a list of items from the range A2:A6.
Here is the syntax of the OFFSET function: =OFFSET(reference, rows, cols, [height], [width])
It takes five arguments, where we specified the reference as A2 (the starting point of the list). Rows/Cols are specified as 0 as we don’t want to offset the reference cell. Height is specified as 5 as there are five elements in the list.
Now, when you use this formula, it returns an array that has the list of the five fruits in A2:A6. Note that if you enter the formula in a cell, select it and press F9, you would see that it returns an array of the fruit names.
Creating a Dynamic Drop Down List in Excel (Using OFFSET)
The above technique of using a formula to create a drop down list can be extended to create a dynamic drop down list as well. If you use the OFFSET function, as shown above, even if you add more items to the list, the drop down would not update automatically. You will have to manually update it each time you change the list.
Here is a way to make it dynamic (and it’s nothing but a minor tweak in the formula):
- Select a cell where you want to create the drop down list (cell C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria. As soon as you select List, the source field appears.
- In the source field, enter the following formula: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”))
- Make sure that the In-cell drop down option is checked.
- Click OK.
In this formula, I have replaced the argument 5 with COUNTIF($A$2:$A$100,”<>”).
The COUNTIF function counts the non-blank cells in the range A2:A100. Hence, the OFFSET function adjusts itself to include all the non-blank cells.
Note:
- For this to work, there must NOT be any blank cells in between the cells that are filled.
- If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).
Copy Pasting Drop-Down Lists in Excel
You can copy paste the cells with data validation to other cells, and it will copy the data validation as well.
For example, if you have a drop-down list in cell C2, and you want to apply it to C3:C6 as well, simply copy the cell C2 and paste it in C3:C6. This will copy the drop-down list and make it available in C3:C6 (along with the drop down, it will also copy the formatting).
If you only want to copy the drop down and not the formatting, here are the steps:
This will only copy the drop down and not the formatting of the copied cell.
Caution while Working with Excel Drop Down List
You need to to be careful when you are working with drop down lists in Excel.
When you copy a cell (that does not contain a drop down list) over a cell that contains a drop down list, the drop down list is lost.
The worst part of this is that Excel will not show any alert or prompt to let the user know that a drop down will be overwritten.
How to Select All Cells that have a Drop Down List in it
Sometimes, it ‘s hard to know which cells contain the drop down list.
Hence, it makes sense to mark these cells by either giving it a distinct border or a background color.
Instead of manually checking all the cells, there is a quick way to select all the cells that have drop-down lists (or any data validation rule) in it.
This would instantly select all the cells that have a data validation rule applied to it (this includes drop down lists as well).
Now you can simply format the cells (give a border or a background color) so that visually visible and you don’t accidentally copy another cell on it.
Here is another technique by Jon Acampora you can use to always keep the drop down arrow icon visible. You can also see some ways to do this in this video by Mr. Excel.
Creating a Dependent / Conditional Excel Drop Down List
Here is a video on how to create a dependent drop-down list in Excel.
If you prefer reading over watching a video, keep reading.
Sometimes, you may have more than one drop-down list and you want the items displayed in the second drop down to be dependent on what the user selected in the first drop-down.
These are called dependent or conditional drop down lists.
Below is an example of a conditional/dependent drop down list:
In the above example, when the items listed in ‘Drop Down 2’ are dependent on the selection made in ‘Drop Down 1’.
Now let’s see how to create this.
Here are the steps to create a dependent / conditional drop down list in Excel:
Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update.
Download the Example File
How does this work? – The conditional drop down list (in cell E3) refers to =INDIRECT(D3). This means that when you select ‘Fruits’ in cell D3, the drop down list in E3 refers to the named range ‘Fruits’ (through the INDIRECT function) and hence lists all the items in that category.
Important Note While Working with Conditional Drop Down Lists in Excel:
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 More. Less
You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create.
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.
Now is a good time to Sort data in a range or table in your drop-down list.
Select the cell in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, then Data Validation.
Note: If you can’t click Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again.
On the Settings tab, in the Allow box, click List.
Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:
If it’s OK for people to leave the cell empty, check the Ignore blank box.
Check the In-cell dropdown box.
Click the Input Message tab.
If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
Click the Error Alert tab.
If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.
Not sure which option to pick in the Style box?
To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Information will show a message with this icon and Warning will show a message with this icon .
To stop people from entering data that isn’t in the drop-down list, click Stop.
Note: If you don't add a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries.
If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.
If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.
To delete a drop-down list, see Remove a drop-down list.
You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples.
Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.
Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool.
Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells.
Select the cells that you want to restrict data entry in.
On the Data tab, under Tools, click Data Validation or Validate.
Note: If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
Click the Settings tab, and then in the Allow pop-up menu, click List.
Click in the Source box, and then on your sheet, select your list of valid entries.
The dialog box minimizes to make the sheet easier to see.
Press RETURN or click the Expand button to restore the dialog box, and then click OK.
You can also type values directly into the Source box, separated by a comma.
To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.
You can specify your own error message to respond to invalid data inputs. On the Data tab, click Data Validation or Validate, and then click the Error Alert tab.
See also
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table.
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.
Now is a good time to Sort your data in the order you want it to appear in your drop-down list.
Select the cell in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, then click Data Validation.
On the Settings tab, in the Allow box, click List.
If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this:
Fruit,Vegetables,Grains,Dairy,Snacks
If it’s OK for people to leave the cell empty, check the Ignore blank box.
Check the In-cell dropdown box.
Click the Input Message tab.
If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
Click the Error Alert tab.
If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message. If you don’t want a message to show up, clear the check box.
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list. To delete a drop-down list, see Remove a drop-down list.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 More. Less
You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create.
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.
Now is a good time to Sort data in a range or table in your drop-down list.
Select the cell in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, then Data Validation.
Note: If you can’t click Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again.
On the Settings tab, in the Allow box, click List.
Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:
If it’s OK for people to leave the cell empty, check the Ignore blank box.
Check the In-cell dropdown box.
Click the Input Message tab.
If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
Click the Error Alert tab.
If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.
Not sure which option to pick in the Style box?
To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Information will show a message with this icon and Warning will show a message with this icon .
To stop people from entering data that isn’t in the drop-down list, click Stop.
Note: If you don't add a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries.
If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.
If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.
To delete a drop-down list, see Remove a drop-down list.
You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples.
Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.
Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool.
Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells.
Select the cells that you want to restrict data entry in.
On the Data tab, under Tools, click Data Validation or Validate.
Note: If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.
Click the Settings tab, and then in the Allow pop-up menu, click List.
Click in the Source box, and then on your sheet, select your list of valid entries.
The dialog box minimizes to make the sheet easier to see.
Press RETURN or click the Expand button to restore the dialog box, and then click OK.
You can also type values directly into the Source box, separated by a comma.
To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.
You can specify your own error message to respond to invalid data inputs. On the Data tab, click Data Validation or Validate, and then click the Error Alert tab.
See also
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table.
Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.
Now is a good time to Sort your data in the order you want it to appear in your drop-down list.
Select the cell in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, then click Data Validation.
On the Settings tab, in the Allow box, click List.
If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this:
Fruit,Vegetables,Grains,Dairy,Snacks
If it’s OK for people to leave the cell empty, check the Ignore blank box.
Check the In-cell dropdown box.
Click the Input Message tab.
If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
Click the Error Alert tab.
If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message. If you don’t want a message to show up, clear the check box.
After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list. To delete a drop-down list, see Remove a drop-down list.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Как сделать выпадающий список в таблице в Excel или Calc.
Пример подобного списка:
Выпадающий список в любом табличном редакторе
Понятно, что в этой клинике зубы вырывают только «пакетным» способом, или по 10, или по 20, или сразу по 30, но никак не по 11 или 27?!
Простейший способ
Подходит, когда будущий список содержит ограниченное количество вариантов. Например,
Excel
Пишем на листе короткий список пациентов. Хватает даже одного — «Иван».
Выделяем ячейку справа от «Ивана» (как на картинке), и выбираем пункты меню Data > Validation > Allow: List > Source.
Пункты «Data» и «Validation» в русскоязычных версиях называются «Данные» и «Проверка»
В поле ‘Source’ вписываем это:
Да;Хз;Нет
Пояснение: это значения выпадающего списка. Если нужно что-то добавить, учитываем, что все значения разделяются через точку с запятой.
Внимание!
В зависимости от некоторых настроек Excel по-умолчанию, бывает, что разделителем является не точка с запятой (;), а простая запятая — (). Еще не могу сказать точно, где это настраивается, поэтому пробуем оба варианта.
Итак, контора пишет:
Создаем выпадающий список
В отдельной ячейке «под курсором» создан выпадающий список
Копируем эту ячейку as is (просто курсор находится «на ячейке», жмем Ctrl+C) повсюду, куда нам нужно (ставим курсор, куда нужно, и жмем Ctrl+V). Можно скопировать даже в другой файл Excel или на другой лист.
Чтобы ячейки всей колонки показывали выпадающий список, можно вставить эту ячейку со списком напротив пациента «Иван», и ухватив курсором ее нижний правый край, не отпуская левую кнопку, потянуть ее «вниз». Весь диапазон заполнится копиями нашей «ячейки со списком».
Итоговый список пациентов и колонка с выпадающим списком
Все то же самое, выбираем пункты меню Data > Validity… > Allow: List > Entries.
Вписываем по одному значению на строку
Составляем список в OpenOffice Calc
А теперь предположим, что бухгалтерия уже две недели шурует с этим файлом, и вдруг требует вставить им еще и варианты «Может быть» и «Частично»…
Простейший способ
Excel
Ставим курсор на ячейку, в которой содержится наш список, и снова взываем к ее редактированию (Data > Validation > Allow: List > Source).
Редактируем список. Но не используем клавиши «влево — вправо».
Почему — просто попробуй, поймешь.
Обязательно жмакаем опцию «Apply these changes to all others cells with same range». Это объяснит Excel, что внесенные изменения относятся ко всем ячейкам, которые содержат редактируемыми нами список.
Надо выбрать все ячейки, в которых находится наш список, снова пройти по Data > Validity… > Allow: List > Entries и изменить значения.
Мудрейший способ
Делаем ссылку на отдельно хранящийся список.
Excel
Пишем на листе короткий список пациентов. Хватает даже одного — «Иван».
На том же листе, где-то в верхних (чтобы поближе было) ячейках следует расписать опции будущих выпадающих списков.
- ячейка А1 — Да
- ячейка В1 — Хз
- ячейка С1 — Нет
- ячейка D1 — Может быть
Переходим к списку пациентов, выделяем первую ячейку в колонке «Заплатил?» (справа от «Ивана»). Ставим курсор туда, где должна будет начинаться будущая колонка с ячейками, которые содержат выпадающий список. В нашем случае — это колонка «Заплатил?» напротив ячейки со значением «Иван».
Выбираем пункты меню Data > Validation > Allow: List > Source.
Пункты «Data» и «Validation» в русскоязычных версиях называются «Данные» и «Проверка»
В поле ‘Source’ вписываем это:
Или ничего не вписываем, а просто кликаем на квадрат, который находится в правом краю поля Source. Окно превратится в узкую полоску. Мы не пугаемся, а курсором выделяем на листе диапазон ячеек, из которых потом будут взяты данные: A1, B1, C1, D1, E1, F1, G1, и тд, если нужно. Можно даже выделять пустые ячейки, рассчитывая заполнить их позже (мало ли что бухгалтерия придумает).
В процессе этого выделения ячеек поле Source будет заполняться самостоятельно.
По-умолчанию Excel запишет выделенный пользователем диапазон через знак «$» — он указывает, что строго-настрого нужна именно эта ячейка, брать данные только из нее, чтобы ни случилось.
Если указать просто =A1:C1, то при изменении расположения ячеек на листе (что часто бывает) Excel будет считать, что адрес указанного диапазона может быть изменен.
Дальше все то же — при наведении курсора на ячейку с выпадающим списком появляется особый указатель. Пользуемся.
Чтобы ее «размножить» — хватаем за угол и тянем вниз… Или копируем куда-нибудь в другое место на листе.
Почти то же самое, но выбираем пункты меню Data > Validity… > Allow: Cell Range > Source.
Нужно указывать диапазон руками: $A$1:$C$1, к примеру. Замечу — без знака «=«.
Кстати
Можно организовать этот список в «реальный» список на языке табличного редактора.
Собственно, шаг необязательный, из разряда «Заголовок следует обрамлять тэгом
Create a Drop-down List | Tips and Tricks Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values. Create a Drop-down List To create a drop-down list in Excel, execute the following steps. 1. On the second sheet, type the items you want to appear in the drop-down list. 2. On the first sheet, select cell B1. 3. On the Data tab, in the Data Tools group, click Data Validation. The ‘Data Validation’ dialog box appears. 4. In the Allow box, click List. 5. Click in the Source box and select the range A1:A3 on Sheet2. 6. Click OK. Result: Note: if you don’t want users to access the items on Sheet2, you can hide Sheet2. To achieve this, right click on the sheet tab of Sheet2 and click on Hide. Tips and Tricks Below you can find a few tips and tricks when creating drop-down lists in Excel. 1. You can also type the items directly into the Source box, instead of using a range reference. Note: this makes your drop-down list case sensitive. For example, if a user types pizza, an error alert will be displayed. 2a. If you type a value that is not in the list, Excel shows an error alert. 2b. To allow other entries, on the Error Alert tab, uncheck ‘Show error alert after invalid data is entered’. 3. To automatically update the drop-down-list, when you add an item to the list on Sheet2, use the following formula: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) Explanation: the OFFSET function takes 5 arguments. Reference: Sheet2!$A$1, rows to offset: 0, columns to offset: 0, height: COUNTA(Sheet2!$A:$A), width: 1. COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are not empty. When you add an item to the list on Sheet2, COUNTA(Sheet2!$A:$A) increases. As a result, the range returned by the OFFSET function expands and the drop-down list will be updated. 4. Do you want to take your Excel skills to the next level? Learn how to create dependent drop-down lists in Excel.
Как сделать выпадающий список в таблице в Excel или Calc.
Пример подобного списка:
Выпадающий список в любом табличном редакторе
Понятно, что в этой клинике зубы вырывают только «пакетным» способом, или по 10, или по 20, или сразу по 30, но никак не по 11 или 27?!
Простейший способ
Подходит, когда будущий список содержит ограниченное количество вариантов. Например,
Excel
Пишем на листе короткий список пациентов. Хватает даже одного — «Иван».
Выделяем ячейку справа от «Ивана» (как на картинке), и выбираем пункты меню Data > Validation > Allow: List > Source.
Пункты «Data» и «Validation» в русскоязычных версиях называются «Данные» и «Проверка»
В поле ‘Source’ вписываем это:
Да;Хз;Нет
Пояснение: это значения выпадающего списка. Если нужно что-то добавить, учитываем, что все значения разделяются через точку с запятой.
Внимание!
В зависимости от некоторых настроек Excel по-умолчанию, бывает, что разделителем является не точка с запятой (;), а простая запятая — (). Еще не могу сказать точно, где это настраивается, поэтому пробуем оба варианта.
Итак, контора пишет:
Создаем выпадающий список
В отдельной ячейке «под курсором» создан выпадающий список
Копируем эту ячейку as is (просто курсор находится «на ячейке», жмем Ctrl+C) повсюду, куда нам нужно (ставим курсор, куда нужно, и жмем Ctrl+V). Можно скопировать даже в другой файл Excel или на другой лист.
Чтобы ячейки всей колонки показывали выпадающий список, можно вставить эту ячейку со списком напротив пациента «Иван», и ухватив курсором ее нижний правый край, не отпуская левую кнопку, потянуть ее «вниз». Весь диапазон заполнится копиями нашей «ячейки со списком».
Итоговый список пациентов и колонка с выпадающим списком
Все то же самое, выбираем пункты меню Data > Validity… > Allow: List > Entries.
Вписываем по одному значению на строку
Составляем список в OpenOffice Calc
А теперь предположим, что бухгалтерия уже две недели шурует с этим файлом, и вдруг требует вставить им еще и варианты «Может быть» и «Частично»…
Простейший способ
Excel
Ставим курсор на ячейку, в которой содержится наш список, и снова взываем к ее редактированию (Data > Validation > Allow: List > Source).
Редактируем список. Но не используем клавиши «влево — вправо».
Почему — просто попробуй, поймешь.
Обязательно жмакаем опцию «Apply these changes to all others cells with same range». Это объяснит Excel, что внесенные изменения относятся ко всем ячейкам, которые содержат редактируемыми нами список.
Надо выбрать все ячейки, в которых находится наш список, снова пройти по Data > Validity… > Allow: List > Entries и изменить значения.
Мудрейший способ
Делаем ссылку на отдельно хранящийся список.
Excel
Пишем на листе короткий список пациентов. Хватает даже одного — «Иван».
На том же листе, где-то в верхних (чтобы поближе было) ячейках следует расписать опции будущих выпадающих списков.
- ячейка А1 — Да
- ячейка В1 — Хз
- ячейка С1 — Нет
- ячейка D1 — Может быть
Переходим к списку пациентов, выделяем первую ячейку в колонке «Заплатил?» (справа от «Ивана»). Ставим курсор туда, где должна будет начинаться будущая колонка с ячейками, которые содержат выпадающий список. В нашем случае — это колонка «Заплатил?» напротив ячейки со значением «Иван».
Выбираем пункты меню Data > Validation > Allow: List > Source.
Пункты «Data» и «Validation» в русскоязычных версиях называются «Данные» и «Проверка»
В поле ‘Source’ вписываем это:
Или ничего не вписываем, а просто кликаем на квадрат, который находится в правом краю поля Source. Окно превратится в узкую полоску. Мы не пугаемся, а курсором выделяем на листе диапазон ячеек, из которых потом будут взяты данные: A1, B1, C1, D1, E1, F1, G1, и тд, если нужно. Можно даже выделять пустые ячейки, рассчитывая заполнить их позже (мало ли что бухгалтерия придумает).
В процессе этого выделения ячеек поле Source будет заполняться самостоятельно.
По-умолчанию Excel запишет выделенный пользователем диапазон через знак «$» — он указывает, что строго-настрого нужна именно эта ячейка, брать данные только из нее, чтобы ни случилось.
Если указать просто =A1:C1, то при изменении расположения ячеек на листе (что часто бывает) Excel будет считать, что адрес указанного диапазона может быть изменен.
Дальше все то же — при наведении курсора на ячейку с выпадающим списком появляется особый указатель. Пользуемся.
Чтобы ее «размножить» — хватаем за угол и тянем вниз… Или копируем куда-нибудь в другое место на листе.
Почти то же самое, но выбираем пункты меню Data > Validity… > Allow: Cell Range > Source.
Нужно указывать диапазон руками: $A$1:$C$1, к примеру. Замечу — без знака «=«.
Кстати
Можно организовать этот список в «реальный» список на языке табличного редактора.
Собственно, шаг необязательный, из разряда «Заголовок следует обрамлять тэгом
Drop down list is a tool that can help you to force users into selecting a specific value from a predefined set of values. It is always good to have drop down lists for accepting user inputs.
This approach is better because it validates each input from user against a predefined set of values and hence users can only input valid and excepted values.Excel also has a feature that allows you to create drop down lists. And today I will guide you on making a drop down list in Excel.How to make a drop down list in Excel:First of all open your excel sheet and select the cell on which you wish to create a drop down.Next, navigate to ‘Data’ tab in the Excel Ribbon and then click the ‘Data Validation’ button.Now, a ‘Data Validation’ window will open. In the ‘Allow’ dropdown, select the “List” option. And in the ‘Source’ textbox enter the values that you wish to have in your dropdown, these values should be separated by a comma (,). Also, please make sure that the checkbox ‘In-cell dropdown’ should be checked.After this hit the ‘OK’ button and your dropdown is ready to be used.Few Important things about Excel Drop downs:1. You cannot delete a dropdown by pressing delete key. It can only be deleted after selecting the dropdown, click ‘Data Validation’ option and in the ‘Data Validation’ window press “Clear All” button.2. The width of your dropdown list is not set automatically. You should manually adjust it according to the longest value in your dropdown.3. Excel dropdowns can have a maximum of 32,767 values.A better type of data source for dropdowns:As you have already seen that you can enter the values of a dropdown directly in the ‘Source’ textbox. This is fine if you are planning to create only small list boxes, but you have a large list of values that you want to populate in your drop down list then there is a better method.You can enter the values that you want to see in your dropdown as a table and then use the range of this table in ‘Source’ column. Below is a step by step procedure to do this:First with an Excel sheet opened, enter the values that you are planning to populate in your dropdown in a table.Next, select the cell on which you are planning to create a drop down.Now, navigate to Data > Data Validation.In the ‘Data Validation’ window select “List” from the ‘Allow’ dropdown, check the ‘In-cell dropdown’ checkbox and in the ‘source’ textbox enter your table range (that we created in step 1). Hit “OK” and your dropdown is created.Cosmetic Tip: If you are using a drop down with a table of values as source, then try to have this table on a separate sheet which you can hide later. This makes your dropdowns look more natural and no one can meddle with your dropdown values.Bonus Tip: Instead of directly entering the source range in ‘source’ textbox you can also name your range and then use the same in ‘source’ textbox.So, this was all about making a drop down list in Excel.
In web forms, surveys, and polls, it can be very useful to limit the choices for a selection with a simple drop-down list. This is also possible in an Excel spreadsheet, but the process isn’t very well known or very intuitive. In Access, you can limit user entries by forcing users to choose a value from a list control. Office applications use the same functionality in built-in drop-down lists. For instance, the Highlight and Font Color controls on most Formatting toolbars use this flexible tool. Simply click the small triangle to the right of the icon to display a list of choices. You can create the same type of control for your users in an Excel sheet, but the process isn’t intuitive. The option is in the Data Validation feature. Fortunately, once you know the feature exists, it’s easy to implement. You need only two things: a list and a data entry cell. The following sheet shows a simple drop-down list in an Excel sheet. Users click the drop-down arrow to display a list of items from A1:A4. If a user tries to enter something that isn’t in the list, Excel rejects the entry. To add this drop-down list to a sheet, do the following:Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1. Select cell E3. (You can position the drop-down list in most any cell or even multiple cells.) Choose Validation from the Data menu. Choose List from the Allow option’s drop-down list. (See, they’re everywhere.) Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4). Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won’t present a drop-down list. Click OK. You can add the drop-down list to multiple cells. Select the range of data input cells (step 2) instead of a single cell. It even works for noncontiguous cells. Hold down the Shift key while you click the appropriate cells. A few quick notes:You can only see the drop-down if you click on the cell.Your users can now only choose one of the options in the drop-down. If they try to enter their own data, then they’ll receive an error message.You can copy-and-paste this drop-down cell to any other cells in your spreadsheet, and you can create as many different drop-downs like this as you’d like.Also seeBuild your Excel skills with these 10 power tips (free PDF) (TechRepublic) 13 handy Excel data entry shortcuts (free PDF) (TechRepublic)10 Excel time-savers you might not know about (free download) (TechRepublic)10 things you should never do in Excel (TechRepublic)Software usage policy (Tech Pro Research)
Читайте также: