Note: In Format Cells dialog you can modify the built-in format codes by selecting the format you want to modify in its own category (i.e. Currency > ($1,234.10)) and then selecting Custom Category. Don’t worry, Excel will not let you delete built-in formats. Show BasicsThe format code has 4 sections separated by semicolons. POSITIVE; NEGATIVE; ZERO; TEXT These sections are optional,
Default format type in Excel is called General. You can type General for sections you don’t want formatted. Make sure you use a minus sign (-) with General if you want to skip negative values. If you want to completely hide a type, leave it blank after the semicolon. For example; to hide 0 values, General;-General;;General Placeholders and the Cheat Sheet
Common PracticesDecimal places in the code are indicated with a period (.). Number of zeroes after the period (.) define the number of decimal places. For example,
If the number has more decimals than the decimal placeholders defined, the number will be rounded to the nearest number of placeholders.
Alternatively, hash (#) and question mark (?) symbols can be used as decimal places. However, because any missing decimal places will be filled with zeroes, using zeroes instead will be easier to read.
Add text to numbersCustom text can be added to the beginning or the end of a value. Text and characters should be added inside quotes ("") and backslashes (\). You can use backslash (\) to add single character.
Quotation marks or backslashes are not necessary for spaces ( ) and some special characters.
Below are some special characters you can use by copying or typing in the numerical code while pressing down Alt button.
Hide valueIf you leave any number of sections blank, the value of those sections will be hidden. A section should always be separated (defined) by a semicolon (;). Here are some examples,
Replace zeroes with dashesZeroes can make data tables look more complicated than they actually are. You can hide them completely by using the previous method, or replace them with any character of your choice. Dash (-) is a common example. All you need to is place a dash into the ‘Zero section’.
Start with zeroesIf try to enter a ZIP number that starts with 0, the leading zeroes will be removed automatically by Excel. To keep the leading zeros, use zero (0) placeholder for whole numbers.
Dealing with thousands, millions, and moreYou may have noticed that ‘0.0’ or other simple formats do not separate thousands or millions. Adding a comma into the code will insert commas to separate numbers.
There must be placeholders for numbers smaller than one thousand, otherwise such values will be hidden. This behavior allows us to round and format our value to show only thousands or millions.
Display numbers as phone numbersPhone numbers can be hard to read without any separators. Custom Number Format Codes is perfect for this job. The hash (#) character should be your best bet to avoid any redundancy of placeholders (0, ?)
Showing Month and Weekday NamesDate and time values are stored as numbers in Excel. When you enter a date, Excel automatically converts it into a numerical value, and then formats the cell. Before jumping into the code, let’s review some basics. Formatting code has special placeholders for date and time formatting that behave a bit differently. For example, while m and mm will show month as a number, mmm and mmmm will show as a text string. Below are some examples.
Here is the full list of options for the date 4/1/2018 23:59:31 ,
Come in Colors EverywhereNumber Formatting can be used color sections of a code. A common example is using the color red for negative numbers. Color code must be placed inside square brackets (i.e. [color]), and entered at the beginning of a section. Here are some available colors,
ConditionsAlthough Excel has conditional formatting menu, basic conditions can be applied through code. Condition should be placed inside square brackets (i.e. [condition]) just like colors. Conditions are similar to the conditions in some functions (i.e. SUMIF). First add a logical operator, and then a value. For example, “[>=1000000]” means “if value of cell is greater than or equal to 1,000,000 apply the following format”. Conditions should come before the actual code, again, just like with colors. If you want to a color as well, the color code should come first. Another important thing to note here is, that section structure changes from Positive, Negative, Zero, Text to First Condition, Second Condition (if exists), if previous conditions are not applied. There should be at least two sections for conditions. If you enter only one condition code and then save the format, Excel will automatically add the second section with ";General". This means that if the condition is not met, General format will be used.
|