Custom Number Formats in Excel and VBA

To totally unlock this section you need to Log-in

Login
In Excel, default format for a cell is "General".

When you first enter a number in a cell, Excel tries to guess an appropriate format, viz. if you type $5 in a cell, it will format the cell to Currency; if you type 5%, it will format to a percentage. However, many times you will need to select or add an appropriate format, especially if you want a customized display.

It is important to note that formatting a cell only effects its display and does not change or effect its actual or true value (which is used by Excel for calculations), for example: if you select a formatted cell, the formula bar will display the value which was typed before it was formatted (Note: in a percentage format, if you type 0.75 in a cell, it is displayed as 75% after applying the format).

In Excel, by default, text is left aligned and non-text (viz. numbers, percentage, date/time, ...) are right aligned, unless you specifically change this.

We show how to create Custom Number Formats: (i) in Excel Spreadsheet, in the Format Cells dialog box; and (ii) in VBA, using the NumberFormat property.

Excel Home Tab, Format Cells dialog box

On the Home Tab, Format menu, click Format Cells (or Cells) and select the Number tab; OR On the Home tab, click the Dialog Box Launcher Button image next to Number. After this, in the Category list, select Custom, and all built-in and custom number formats will be displayed. In the "Type" box, type the number format code.

The built-in number formats cannot be changed or deleted. You can type in to add a custom number format, which will get added at the bottom of the list. You can delete a custom number format by clicking on the Delete button. If you edit a custom number format, the original is retained and the new format also gets added to the list. It is therefore advisable to delete the custom number formats which are not required.

Custom number formats are specific to the workbook they are created in and will be saved and available only in that specific workbook. You can save the original workbook and use the custom number formats in additional workbooks.

Enter Format Code in Excel (Home Tab) Format Cells dialog box

Select the cells / range in your worksheet in which to apply the format, and then in the "Type" box type the number format code. Note that the number format code is not required to be enclosed in double quotation marks like in VBA. Type below format code:

 
_(#,###.00_);[Red](#,###.00);0.00

VBA, NumberFormat property

Use the NumberFormat property to set or return the number format for the specified object, in VBA.

You can apply the NumberFormat property to set the format code of a Range object, CellFormat object, PivotField object (only for a data field), DataLabel object, DataLabels object, and so on.

Syntax: expression.NumberFormat.

It is necessary to specify an expression, which returns an object (say, Range object) for which you want to set or return the format code. If the number format for all cells in the specified range is not the same, this property will return Null.

NumberFormat property sets the number format in VBA. Use this property to format numbers and how they appear in cells. You can define a number format by using the same format code strings as Microsoft Excel (in the Format Cells dialog box). The NumberFormat property uses different code strings than the Format function.

Examples of VBA Code, with NumberFormat property:

 
ActiveSheet.Columns("D").NumberFormat = "#,###.00;[Red](#,###.00);0.00"


ActiveSheet.Rows(2).NumberFormat = "hh:mm:ss.00"


ActiveSheet.Range("A5").NumberFormat = "General"

The three codes set the number format in the Active Worksheet, for Column D, Row 2 and Cell A5 respectively. Note that the number format code is required to be enclosed in double quotation marks unlike when you enter in Excel,Home Tab, Format Cells dialog box.

Number Codes

Zero (0)

Digit placeholder. If the number has lesser number of digits than zeros in the format code, the insignificant zeros are displayed.

This means that the minimum number of digits are determined by the position of zero at the extreme left before decimal and position of zero at extreme right after decimal, in the format code.

If the number has more digits than zeros to the left of the decimal point in the format code, the extra digits are displayed.

The number of zeros to the right of the decimal point in the format code, determine the round off digits. The "00" format code rounds off to the nearest digit preceding the decimal point.

# (Number Character)

Digit placeholder. This follows the same rules as 0 (zero), except that, if the number has lesser number of digits than "#" characters in the format code, the insignificant zeros are NOT displayed, even though it may be the only digit.

If the number has more digits than # characters to the left of the decimal point in the format code, the extra digits are displayed.

The number of # to the right of the decimal point in the format code, determine the round off digits. The "##" format code rounds off to the nearest digit preceding the decimal point.

? (Question Mark)

Digit placeholder. This follows the same rules as 0 (zero), except that, the character "?" leaves (ie. adds) a space for insignificant zeros either side of the decimal, while character zero displays "0" for them. It is particulalry useful to align decimal points in a column - you can align numbers decimally by adding spaces on either side of a number as required.

If the number has more digits than ? characters to the left of the decimal point in the format code, the extra digits are displayed.

The number of ? to the right of the decimal point in the format code, determine the round off digits. The "??" format code rounds off to the nearest digit preceding the decimal point.

. (Period)

Decimal point. The decimal point (ie. the "." character) in the format code determines the decimal place.

The number of Digit Placeholders to the right of the decimal point in the format code, determine the round off digits. The "00" or "##" or "??" format codes round off to the nearest digit preceding the decimal point.

, (Comma)

Comma is a thousand separator and number scaling sign.

Thousand separator: In the format code, placing comma between two digit placeholders and to the left of decimal, will act as a thousand separator.

Number Scaling: If comma is placed to the immediate left of the decimal point (in the format code), the number is divided by 1,000, n number of times wherein n is the number of characters ",".

The format string "0,," will scale down the number 100 million to 100 (divides 100 million by 1,000 * 1,000); and the format code "0,,," will divide the number by 1,000 * 1,000 * 1,000. Note that this number scaling will not apply the thousand separator, which will have to be done separately after the number scaling.

% (Percent Sign)

Percentage indicator. Numbers are displayed as a percentage of 100 with this. The percent sign in the format code multiplies the number by 100, before it is formatted.

The symbol "%" is displayed at the same position at which it is inserted in the format code.

/ (forward slash)

Fraction format. The forward slash displays number in a fraction format (ie. non-decimal format). The extent of accuracy of the fraction is determined by the number of digit placeholders on the right of the "/" character.

Text Codes

E+ E- e+ e-

Scientific notation (exponential format). "E+" and "E-" followed by atleast one "0" character (in format code), displays the number in scientific notation, with the character "E" mentioned between the number and the exponent.

The number of "0" characters determine the minimum number of exponent digits. "E+" indicates that the sign character (plus or minus) will always precede the exponent, whereas "E-" indicates that the sign character (minus) precedes only negative exponents.

The E stands for exponent. To avoid writing extremely long numbers, use scientific notation (SN), ie. a numeric value containing the letter E followed by a number. To convert a SN number say 5.0E+3 to the actual number, move the decimal position 3 positions to the right OR multiply the number to the left of E by 10 raised to the powerof 3 viz. the actual number is 5000.

If the exponent is negative, like in 5.25E-3, move the decimal position 3 positions to the left OR multiply the number to the left of E by 10 raised to the power of -3 viz. the actual number is -0.00525.

$ / + - ( ) space

Literal characters. These are displayed as literals (exactly as typed) as per their position in the format code.

Use these to display currency, to differentiate positive and negative numbers and for a more user-friendly display.

\ (Backslash)

Backslash. Any character appearing after backslash (\) will display as a literal, even though it may be reserved as an operator (say, %). The number 0.75 with the format code #.00% will format to 75.00%, but with the format code #.00\% it will format to .75%, ie. format code will not use % as operator but as a literal. To display several characters as literals enclose these in double quotation marks (" ") or in case of a single character, precede it with a backslash (\).

On typing any of the characters { } = <> : ! ^ & ' ~ backslash (\) is automatically inserted before the character and it gets displayed as a literal. Hence these characters, alongwith the literal characters mentioned above, are displayed exactly as typed without the use of quotation marks or backslash.

_ (Underscore)

Add spaces. An underscore followed by a character in the format code, creates a space which is equivalent to the width of that character. A usual format is an underscore followed by left or right paranthesis viz. _( _), which aligns the decimal points of positive numbers with negative numbers that are enclosed in paranthesis.

* (Asterisk)

Repeat characters. An asterisk followed by a character in the format code, will repeat that character to fill the column width. Only one asterisk can be included in a section.

Use asterisk to fill in dashes after a number, to fill in lead spaces before a number (which will right align it) or lead zeros before a number.

@ (AT Character)

Text placeholder. Insert the @ character in the text section (the last & fourth section), to display text which you type in a cell. If you want to always display a specified text in addition to the text you type in a cell, insert this 'always display text' within double quotation marks (" ") and insert the @ character in the text section viz. "Text"@.

Text will not be displayed if the "@" character is not inserted in this section.

Text Section Format, in both Excel Format Cells dialog box and VBA with NumberFormat property:

Text Section Format, in Excel Format Cells dialog box:

£ € ¥

Currency symbols. In VBA, use the Chr function to insert currency symbols though their ANSI / ASCII code. Note that $ can be entered as a literal character, as mentioned above.

Enter Currency symbols, in VBA with NumberFormat property:

Enter Currency symbols, in both Excel Format Cells dialog box and VBA with NumberFormat property: "£0.00", "€0.0", "#,### ¥", "£#,###":

Specify Font Color

Font color. Enter color name (viz. Red) or color index (viz. Color 3) in square brackets (in the format code) to determine font color. Enter color as the first item in the section. Supported colors are the first 8 colors in the palette: Black (Color 1), White (Color 2), Red (Color 3), Green (Color 4), Blue (Color 5), Yellow (Color 6), Magenta (Color 7), Cyan (Color 8).

Four Sections of Code

; (Semicolon)

Section separator. There can be up to four sections of code in a custom number format, wherein each section is separated by a semicolon. These sections determine the display of positive numbers, negative numbers, zero value and text, in that order.

If only one section is specified in the format code, it applies to all the four sections; if two sections are specified, the first applies to positive and zero values and the second section applies to negative numbers. If all four sections are specified, only then is the text affected by the fourth section.

You can also skip a section and specify code for the following or preceding section, but then you must enter the ending semicolon for the skipped section. Skipping a section(s) will result in a blank display for that section.

If all sections are skipped ie. only 3 semicolons are entered, will mean a blank display for all numbers & text. However, if the first 3 sections are skipped and the text section (fourth section) is entered with the @ character, will mean a text display for all numbers & text (ie. for all 4 sections).

Specify Conditions

= <> < = >= <>

Specify Conditions. You have an option to specify conditions subject to which a number format is to be applied. The condition should be enclosed in square brackets and it consists of a comparison operator and a value.

Comparison operators which can be used are: = <> < = >= <>. The first three sections in the number format are by default applied to positive, negative, and zero values. However you can specify up to two of your own conditions by using comparison operators.