Условное форматирование в Excel: правила, формулы, примеры
Условное форматирование в Excel автоматически меняет вид ячейки - заливку, цвет шрифта, рамку - когда значение в ней отвечает заданному условию. Включается оно на вкладке "Главная" кнопкой "Условное форматирование", а правила можно задавать как готовыми шаблонами, так и собственной формулой.

Содержание
- Что такое условное форматирование и когда оно нужно
- Как сделать условное форматирование: порядок действий
- Готовые правила выделения ячеек
- Первые и последние значения, гистограммы, цветовые шкалы и значки
- Условное форматирование по формуле
- Условное форматирование в сводной таблице
- Как управлять, копировать и удалить правила
- Частые ошибки и как их исправить
Что такое условное форматирование и когда оно нужно
Обычное форматирование вы назначаете вручную: закрасили ячейку - она так и останется. Условное работает иначе. Excel сам проверяет значения и применяет оформление только к тем ячейкам, которые подходят под правило. Изменили число - цвет пересчитался.
Типичные задачи: подсветить просроченные платежи, выделить продажи ниже плана, найти повторяющиеся фамилии, показать динамику цветовой шкалой, закрасить всю строку таблицы по статусу заказа. Всё это делается без макросов и без ручной покраски.
Условное форматирование можно применять к диапазону, к целым столбцам и строкам, к умной таблице, а в Excel для Windows и к сводной таблице. Правил на одном диапазоне может быть сколько угодно, они работают одновременно.
Как сделать условное форматирование: порядок действий
- Выделите ячейки, к которым нужно применить правило, например столбец с суммами.
- Откройте вкладку "Главная" и нажмите кнопку "Условное форматирование" в группе "Стили".
- Выберите тип правила: "Правила выделения ячеек", "Правила отбора первых и последних значений", "Гистограммы", "Цветовые шкалы" или "Наборы значков".
- Задайте условие и выберите оформление в открывшемся окне, например "Больше..." и значение 50000.
- Нажмите "ОК". Ячейки, подходящие под условие, сразу изменят вид.

Перед тем как нажать "ОК", посмотрите на предварительный просмотр: Excel показывает результат прямо на листе. Если оформление не нравится, в выпадающем списке справа есть варианты "Пользовательский формат...", где можно выбрать любую заливку, шрифт и границы.
Готовые правила выделения ячеек
Раздел "Правила выделения ячеек" содержит самые востребованные условия. Их хватает для большинства таблиц, и формулы для них писать не нужно.
| Правило | Что делает | Пример |
|---|---|---|
| Больше / Меньше | Сравнивает с числом или ячейкой | Выручка меньше плана |
| Между | Значения в заданном диапазоне | Оценки от 3 до 4 |
| Равно | Точное совпадение числа или текста | Статус равен "Оплачено" |
| Текст содержит | Часть слова в ячейке | Адреса с "Москва" |
| Дата | Вчера, на этой неделе, в прошлом месяце | Сроки на текущей неделе |
| Повторяющиеся значения | Дубли или уникальные | Одинаковые артикулы |
В поле значения можно указать не число, а адрес ячейки, например $F$1. Тогда порог хранится в одном месте, и чтобы поменять условие для всей таблицы, достаточно исправить одну ячейку.
Первые и последние значения, гистограммы, цветовые шкалы и значки
Правила отбора первых и последних значений выделяют 10 наибольших или наименьших чисел, первые и последние 10 процентов, значения выше и ниже среднего. Число 10 можно заменить на любое другое.
Гистограммы рисуют внутри ячейки полоску, длина которой пропорциональна числу. Цветовые шкалы раскрашивают диапазон плавным переходом, например от красного к зелёному. Наборы значков добавляют стрелки, светофоры и флажки по порогам значений. Эти три вида удобны для обзора большой таблицы с первого взгляда.
Если значки или шкалы ведут себя не так, как хотелось, откройте "Условное форматирование" - "Управление правилами", выберите правило, нажмите "Изменить правило" и задайте границы вручную: в процентах, числах или процентилях.
Условное форматирование по формуле
Когда готовых правил мало, выберите "Условное форматирование" - "Создать правило" - "Использовать формулу для определения форматируемых ячеек". Формула должна возвращать ИСТИНА или ЛОЖЬ. Для ячеек, где результат ИСТИНА, применится выбранный формат.
Главное правило: формулу пишут для левой верхней ячейки выделенного диапазона, а ссылки делают смешанными. Чтобы закрасить всю строку, закрепите столбец знаком доллара перед буквой.
| Задача | Формула (диапазон A2:E100) |
|---|---|
| Строка целиком, если в столбце E слово "Просрочено" | =$E2="Просрочено" |
| Срок в столбце D уже прошёл | =$D2<СЕГОДНЯ() |
| Чётные строки (зебра) | =ОСТАТ(СТРОКА();2)=0 |
| Дубликаты в столбце A | =СЧЁТЕСЛИ($A$2:$A$100;$A2)>1 |
| Пустая ячейка в обязательном столбце B | =ЕПУСТО($B2) |
Функции СЧЁТЕСЛИ и ЕПУСТО подробно разобраны в отдельных материалах. Если нужна формула с условием для самой таблицы, а не для оформления, соберите её в конструкторе формул Excel.

Если формула в окне правила не срабатывает, проверьте три вещи: нет ли лишних кавычек вокруг ссылки, правильно ли стоит знак доллара и совпадает ли формула с левой верхней ячейкой диапазона. Именно сдвиг ссылок - причина большинства ошибок.
Открыть инструмент отдельной страницей
Условное форматирование в сводной таблице
В сводной таблице правило привязывается не к ячейкам, а к полю, поэтому оформление переживает обновление и перестановку полей. Выделите ячейку с нужным значением, создайте правило и в окне внизу переключатель "Применить правило форматирования к" оставьте на "Всем ячейкам, показывающим значения" для нужного поля.
Так можно подсветить, например, регионы с суммой продаж ниже среднего. Как строить саму сводную, описано в материале про сводные таблицы в Excel. А для ввода данных по заданным вариантам удобно сочетать форматирование с выпадающими списками: выбрали статус - строка сменила цвет.
Как управлять, копировать и удалить правила
Все правила листа собраны в окне "Условное форматирование" - "Управление правилами". В списке "Показать правила форматирования для" выберите "Этот лист", и вы увидите все условия, порядок их выполнения и диапазоны. Правила можно изменять, удалять, а стрелками менять приоритет: верхнее выполняется первым.
- Чтобы скопировать правило, выделите ячейку с ним, нажмите "Формат по образцу" на вкладке "Главная" и закрасьте нужный диапазон.
- Чтобы убрать условное форматирование с диапазона, выделите его и выберите "Условное форматирование" - "Удалить правила" - "Удалить правила из выделенных ячеек".
- Чтобы очистить весь лист, выберите "Удалить правила со всего листа".
- Чтобы временно убрать оформление, удалите правило или перенесите его: флажок "Остановить, если истинно" лишь прекращает проверку правил ниже и само правило не отключает.
Копировать ячейки обычной вставкой тоже можно, но правила при этом дробятся на множество диапазонов, и в менеджере появляется десятки одинаковых строк. После такого копирования открывайте управление правилами и объединяйте диапазоны вручную.
Частые ошибки и как их исправить
Правило не срабатывает на числах, записанных как текст: ячейка с "100" слева, зелёный уголок в углу. Преобразуйте такие данные в числа, иначе сравнение "больше 50" даст неверный результат.
Две причины, по которым цвет "не тот": пересекающиеся правила и порядок приоритета. Если у двух правил разное оформление одного свойства (заливка), побеждает верхнее правило. Поднимите нужное стрелкой в менеджере правил.
Если после удаления данных остались цветные пустые ячейки, правило применено ко всему столбцу. Ограничьте диапазон в поле "Применяется к", например $A$2:$E$500. Это же ускорит работу большого файла: формулы на целых столбцах заставляют Excel пересчитывать миллионы ячеек.
Для печати на чёрно-белом принтере цветовые шкалы теряют смысл. В таких случаях используйте жирный шрифт, рамки или значки вместо заливки.
Частые вопросы
Где находится условное форматирование в Excel?
На вкладке "Главная", в группе "Стили", кнопка "Условное форматирование". Если окно Excel узкое, группа может быть свёрнута в одну кнопку "Стили", тогда нажмите на неё.
Как закрасить всю строку по значению одной ячейки?
Выделите всю таблицу, создайте правило с формулой, например =$E2="Просрочено", и выберите заливку. Знак доллара перед буквой столбца фиксирует столбец, а номер строки остаётся относительным.
Как убрать условное форматирование в Excel?
Выделите нужные ячейки, откройте "Условное форматирование" - "Удалить правила" и выберите удаление из выделенных ячеек или со всего листа. Обычная заливка, назначенная вручную, при этом останется.
Как скопировать условное форматирование в другие ячейки?
Выделите ячейку с правилом, нажмите "Формат по образцу" и протяните по диапазону. Другой путь - изменить поле "Применяется к" в менеджере правил.
Работает ли условное форматирование в сводной таблице?
Да, в Excel для Windows. Правило привязывается к полю, поэтому не пропадает при обновлении данных и перестановке полей сводной таблицы.
Нашли ошибку или устаревший пункт меню? Напишите на admin@programistik.top с адресом страницы. Перед правкой реестра, системных файлов и разделов диска делайте резервную копию.
Читайте по теме
- Как посчитать ячейки с текстом в Excel: формулы и примеры
- Функция ВПР в Excel: синтаксис, пошаговый пример, ошибки #Н/Д
- Как перевернуть таблицу в Excel: транспонирование строк и столбцов
- Как сцепить ячейки в Excel: СЦЕПИТЬ, СЦЕП, амперсанд и пробелы
- СУММЕСЛИ в Excel: как работает функция и как её применять
- Как объединить ячейки в Excel без потери данных