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 2010 will appear as numbers somewhere around 41,000.

To see the many ways that numbers can appear in Excel, have a look at the table below. The value 41315.375 was placed in each cell appearing on the left side of the table.

Value: 41315.375
Looks like:Formatted with:
2/10/13 12:00 AM m/d/yy h:mm AM/PM
Sunday, 02/10/2013 dddd, mm/dd/yyyy
Sunday dddd
Feb 10, 2013 mmm dd, yyyy
41,315 #,###
41,315.38 #,###.00
41.0 K #.0 , K
.04 M #.00,, \M
$41.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 41315.375 appearing as only "Sunday"!

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—41,315 becomes 41. 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 the Home tab on the Ribbon. In the Number section, click the Dropdown menu  and choose the last option: More Number Formats... (You can also use the shortcut key Ctrl-1)

In the textbox labeled Type (see below), 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".