Most of the data you’re analyzing in Excel is probably numbers. And, formatting those numbers properly is extremely easy when using the Formatting toolbar.
Right at your fingertips, you have access to Currency, Percentage, and Comma formats as well as the ability to quickly increase or decrease the number of displayed decimals.
But, to use more advanced number formats, you’ll need to access the Format Cells Dialog box, specifically the Number tab.
Figure 14. Format Cells Dialog Box
To view the Format Cells Dialog box, simply choose Format: Cells… from the Menu bar..
Custom Number Formats
If none of the default number formats suit your number formatting needs, you can create your own custom number formats.
Custom number formats are easy once you understand how a number format is laid out. Each number format consists of a series of symbols separated by semicolons (;). A number format can up to four sections, which each section defined as follows:
1st Section: Positive Numbers
2nd Section: Negative Numbers
3rd Section: Zero Values
- 4th Section: Text
Now that you know what each section of a number format represents, all you need to know next is what each placeholder represents. For that, see the following table.
What it does…
Displays significant digits. So, when you see #, that tells you that any number with a decimal will display with zero decimal places (2.5 = 3)
Displays insignificant digits. So, when you see 0, that tells you the number of decimal places that will be displayed (#.00 = 2.50)
Displays a decimal point
Displays a comma at the thousand mark in numbers
Short Month (February = 2)
Short Month with Leading Zero (February = 02)
Short Alphabetic Month (February = Feb)
Long Alphabetic Month (February = February)
Short Day (First Day of the Month = 1)
Short Day with Leading Zero (First Day of Month = 01)
Short Alphabetic Day (Monday = Mon)
Long Alphabetic Day (Monday = Monday)
Two Digit Year (2003 = 07)
Four Digit Year (2003 = 2003)
Displays any text within the quotes as text
Let’s dissect some custom number formats.
SAMPLE #1: $#,##0.00_);[Red]($#,##0.00);_($* “-“_)
The number format above consists of three sections.
The first section tells Excel to display positive numbers as currency with a comma (,) separator at the thousand place with two decimals.
The second section tells Excel to display negative numbers the same way it displays positive numbers but in Red and surrounded by parenthesis.
The third section tells Excel to display a cell with a blank value with a currency symbol and a hyphen (Like this: “$ _ ”).
SAMPLE #2: dd-mmmm-yyyy
The number format above consists of only one section.
The first section tells Excel to display all entries as a short day, hyphen, long alphabetic month, hyphen, and four-digit year. So, entries in a cell with this format would display as: 08-September-2003.
Creating a Custom Number Format
Click Format on the Menu bar
On the Number tab, under Category, click Custom
Erase the text located below Type and enter your own format.