Applying number formatting changes the appearance of values contained in cells. Excel
provides a variety of number formatting options. In the following sections, you will see
how to use many of Excel’s formatting options to improve the appearance and readability of
your worksheets quickly.
Values that you enter into cells normally are unformatted. In other words, they simply
consist of a string of numerals. Typically, you want to format the numbers so that they’re
easier to read or are more consistent in terms of the number of decimal places shown.
The 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.
You can download the workbook used in this post from here – click here to download
Using automatic number formatting –
Excel is able to 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 commas to separate thousands (such as 123,456), Excel applies
comma formatting for you. And if you precede your value with a dollar sign, the cell is for-
matted for currency (assuming that the dollar sign is your system currency symbol).
Anything you enter that can possibly be construed as a date will be treated as such. And
depending on how you enter it, Excel will choose a date format to match. If you enter
1/31/2020, Excel will interpret that as a date and format the cell as 1/31/2020 (just as it
was entered). If you enter Jan 31, 2020, Excel will the format it as 31-Jan-20 (if you omit
the comma, Excel won’t recognize it as a date). The less obvious example of entering 1-31
causes Excel to display 31-Jan. If you need to enter 1-31 in a cell and it’s not supposed to
be a date, type an apostrophe (‘) first.
Formatting numbers by using the Ribbon –
The Home ➪ Number group in the Ribbon contains controls that let you quickly apply com-
mon number formats.
The Number Format drop-down list contains 11 common number formats (see Below Figure ).
Additional options in the Home ➪ Number group include an Accounting Number Format
drop-down list (to select a currency format), 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.
When you select one of these controls, the active cell takes on the specified number format.
You also can select a range of cells (or even entire rows or columns) before clicking these
buttons. If you select more than one cell, Excel applies the number format to all of the
selected cells.
Using shortcut keys to format numbers –
Another way to apply number 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 located together, in the
lower left of your keyboard.
Formatting numbers by using the Format Cells dialog box –
In most cases, the number formats that are accessible from the Number group on the Home
tab are just fine. Sometimes, however, you want more control over how your values appear.
Excel offers a great deal of control over number formats through the use of the Format Cells
dialog box, as shown in Figure below. For formatting numbers, you need to use the Number
tab.
You can bring up the Format Cells dialog box in several ways. Start by selecting the cell or
cells that you want to format and then do one of the following:
- 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 Ctrl+1.
The Number tab of the Format Cells dialog box displays 12 categories of number formats.
When you select a category from the list box, the right side of the tab changes to display
options appropriate to that category.
The Number category has three options that you can control: the number of decimal places
displayed, whether to use a thousands separator, and how you want negative numbers
displayed. The Negative Numbers list box has four choices (two of which display negative
values in red), and the choices change depending on the number of decimal places and
whether you choose to separate thousands.
The top of the tab displays a sample of how the active cell will appear with the selected
number format (visible only if a cell with a value is selected). After you make your choices,
click OK to apply the number format to all of 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 specify how to display negative numbers (with a minus sign, in red, in parentheses, 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 nine 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 number of decimal places to display to the left of E.
The second example can be read as “2.05 times 10 to the fifth.”
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 U.S. 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 aren’t included in any other
category.
Adding your own custom number formats –
Sometimes you may want to display numerical values in a format that isn’t included in
any of the other categories. If so, the answer is to create your own custom format. Basic
custom number formats contain four sections separated by semicolons. Those four sections
determine how a number will be formatted if it is a positive value, negative value, a zero, or
text.