Custom Formatting In Excel
When it comes to numeric values in Excel, what you see is definitely NOT what
you get. The actual values stored in Excel can be formatted in thousands of
different ways.
For instance, dates are stored as floating point numbers. The integer part of
the date is the number of days since
Jan 1, 1900 (actually, Jan 0th, but who’s
counting?) The decimal part is the amount of time since midnight. .00 is
midnight, .25 is 6 AM, .50 is noon, etc. This means that any date can be
formatted as a decimal number. Dates in 2004 will appear as numbers somewhere
around 38,000.
To see the many ways that numbers can appear in Excel, have a look at the
table below. The value 38027.375 was placed in each cell appearing on the left
side of the table.
|
Value: 38027.375 |
|
Looks like: |
Formatted With: |
|
2/10/04 9:00 AM |
m/d/yy h:mm AM/PM |
|
02/10/04 |
mm/dd/yy |
|
Tuesday, 02/10/2004 |
dddd, mm/dd/yyyy |
|
Tuesday |
dddd |
|
Feb 10, 2004 |
mmm dd, yyyy |
| |
|
|
38,027 |
#,### |
|
38,027.38 |
#,###.00 |
|
38.0K |
#.0,K |
|
.04M |
#.00,,\M |
|
$38.0 K |
$#,K |
Each cell was then formatted differently while leaving the value in the cell
untouched. The top half of the table shows a sampling of date formats. Some are
quite normal and show date or date and time. One is a bit strange and shows only
the day of the week. It’s hard to imagine 38,027.375 appearing as only “Tuesday”!
The bottom half of the table treats the initial value not as a date, but as a
standard number. The formats in the bottom right show how to affect the number
of decimals to display as well as how many numbers to the left of the decimal to
display. Note that a comma will remove three digits from a number—38,027
becomes 38. A “K” is placed at the end to denote thousands. Two commas will
replace 6 digits. In this case, a “\M” is used to denote millions as opposed
to just “M” which has already been used for months.
These “shortened” formats are especially useful in Excel charts to keep
the labels small. In the two identical charts below, you can see the labels with
standard format on the left. On the right is a chart with the same values
formatted “#.0,, \M”.
To create a custom format in Excel, go to Format > Cells. On the Number
tab choose the last option: Custom. In the textbox at the top right, you can
type in any of the formats contained on the right side of the table above.
For more information on custom formats, do a search in Excel Help for "number
format codes".