Как убрать условное форматирование в excel

Обновлено: 02.07.2024

Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

При нажатии на стрелочку справа открывается меню для условий форматирования.

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

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

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

Выделите первую ячейку диапазона с условным форматированим.
Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование даты в Excel

Выделяем диапазон с датами.

Применим к нему «УФ» - «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

Задача: выделить цветом строку, содержащую ячейку с определенным значением.

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

Условное форматирование – это инструмент в Excel, который служит для присвоения особенного формата ячейкам или целым диапазонам ячеек на основе критериев условий определенных пользователем. Вы познакомитесь с примерами использования условий основанных на сложных формулах. А так же научитесь управлять такими функциями как:

поля данных;
цветовая палитра;
настройка шрифтов.

Научитесь работать со значениями, которые можно вставлять в ячейки в зависимости от их содержимого.

Как сделать условное форматирование в Excel

Для начала рассмотрим: как подбирать соответствующие критерии форматирования и как их изменять. Принцип его действия проще всего понять на готовом примере:

Допустим столбец, содержит диапазон ячеек с числовыми значениями. Если определить их соответствующим условием форматирования, то все значения с числом более чем 100 будут отображаться красным цветом. Чтобы реализовать эту задачу данный инструмент Excel проанализирует в соответствии с условиями критериев значение каждой ячейки заданного диапазона. Результаты анализа дают положительный результат, например (A2>100=ИСТИНА), тогда будет присвоен предварительно заданный новый формат (красный цвет). В противоположном результате (A2>100=ЛОЖЬ), формат ячеек не изменяется.

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

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

Данный инструмент Excel предоставляет к вашему распоряжению 3 правила для форматирования, которые могут взаимно выключать значения несоответствующие заданным критериям. Принцип использования нескольких условий в условном форматировании рассмотрим на простом примере.

Допустим в ячейке A1 находиться числовое значение 50:

Определим следующие условия для формата отображения значений в A1:

Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
Если число больше чем 30, то шрифт будет отображаться желтым цветом.
Если число больше чем 40, то шрифт будет отображаться красным.

Определенно вы заметили, что значение 50 в ячейке A1 соответствует всем условиям (A1>15, A1>30 и A1>40 = ИСТИНА). Каким же цветом шрифта Excel отобразит числовое значение 50?

Ответ следующий: формат будет присвоен тот, который соответствует последнему условию. А следовательно это красный цвет . Важно запомнить об этом принципе, когда нужно конструировать более сложные условия.

Примечание. В старых версиях Excel при определении условий форматирования необходимо было сосредотачивать свое внимание на то, чтобы условия не накладывались друг на друга. Данная ситуация чаще всего применяется, когда экспонированные данные должны уступить значениям на определенном уровне. Но начиная с версии Excel 2010 нет никаких ограничений при наложении условий.

Создать второе правило

Второй пример. Допустим нам нужно отформатировать расходы в колонке C следующим образом:

Все суммы в пределах 300$-600$ должны закрасить свои ячейки желтым фоном, а суммы меньше чем 500$ должны еще иметь и красный цвет шрифта.

Попробуем сконструировать данные условия:

Выделите диапазон ячеек C4:C7 и выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
В появившемся окне нажмите на кнопку «Создать правило».
В окне «Создание правила форматирования», выберите тип правила «Форматировать только ячейки, которые содержат».
В первом выпадающем списке укажите «Значение ячейки», во втором «между». А в остальных двух введите границы придела 300 и 600.
Нажмите на кнопку «Формат» и на вкладке «Заливка» укажите желтый цвет.
Нажмите «ОК» для возврата к окну «Диспетчер правил условного форматирования».
Снова нажмите на кнопку «Создать правило» и снова же выберите тип правила «Форматировать только ячейки, которые содержат».
Первый выпадающий список оставляем без изменений «Значение ячейки», а во втором выбираем опцию «меньше». В поле ввода указываем значение 500.
Нажмите на кнопку «Формат» и на вкладке «Шрифт» укажите красный цвет.
На всех окнах нажмите «ОК».

Обратите внимание, в какой способ Excel применил форматирование. Суммы в ячейках C10, С13 и С15 соответствуют обоим условиям. Поэтому к ним применены оба стиля форматирования. А там где значение соответствует только одному из условий, они отображаются соответственным форматам.

Если в выбранном диапазоне есть пустые ячейки, при использовании формулы в правиле условного форматирования пустые ячейки будут отформатированы. Для удаления условного форматирования из пустых ячеек (не отображать условное форматирование в пустых ячейках) в Excel есть два метода. Пожалуйста, просмотрите приведенное ниже руководство для получения более подробной информации.

Удалите условное форматирование из пустых ячеек с добавлением нового правила для пробелов

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

1. Выберите диапазон с условным форматированием, которое вы создали ранее.

2. Нажмите Условное форматирование > Управление правилами под Главная таб. Смотрите скриншот:

3. в Диспетчер правил условного форматирования диалогового окна, нажмите Новое правило кнопку.

4. в Новое правило форматирования диалоговое окно, вам необходимо:

1). в Выберите тип правила раздел, выберите Форматировать только ячейки, содержащиеn; 2). в Редактировать описание правила раздел, выберите Пробелы в первом Форматировать только ячейки с раскрывающийся список; 3). Сохраните формат в Формат не задан положение дел; 4). Щелкните значок OK кнопка. Смотрите скриншот:

5. Когда он вернется в Диспетчер правил условного форматирования диалоговое окно, проверьте Остановить, если истина поле для нового правила пробелов, а затем щелкните OK кнопку.

6. После этого вы увидите, что все пустые ячейки в выбранном диапазоне добавлены без установленного формата.

Удалите условное форматирование из пустых ячеек с помощью формулы

Помимо описанного выше метода, здесь я покажу вам формулу для удаления условного форматирования из пустых ячеек. Как и в предыдущем случае, для пустых ячеек применяется условное правило отсутствия формата.

1. Выберите диапазон, в котором вы добавили условное форматирование.

2. Затем перейдите в Главная вкладку нажмите Условное форматирование > Управление правилами.

3. в Диспетчер правил условного форматирования диалогового окна, нажмите Новое правило кнопку.

4. в Новое правило форматирования диалоговое окно, выполните следующие действия шаг за шагом.

1). Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел; 2). Скопируйте и вставьте формулу = ISBLANK (A1) = ИСТИНА в Изменить значения, в которых эта формула верна коробка; 3). Держать Формат не задан положение дел; 4). Щелкните значок OK кнопка. Смотрите скриншот:

Внимание: A1 в формуле - это первая ячейка в выбранном диапазоне. Вам необходимо перейти на выбранный вами.

5. в Диспетчер правил условного форматирования диалоговое окно, проверьте Остановить, если истина поле для нового созданного правила, а затем щелкните OK кнопку.

В Excel вы можете применить условное форматирование для форматирования ячеек или строк на основе определенного критерия. Но в некоторых случаях может потребоваться удалить правила условного форматирования, но оставить формат только в ячейках. Здесь, в этой статье, вы можете найти уловки по удалению условного форматирования, но сохранению формата в Excel.

Легко форматируйте ячейки на основе критериев Kutools for Excel

Удалите условное форматирование, но сохраните формат с помощью VBA

В Excel нет прямого метода удаления условного форматирования, кроме VBA.

1. Включите лист, на котором вы хотите удалить условные правила форматирования, но сохранить формат, и нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.

Нажмите 2 Вставить > Модульи вставьте ниже VBA в скрипт модуля. Смотрите скриншот:

VBA: удалите правила условного форматирования, но сохраните формат.

3. Нажмите F5 нажмите клавишу для запуска кода, и появится диалоговое окно для выбора диапазона для удаления условного форматирования. Смотрите скриншот:

4. Нажмите OK, правила условного форматирования были удалены из указанного диапазона, но сохранить формат.

Легко форматируйте ячейки на основе критериев Kutools for Excel

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

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите диапазон, который нужно отформатировать, и нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:

2. в Выбрать определенные ячейки диалоговом окне выберите нужный вариант из Тип выбора раздел, а затем укажите критерии из раскрывающегося списка и текстового поля под Конкретный тип раздел. Смотрите скриншот:

3. Нажмите Ok. Затем появляется диалоговое окно, в котором показано, сколько ячеек было выбрано. Пожалуйста, нажмите OK чтобы закрыть это диалоговое окно.

4. Затем вы можете увидеть, что ячейки на основе критериев были выбраны, а затем щелкните правой кнопкой мыши выбранные ячейки и выберите Формат ячеек формируем контекстное меню, а во всплывающем Формат ячеек в диалоговом окне укажите нужный формат. Смотрите скриншот:

5. Нажмите OK. Теперь выбранные ячейки отформатированы без каких-либо правил условного форматирования. Смотрите скриншот:

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