Как сделать выпадающий список в Excel

Выделите ячейки, откройте «Данные», «Проверка данных», выберите тип «Список» и укажите значения. Разбираем списки из диапазона, из таблицы, зависимые списки и типичные сбои.

Выпадающий список в Excel создаётся через проверку данных: выделите ячейки, где он нужен, откройте вкладку «Данные», нажмите «Проверка данных», в поле «Тип данных» выберите «Список» и в строке «Источник» впишите варианты через точку с запятой либо укажите диапазон с готовыми значениями. После нажатия «ОК» справа от ячейки появится стрелка, а выбор будет происходить мышью, без ручного ввода и опечаток.

Список из значений, набранных вручную

Подходит, когда вариантов мало и они не меняются: «Да; Нет», «Новый; В работе; Закрыт».

Выделите ячейку или целый столбец, где нужны списки.
Откройте «Данные», «Проверка данных» (значок с галочкой и кружком).
На вкладке «Параметры» в поле «Тип данных» выберите «Список».
В поле «Источник» напишите значения через точку с запятой: Новый;В работе;Закрыт.
Оставьте включёнными галочки «Игнорировать пустые ячейки» и «Список допустимых значений».
Нажмите «ОК».

Разделителем в русской версии служит точка с запятой. Если у вас стоят региональные настройки с другим разделителем списков, Excel может ожидать запятую: при ошибке попробуйте оба варианта. Пробелы вокруг разделителей Excel не отбрасывает, поэтому пишите без лишних пробелов, иначе они попадут в значение.

Список из диапазона ячеек

Для длинных или меняющихся перечней удобнее держать значения прямо на листе, например в столбце на отдельном листе «Справочник».

Впишите все варианты в столбец, по одному в ячейку, без пустых строк.
Выделите ячейки, где нужен выпадающий список.
Откройте «Проверка данных» и выберите тип «Список».
Щёлкните в поле «Источник» и мышью выделите диапазон со значениями. Excel подставит адрес, например =$A$2:$A$8.
Подтвердите кнопкой «ОК».

Перечень можно положить на другой лист: Excel позволяет выбирать диапазон с других листов той же книги. Если версия программы старая и возражает, сначала присвойте диапазону имя (вкладка «Формулы», «Присвоить имя») и введите в источнике =ИмяСписка.

Список, который растёт сам

Недостаток обычного диапазона: добавили вариант внизу столбца, а в выпадающем списке его нет, потому что адрес зафиксирован. Решение - превратить справочник в умную таблицу.

Выделите значения справочника и нажмите Ctrl+T (поставьте галочку «таблица с заголовками» при наличии шапки).
На вкладке «Конструктор таблиц» дайте таблице имя, например Статусы.
Присвойте имя самому столбцу: выделите значения без заголовка и задайте имя СписокСтатусов через «Формулы», «Присвоить имя», в поле «Диапазон» укажите =Статусы[Статус].
В источнике проверки данных введите =СписокСтатусов.

Теперь любое новое значение, дописанное под последней строкой таблицы, сразу появится в раскрывающемся перечне во всех ячейках. Удалённое значение пропадёт тоже. Для живого справочника это самый удобный путь, и формулы с функцией СМЕЩ, которые встречаются в старых инструкциях, не нужны.

Зависимые списки: страна и город

Бывает, что вариант во втором списке должен зависеть от первого. Выбрали «Фрукты» - во второй ячейке только яблоки и груши, выбрали «Овощи» - морковь и капуста. Делается это через именованные диапазоны и функцию ДВССЫЛ.

Подготовьте столбцы-справочники: над каждым запишите название категории («Фрукты», «Овощи»), под ним - варианты.
Присвойте каждому столбцу имя, совпадающее с заголовком (для этого выделите столбец с заголовком и на вкладке «Формулы» нажмите «Создать из выделенного», оставив галочку «в строке выше»).
В первой ячейке создайте обычный список категорий.
Во второй ячейке при настройке проверки данных в источнике введите =ДВССЫЛ(A2), где A2 - адрес первой ячейки.

Теперь содержимое второго списка определяется выбором в первом. Есть ограничение: имена не могут содержать пробелов. Вместо «Мясные продукты» придётся писать «Мясные_продукты» и добавить к ним замену пробела в формуле: =ДВССЫЛ(ПОДСТАВИТЬ(A2;" ";"_")).

Подсказка и сообщение об ошибке

Окно проверки данных содержит ещё две вкладки, и они делают таблицу удобнее для коллег.

Сообщение для ввода. Текст, который всплывает рядом с ячейкой при выборе: «Выберите статус из списка». Включается галочкой «Отображать подсказку, если ячейка является текущей».
Сообщение об ошибке. Решает, что произойдёт, если кто-то введёт значение вручную, а не выберет. Вид «Останов» блокирует ввод посторонних слов, «Предупреждение» спрашивает подтверждение, «Сообщение» просто уведомляет. Для строгого справочника выбирайте «Останов» и впишите понятный текст ошибки.

Если нужно разрешить и свои варианты, оставьте вид «Предупреждение» или снимите галочку «Выводить сообщение об ошибке». Список при этом останется как подсказка, но не станет ограничением.

Как выбирать значения с клавиатуры

Мышь не обязательна. Встаньте в ячейку со списком и нажмите Alt и стрелку вниз: список раскроется, стрелками выберите вариант, Enter подтвердит. Если вводить первые буквы вручную, Excel подставит подходящий вариант сам, но только в пределах значений того же столбца. Для работы с проверкой данных надёжнее открывать список клавишами.

Как изменить, скопировать и убрать список

Изменить список. Выделите ячейку, откройте «Проверка данных» и поправьте источник. Если список стоит во многих клетках, поставьте галочку «Распространить изменения на другие ячейки с тем же условием», и правка применится ко всем.

Скопировать. Обычное копирование и вставка (Ctrl+C, Ctrl+V) переносит список вместе со значением. Если нужно скопировать только правило, а значение оставить прежним, используйте специальную вставку, пункт «Условия на значения».

Убрать. Выделите ячейки, откройте «Проверка данных» и нажмите «Очистить все». Стрелка пропадёт, а введённые значения останутся в ячейках.

Почему список не работает

Стрелка не появляется. Проверьте, что в окне проверки данных включена галочка «Список допустимых значений». Без неё стрелка скрыта, хотя ограничение действует. Ещё один случай: ячейка находится в режиме редактирования, нажмите Esc.
Вместо вариантов пустота. Источник указывает на пустые ячейки либо на диапазон другого файла, который закрыт. Ссылки на другие книги в проверке данных не работают, пока исходник не открыт. Держите справочник в той же книге.
Значения пропадают после вставки. Вставка скопированных ячеек поверх ячейки со списком стирает правило. Проверка данных не защищает от вставки: чтобы выявить посторонние значения, на вкладке «Данные» в меню проверки выберите «Обвести неверные данные».
В списке видны дубли. Excel показывает ровно то, что лежит в диапазоне. Уберите повторы на вкладке «Данные» командой «Удалить дубликаты» в справочнике.
Не вмещается длинный список. Для вручную набранных значений существует ограничение на общую длину строки в источнике, порядка двух с лишним сотен знаков. Если вариантов много, выносите их в диапазон.

Где такие списки пригодятся

Выпадающие перечни незаменимы в таблицах учёта и отчётах: статусы заявок, ответственные, категории расходов, названия товаров. Они избавляют от опечаток, из-за которых сводные таблицы считают «Москва» и «москва ». Например, чтобы подвести итоги по группам, лучше сначала привести данные к единым названиям через список, а потом строить сводную таблицу.

Если справочник нужно подготовить на основе PDF-документа или сканированного бланка, пригодится статья как распознать текст с картинки. А когда файл с настроенными списками был испорчен или перезаписан, помогут способы из материала как восстановить предыдущую версию файла Excel.

Итог: для двух-трёх фиксированных вариантов достаточно ввести их в источник через точку с запятой, для рабочего справочника - держите значения в умной таблице и ссылайтесь на неё по имени, а зависимые списки стройте через именованные диапазоны и ДВССЫЛ.