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

Обновлено: 06.01.2025

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

Процесс выравнивания размеров

Выполнить данную задачу можно двумя путями:

настроить ширину столбцов – в пределах от 0 до 255 пунктов (по умолчанию – 8,43)

Примечание: примерный размер одного пункта в сантиметрах – 0,35 мм.

Стандартные единицы измерения можно изменить на свое усмотрение. Вот, что нужно для этого сделать:

1. Переходим в меню “Файл”, где находим раздел “Параметры” и кликаем по нему.
2. В появившихся параметрах в перечне слева щелкаем по подразделу “Дополнительно”. В правой части окна находим группу настроек под названием “Отображение” (“Экран” – в более старых версиях программы). Находим параметр “Единицы на линейке”, щелкаем по текущему варианту, чтобы раскрыть список всех доступных опций и выбираем:
   • дюймы
   • сантиметры
   • миллиметры
3. Определяемся с нужным вариантом, после чего нажимаем кнопку OK.
4. Данная процедура позволяет задать наиболее удобную единицу измерения, которая будет дальше участвовать в настройке ширины столбцов и высоты строк таблицы.

Метод 1: выравниваем область ячеек

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

Рассмотренный выше алгоритм действий является не единственным. Для того, чтобы задать нужные размеры, можно воспользоваться панелями координат:

Метод 2: выравниваем все ячейки листа

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

1. Конечно же, выделять вручную все ячейки листа не нужно. Просто щелкаем по небольшому прямоугольнику, который находится между вертикальной и горизонтальной панелями координат. Также для выполнения данной процедуры можно воспользоваться комбинацией клавиш Ctrl+A.
2. Теперь, когда все ячейки листа выделены, мы можем настроить их высоту и ширину, пользуясь методом 1, описанным выше.

Метод 3: ручная настройка размера путем сдвига границ

Данный метод предполагает работу с границами столбцов и строк. Вот, как он реализуется:

1. Выделяем диапазон или все столбцы листа (в зависимости от поставленной задачи) на горизонтальной панели координат. Затем наводим курсор на границу любого столбца выделенной области (именно на панели координат). Когда он сменит вид на небольшой плюсик со стрелками в обе стороны, зажав левую кнопку мыши двигаем его влево или вправо, чтобы раздвинуть или сдвинуть границу. Так как мы выделили сразу диапазон, то и изменения будут применены ко всем ячейкам в нем.
2. Ширина всех столбцов выделенного диапазона выровнена.
3. Теперь аналогичным образом выравниваем строки таблицы. Для этого на вертикальной координатной панели выделяем нужные строки (или все строки) и сдвигаем границы любой из строк.
4. Все готово. Размеры ячеек выделенного фрагмента выровнены как по высоте, так и по ширине.

Примечание: если нужно задать одинаковые размеры всех ячеек листа, можно сразу их выделить, нажав комбинацию клавиш Ctrl+A (или кликнув по специальному прямоугольнику между панелями координат), после чего, также, с помощью сдвига границ строк и столбцов задать требуемые значения.

Метод 4: выравнивание при вставке таблицы

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

1. Для начала нам нужно выделить исходную таблицу, которую мы хотим скопировать. После это в группе инструментов “Буфер обмена” вкладки “Главная” щелкаем по кнопке “Копировать”. Также для копирования можно воспользоваться комбинацией клавиш Ctrl+C.
2. Встаем в ячейку (в другом месте этого листа или на другом листе/книге), куда планируем вставить скопированную таблицу. Именно эта ячейка будет являться самым верхним левыми элементом вставленной таблицы. Щелкаем по ней правой кнопкой мыши, в появившемся окне выбираем “Специальная вставка” (именно по надписи, а не на стрелку рядом с ней).
3. В появившемся окне в группе параметров “Вставить” отмечаем пункт “Ширины столбцов”, после чего кликаем OK.
4. В результате произойдет автоматическая настройка размеров ячеек в соответствии с выделенной таблицей на исходном листе.
5. Теперь, мы можем вставить сюда скопированную таблицу, и она будет выглядеть абсолютно также, как и на начальном листе. Для этого щелкаем по любому месту выделенной области правой кнопкой мыши и в раскрывшемся перечне выбираем команду “Вставить”. Либо можно кликнуть по кнопке “Вставить” во вкладке “Главная”, группа инструментов “Буфер обмена”, или нажать комбинацию клавиш Ctrl+V.
6. Процесс копирования таблицы из одного места в другое с сохранением идентичных размеров ячеек благополучно завершен.

Заключение

Стиль в Microsoft Excel - это сохраненная совокупность параметров форматирования ячейки. Единожды создав стиль, его затем можно многократно применять к другим ячейкам, моментально оформляя их нужным вам образом, что неимоверно ускоряет повседневную работу в Excel.

Весь список стилей доступен на вкладке Главная в группе Стили (Home - Styles) :

Важно отметить, что стиль - это не только внешнее оформление (цвет, шрифт, заливка), как многие думают. В стиле ячейки Excel можно сохранить:

• Числовой формат (денежный, процентый, даты/времени. ), в том числе и пользовательский (кг, шт, °С. )
• Параметры выравнивания (по горизонтали и вертикали), направление текста, перенос по словам.
• Параметры шрифта (цвет, начертание, размер. )
• Цвет и тип заливки (сплошная, градиент)
• Обрамление ячейки (тип, толщина и цвет линий с каждой стороны и внутри)
• Параметры защиты (нужно ли защищать ячейку и прятать в ней формулы при включении защиты листа)

Как создать стиль

Выделите ячейку, оформление которой хотите сохранить в стиле (или пустую ячейку, если хотите создать стиль "с нуля") и на вкладке Главная в группе Стили выберите команду Создать стиль ячейки (New cell style) .

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

Чтобы создать стиль на основе уже имеющегося стиля, можно просто щёлкнуть правой кнопкой мыши по нужному стилю и выбрать команду Дублировать (Duplicate) :

Там же в контекстном меню любой стиль можно удалить или изменить его параметры.

Всё весьма несложно и даже слегка скучновато, не правда ли? Так что давайте перейдем к практическим примерам.

Стиль для целых чисел с разделителями

Начнем простого для затравки.

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

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

Кстати, третья иконка со значком "000", про которую обычно думают в этом случае, делает совершенно не то, что нужно - она не убирает дробную часть, да ещё и зачем-то выводит вместо нуля прочерк, как в финансовом формате. Совсем не оно.

Давайте устраним этот недостаток, создав свой стиль. Выбираем, как было описано выше, последовательно Главная - Стили - Создать стиль ячейки (Home - Styles - New cell style) . В окне создания стиля введём его имя и снимем все галочки, кроме первой, чтобы создаваемый нами стиль не влиял ни на какие другие параметры ячейки, кроме числового формата. Затем жмём кнопку Формат (Format) и в знакомом окне выбираем Числовой (Number) без десятичных знаков и с разделителем групп разрядов:

Жмём на все ОК и теперь у нас есть стиль для наглядного форматирования чисел - красота!

Стили для своих единиц измерения (шт, чел, упак, кг. )

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

Выполняем те же действия, что и в предыдущем пункте, но в окне Формат ячеек выбираем на вкладке Число опцию (все форматы). В поле Тип (Type) вводим маску (шаблон) для нашего пользовательского формата:

Такой шаблон говорит Excel, что мы хотим выводить только целую часть числа и добавить к ней пробел и ШТ. Обратите внимание, что первый пробел здесь нужен для разделения тысяч-миллионов-миллиардов, а второй - чтобы число и текст не слиплись. И вот этот второй должен обязательно находиться внутри кавычек вместе с ШТ.

После применения такого стиля получим красивое представление для штук:

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

Стили для тысяч (К) и миллионов (М)

Ещё один пример весьма распространённой задачи - компактное представление для очень больших чисел. Вы наверняка видели варианты для тысяч с буквой "К" (1289 = 1,3К), для миллионов с буквой "М" (1 595 000 = 1,6М) или что-то похожее. Чтобы создать такие стили нам опять потребуется пользовательский формат, но с немного другим шаблоном:

Обратите внимание на пробел (без кавычек) после нуля - именно он, являясь разделителем разрядов, округляет наше исходное значение до тысяч. Если поставите два пробела - получите округление до миллионов, три - до миллиардов и т.д.

Если нужно показать после запятой один или несколько знаков (например 3,5K или 5,23М), то добавляем нули, как в прошлом примере:

Вместо международных "К" и "М" можно, само-собой, использовать и наши привычные " тыс.руб" и " млн.$" - кому как привычнее.

Стиль с цветом и процентами для отклонения от плана

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

Давайте создадим для последнего столбца особый стиль, который будет:

• Положительные значения выводить зелёным цветом шрифта, со знаком плюс перед значенем процентов.
• Отрицательные значения - красным цветом и с минусом, соответственно.

Создаём стиль как и ранее, а вот маска формата будет хитрая:

[Цвет10]+0% ; [Красный]-0% ;0%

Давайте разберём её подробнее:

• Маска формата здесь состоит из 3 фрагментов, разделенных точкой с запятой. Первая применяется, если число в ячейке больше нуля, вторая - если меньше нуля, третья - если число равно нулю.
• Отклонения от плана мы хотим указывать явно - с плюсом (перевыполнение) и минусом (невыполнение), поэтому знаки плюса и минуса явно прописываем в шаблоне.
  
• Перед маской в каждом блоке можно указать в квадратных скобках цвет шрифта - словом (красный, синий, зеленый, желтый, черный, белый) или с помощью кода (Цвет1, Цвет2 и т.д.) Всего поддерживается 56 цветов - выбирайте любой на ваш вкус:

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

Стили со значками и спецсимволами

Чтобы сделать ещё красивее, можно вместо плюса и минуса выводить символы стрелки вверх и вниз, соответственно. Для этого выделим любую пустую ячейку на листе и вставим туда наши значки, через команду Вставка - Символ (Insert - Symbol) :

Затем выделим вставленные символы в ячейке или в строке формул и скопируем их с помощью сочетания Ctrl + C .

Теперь, при создании стиля для отличий из предыдущего пункта, останется вставить с помощью Ctrl + V эти символы в нашу маску формата вместо плюса и минуса:

После применения такого стиля наше отклонение от плана станет ещё нагляднее:

Думаю, понятно, что такой трюк со вставкой спецсимволов позволит аналогичным образом создать стили для отображения любых нестандартных единиц измерения:

Стили со сложными условиями и эмодзи

Предположим, что у нас есть таблица с результатами экзаменов студентов или аттестации сотрудников:

Задача - показать рядом с каждым результатом его графическое представление в виде иконки (эмодзи) по следующей логике:

• больше или равно 80 баллов - отлично
• от 40 до 80 - хорошо
• меньше 40 - плохо
  

Создаем новый стиль, где в шаблоне числового формата задаём:

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

Эмодзи легко вставляются сочетанием клавиш Win + точка , если у вас Windows 10 со всеми обновлениями. Если же у вас более древняя операционная система, то придется их заранее вставить через Вставка - Символ из шрифтов типа Webdings, Windings и т.п. и скопировать, как мы делали в предыдущем примере.

Стиль для полей формы с защитой

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

Для этого при создании стиля убираем все флажки, кроме первого и, нажав на кнопку Формат (Format) зададим характерный дизайн (например, жёлтую заливку, курсив, выравнивание по центру) и на вкладке Защита (Protection) снимем галочку Защищаемая ячейка (Locked) .

Как лучше хранить стили

Все созданные пользовательские стили хранятся в текущей книге. Если вы хотите использовать их в других своих файлах, то есть три варианта:

• Копировать формат ячеек с помощью инструмента Формат по образцу ("кисточка" или Format Painter ).
  
• Подгрузить в рабочую книгу все стили из другой книги с помощью команды Главная - Стили - Объединить стили (Home - Styles - Merge styles)
  
  
• Использовать инструмент автоматической загрузки стилей из файла-библиотеки в надстройке PLEX последней версии.
  

Идеи для продолжения

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

• Стили с корпоративными цветами и шрифтами вашей компании или бренда.
• Стили с валютами, которых нет на Главной и которые долго ставить через окно Формат ячеек (например, тенге, сомони, сом и др. валюты союзных государств).
• Стили с маркерами для имитации маркированных списков в Excel как в Word.
• Стили для скрытия данных "белым на белом" или с помощью маски из трех подряд точек с запятой.
• Стили для отображения отрицательных чисел без минуса, но в скобках (как это принято в финансах для расходов).
• Стили для отображения исходных данных, вычислений и ячеек со ссылками в финансовых моделях.
  
• . и т.д.

Делитесь полезными стилями в комментариях - прогрессивное человечество и карма скажут вам "спасибо" :)

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

Метод A Изменение применимости к в диспетчере правил условного форматирования

Например, здесь вы хотите применить цвет фона заливки к ячейкам, если A2> B2, A3> B3,…, An> Bn с условным форматированием.

1. Во-первых, примените условное форматирование к A2: B2. Выберите A2: B2, затем щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна.

3. Нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно, а затем вы можете выбрать нужный тип форматирования. Например, заливка цветом фона. Нажмите OK > OK закрыть диалоги.

Теперь к строке A2: B2 применяется условное форматирование.

4. Не снимая выделения с A2: B2, нажмите Главная > Условное форматирование > Управление правилами.

5. в Диспетчер правил условного форматирования найдите правило, которое вы применили к A2: B2, измените диапазон, как вам нужно, в Относится к раздел и нажмите OK.

Затем это правило условного форматирования будет применяться к каждой строке в новом диапазоне.

Метод B Перетащите маркер автозаполнения

Если вы используете Excel 2013 или более поздние версии, вы можете использовать дескриптор автозаполнения, чтобы применить условное правило к соседним строкам.

Предположим, в строке A2: B2 применено два условного форматирования, если A2> B2, заливка красным цветом фона, если A2

Теперь вы хотите применить эти два правила к A3: B9.

1. Во-первых, вам нужно применить правила условного форматирования к строке A2: B2. Если в строке есть правила, просто перейдите к шагу 4. Выберите диапазон A2: B2, щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать, из Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите красный цвет. Нажмите OK > OK.

3. Не снимая выделения с строки A2: B2, включите Диалоговое окно "Новое правило форматирования" снова выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2 <$ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите зеленый цвет. Нажмите OK > OK.

4. Затем перетащите Автозаполнение дескриптор соседних строк, к которым вы хотите применить условное правило, затем выберите Только форматирование заливки из Параметры автозаполнения.

Файл примера

Другие операции (статьи), связанные с форматированием Conditioanl

Подсчет / суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.

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

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

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

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

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

Виды форматов ячеек

Для начала перечислим основные форматы ячеек, которые доступны в табличном редакторе:

• Общий;
• Числовой;
• Денежный;
• Финансовый;
• Дата;
• Время;
• Процентный;
• Дробный;
• Экспоненциальный;
• Текстовый;
• Дополнительный.

Примечание: для большинства из перечисленных форматов можно настроить вид отображения данных (например, “Дата”). Ненастраиваемыми являются форматы: Общий и Текстовый.

Давайте теперь перейдем, непосредственно, к методами изменения формата ячеек.

Метод 1: через контекстное меню

Данный метод является самым распространенным среди пользователей. Вот что мы делаем:

1. Щелкаем правой кнопкой мыши по ячейке, формат которой требуется задать/изменить, и в появившемся списке команд выбираем “Формат ячеек”.Если данное действие требуется выполнить для диапазона ячеек, сначала любым удобным способом выделяем его, после чего, также, правой кнопкой мыши кликаем по любому месту внутри выделенной области и выбираем нужный пункт.
2. На экране отобразится окно “Формат ячеек”. Автоматически должна быть выбрана вкладка “Число” (если по каким-то причинам этого не произошло, переключаемся в нее). В перечне слева определяемся с форматом, после чего справа задаем детальные параметры (если предлагаются). По готовности щелкаем OK.
3. В результате нам удалось сменить формат ячеек (в нашем случае – числовой, с разделителем разрядов, две цифры после запятой).

Метод 2: применение инструментов на лене

На ленту программы Excel разработчики вынесли самые популярные функции и команды, в т.ч., здесь есть кнопки для изменения формата ячеек.

Блок “Число”

Блок “Ячейки”

Метод 3: использование горячих клавиш

Комбинации клавиш позволяют пользователям быстрее выполнять популярные команды или запускать распространенные функции. Для смены формата ячеек также предусмотрено отдельное сочетание.

Примечание: Чаще всего, комбинации Ctrl+1 достаточно. Для тех, кто еще больше хочет ускорить работу, предусмотрены следующие горячие клавиши, позволяющие выбрать формат (основной вид) без открытия соответствующего окна:

• Ctrl+Shift+1 – числовой (с разделителем и двумя цифрами после запятой);
• Ctrl+Shift+4 – дата (ДД.ММ.ГГГГ);
• Ctrl+Shift+5 – процентный (без десятичных знаков);
• Ctrl+Shift+6 – время (Часы:Минуты).

Заключение

Таким образом, изменение формата ячеек в Эксель – процедура, которую можно выполнить разными способами. Каждый пользователь может выбрать для себя тот, который покажется наиболее удобным и быстрым.

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

  
• Эксель не печатает заливку ячеек
  
• 1с бит менеджер обмена данными
  
• Можно ли удалить файл с атрибутом только чтение
  
• Как сделать содержание в гугл ворде
  
• 1с договор тип взаимоотношений