Excel’s custom number formatting is very important
yet probably the most underutilized feature.
Understanding and mastering this feature can greatly enhance the visual design
of your Excel workbooks. The key benefit
of custom number formatting is that it allows you to change the appearance of
your data without actually changing the data value behind the appearance. A
number format does not affect the actual cell value that Microsoft Excel uses
to perform calculations. The actual value is displayed in the formula bar,
which is the bar at the top of the Excel window that you use to enter or edit
values or formulas in cells or charts that displays the constant value or
formula stored in the active cell.
Now
for one who doesn’t have understand the custom number formatting will often go
through the process of changing their data values rather than just the
presentation of those values. For example, consider a user who wants to show 1,000,000 as 1.0 M. Instead of using
formatting, the user decides to divide his value by 1,000,000 and concatenates
an “M” to the end of it. In doing so, not only does he add an additional
step in his model, but such a step is also rarely documented, which can create
confusion if the work needs to be handed off to another person. Moreover, if he
or she has to use this number, the concatenated 1.0 M will not suffice since it
now contains a text formula. An even worse case if is the user decides to
remove the link between the display value and the actual value
altogether. He or she simply hardcodes the value by typing in 1.0 M. At that point, any
changes made to the original 1,000,000 value will not translate through to what
is displayed.
Number formats
that are available on the Number ribbon of the Home main tab. By
clicking on Number ribbon, a dialog box named Format Cells will open.
The formatting options are available if one clicks
on the Number section.
General
This is the default number format
that Excel applies when you type a number, which means it does not have any
special formatting rules. For the most part, numbers that are formatted with
the General format are displayed just the way you type them. However, if the
cell is not wide enough to show the entire number, the General format rounds
the numbers with decimals. The General number format also uses scientific
(exponential) notation for large numbers (12 or more digits). This means when you enter data in a cell, Excel
tries to guess what format it should have. When it doesn’t guess correctly, you
need to change the format.
Number
This format is used for the
general display of numbers. You can specify the number of decimal places that
you want to use, whether you want to use a thousand separator (such as 1,234
instead of only 1234) and how you want to display negative numbers.
Currency
This format is used for
general monetary values and displays the default currency symbol with numbers.
You can specify the number of decimal places that you want to use, whether you
want to use a thousand separator, and how you want to display negative numbers.
Accounting
This format is also used for
monetary values, but it aligns the currency symbols and decimal points of
numbers in a column.
Date
This format displays date and time
serial numbers as date values, according to the type and locale (location) that
you specify. Except for items that have an asterisk (*) in the Type list
(Number tab, Format Cells dialog box), date formats that you apply do not
switch date orders with the operating system.
Time
This format displays date and time
serial numbers as time values, according to the type and locale (location) that
you specify. Except for items that have an asterisk (*) in the Type list
(Number tab, Format Cells dialog box), time formats that you apply do not
switch time orders with the operating system.
Percentage
This format multiplies the cell
value by 100 and displays the result with a percent symbol (%). You can specify
the number of decimal places that you want to use.
Fraction
This format display a number as a
fraction, according to the type of fraction that you specify.
Scientific
This format displays a number in
exponential notation, replacing part of the number with E+n, where E (which
stands for Exponent) multiplies the preceding number by 10 to the nth power.
For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10,
which is 1.23 times 10 to the 10th power. You can specify the number of decimal
places that you want to use.
Text
This format treats the content of
a cell as text and displays the content exactly as you type it, even when
numbers are typed.
Special
This format displays a number as a
postal code (ZIP Code), phone number, or Social Security number.
Custom
This format allows you to modify a
copy of an existing number format code. This creates a custom number format
that is added to the list of number format codes. You can add between 200 and 250
custom number formats, depending on the language version of Excel that you have
installed.
