Custom - Examples

Example 1 - Hiding Zero Values

Number Format : 0;-0;;@
It is also possible to hide just zero values by using the (Options, Advanced)(Display - Worksheet group).

Example 2 - Hiding Zero Values and Negative

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

Example 3 - Hiding Zero Values and Decimal Places

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

Example 4 - 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 5 - 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 6 - 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 7 - Display 3 Decimal Places

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

Example 8 - Display 3 Decimal Places without Insignificant Zeros

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

Example 9 - Whole Number and Fraction

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

Example 10 - 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 11 - 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 12 - 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 13 - 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 14 - 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 15 - Less Than 100 In Red

To display all numbers that are less than 100 in Red (including positive, negative and zero).
Number Format : [Red][<100]0;0
Positive numbers greater than 100 do not meet the condition in the first section.
There is another section so everything else has this format applied to it.
Including the positive numbers that are greater than 100, rows 151 and 152.
There is no custom format specified for text values so these are displayed using the default format which is black text.