Чтобы сделать выпадающий список в Excel, выделите ячейку, откройте Данные → Проверка данных, в поле «Тип данных» выберите «Список» и укажите в поле «Источник» диапазон со значениями или впишите их через точку с запятой. После «ОК» справа от ячейки появится стрелка, и в ячейку можно будет выбрать только значение из списка. Так в Excel делают выбор в ячейке из готовых вариантов: статусы, города, фамилии менеджеров.
Ниже разберём, откуда брать значения, как сделать список, который пополняется сам, как связать два списка между собой и почему стрелка иногда не появляется. Если вы только осваиваете программу, начните с подборки приёмов для быстрой работы в Excel, а потом возвращайтесь сюда. Всё, что есть по программе в формате курсов, собрано в каталоге обучения Excel.
CheckroiПодборка курсов по Microsoft Excel686 курсов • 63 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Как сделать выпадающий список в Excel за пять шагов
Порядок одинаковый в Excel для Microsoft 365, 2016, 2019, 2021 и 2024. Пункты меню взяты из справки Microsoft о раскрывающихся списках.
- Введите варианты в один столбец без пустых строк, например на отдельном листе «Справочник». Заголовок над ними можно оставить, в список он не попадёт.
- Выделите ячейку или весь столбец, где нужен выбор из списка. Если таблица длинная, заранее закрепите шапку, чтобы заголовки не уезжали при прокрутке.
- На ленте откройте вкладку «Данные», в группе «Работа с данными» нажмите «Проверка данных».
- На вкладке «Параметры» в поле «Тип данных» выберите «Список». В некоторых версиях это поле подписано «Разрешить».
- Щёлкните в поле «Источник», выделите мышью ячейки с вариантами (без заголовка) и нажмите «ОК».
Флажок «Список допустимых значений» должен стоять, иначе проверка сработает, а стрелка у ячейки не появится. Флажок «Игнорировать пустые ячейки» разрешает оставить ячейку незаполненной.
Кнопка «Проверка данных» серая? Лист защищён или книга открыта в общем доступе. Снимите защиту на вкладке «Рецензирование» и повторите шаг 3.
На Mac путь тот же: «Данные» → «Проверка данных». В веб-версии Excel кнопка тоже на вкладке «Данные», но окно настроек компактнее.
Откуда брать значения для списка
У поля «Источник» три рабочих варианта. Выбор зависит от того, будет ли список меняться.
| Источник | Что вписать в поле | Когда подходит | Чем неудобен |
|---|---|---|---|
| Значения вручную | Да;Нет;Возможно |
2–5 вариантов, которые не меняются | Чтобы добавить вариант, надо открывать окно настроек |
| Диапазон на листе | =Справочник!$A$2:$A$20 |
Справочник длиннее пяти строк | Новая строка ниже A20 в список не попадёт |
| Умная таблица | =ДВССЫЛ("Города[Город]") |
Справочник растёт: клиенты, товары, сотрудники | Нужно один раз превратить диапазон в таблицу |
Значения через точку с запятой
Разделитель в русской Excel такой же, как между аргументами формул, то есть точка с запятой. Пробелы после неё не нужны: Excel сохранит их как часть варианта, и в списке появится « Нет» с пробелом в начале.
Если варианты пришли одной строкой через запятую, скажем из письма, быстрее разделить текст по столбцам, перенести результат в столбец и сослаться на него как на диапазон.
Диапазон на другом листе
Справочник удобно хранить на отдельном листе и скрыть его, чтобы никто случайно не стёр строки. Ссылку пишите со знаками доллара, иначе при копировании ячейки со списком вниз диапазон «поедет» вместе с ней.
Умная таблица: список пополняется сам
Выделите справочник и нажмите Ctrl+T (или «Главная» → «Форматировать как таблицу»). В таблице Excel новая строка сразу становится её частью, поэтому и в выпадающем списке она появится без ручной правки. Имя таблице задайте на вкладке «Конструктор таблиц», например «Города».
Таблица заодно сама протягивает формулы на новые строки, поэтому и автоматическая нумерация строк в справочнике не сбивается.
Ссылку вида Города[Город] поле «Источник» напрямую не принимает. Её оборачивают в функцию ДВССЫЛ, которая превращает текст в ссылку:
=ДВССЫЛ("Города[Город]")Если справочник уже лежит в таблице, можно просто выделить его ячейки мышью на шаге 5: Excel запомнит диапазон и будет расширять его вместе с таблицей. Так рекомендует и справка о добавлении и удалении элементов списка.
Канал основателя Checkroi Вани Буявца3 700 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяСписок без повторов и по алфавиту
Частая задача: в столбце с продажами сотни строк, менеджеры повторяются, а нужен список, где каждое имя встречается один раз. В Excel для Microsoft 365, 2021 и 2024 это решают две функции, УНИК и СОРТ. В Excel 2019 и более ранних версиях их нет.
- В свободной ячейке, например F2 на листе «Справочник», напишите формулу:
=СОРТ(УНИК(Продажи!B2:B500)) - Excel выведет имена без повторов на столько строк вниз, сколько нужно.
- В поле «Источник» выпадающего списка укажите
=Справочник!$F$2#
Решётка после адреса называется оператором перенесённого диапазона. Она берёт весь результат формулы, сколько бы строк в нём ни оказалось. Появился новый менеджер в продажах, и он сам попадёт в список.
Если в исходном столбце есть пустые ячейки, УНИК выдаст ноль строкой. Отфильтруйте их заранее:
=СОРТ(УНИК(ФИЛЬТР(Продажи!B2:B500;Продажи!B2:B500<>"")))Если такие формулы пока пугают, сначала разберитесь с функцией ВПР на простых примерах: логика ссылок на диапазоны там та же.
Как сделать зависимый выпадающий список в Excel
Зависимый, или связанный, список меняет варианты в зависимости от выбора в соседней ячейке. Выбрали в A2 «Фрукты», и в B2 видны только яблоки, груши и сливы.
Делается он через именованные диапазоны и ту же функцию ДВССЫЛ.
- На листе «Справочник» разложите варианты по столбцам. В первой строке напишите категории (Фрукты, Овощи, Ягоды), под каждой свои позиции.
- Выделите всю таблицу вместе с заголовками и выберите «Формулы» → «Создать из выделенного», флажок «в строке выше». Excel создаст по имени на каждый столбец.
- В ячейке A2 сделайте обычный список из категорий: источник
=Справочник!$A$1:$C$1. - Выделите B2, откройте «Проверка данных» → «Список» и впишите в «Источник»:
=ДВССЫЛ($A2)
Доллар стоит только перед буквой столбца. Тогда при копировании формулы вниз B3 будет смотреть на A3, B4 на A4 и так далее.
Пока A2 пустая, Excel при сохранении предупредит, что источник возвращает ошибку. Это нормально, нажмите «Да».
Главная ловушка: пробелы в названиях категорий. По правилам имён Excel пробел в имени запрещён, поэтому «Бытовая техника» превратится в имя «Бытовая_техника», и ДВССЫЛ его не найдёт. Решение: заменить пробел прямо в формуле источника:
=ДВССЫЛ(ПОДСТАВИТЬ($A2;" ";"_"))Имя также не может начинаться с цифры, так что категория «1С» без переименования не сработает. Все созданные имена видны в Диспетчере имён (Ctrl+F3), там же их можно поправить.
Подсказка и сообщение об ошибке
В окне «Проверка данных» есть ещё две вкладки, и про них часто забывают. Они нужны, когда таблицу заполняет несколько человек. Типичный пример: общий шаблон воронки продаж, где менеджеры выбирают этап сделки из списка.
- «Сообщение для ввода» показывает всплывающую подсказку при выборе ячейки. Например: «Выберите город из списка, нового города нет, пишите в общий чат».
- «Сообщение об ошибке» срабатывает, если в ячейку ввели значение не из списка.
В поле «Вид» на второй вкладке три варианта, и разница между ними большая:
| Вид | Что происходит при чужом значении |
|---|---|
| Останов | Ввод запрещён, пока не выберут значение из списка |
| Предупреждение | Excel спрашивает «Продолжить?» и пропускает значение после «Да» |
| Сообщение | Просто показывает текст и принимает любое значение |
В разных версиях первый пункт подписан «Останов» или «Остановка». Для справочников, по которым потом строят сводную таблицу, ставьте его: одна опечатка вроде «Москва » с пробелом даёт в отчёте лишнюю строку.
Поиск по списку при вводе
В Excel для Microsoft 365 и веб-версии длинный список не обязательно листать. Начните печатать в ячейке, и Excel оставит в раскрывающемся списке только совпадения, причём ищет и с середины слова.
Microsoft объявила об этом в блоге программы предварительной оценки; страница из России может не открываться, при этом сама функция в Excel работает.
Как изменить или удалить выпадающий список
Чтобы поменять варианты, выделите ячейку со списком, снова откройте «Проверка данных» и поправьте поле «Источник».
Если список стоит в сотне ячеек, перед «ОК» поставьте флажок «Распространить изменения на другие ячейки с тем же условием». Иначе правка затронет только выделенную ячейку.
Удаление занимает три клика, порядок описан в справке об удалении раскрывающегося списка:
- Выделите ячейки со списком.
- Откройте «Данные» → «Проверка данных».
- Нажмите «Очистить все» и «ОК». Значения в ячейках останутся, пропадёт только стрелка и ограничение.
Не помните, где на листе спрятаны списки? Нажмите Ctrl+G → «Выделить» → «Проверка данных» → «всех». Excel выделит все ячейки с проверкой, и их можно очистить одним действием.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналЧастые ошибки и что с ними делать
| Симптом | Причина | Что сделать |
|---|---|---|
| Стрелки у ячейки нет | Снят флажок «Список допустимых значений» | Откройте «Проверка данных» и поставьте флажок |
| В списке пустые строки | В источник попали незаполненные ячейки снизу | Сузьте диапазон или переведите справочник в умную таблицу |
| Новое значение не появляется | Источник задан обычным диапазоном, а строку добавили ниже него | Расширьте диапазон или сделайте справочник таблицей (Ctrl+T) |
| Зависимый список пустой или с ошибкой | В категории пробел или имя начинается с цифры | Используйте ПОДСТАВИТЬ, проверьте имена в Диспетчере имён |
| В ячейках со списком стоят чужие значения | Данные вставили из буфера или протянули маркером | Найдите их командой «Обвести неверные данные» |
Последний пункт удивляет многих. Проверка данных срабатывает только на ручной ввод: значение, скопированное из другой ячейки или посчитанное формулой, она пропускает молча. Это прямо сказано в справке о кружках вокруг недопустимых данных.
Решение: «Данные» → стрелка рядом с «Проверка данных» → «Обвести неверные данные». Excel обведёт красным все ячейки, где значение не из списка.
Если у вас не Excel
В марте 2022 года Microsoft остановила новые продажи в России, и многие компании перешли на российские офисные пакеты. Выпадающие списки есть и там, логика та же.
- Р7-Офис. «Данные» → «Проверка данных», в поле «Разрешить» выбрать «Список» и указать источник. Есть и флажок «Распространить изменения на все другие ячейки с тем же условием». Порядок описан в руководстве пользователя редактора таблиц, раздел «Проверка данных».
- МойОфис Таблица. На вкладке «Параметры» тип данных «Список», источник задаётся диапазоном из одного столбца или строки. Подробности в справке МойОфис о проверке данных.
- Google Таблицы. Список настраивается на панели «Правила проверки данных», в разделе «Критерии» выбирают «Раскрывающийся список из диапазона». Там же можно разрешить выбор нескольких значений, это описано в справке Google о раскрывающихся списках. Про основы сервиса есть наша инструкция для новых пользователей Google Таблиц.
Сравнение офисных пакетов по другим функциям собрано в обзоре программ для работы с электронными таблицами.
Где освоить Excel системно
Выпадающий список закрывает одну задачу: чистый ввод данных. Дальше обычно нужны формулы, сводные таблицы, Power Query и дашборды, и учить их по одной инструкции за раз долго.
Если Excel нужен для работы каждый день, посмотрите, чем занимается специалист по Excel и какие навыки от него ждут. А пройти программу целиком можно на курсах:
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 29 000 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
Все программы по Excel с ценами и отзывами собраны в каталоге курсов по Microsoft Excel.
