Tips Excel : Create Custom Numeric Formats

You are not limited to predefined numeric formats in Excel; you can create a custom cell format that displays information just how you want it. The predefined numeric formats give you a lot of control over how your numbers are displayed, but they have their limitations. For example, no built-in format enables you to display a number such as 0.5 without the leading zero, or to display temperatures using, say, the degree symbol. To overcome these and other limitations, you need to create your own custom numeric formats.

You can create custom numeric formats either by editing an existing format or by creating your own format. The formatting syntax and symbols are explained in the Tip. Every Excel numeric format, whether built-in or customized, has the following syntax: positive;negative;zero;text The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how text is displayed.

How To Custom Numeric Format 

1. Select the range you want to format.
2. Click the Home tab.
3. In the Number group, click the dialog box launcher icon. 

The Format Cells dialogbox appears with theNumber tab selected.
● If you want to base your custom format on an existing format, you can click the category and then click the format.
4. Click Custom.

5. Type the symbols and text that define your custom format.
6. Click OK.

Excel applies the custom format.
 

Customize It!

Use the symbols listed in the following table to build your custom numeric formats.
Symbols for Custom Numeric Formats
# 
Holds a place for a digit and displays the digit exactly as typed. Excel displays nothing if no number is entered.
0 
Holds a place for a digit and displays the digit exactly as typed. Excel displays 0 if no number is entered.
? 
Holds a place for a digit and displays the digit exactly as typed. Excel displays a space if no number is entered.
. (period)
 Sets the location of the decimal point.
, (comma)
Sets the location of the thousands separator. Excel marks only the location of the first thousand.
/ (forward slash)
 Sets the location of the fraction separator.
% 
Multiplies the number by 100 (for display only) and adds the percent (%) character.

Categories: Excel 2010