Microclimate.su

IT Новости
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel format cells custom

Microsoft Excel Training- From Beginner to Expert in 6 Hours / EXCEL DASHBOARD REPORTS

| | Information Helpful? Why Not Donate.

Got any Excel Questions? Free Excel Help

It is very important to understand how cell formats are seen by Excel. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section.

It is also very important to understand that the formatting of a cells value does not effect its underlying true value. To show this we can type any number into cell A1, then go to Format>Cells-Number-Custom and, using any format as a Starting point, type in «Hello», with the quotation marks, now click OK. Now, while the cell displays the word Hello, it’s true value can be seen by selecting the cell and looking in the Formula bar, or by pressing F2. If you were to reference this cell in a formula, e.g. =A1+20 the result cell would also take on the custom format. If we were to reference cell A1 and many other cells that have any standard Excel format, e.g. =SUM(A1:A10) our result cell would still take on the custom format of cell A1. This is an educated guess by Excel that you want the result cell formatted the same as the referenced cell(s). If the referenced cells contain more than one type of format, any custom format will take precedence. So, you must always remember that Excel uses a cells true value and not it’s displayed value. This can catch out the unwary if you are calculating cells that are formatted for no, or few, decimal places. For example, enter 1.4 in A1 and 1.4 in A2, format both these cells to show zero decimal places and then place =A1+A2 and the result is 3. Excel does have an option called Precision as displayed, found under Tools>Options-Calculation, but you should be aware that this option will permanently change stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. In other words, once it’s been checked and given the OK, there is no turning back.

The default format for any cell is «General«. If we enter a number into a cell, Excel will often guess the number format that is most appropriate. For example, if you type in 10%, Excel will format the cell as a percentage. Most often Excel gets this correct, but sometimes we need to change this. One point I will make on this is, when using Format>Cells, resist the temptation of forcing a left, right or centre horizontal format! By default numbers are right aligned and text left aligned. If you leave this alone, you can tell at a glance whether a cell is text or numeric, as in the case of my earlier example where cell A1 appears to hold text, when in fact, it holds a number.

Ok, getting back to the all important Sections that a formatted cell contains. Within these Sections we are able to use Formatting Codes. It is these codes that force Excel to make our data appear how we would like. Let’s use a simple Example. Suppose you would like any negative number to appear inside parenthesis, and all numbers, positive, negative or zero, to show two decimal places. The Custom Format we could use is: 0.00_ ;(-0.00). If you also wanted negatives to be red, use: 0.00_ ;[Red](-0.00) Note the use of the square brackets in the Section for negative numbers. This is the Formatting Code that tells Excel to make the number red.

There are many different Formatting Codes that can be used within Sections of a Custom Format. The tables below show these. The Table is

Formatting Codes

Number CodeDescription
GeneralGeneral number format.
0 (zero)Digit placeholder. This code pads the value with zeros to fill the format.
#Digit placeholder. This code does not display extra zeros.
?Digit placeholder. This code leaves a space for insignificant zeros but does not display them.
. (period)Decimal number.
%Percentage. Microsoft Excel multiplies by 100 and adds the % character.
, (comma)Thousands separator. A comma followed by a placeholder scales the number by a thousand.
E+ E- e+ e-Scientific notation.
Text CodeDescription
$ — + / ( ) : spaceThese characters are displayed in the number. To display any other character, enclose the character in quotation marks or precede it with a backslash.
characterThis code displays the character you specify.

, <, >, =, automatically places a backslash in front of the character.

«text»This code displays text.
*This code repeats the next character in the format to fill the column width.

Note Only one asterisk per section of a format is allowed.

_ (underscore)This code skips the width of the next character. This code is commonly used as «_)» (without the quotation marks) to leave space for a closing parenthesis in a positive number format when the negative number format includes parentheses. This allows the values to line up at the decimal point.
@Text placeholder.
Date CodeDescription
mMonth as a number without leading zeros (1-12)
mmMonth as a number with leading zeros (01-12)
mmmMonth as an abbreviation (Jan — Dec)
mmmmUnabbreviated Month (January — December)
dDay without leading zeros (1-31)
ddDay with leading zeros (01-31)
dddWeek day as an abbreviation (Sun — Sat)
ddddUnabbreviated week day (Sunday — Saturday)
yyYear as a two-digit number (for example, 96)
yyyyYear as a four-digit number (for example, 1996)
Time CodeDescription
hHours as a number without leading zeros (0-23)
hhHours as a number with leading zeros (00-23)
mMinutes as a number without leading zeros (0-59)
mmMinutes as a number with leading zeros (00-59)
sSeconds as a number without leading zeros (0-59)
ssSeconds as a number with leading zeros (00-59)
AM/PM am/pmTime based on the twelve-hour clock
Miscellaneous CodeDescription
[BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n]These codes display the characters in the specified colors.

Note n is a value from 1 to 56 and refers to the nth color in the color palette.

[Condition value]Condition may be , =, >=, and value may be any number.

Note A number format may contain up to two conditions.

If you do a lot of Custom Formatting you might find it useful to print these tables.

As you are no doubt aware, you can use custom formats to change the way Excel displays Text, Numbers, Dates and Times. What you may not realize is that by changing a cell or cells formatting you are NOT changing it’s underlying value. Even if you are counting drugs for a pharmacy or selling generics, you can use this function. So even if you use the custom format option in Excel to force Excel to display the number 20 as «Twenty» you can still use the cell in a calculation.

Below are just some of the custom formats that can be applied to cells in Excel. For all examples you must select the cell or cells and go to Format>Cells>Number>Custom. Then use any one of the predefined formats as a Starting point.

Dates
For all these examples I will use the date: 25/12/2001

To display as:Use the format:
251201dmy
Tues-12-2001ddd-m-yyyy
Tuesday 25-12-2001dddd d-m-yyyy
Tuesday December 25 2001dddd mmmm d yyyy
Christmas Day«Christmas Day»

The format dddd can also be used on a date to quickly find out the weekday of a specific date.

Times
For all these examples I will use the time 18:30:30

To display as:Use the format:
1830hhmm
1830 hrshhmm «hrs»
18 hours and 30 minuteshh «hours and» mm «minutes»
6:30pmh:mm AM/PM

If you are working with times and you want Excel to display hours greater than 24, use the custom format [h]:mm:ss

If you want to display the amount of Minutes that have passed since midnight, use the format [m]. The same applies for Seconds ie; use [s]

There is not an awful lot more you can do with currency that Excel doesn’t allow with it’s built in formats. But there is one and that is display the amount as dollars and cents. You can easily display $75.89 as:
75 dollars and 89 cents. To do this use the custom format: 0 «dollars and» .00 «cents»

As with currency there is not an awful lot more you can do with text, but here is one tip. Let’s say you want a list of text entries but do not want any blank space after the text. That is you want the entire cell filled. To do this use the custom format: @*. This will fill all the space after the last letter of a word with period (fullstop) so your list will look like:

Dog.
Mouse.
Elephant.

There are many other types of custom formats you can use.

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site . Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours / EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Six Things Excel Custom Number Formatting can Do for You

Excel Custom Number formatting is the clothing for data in excel cells. You can dress these the way you want. All you need is a bit of know-how of how Excel Custom Number Format works.

Excel Custom Number Format Construct

Before I show you the awesomeness of it, let me briefly explain its construct.

All the formats in excel are divided into four parts:

– Here goes the format for positive numbers.

  • – Here goes the format for negative numbers.
  • – Here goes the format for 0.

    – Here goes the format for Text.

    Note that all these are separated by semi-colons (;).

    Anything that you enter in a cell in excel would fall in either of these four categories and hence a custom format for it can be specified.

    How this construct works

    You can specify a format for any or all of these four parts.

    For example, if you write General;General;General;General then everything would be displayed in the General format.

    But if you write 0.00;-0.00;0.00;General , positive numbers are displayed with 2 decimals, negative with a negative sign and 2 decimals, zero as 0.00 and text as normal text string.

    Similarly, you can specify the format for any of the four parts.

    If you mention only:

    • One format : It is applied to all the four sections.
      • For example, if you just write General , it will be applied for all the four sections.
    • Two formats : First one is applied to positive numbers and zeros, and the second one is applied to negative numbers. Text format by default becomes General.
    • Three Formats : First one is applied to positive numbers, the second one is applied to negative numbers, third is applied to zero, and text disappears as nothing is specified for text.

    If you want to learn more about custom number formatting, I would highly recommend Office Help section.

    In this blog, I will show you 6 amazing things you can achieve using Excel Custom Number Format.

    #1 – Display Negative Numbers in Red Color

    1. Select the data set where you want to highlight the negatives numbers in red
    2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
    3. Select Custom number format category
    4. In the Type field write

    If you want the negative numbers in red color, without the negative sign (-) use the following:

    #2 – Add text to Numbers (such as Millions/Billions)

    1. Select the data set.
    2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1).
    3. Select Custom number format category.
    4. In the Type field write:

    Note that this custom number format will only affect numbers. If you enter text in the same cell, it would appear as it is..

    #3 – Disguise Numbers and Text

    1. Select the data set
    2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
    3. Select Custom number format category
    4. In the Type field write

    To display 0 as False and 1 as True


    To display marks less than 35 as Fail and more than 35 as Pass

    Again, these look like text, but these are in fact numbers, and can be used in calculations or formulas.

    #4 – Hide text but display Numbers

    This could be useful when you import data and there are NA or Not Available in some cells, instead of numbers. You can use this trick to quickly hide all the text while the numbers get displayed.

    1. Select the data set
    2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
    3. Select Custom number format category
    4. In the Type field write

    Note that I have used General format. You can any format you wish (such as 0, 0.#, #0.0%). Also, note that there is a negative sign (-) before the second General format, as it represents the format for negative numbers.

    This works as we have specified the format for the positive numbers, negative numbers, and zero only (and not for Text). So the text is not displayed.

    If you wish to hide all type of contents (numbers and text) from a cell, use the following excel custom number format:

    #5 – Display Numbers as Percentages (%)

    1. Select the data set
    2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
    3. Select Custom number format category
    4. In the Type field write

    This will change the numbers to their percentages. For example, it changes 0.11 to 11%.

    TIP: In case you want to keep 0.11 as 0.11%, use 0% instead.

    #6 – Display Numbers in a Different Unit (Millions as Billions or Grams as Kilograms)

    1. Select the data set
    2. Open Format Cells dialogue box (Key Board Shortcut – Control + 1)
    3. Select Custom number format category
    4. In the Type field write

    If you want to display 2 decimal points, use 0.00,, instead.

    These Excel custom number format tricks can be used in your daily work. If you use anything else, do share it with,e by leaving a comment.

    You May Also Like the Following Excel Tutorials:

    Custom Number Format

    Excel has many built-in formats that you can use: Currency, Accounting, Date, Time, Percentage, etc. If you cannot find the right format, you can create a custom number format.

    Leading Zeros

    For example, you might have codes that consist of 5 numbers. Instead of typing 00041, simply type 41 and let Excel add the leading zeros.

    1. Enter the value 41 into cell A1.

    2. Select cell A1, right click, and then click Format Cells.

    3. Select Custom.

    4. Type the following number format code: 00000

    Note: Excel gives you a life preview of how the number will be formatted (under Sample).

    Note: cell A1 still contains the number 41. We only changed the appearance of this number, not the number itself.

    Decimal Places

    You can also control the number of decimal places. Use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places, etc.

    1. Enter the value 839.1274 into cell A1.

    2. Use the following number format code: 0.00

    Add Text

    You can also add text to your numbers. For example, add «ft».

    1. Enter the value 839.1274 into cell A1.

    2. Use the following number format code: 0.0 «ft»

    Note: remember, we only changed the appearance of this number, not the number itself. You can still use this number in your calculations.

    Large Numbers

    You can also control large numbers. Use one comma (,) to display thousands and use two commas (,,) to display millions.

    1. Enter the following values in cells A1, B1, C1 and D1: 1000000, 2500000, 81000000 and 700000.

    2. Use the following number format code: 0.0,, «M»

    Note: we used 0.0 for one decimal place and «M» to add the letter M.

    Repeat Characters

    Use the asterisk (*) followed with a character to fill a cell with that character.

    1. Type Hi into cell A1.

    2. Use the following number format code: @ *-

    Note: the @ symbol is used to get the text input.

    Colors

    You can control positive numbers, negative numbers, zero values and text all at the same time! Each part is separated with a semicolon (;) in your number format code.

    1. Enter the following values in cells A1, B1, C1 and A2: 5000000, 0, Hi and -5.89.

    2. Use the following number format code: [Green]$#,##0_);[Red]$(#,##0);»zero»;[Blue]»Text:» @

    Note: #,## is used to add comma’s to large numbers. To add a space, use the underscore «_» followed by a character. The length of the space will be the length of this character. In our example, we added a parentheses «)». As a result, the positive number lines up correctly with the negative number enclosed in parentheses. Use two parts separated with a semicolon (;) to control positive and negative numbers only. Use three parts separated with a semicolon (;) to control positive numbers, negative numbers and zero values only.

    Dates and Times

    You can also control dates and times. Use one of the existing Date or Time formats as a starting point.

    1. Enter the value 42855 into cell A1.

    2. Select cell A1, right click, and then click Format Cells.

    3. Select Date and select the Long Date.

    Note: Excel gives you a life preview of how the number will be formatted (under Sample).

    4. Select Custom.

    5. Slightly change the number format code to: mm/dd/yyyy, dddd

    General note: a custom number format is stored in the workbook where you create it. If you copy a value with a custom number format to another workbook, it will also be available in that workbook.

    Применение пользовательских форматов

    Создание пользовательских форматов

    Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.

    Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

    В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.

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

    [цвет]»любой текст»КодФормата»любой текст»

    Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)

    Какие цвета можно применять

    В квадратных скобках можно указывать один из 8 цветов на выбор:

    Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

    Далее рассмотрим коды форматов в зависимости от типа данных.

    Числовые форматы

    СимволОписание примененияПример форматаДо форматированияПосле форматирования
    #Символ числа. Незначащие нули в начале или конце число не отображаются######0012341234
    Символ числа. Обязательное отображение незначащих нулей0000001234001234
    ,Используется в качестве разделителя целой и дробной части####,#1234,121234,1
    пробелИспользуется в качестве разделителя разрядов# ###,#01234,11 234,10

    Форматы даты

    ФорматОписание примененияПример отображения
    МОтображает числовое значение месяцаот 1 до 12
    ММОтображает числовое значение месяца в формате 00от 01 до 12
    МММОтображает сокращенное до 3-х букв значение месяцаот Янв до Дек
    ММММПолное наименование месяцаЯнварь — Декабрь
    МММММОтображает первую букву месяцаот Я до Д
    ДВыводит число датыот 1 до 31
    ДДВыводит число в формате 00от 01 до 31
    ДДДВыводит день неделиот Пн до Вс
    ДДДДВыводит название недели целикомПонедельник — Пятница
    ГГВыводит последние 2 цифры годаот 00 до 99
    ГГГГВыводит год даты полностью1900 — 9999

    Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.

    Форматы времени

    Аналогичные форматы есть и для времени.

    ФорматОписание примененияПример отображения
    чОтображает часыот 0 до 23
    ччОтображает часы в формате 00от 00 до 23
    мОтображает минутыот 0 до 59
    ммМинуты в формате 00от 00 до 59
    сСекундыот 0 до 59
    ссСекунды в формате 00от 00 до 59
    [ч]Формат истекшего времени в часахнапример, [ч]:мм -> 30:15
    [мм]Формат истекшего времени в минутахнапример, [мм]:сс -> 65:20
    [сс]Формат истекшего времени в секундах
    AM/PMДля вывода времени в 12-ти часовом форматенапример, Ч AM/PM -> 3 PM
    A/PДля вывода времени в 12-ти часовом форматенапример, чч:мм AM/PM -> 03:26 P
    чч:мм:сс.00Для вывода времени с долями секунд

    Текстовые форматы

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

    ДО форматированияПОСЛЕ форматированияПримененный формат
    Россиястрана — Россия«страна — «@

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

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

    1. Положительные числа
    2. Отрицательные числа
    3. Нулевые значения
    4. Текстовый формат

    Для этого мы можем в поле Тип указать следующую конструкцию:

    Формат положительных значений ; отрицательных ; нулевых ; текстовых

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

    [цвет]»любой текст»КодФормата»любой текст»

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

    Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».

    Редактирование и копирование пользовательских форматов

    Чтобы отредактировать созданный пользовательский формат необходимо:

    1. Выделить ячейки, формат которых вы хотите отредактировать.
    2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
    3. Изменить строку форматирования в поле Тип.

    Распространить созданный пользовательский формат на другие ячейки можно следующими способами:

    1. Использовать функцию копирования по образцу.
    2. Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.

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

    Читать еще:  Структурированная ссылка в excel
  • Ссылка на основную публикацию
    Adblock
    detector