Условное форматирование в Excel: как сделать (с примерами формул)

Чтобы применить условное форматирование в Excel, выделите ячейки, откройте Главная → Условное форматирование и выберите правило, например Правила выделения ячеек → Больше, Гистограммы или Цветовые шкалы. Для всего, чего нет в меню, например для выделения целой строки или сравнения двух столбцов, выберите Создать правило → Использовать формулу для определения форматируемых ячеек и введите формулу, которая возвращает ИСТИНА для ячеек, которые нужно отформатировать.

Большинство проблем связано с одной деталью: в формуле правила знаки $ определяют, на какие ячейки она смотрит. Если расставить их правильно (обычно $C2: столбец зафиксирован, строка свободна), правило работает в каждой строке. Если неправильно, Excel выделит не те ячейки или вообще ничего.

Названия команд и функций приведены по русской версии Excel; в английской версии они называются иначе (например, Conditional Formatting, TODAY). В русском Excel аргументы функций разделяются точкой с запятой, в английском запятой; в формулах ниже для русской версии указан русский вариант, а там, где это важно, в скобках английский.

Как использовать встроенные правила условного форматирования

Эти шаги одинаковы в Excel для Microsoft 365, 2024, 2021 и 2019 в Windows.

1. Выделите ячейки, которые нужно отформатировать, например столбец Сумма (D2:D10), без заголовка.
2. Откройте Главная → Условное форматирование. В английской версии Excel для Windows можно также нажать по очереди Alt, H, L (в русской версии буквы другие, ориентируйтесь на подсказки на ленте).
3. Наведите указатель на Правила выделения ячеек и выберите правило:
   • Больше, Меньше, Между или Равно для чисел
   • Текст содержит для слов вроде «Просрочено»
   • Дата для дат, например Вчера, За последние 7 дней, На следующей неделе или В этом месяце
   • Повторяющиеся значения, чтобы отметить повторы (или переключите список на уникальные)
4. Введите значение, выберите формат в списке с (например, Светло-красная заливка и тёмно-красный текст) или Пользовательский формат.
5. Нажмите OK.

Остальные пункты меню работают так же:

• Правила отбора первых и последних значений выделяют 10 первых элементов, первые 10%, 10 последних элементов, последние 10%, а также значения выше среднего и ниже среднего. Число 10 можно заменить любым другим.
• Гистограммы рисуют полосу внутри каждой ячейки, длиннее для больших значений (Градиентная заливка или Сплошная заливка).
• Цветовые шкалы закрашивают ячейки, плавно переходя от одного цвета к другому, например красный для малых значений и зелёный для больших.
• Наборы значков добавляют стрелки, светофоры или рейтинги в зависимости от того, где значение находится в диапазоне.

Как найти или удалить дубликаты, рассказано в руководстве по удалению дубликатов в Excel.

Как выделить всю строку по значению в ячейке

Встроенные правила форматируют только ячейки, удовлетворяющие условию. Чтобы закрасить всю строку, когда в Статусе написано «Просрочено», нужно правило с формулой. Допустим, данные находятся в A1:D100: Клиент в A, Срок оплаты в B, Статус в C, Сумма в D.

1. Выделите весь диапазон данных без строки заголовков, начиная с левой верхней ячейки: щёлкните A2 и протащите до D100.
2. Откройте Главная → Условное форматирование → Создать правило.
3. В разделе Выберите тип правила выберите Использовать формулу для определения форматируемых ячеек.
4. В поле Форматировать значения, для которых следующая формула является истинной введите: =$C2="Просрочено"
5. Нажмите Формат, откройте вкладку Заливка, выберите светло-красный цвет и нажмите OK.
6. Убедитесь, что в области Образец виден нужный формат, и нажмите OK.

Теперь каждая строка, в столбце C которой написано «Просрочено», окрашивается в красный от A до D, а при смене статуса цвет меняется вместе с ним. Регистр при сравнении не учитывается. Если статус вводят вручную, выпадающий список обеспечит единое написание, и правило не пропустит ни одной строки.

Почему важен знак $ (ошибка № 1)

Excel записывает формулу для активной ячейки, то есть первой ячейки выделения (A2 в примере выше), а затем подстраивает её под остальные ячейки, как при копировании формулы вниз и вправо.

При формуле =C2="Просрочено" ячейка A2 проверяет C2, а B2 проверяет D2, поэтому подсвечивается только часть строки. При формуле =$C$2="Просрочено" каждая ячейка проверяет C2, поэтому весь диапазон либо полностью красный, либо не выделен совсем.

Будьте внимательны: если во время ввода формулы щёлкнуть ячейку, Excel вставит абсолютную ссылку вроде $C$2. Нажимайте F4 (на Mac Cmd+T или Fn+F4), чтобы переключать варианты $C$2, C$2, $C2 и C2, пока не получите нужный.

Другие примеры формул

Выполняйте те же шаги с этими формулами. Везде предполагается, что выделение начинается со строки 2.

Форматирование ячейки по значению другой ячейки. На листе продаж, где Факт в столбце D, а План в столбце E, выделите D2:D100 и используйте: =$D2<$E2 Каждое фактическое значение ниже плана станет красным. Выделите вместо этого A2:E100, чтобы подсветить всю строку.

Сравнение с одной ячейкой ввода. Поместите порог в H1, выделите D2:D100 и используйте =$D2>$H$1. Ссылка $H$1 полностью абсолютная, поэтому все строки смотрят на одну и ту же ячейку.

Выделение прошедших дат. Выделите A2:D100 и используйте: =$B2<СЕГОДНЯ() (в английской версии =$B2<TODAY()). Пустые ячейки считаются нулём (дата в 1900 году), поэтому пустые сроки оплаты тоже подсветятся. Чтобы пропустить пустые ячейки и оплаченные счета, используйте =И($B2<>"";$B2<СЕГОДНЯ();$C2<>"Оплачено") (в английской версии =AND($B2<>"",$B2<TODAY(),$C2<>"Paid")).

Закраска каждой второй строки. Выделите данные и используйте: =ОСТАТ(СТРОКА();2)=0 (в английской версии =MOD(ROW(),2)=0). Так закрашиваются чётные строки (для нечётных используйте =1). Команда Главная → Форматировать как таблицу с чередующимися строками даёт тот же результат без правила.

Сочетание условий. Используйте И или ИЛИ (в английской версии AND и OR), например =И($C2="Ожидает";$D2>5000) для крупных неоплаченных счетов. Функция ЕСЛИ не нужна: формула должна лишь возвращать ИСТИНА или ЛОЖЬ.

Управление правилами, порядок правил и «Остановить, если истина»

Откройте Главная → Условное форматирование → Управление правилами, чтобы открыть Диспетчер правил условного форматирования.

1. В списке Показать правила форматирования для выберите Этот лист, чтобы увидеть все правила на листе.
2. Используйте Изменить правило или Удалить правило либо исправьте диапазон правила в поле Применяется к (например, =$A$2:$D$100) и нажмите Применить.
3. Стрелками Вверх и Вниз меняйте порядок правил. Правила выше в списке имеют приоритет.

Если два правила по-разному задают одно и то же, например два цвета заливки, побеждает правило выше. Если они не конфликтуют (одно делает шрифт полужирным, другое закрашивает ячейку красным), применяются оба. Форматирование по правилу также перекрывает форматирование, которое вы применили вручную.

Установите флажок Остановить, если истина, чтобы Excel, как только это правило сработает, не проверял правила ниже. Это удобно для исключений вроде «если статус „Оплачено“, больше ничего не показывать». Для правил с гистограммами, цветовыми шкалами и наборами значков этот параметр недоступен.

Чтобы убрать форматирование, используйте Главная → Условное форматирование → Удалить правила → Удалить правила из выделенных ячеек или Удалить правила со всего листа.

Как скопировать условное форматирование в другие ячейки

• Формат по образцу: выделите отформатированную ячейку, нажмите Главная → Формат по образцу, затем протащите по новым ячейкам. Дважды щёлкните кнопку, чтобы закрасить несколько диапазонов; нажмите Esc, чтобы остановиться.
• Специальная вставка: скопируйте ячейку, выделите целевые ячейки, затем Главная → Вставить → Специальная вставка → Форматы.
• Расширить само правило: измените диапазон Применяется к в Управлении правилами. Так у вас останется одно аккуратное правило вместо нескольких копий.

Excel для Mac и Excel для Интернета

В Excel для Mac то же меню Главная → Условное форматирование. Для правила с формулой выберите Создать правило, измените Стиль на Классический, в списке под ним выберите Использовать формулу для определения форматируемых ячеек, введите формулу и в поле Формат выберите Пользовательский формат.

В Excel для Интернета тоже используется Главная → Условное форматирование, но правила открываются на панели Conditional Formatting, а не в диалоговом окне. Для правила с формулой выберите New Rule и задайте тип правила Formula (названия на этой панели в русской версии могут отличаться). Правила, созданные на компьютере, работают в браузере и наоборот.

Почему условное форматирование не работает

Выделяются не те строки (со смещением на одну). Если выделение начинается с A2, формула должна ссылаться на строку 2. Обычно так бывает, когда в выделение попала строка заголовков или диапазон выделен снизу вверх.

Выделен только один столбец или вообще всё. Проверьте знаки доллара. Для правил по всей строке используйте $C2, а не C2 и не $C$2.

Числа хранятся как текст. Импортированные значения могут выглядеть как числа, но быть текстом (часто с маленьким зелёным треугольником в углу), и тогда правила вроде Больше работают неправильно. Выделите ячейки, нажмите значок предупреждения и выберите Преобразовать в число.

Текст не совпадает. Пробел в конце («Просрочено ») или другое написание не будет равно «Просрочено». Очистите данные функцией СЖПРОБЕЛЫ (TRIM) или используйте Текст содержит вместо Равно.

Правила пересекаются или конфликтуют. Откройте Управление правилами, покажите Этот лист и проверьте порядок и диапазоны Применяется к. При копировании, вставке и добавлении строк одно правило часто разбивается на множество фрагментов.

Книга стала медленной. Сотни фрагментированных правил, правила на целые столбцы (=$A:$D) и изменчивые функции вроде СЕГОДНЯ() или ДВССЫЛ (TODAY(), INDIRECT) увеличивают объём пересчётов. Удалите дубликаты и ограничьте Применяется к теми строками, которые используете.

Часто задаваемые вопросы

Как выделить строку по значению в ячейке в Excel? Выделите данные без заголовков, выберите Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек, введите =$C2="Просрочено" и выберите заливку. Знак $ перед буквой столбца заставляет менять цвет всю строку.

Как применить условное форматирование по значению другой ячейки? Используйте правило с формулой, которая ссылается на эту ячейку, например =$D2<$E2, чтобы выделить значения ниже плана, или =$D2>$H$1, чтобы сравнить каждую строку с одной ячейкой ввода.

Может ли условное форматирование ссылаться на другой лист? Да, например =$D2>Настройки!$B$1. Ссылки на другие книги не допускаются.

Можно ли сортировать или фильтровать по цвету условного форматирования? Да. Включите Данные → Фильтр, откройте стрелку фильтра столбца и выберите Фильтр по цвету или Сортировка по цвету. Это работает и с цветами, заданными правилами.

Как убрать условное форматирование, не удаляя данные? Выделите ячейки и выберите Главная → Условное форматирование → Удалить правила → Удалить правила из выделенных ячеек. Значения останутся как были.

Пусть ИИ подскажет, что выделять

Условное форматирование показывает то, что вы уже решили искать. CoreGPT для Excel работает наоборот: спросите «что выделяется в продажах за 3 квартал?», и он прочитает ваши данные и укажет лучших исполнителей, неожиданные спады и тенденции, чтобы вы знали, какие правила стоит настроить. Начать можно бесплатно, и он работает в Excel для Windows, Mac и Интернета.

Попробуйте: CoreGPT для Excel

Читайте также:

• Как удалить дубликаты в Excel: 5 способов (с удалением данных и без)
• Как сделать выпадающий список в Excel (и чтобы он обновлялся автоматически)
• XLOOKUP и VLOOKUP (ПРОСМОТРX и ВПР): отличия, примеры и что выбрать