1-866-245-5224 sales@keystonelearning.com

Excel 2003: Number Formats

Excel 2003: Number Formats

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.

               

Placeholder

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)

0

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

m

Short Month (February = 2)

mm

Short Month with Leading Zero (February = 02)

mmm

Short Alphabetic Month (February = Feb)

mmmm

Long Alphabetic Month (February = February)

d

Short Day (First Day of the Month = 1)

dd

Short Day with Leading Zero (First Day of Month = 01)

ddd

Short Alphabetic Day (Monday = Mon)

dddd

Long Alphabetic Day (Monday = Monday)

yy

Two Digit Year (2003 = 07)

yyyy

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

     
  1. Click Format on the Menu bar
     
  2.  
  3. Choose Cells…
     
  4.  
  5. On the Number tab, under Category, click Custom
     
  6.  
  7. Erase the text located below Type and enter your own format.
     
  8.  
  9. Click OK