BET: Microsoft Excel Formatting Numbers - Custom Number Formats

Microsoft Office Development and Consultancy

|

Excel

|

|

|

|

|

|

|

Excel > Formatting Numbers > Custom Number Formats

< Previous | Next >

Step 1 - What is a Custom Number Format ?

	A custom number format allows you to change and control how your numbers, dates and text are displayed.
	When you select "Custom" from the category list all the built-in and custom number formats are displayed on the right hand side.
	Custom number formats are workbook specific and are saved with your workbook.
	Custom number or date formats are only available in that particular workbook. You must explicitly add them to any other workbooks.
	This list offers a variety of number, date and time formats and all your custom number formats will appear at the bottom of this list.

Step 2 - Positive numbers, Negative numbers, Zero Value, Text

	Custom number formats can contain up to four sections and you can specify format codes to describe how you want to display your numbers, dates, times and text.
	The sections are separated by semicolons.
	The four sections are positive values, negative values, zero values and text.
	If you submit one section then this is applied to all four sections.
	If you submit two sections the first is applied to positive and zero and the second to negative.
	For each section of the number format enter the symbols to represent how you want the value to look.
	The following is an example of a custom number format.

1	#,###.00_);(#,###.00);0.00;"The department is"@

Step 3 - Important Rules

		If you have not defined a format for negative values, then the positive format is used.
		If you have not defined a format for zero values, then the positive format is used.
		The text is only affected if you have defined all four sections.

Step 4 - Formatting Codes

Number Codes

Symbol	Description
0	Digit placeholder - pads the number with zeros. This symbol ensures that a specified number of digits appears on each side of the decimal point. For example, if the format is 0.000, the value .987 would be displayed as 0.987. If the format is 0.0000, the value .987 would be displayed as 0.9870. If a number has more digits to the right of the decimal point than the number of 0s specified in the format, the number in the cell is rounded. For example if the format is 0.00, the value .987 would be displayed as 0.99.
?	Digit placeholder - leaves a space for insignificant zeros but does not display them. This symbol follows the same rules as those for the "0" symbol except that space is left for insiginificant zeros on ether side of the decimal point. This placeholder align numbers on the decimal places. For example 0.34 and 12.45 would line up on the decimal point if both were formatted with 0.??
#	Digit placeholder - does not display any extra zeros. This symbol works like 0 except that extra zeros do not appear if the number has fewer digits on either side of the decimal point than #s specified in the format. This symbol shows Excel where to display commas or other separarting symbols. The format #,### for example displays a comma after every third digit to the left of the decimal place.
.	Decimal point placeholder. This symbol determines how many digits (0 or #) appear to the right or left of the decimal point. If the format contains only #s to the left of this symbol, Excel begins numbers smaller than 1 with a decimal point. To avoid this, use 0 as the first digit placeholder to the left of the decimal point instead of #. If you want Excel to include commas and display at least one digit to the left of the decimal point in all cases specify the format as #,##0
%	Percentage Indicator - value is multiplied by 100 and a percentage sign is added to the end.
/	Fraction format character. This symbol displays the fractional part of a number in a nondecimal format. The number of digit placeholders that surround this character determines the accuracy of the display. For example, the decimal fraction 0.269 when formatted as #?/? is displayed as 1/4 but when formatted with #???/??? is displayed as 46/171
, (comma)	Thousand separator - A comma followed by a placeholder scales the number by 1000. If the format contains a comma surrounded by #s, os or ?s Excel uses commas to separate hundreds from thousands, thousands from millions etc. In addition the comma acts as a rounding and scaling agent. Use one comma at the end of a format to tell Excel to round a number and display is in thousands; two commas tell Excel to round to the nearest million. For example the format #,###,###, rounds 4567890 to 4,568 (thousands) and the format #,###,###,, rounds the same number to 5 (millions).

Text Codes

E+ E- e+ e-	Scientific format character. If a format contains one 0 or # to the right of an E-, E+, e- or e+, Excel displays the number in scientific notation and insert E or e in the displayed number. The number of 0s or #s to the right of the E or e determines the minimum number of digits in the exponent. Use E- or e- to place a negative sign by negative exponents and use E+ or e+ to place a positive sign by positive exponents.
$ - + / ( ) space	Standard formatting characters which is displayed in your format.
\ character	This displays the specific character. Typing ! ^ & ` ~ { } = < > automatically places a backslash in front of the character. Precede each character you want to display in the cell (except for : $ - + / ( ) and space) with a backslash. Excel will not display the backslash. The backslash can be used insert single characters. To insert several characters use the quotation mark "".
_ (underscore)	This code skips the width of the next character. This code is commonly used as "_)" (without the quotation marks) top leave space for a closing parenthesis in a positive number format when a negative number format includes parentheses. This allows the values to line up at the decimal point.
"Text"	Literal character string - Displays the text string within the quotation marks.
*	Repetition initiator - This is used to pad the cell with the character immediately after it. Repeats the next character to fill the column width. Only one asterisk per section is allowed.
@	Text placeholder.

Step 5 - Adding Colours

	It is possible to automatically change the colour of the text (or value) depending on the characters (or values) that are in the cells.
	You can display different values in different colours. You could have all the values greater or less than a specified value appear in a different colour.
	This allows you to emphasis particular values in the worksheet.

2	#,###.00_);[Red](#,###.00);0.00;[Green]"The department is"@

	Note that any colours that you specify with a number format will take precedence over any manual formatting applied.
	Each of the four categories (positive, negative, zero, text) can have its own colour associated with it.

	To define a colour place it in square brackets at the start of the section.
	You can use colour to help validate whether the data entered is correct.
	It is not possible to display different parts of the text in different colours using this method.

Step 6 - Using a Condition

	It is possible for a number format to also contain a condition.
	These conditions can contain one of the comparison operators ( < > = <= >= <> ).

3
4
5

[Condition <10]
[<3000][RED]0;0
[<20][RED]0;[>40][GREEN]0

Step 7 - Things to Remember

		Using a lot of custom number formats in a single workbook will use up a lot of memory.
		Every time you edit a custom number format it will be added to the list. You should delete any custom number formats that you are not using.
		It is not possible to display different parts of the text in different colours using this method.

© Better Solutions Limited 10-May-2013

< Previous | Top | Next >