Условное форматирование в Excel и Google Таблицах: как сделать и примеры

Условное форматирование в Excel само раскрашивает ячейки по правилам, которые вы задали: суммы больше 5000 становятся зелёными, просроченные счета красными, повторяющиеся адреса почты подсвечиваются, а в ячейках появляются полоски и значки светофора. Когда данные меняются, цвет меняется вместе с ними, ничего не нужно перекрашивать руками.

Ниже разберём, как сделать условное форматирование по шагам: цвет ячейки по значению, выделение дубликатов, правило по формуле для всей строки, гистограммы, цветовые шкалы и значки, а затем управление правилами и типичные ошибки. В конце есть раздел про Google Таблицы. Все примеры я собрал в Microsoft Excel 2021 (сборка 16.0.17002.20000) с русским интерфейсом, картинки сняты прямо из него. Учебный файл со всеми правилами можно скачать и смотреть, как они устроены.

Где найти условное форматирование в Excel

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

Есть и быстрый путь: выделите диапазон и нажмите Ctrl+Q. Откроется «Экспресс-анализ», где на вкладке «Форматирование» есть гистограммы, цветовые шкалы и самые частые правила.

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

Как сделать условное форматирование в Excel: цвет ячейки по значению

Начнём с самого частого случая: подсветить крупные и мелкие суммы в списке заказов. В учебном файле это лист «Заказы».

Больше, меньше, между

1. Выделите столбец с суммами без заголовка (в примере E2:E13).
2. Главная → Условное форматирование → Правила выделения ячеек → Больше.
3. В левом поле введите 5000, в правом выберите «Зелёная заливка и тёмно-зелёный текст». Нажмите ОК.
4. Не снимая выделения, снова откройте Правила выделения ячеек, выберите Меньше, введите 1000 и выберите «Светло-красная заливка и тёмно-красный текст».

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

Обратите внимание на заказ 1010 на 5 000 ₽: он остался без цвета. Правило «Больше» строгое, ровно 5000 под него не попадает. Если граница должна входить, создайте правило через Создать правило → «Форматировать только ячейки, которые содержат» и выберите «больше или равно». Там же есть «между» и «не между».

Число в правиле можно не вписывать, а сослаться на ячейку: щёлкните по ней, когда курсор стоит в поле значения. Excel подставит адрес вида =$H$1, и порог можно будет менять, не открывая правило.

Текст и даты

В том же меню Правила выделения ячеек есть ещё три полезных пункта:

• Текст содержит ищет кусок текста в любом месте ячейки. По справке Microsoft, строка в условии может быть не длиннее 255 знаков.
• Дата выделяет даты за вчера, сегодня, завтра, последние 7 дней, прошлую, текущую или следующую неделю и такие же месяцы. Период отсчитывается от текущей даты компьютера, поэтому подсветка со временем меняется сама.
• Равно подходит и для чисел, и для текста целиком.

Регистр букв правилу «Текст содержит» не важен: я проверил, условие «Отмен» подсветило и «отменён», и «ОТМЕНЁН».

Как выделить дубликаты в Excel

Повторы в списке клиентов, артикулов или адресов почты находит готовое правило.

1. Выделите столбец, где ищете повторы (в примере B2:B11 на листе «Клиенты»).
2. Главная → Условное форматирование → Правила выделения ячеек → Повторяющиеся значения.
3. В левом списке оставьте «повторяющиеся» (там же можно выбрать «уникальные»), справа выберите цвет и нажмите ОК.

На этом примере видно три особенности, которые я проверил на своём файле:

• Подсвечиваются все копии, включая первое появление. Если оба «petrova@yandex.ru» красные, непонятно, какую строку оставить.
• Регистр не важен. «ivanov@mail.ru» и «IVANOV@mail.ru» Excel считает одинаковыми.
• Лишний пробел делает значение уникальным. У второй Козловой адрес записан с пробелом в конце, и правило его пропустило. Проверить это помогает функция ДЛСТР: у «kozlova@mail.ru» длина 15 знаков, у записи с пробелом 16.

Подсветить только повторы и всю строку

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

1. Выделите всю таблицу без заголовка, A2:C11.
2. Условное форматирование → Создать правило → «Использовать формулу для определения форматируемых ячеек».
3. Введите формулу и выберите заливку через кнопку Формат:

=СЧЁТЕСЛИ($B$2:$B2;$B2)>1

Формула считает, сколько раз адрес из текущей строки встречался от начала списка до этой строки. Первое появление даёт 1, и строка остаётся белой, второе даёт 2, и строка краснеет. Начало диапазона $B$2 закреплено полностью, а конец $B2 сдвигается вместе со строкой.

Когда повторы найдены, удалить их можно через Данные → Удалить дубликаты. Microsoft предупреждает, что удаление необратимо, поэтому перед ним скопируйте данные на другой лист. Пробелы в конце сначала уберите функцией СЖПРОБЕЛЫ, иначе такие строки не будут считаться повторами.

Условное форматирование по формуле: выделить всю строку

Готовые правила красят только те ячейки, в которых проверяют значение. Чтобы перекрасить всю строку по значению в одном столбце, нужно условное форматирование по формуле. Пример на листе «Оплата»: список счетов со сроком оплаты и статусом, а в ячейке H1 дата отчёта 15.10.2026.

Задача такая: неоплаченные просроченные счета красим красным, счета со сроком в ближайшие три дня жёлтым, оплаченные пишем серым.

1. Выделите всю таблицу без заголовка, начиная с левой верхней ячейки: A2:E11.
2. Главная → Условное форматирование → Создать правило.
3. Выберите «Использовать формулу для определения форматируемых ячеек».
4. В поле «Форматировать значения, для которых следующая формула является истинной» введите формулу первого правила (см. таблицу ниже).
5. Нажмите Формат, на вкладке «Заливка» выберите цвет, на вкладке «Шрифт» цвет текста, затем ОК и ещё раз ОК.
6. Повторите шаги 2-5 для двух других правил.

Формула должна начинаться со знака «=» и возвращать ИСТИНА или ЛОЖЬ. Excel проверяет её для каждой ячейки диапазона, сдвигая ссылки так же, как при копировании обычной формулы. Поэтому формулу пишут так, будто она стоит в первой ячейке выделения (A2), а ссылки закрепляют знаком $ там, где сдвиг не нужен.

Вместо ячейки с датой отчёта можно взять функцию СЕГОДНЯ(): =И($E2<>"Оплачен";$D2<СЕГОДНЯ()). Тогда цвета будут меняться каждый день сами. В учебном файле я оставил ячейку H1, чтобы картинка совпадала с файлом в любой день.

В английском Excel та же формула записывается так: =AND($E2<>"Оплачен",$D2<$H$1). Функции по-английски, а аргументы разделяются запятой.

Знак $ в формуле: главная ошибка

Правило «столбец закрепить, строку нет» выглядит просто, но ошибиться легко. Если в поле формулы щёлкнуть мышью по ячейке, Excel вставит адрес с двумя долларами, $E$2. С такой формулой каждая ячейка таблицы проверяет вторую строку, а не свою:

=И($E$2<>"Оплачен";$D$2<$H$1)

Счёт С-01 во второй строке просрочен, поэтому условие истинно для всех ячеек, и красной стала вся таблица, включая оплаченные счета:

Как запомнить:

Вторая частая причина сдвига: формула написана не для первой строки выделения. Если вы выделили A2:E11, а в формуле стоит $E3, каждая строка будет окрашиваться по соседней снизу.

Гистограммы, цветовые шкалы и значки

Эти три вида оформления не требуют формул: выделите числа и выберите вариант в меню. Они показывают не «да или нет», а величину значения.

Цветовые шкалы

Цветовая шкала заливает ячейки плавным переходом цветов. Трёхцветная шкала «зелёный, жёлтый, красный» превращает таблицу в тепловую карту: большие значения зелёные, средние жёлтые, малые красные. Выделите числа (на листе «Месяцы» это B2:G9) и выберите Условное форматирование → Цветовые шкалы → вариант с нужным порядком цветов.

Пороги шкалы по умолчанию такие: минимум диапазона, 50-й процентиль посередине и максимум. Я проверил это на своём файле: Excel создал среднюю точку типа «процентиль» со значением 50. Поменять пороги и цвета можно в Управлении правилами → Изменить правило: там для каждой точки выбирается тип (наименьшее значение, число, процент, процентиль, формула) и цвет.

Учтите, что шкала на всю таблицу сравнивает все ячейки между собой. В примере флешки всегда зелёные, а мини-ПК всегда красные, хотя продажи мини-ПК за полгода выросли с 3 до 8 штук. Если важна динамика каждого товара, применяйте шкалу к каждой строке отдельно.

Гистограммы

Гистограмма рисует в ячейке полоску, длина которой зависит от значения. Выделите числа (B2:B7 на листе «План») и выберите Условное форматирование → Гистограммы → градиентную или сплошную заливку.

В Изменении правила гистограммы есть галочка «Показывать только столбец»: число исчезнет, останется одна полоска. Там же настраиваются направление, граница и отдельный цвет для отрицательных значений.

Наборы значков

Набор значков делит значения на 3, 4 или 5 групп и ставит рядом с каждым числом стрелку, светофор, флажок или рейтинг. Всего в Excel 2021 двадцать наборов, это видно и в меню, и через свойство IconSets книги. Выделите проценты выполнения плана (D2:D7) и выберите Условное форматирование → Наборы значков → «Три светофора».

Здесь кроется ловушка. По умолчанию пороги заданы в процентах от разброса значений: зелёный значок получают значения из верхней трети разброса (от 67 %), красный из нижней (ниже 33 %). Это не 67 % плана, а 67 % пути от худшего результата к лучшему. В примере минимум 66 % плана, максимум 111 %, и Сергей с 98 % получил зелёный, хотя план не выполнил.

Чтобы зелёный означал «план выполнен», задайте пороги вручную:

1. Условное форматирование → Управление правилами, выберите правило со значками → Изменить правило.
2. Для зелёного значка выберите тип «Число» и значение 1 (это 100 %: значение 103 % хранится в ячейке как 1,03).
3. Для жёлтого выберите тип «Число» и значение 0,8.
4. Нажмите ОК.

В том же окне есть «Обратный порядок значков» и «Показать только значок». Пороги, по справке Microsoft, задаются от большего к меньшему. Если значок не помещается, расширьте столбец: размер значка зависит от размера шрифта.

Как изменить, удалить и упорядочить правила

Все правила, которые задаёт условное форматирование в Excel, собраны в окне Главная → Условное форматирование → Управление правилами. Наверху окна выберите «Показать правила форматирования для: Этот лист», иначе увидите только правила выделенных ячеек.

В списке для каждого правила видно формулу или условие, формат и диапазон в столбце «Применяется к». Диапазон можно исправить прямо там, не создавая правило заново.

Порядок имеет значение. Правило выше в списке важнее. Если правила не спорят (одно делает шрифт жирным, другое красит фон), применяются оба. Если оба задают заливку, побеждает верхнее. На листе «Оплата» просроченные счета подходят и под красное правило, и под жёлтое (разница дат там отрицательная, то есть меньше трёх). Красными они остаются только потому, что красное правило стоит первым. Я проверил это: когда я поднял жёлтое правило наверх, счёт С-01 стал жёлтым. Порядок меняется стрелками «Вверх» и «Вниз» в окне управления.

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

Убрать правила можно двумя способами:

• Условное форматирование → Удалить правила → «Удалить правила из выделенных ячеек» или «Удалить правила со всего листа».
• В Управлении правилами выбрать правило и нажать Удалить правило.

Перенести правила на другой диапазон проще всего кнопкой Формат по образцу (кисточка на вкладке «Главная»). При копировании ячеек правила тоже копируются вместе с ними, и в «Управлении правилами» потом может оказаться много одинаковых правил на кусочках диапазона. Их стоит объединить: оставить одно и поправить у него «Применяется к».

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

Условное форматирование в Google Таблицах

В Google Таблицах условное форматирование устроено похоже, но вариантов меньше. Раздел ниже написан по официальной справке Google, сам я его в Таблицах не проверял.

1. Выделите ячейки.
2. Откройте меню Формат → Условное форматирование. Справа появится панель правил.
3. Выберите вкладку правила:
   • Один цвет: в разделе «Форматирование ячеек» выберите условие, в разделе «Формат» цвет и шрифт.
   • Градиент: аналог цветовой шкалы. Выберите цветовую гамму и задайте минимум, максимум и при необходимости среднюю точку.

Для своих условий в списке «Форматирование ячеек» есть пункт Ваша формула. Правила для строк строятся так же, как в Excel, со знаком $ перед буквой столбца. Примеры из справки Google:

=$B1="Да"
=СЧЁТЕСЛИ($A$1:$A$100;A1)>1

Первая формула выделяет строку, если в столбце B стоит «Да», вторая подсвечивает повторы в диапазоне A1:A100. Формулу пишут для первой строки выделенного диапазона.

Отличия от Excel, которые стоит знать:

• Ссылка на другой лист в формуле правила напрямую не работает. Справка Google советует функцию ДВССЫЛ (INDIRECT).
• Порядок правил тоже важен: по справке Google, правила применяются в порядке следования. Порядок меняется перетаскиванием правила в панели.
• Гистограмм и наборов значков в условном форматировании Google Таблиц нет, справка описывает только «Один цвет» и «Градиент». Полоску в ячейке можно нарисовать функцией SPARKLINE с параметром charttype равным bar. Пример из справки Google: =SPARKLINE(A2:E2,{"charttype","bar";"max",40}). Он записан с английскими разделителями, в таблице с русскими региональными настройками разделители аргументов могут отличаться.

Правила копируются вместе с ячейками и в Google Таблицах. Учебный файл xlsx можно загрузить в Google Таблицы, но как перенесутся правила со значками и гистограммами, я не проверял.

Частые вопросы