## Custom Format Code in Excel - Part I

Posted by
Sokhom
Labels:
Format

### Text and spacing

Displaying both text and numbersTo display both text and numbers in a cell, enclose the text characters in double quotation marks (

The following characters are displayed without the use of quotation marks:

Including a section for text entry**" "**) or precede a single character with a backslash (**\**). Include the characters in the appropriate section of the format codes. For example, type the format**$0.00" Surplus";$-0.00" Shortage"**to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage."The following characters are displayed without the use of quotation marks:

$ | Dollar sign | - | Negative sign | |||
---|---|---|---|---|---|---|

+ | Plus sign | / | Solidus (slash) | |||

( | Left parenthesis | ) | Right parenthesis | |||

: | Colon | ! | Exclamation mark | |||

^ | Circumflex accent (caret) | & | Ampersand | |||

' | Apostrophe | ~ | Tilde | |||

{ | Left curly bracket | } | Right curly bracket | |||

< | Less-than sign | > | Greater-than sign | |||

= | Equals sign | Space character |

If included, a text section is always the last section in the number format. Include an at sign (

If the format does not include a text section, text you enter is not affected by the format.

Adding spaces**@**) in the section where you want to display any text entered in the cell. If the @ character is omitted from the text section, text you enter will not be displayed. If you want to always display specific text characters with the entered text, enclose the additional text in double quotation marks (**" "**). For example,**"gross receipts for "@**If the format does not include a text section, text you enter is not affected by the format.

To create a space the width of a character in a number format, include an underscore, followed by the character. For example, when you follow an underscore with a right parenthesis, such as

Repeating characters**_)**, positive numbers line up correctly with negative numbers that are enclosed in parentheses.To repeat the next character in the format to fill the column width, include an asterisk (

*****) in the number format. For example, type**0*-**to include enough dashes after a number to fill the cell, or type***0**before any format to include leading zeros.### Decimal places, spaces, colors, and conditions

Including decimal places and significant digitsTo format fractions or numbers with decimal points, include the following digit placeholders in a section. If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (

Displaying a thousands separator**#**) to the left of the decimal point, numbers less than one begin with a decimal point.**#**(number sign) displays only significant digits and does not display insignificant zeros.**0**(zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.**?**(question mark) adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font, such as Courier New. You can also use ? for fractions that have varying numbers of digits.

To display | As | Use this code |
---|---|---|

1234.59 | 1234.6 | ####.# |

8.9 | 8.900 | #.000 |

.631 | 0.6 | 0.# |

12 1234.568 | 12.0 1234.57 | #.0# |

44.398 102.65 2.8 | 44.398 102.65 2.8 (with aligned decimals) | ???.??? |

5.25 5.3 | 5 1/4 5 3/10 (with aligned fractions) | # ???/??? |

To display a comma as a thousands separator or to scale a number by a multiple of one thousand, include a comma in the number format.

Specifying colorsTo display | As | Use this code |
---|---|---|

12000 | 12,000 | #,### |

12000 | 12 | #, |

12200000 | 12.2 | 0.0,, |

To set the color for a section of the format, type the name of one of the following eight colors in square brackets in the section. The color code must be the first item in the section.

Specifying conditions[Black] | [Blue] | [Cyan] | ||||
---|---|---|---|---|---|---|

[Green] | [Magenta] | [Red] | ||||

[White] | [Yellow] |

To set number formats that will be applied only if a number meets a condition you specify, enclose the condition in square brackets. The condition consists of a comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) and a value. For example, the following format displays numbers less than or equal to 100 in a red font and numbers greater than 100 in a blue font.

[Red][<=100];[Blue][>100]

To apply conditional formats (conditional format: A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.) to cells — for example, color shading that depends on the value of a cell — use the

[Red][<=100];[Blue][>100]

To apply conditional formats (conditional format: A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.) to cells — for example, color shading that depends on the value of a cell — use the

**Conditional Formatting**command on the**Format**menu.### Currency, percentages, and scientific notation

Including currency symbolsTo enter one of the following currency symbols in a number format, turn on NUM LOCK and use the numeric keypad to enter the ANSI code for the symbol.

Displaying percentagesTo enter | Press this code |
---|---|

¢ | ALT+0162 |

£ | ALT+0163 |

¥ | ALT+0165 |

ALT+0128 |

**Note**Custom formats are saved with the workbook. To have Microsoft Excel always use a specific currency symbol, change the currency symbol selected in**Regional Settings**in Control Panel before you start Excel. For information on how to change regional settings, see Change the default country/region.To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (

Displaying scientific notations**%**) in the number format.To display numbers in scientific format, use exponent codes in a section — for example,

If a format contains a zero (

**E-**,**E+**,**e-**, or**e+**.If a format contains a zero (

**0**) or number sign (**#**) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. "E-" or "e-" places a minus sign by negative exponents. "E+" or "e+" places a minus sign by negative exponents and a plus sign by positive exponents.### Dates and times

Displaying days, months, and yearsTo display | As | Use this code |
---|---|---|

Months | 1–12 | m |

Months | 01–12 | mm |

Months | Jan–Dec | mmm |

Months | January–December | mmmm |

Months | J–D | mmmmm |

Days | 1–31 | d |

Days | 01–31 | dd |

Days | Sun–Sat | ddd |

Days | Sunday–Saturday | dddd |

Years | 00–99 | yy |

Years | 1900–9999 | yyyy |

**Month versus minutes**If you use the "m" or "mm" code immediately after the "h" or "hh" code (for hours), or immediately before the "ss" code (for seconds), Excel displays minutes instead of the month.

To display | As | Use this code |
---|---|---|

Hours | 0–23 | H |

Hours | 00–23 | hh |

Minutes | 0–59 | m |

Minutes | 00–59 | mm |

Seconds | 0–59 | s |

Seconds | 00–59 | ss |

Time | 4 AM | h AM/PM |

Time | 4:36 PM | h:mm AM/PM |

Time | 4:36:03 P | h:mm:ss A/P |

Time | 4:36:03.75 | h:mm:ss.00 |

Elapsed time (hours and minutes) | 1:02 | [h]:mm |

Elapsed time (minutes and seconds) | 62:16 | [mm]:ss |

Elapsed time (seconds and hundredths) | 3735.80 | [ss].00 |

**Minutes versus month**The "m" or "mm" code must appear immediately after the "h" or "hh" code or immediately before the "ss" code; otherwise, Microsoft Excel displays the month instead of minutes.

**AM and PM**If the format contains an AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock.

Subscribe to:
Post Comments (Atom)

Post a Comment