All about number formatting in excel

Number formatting in excel.

The appearance of numbers in the cells can be changed by changing the formatting of numbers. There are many number formatting options available in excel. In the following sections you see how to use many number formatting in excel to quickly improve the appearance of your worksheets.
When you enter the numbers in cells it is unformatted, it simply consists of string of numbers. You can format number to make it easier to read with consistent terms and common decimal places with uniform font and size.
Below figure shows a worksheet that has two columns of values. The first column consists of unformatted values. The cells in the second column are formatted to make the values easier to read. The third column describes the type of formatting applied.
number formatting in excel

Auto number formatting in excel:

Excel is a smart software which will perform some formatting for you automatically. For example, if you enter 12.2 % into a cell, Excel knows that you want to use a percentage format and applies it for you automatically. If you use 1 oct 2016, Excel applies date formatting for you. And if you precede your value with a Dollar sign, the cell is formatted for currency (assuming that the Dollar sign is your system currency symbol).

Number formatting in excel by using ribbon:

Under Home tab, number group contains controls that let you quickly do common number formats.
number formatting in excel
The number format drop-down-list  contains 11 common number formats.  Additional options include an accounting number format drop -down list  a percent style button, and a comma style button. The group also contains a button to increase the number of decimal places, and another to decrease the number of decimal places.

number formatting in excel

When you select  one of those controls, the active cell takes on the specified number format. You also can select a range of cells before clicking these buttons. If you select more than one cell, Excel applies the number format to all the selected cells.

The following are the number format categories, along with some general  comments:

General : The default format; it displays numbers as  integers,  as decimals, or in scientific notation if the value is too wide to fit in the cell.
Number : Enables you to specify the number of decimal places, whether to use a comma to separate thousands, and how to display negative numbers (with a minus sign, in red, in parentheses, or in Red and in parentheses).
Currency : Enables you  to specify the number of decimal places, choose a currency  symbol, and how to display negative numbers (with a minus sign,  in red, in parenthese,  or in  red and  in parentheses). This format always uses a comma to  separate thousands.
Accounting : Differs from  the currency format in that the currency symbols always align vertically.
Date : Enables  you to choose from several different date formats.
Time :  Enables you to choose from several different time formats.
Percentage: Enables you  to choose the number of decimal places and always displays a percent sign.
Fraction: Enables you to choose from among  9 fraction formats.
Scientific : Displays numbers in  exponential notation  (with an E): 2.00E+05=200,000; 2.05E+05=205,000. You can choose the numbers of decimal places to display to the left of E. The second example can be read as “2.05 Times  10 to the 5th.
Text : When applied to a value, causes Excel to treat the value as text (even if it looks like a number). This feature is useful for such items as part numbers and credit card numbers.
Special : Contains additional number formats. In the US version of Excel, the additional number formats are ZIP code, zip code + 4,  phone number, and social security number.
Custom:  Enables you to define custom number formats that are not included in any other category.

Shortcut keys for number formatting in excel:

Another way to apply numbers formatting is to use shortcut keys. Below table summarizes the shortcut key  combinations that you can use to apply common number  formatting to the selected cells or Range. Notice that these Ctrl+ Shift characters are all located together, in the upper left of your keyboard.
Number formatting keyboard shortcuts:
Ctrl+Shift+`   General number format (that is, unformatted values)
Ctrl+Shift+4   Currency format with two decimal places (negative numbers appear in parentheses)
Ctrl+Shift+5   Percentage format, with no decimal places
Ctrl+Shift+ 6   Scientific notation number format, with two decimal places
Ctrl+Shift+3   Date format with the day, month, and year
Ctrl+Shift+2   Time format with the hour, minute, and AM or PM
Ctrl+Shift+1  Two decimal places, thousands separator, and a hyphen for negative values.

 Entering numbers with fractions:

To enter a fractional value into a cell, leave a space between the whole number and fraction. For example, to enter 6 7/ 8, enter 6 7/8 and then press enter. When you select the cell, 6.875  appears in the formula bar, and cell entry appears as a fraction.  If you have a fraction only (for example, 1/ 8), you must enter a zero first, like this  01/ 8  or excel will likely assume that you are entering a date.  When you select the cell and look at the formula bar, you see 0. 125. In the cell, you see 1/ 8.

Number formatting in excel using the format cells dialog box:

The number formatting can be accessed from the number group on the Home tab. But if you want more control over how your values appear, you can get more number formats through the use of  the format  cells dialog box,  shown in below figure. For formatting numbers, you need to use the number tab in the dialog box.
You can bring up the format cells dialog box in many ways. Select the cell or cells that you want to format and then follow the below mentioned methods:
Choose Home- Number and click the small dialog box launcher icon ( in the lower -right corner of the Number Group).
Choose Home -number, click the number format drop-down list, and choose more number formats from the drop-down list.
Right- click the cell and choose format cells from the shortcut menu.
Press control + 1 (Shortcut to open dialog box for formatting)
The number tab of the format cells dialog box displays 12 categories of number formats. Under each of the category from the list box, there are different options which will be displayed in right side of the tab.
The number category has three options that you can control: the number of decimal places displayed, whether to use a thousand separator, and how you want negative numbers displayed. Notice that the negative numbers list box has four choices and the choices change depending on the number of decimal places and whether you choose to separate thousands.
number formatting in excel
The top of the tab displays  a sample of how the active cell will appear  when you selected a particular number format . After you select a particular format, click OK to apply the number format to all the selected cells.

Leave a Comment