Custom

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 you can see all the built-in and custom number format.
Custom number formats are workbook specific and are saved with your workbook.
Once you delete a custom number format any cells which had it applied, revert back to the "General" number format.


Formatting Codes - Numbers

SymbolDescription
0Digit placeholder - This symbol 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 - This symbol leaves a space for insignificant zeros to help with alignment. Represents a space on either side of a number. This symbol follows the same rules as those for the "0" symbol except that space is left for insiginificant zeros on either side of the decimal point. This placeholder aligns 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 - This symbol allows separating characters to be added. 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 starts 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).

Formatting Codes - Text

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.
$ - + / ( ) spaceStandard formatting characters which is displayed in your format.
\ characterThis 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. Represents a space that is the same width as the character that follows in the code string. 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. Fills the empty space by repeating a character that you specify. Repeats the next character to fill the column width. Only one asterisk per section is allowed.
@Text placeholder.

Positive; Negative; Zero; Text

Custom number formats can contain up to four sections separated by semicolons.
Positive Numbers - If there is only one section provided then this format is applied to all values (positive, negative and zero).
Negative Numbers - If two sections are provided then the first section is applied to all values (positive, negative and zero).
Zero Values - If three sections are provided then the first section is applied to all values (positive, negative and zero).
Text - If four sections are provided then the last section will override any text values with the format provided in the fourth section.



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.

#,###.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.


Adding Conditions

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

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

Condition1; Condition2; Zero; Text

Custom number formats can contain up to four sections separated by semicolons.
You can provide a different format code for each section.
The different sections are NOT Exclusive but are in fact taken from left to right.
Condition1 - (Defaults to Positive Numbers) If there is only one section provided then this format is applied to all values (positive, negative and zero).
"[Red]" will make everything the colour red, positive, negative and zero.
Condition2 - (Defaults to Negative Numbers) If two sections are provided then the first section is applied to all values (positive, negative and zero).
The second section will override any negative numbers with the format provided in the second section.
"[Red];[Blue]" will make everything the colour red, except for negative numbers which will be blue.
Zero Values - If three sections are provided then the first section is applied to all values (positive, negative and zero).
The second section will override any negative numbers with the format provided in the second section.
The third section will override any zero values with the format provided in the third section.
"[Red];[Blue];[Green]" will make everything the colour red, negative numbers will be blue, zero values will be green.
Text - If four sections are provided then the last section will override any text values with the format provided in the fourth section.


Creating New Formats

To create a custom number format you can either type it directly into the Type textbox or you can edit an existing format.
To create a custom format select the cells, and select the custom category.
Type in the appropriate codes to define the format you want.
You can use the "Delete" button to remove a custom number format. You cannot remove any of the built-in number formats.


Example 1 - Hiding Negative and Zeros

Worksheets can often display erroneous zero values particularly when formulas are referencing blank cells.
Number Format : 0.00;;

It is also possible to hide just zero values by using the (Options, Advanced)(Display - Worksheet group).


Example 2 - Currency Symbol on LHS

If you want to have your currency symbols appearing on the left of the cell rather than to the left of the value, just include an asterisk and a space after the currency symbol when entering the custom number format.
The asterisk means to repeat the character and in this case it's a space to fill the width of the column.
Number Format : £* #,##0.00;-£* #,##0.00


Example 3 - Align Decimal Points

If you want to align all the decimal points
Just make sure you include enough question marks on either side of the decimal place to accommodate all your digits.
Number Format : ??????.??????


Example 4 - Rounded to Whole Numbers

If you want to have your numbers rounded up to whole numbers.
Any positive numbers are rounded up.
Any negative numbers are rounded down.
Number Format : ###,#


Example 5 - Display 3 Decimal Places

If you want to have your numbers rounded to 3 decimal places
Number Format : #.000

If you want to have your numbers rounded to 3 decimal places with any insignificant zeros removed.
Number Format : #.???


Example 6 - Whole Number and Fraction

If you want to have the numbers displayed as a whole number and a corresponding fraction.
Number Format : # ???/???


Example 7 - Display in Thousands

To display numbers in thousands create a number format that ends with one comma.
Number Format : #,

To display numbers in millions create a number format that ends with two commas.
Number Format : #,,


Example 8 - Hide Everything

You can hide numerical data in cells with the null format.
Just apply a different format in order to view the values.
Number Format : ;;; (semi-colons)


Example 9 - Negative or Text As Blue Error

To display any negative numbers or text with blue Error text
Number Format : ?;[Blue]"Error";0;[Blue]"Error"


Example 10 - Hiding Zeros and Decimal Places

If you want to hide all zero values and any decimal places.
Number Format : 0;-0;;@


Example 11 - Text and Left Aligned

If you want to have all the numbers converted to text and left aligned.
This is the custom number format used when you choose the standard "text" number format.
Number Format : @


Example 12 - Text and Left Aligned with Padding

If you want to have all the numbers converted to text, left aligned with text values padded.
Number Format : @*>


Example 13 - Less Than 100 In Red

To display all numbers that are less than 100 in Red (including positive, negative and zero).
To display negative numbers with a minus sign.
But what about positive numbers that are greater than 100.
Positive numbers greater than 100 do not meet the condition in the first section so the custom format is not applied to these numbers. They are displayed using the default format which is black text.
There is no custom format specified for text values so these are also displayed using the default format which is black text.
I cannot explain the minus sign infront of numbers greater than 100 though ???
Number Format : [Red][<100]0;-0


Remove Percentage Symbols - (Ctrl + J)

If you want to link to a cell formatted as a percentage, but not display the percentage symbol.
SS - cell with 0.12, formatted as a percentage
linked to cell below
Enter 0, press (Ctrl + J) to insert a carriage return and then enter the % symbol on the line below.
Wrap the cell - Alignment tab, wrap text


Important

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.


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext